[SS2K5] : Dynamically construct a FROM statement with current SCHEMA

Hi,

I've got a DB with some schemas.

There's same tables under these schemas as for example :

MySchema1.MyTable1

MySchema2.MyTable1

I wrote CRUD stored procedures for these tables.

I'd like to dynamically create the FROM statement inside the stored procedures by getting the schema name from the current context.

Kind of :

FROM SCHEMA_NAME().MyTable1

So I'll have one generic stored procedure for MyTable1.

This stored proc will be under the DB level and will not have to be replicate under all the schemas.

Is it possible ?

Thanks for any help.

BERTRANDR

Sure, sp_executesql is such a utility to run dynamic sql. Basically you construct a SQL string(NVARCHAR) with parameters first, then use sp_executesql to execute it. Look at Book Online for details.|||Why do you want to use a generic SP? Using dynamic SQL has lot of issues - complexity, performance (it depends on the case), security risks, permissions management, debugging pain, ownership chaining issues in your case etc. You should create a SP in each schema that handles the modifications for each table. This is much more robust, simpler to manage, debug, code etc.|||

Well my point is that I don t want to duplicate objects that have the same purposes and do exactly the same things for the same table but under differents schemas.

I thought it was the best thing to do .

But maybe am I wrong ..

[SS2K5] : Dynamically construct a FROM statement with current SCHEMA

Hi,

I've got a DB with some schemas.

There's same tables under these schemas as for example :

MySchema1.MyTable1

MySchema2.MyTable1

I wrote CRUD stored procedures for these tables.

I'd like to dynamically create the FROM statement inside the stored procedures by getting the schema name from the current context.

Kind of :

FROM SCHEMA_NAME().MyTable1

So I'll have one generic stored procedure for MyTable1.

This stored proc will be under the DB level and will not have to be replicate under all the schemas.

Is it possible ?

Thanks for any help.

BERTRANDR

Sure, sp_executesql is such a utility to run dynamic sql. Basically you construct a SQL string(NVARCHAR) with parameters first, then use sp_executesql to execute it. Look at Book Online for details.|||Why do you want to use a generic SP? Using dynamic SQL has lot of issues - complexity, performance (it depends on the case), security risks, permissions management, debugging pain, ownership chaining issues in your case etc. You should create a SP in each schema that handles the modifications for each table. This is much more robust, simpler to manage, debug, code etc.|||

Well my point is that I don t want to duplicate objects that have the same purposes and do exactly the same things for the same table but under differents schemas.

I thought it was the best thing to do .

But maybe am I wrong ..

[Cummulative] does not work

-- Take all year but current year

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[Year],

Ancestor([Time].[FiscalYear].CurrentMember, [Time].[FiscalYear].Year).PrevMember

)

, [Measures].[PTD Actual])

+

-- Take current YTD

SUM(Ytd([Time].[FiscalYear].CurrentMember), [Measures].[PTD Actual])

-

Above MDX is trying get a Cummulative number up to current time period. The time.FiscalYear was defined with Y, Q and M levels. I tried with an (All) level and w/o (All) level. But PeriodsToDate just does not go beyond pervious year to get all the Periods (here all the periods are all the years up to last year). So when I look at Y2007 cummulative number, I only see it adds up all the number in Y2006 and whatever periods up current in Y2007. Anything before 2006 was not included.

Is there a correct way to do cummulative actual (add up all the previous years plus all the period up to CurrentMember in current year)?

Try this:

SUM({NULL:[Time].[FiscalYear].CurrentMember}, [Measures].[PTD Actual])

|||

Hi, Deepak:

Could you explain a bit what the purpose of NULL in the function?

Thanks!

Julius

|||

Sorry, Deepak.

I am on AS 2000. It gives me syntax error on NULL.

Could you help?

Thanks again!

Julius

|||

The "NULL:" won't work in AS 2000, so (assuming that there is an "All" level) you could try:

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[All]

)

, [Measures].[PTD Actual])

|||

Deepak:

Interesting that AS2000 took this MDX.

But it seems the set is not the calc member is meant to be. By using [All] w/o defining a CurrentMember, it seems return the ENTIRE time dimension which includes the future period.

If we have 2003, 2004, 2005, 2006, 2007, 2008 as Yearl level members, how do I ask MDX to give the "Cumulative up to date" figure up to 2006 (including 2003, 2004, 2005, 2006) w/o anything from 2007 and 2008? I think (All) will just return entire dimension member set, right? Or in other words, how do I tell MDX that I only want to PeriodsToDate up to my current Year level member (or Previous Year level member, so I can add YTD accurately for current year) when using (All)?

I am confused.

Thanks much!

Julius

|||Depends on how you define your current date - if it's based on the latest date with data in the cube, then previous year (2006) could be computed like Tail(NonEmptyCrossJoin([Time].[Year].Members)).Item(0).PrevMember. Without explicitly selecting a CurrentMember, the DefaultMember will be used instead. So you could set the Time dimension's DefaultMember to the above expression, so that the year 2006 is selected by default.

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together
.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? Ho
w
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert
a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
in
> each set MUST be kept together, and sets in each batch MUST be kept togeth
er.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updatin
g
> won't be a problem, but inserting will be) before I start inserting rows?
How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So giv
e
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonagh
Looking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh
|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David
|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

@@servername NULL

I am running SQL 7 Standard on a W2K server, both at the most current
service packs. After applying the lastest security update to Windows
and rebooting the server, SQL has suddenly fogottern its name - SELECT
@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
various maintenance plan jobs seem to hang and never complete. This is
wreaking all sorts of havoc with my backups since none of them complete
successfully.
Will the sp_dropserver/sp_addserver combination fix the NULL problem?
Is there anything else I need to do?
Thanks,
M. E. HoustonYes...the dropserver, addserver should clean it up.
If you query the system table sysserver, you will probably
find that there isn't a server with the srvid = 0. The 0 is
used for the local server. If there is an entry for the
local server and srvid is not 0, you can remove it with
sp_dropserver. Uisng sp_addserver with 'local' adds the
server with the srvid = 0. If you don't specifiy 'local',
it's not added with the 0 for srvid.
-Sue
On Tue, 30 Sep 2003 11:19:09 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>I am running SQL 7 Standard on a W2K server, both at the most current
>service packs. After applying the lastest security update to Windows
>and rebooting the server, SQL has suddenly fogottern its name - SELECT
>@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
>various maintenance plan jobs seem to hang and never complete. This is
>wreaking all sorts of havoc with my backups since none of them complete
>successfully.
>Will the sp_dropserver/sp_addserver combination fix the NULL problem?
>Is there anything else I need to do?
>Thanks,
>M. E. Houston|||This solved my server name problem, but I'm still having
issues with jobs created by maintenance plans hanging. In
order to make them stop, I have to kill the process. When
I do that, the following message gets written to the log --
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv()).
[Microsoft][ODBC SQL Server Driver][Shared Memory]General
network error. Check your network documentation.
-- Anyone have any clue as to what this really means?
Thanks,
Margit
>--Original Message--
>Yes...the dropserver, addserver should clean it up.
>If you query the system table sysserver, you will probably
>find that there isn't a server with the srvid = 0. The 0
is
>used for the local server. If there is an entry for the
>local server and srvid is not 0, you can remove it with
>sp_dropserver. Uisng sp_addserver with 'local' adds the
>server with the srvid = 0. If you don't specifiy 'local',
>it's not added with the 0 for srvid.
>-Sue|||Just curious based on the first issue and not sure if it's
the issue or not but did you by any chance rename the server
after the jobs were created? Do you have any problems
changing, modifying jobs?
-Sue
On Thu, 2 Oct 2003 13:22:09 -0700, "M. E. Houston"
<margit.houston@.no_spam.gsiusa.com> wrote:
>This solved my server name problem, but I'm still having
>issues with jobs created by maintenance plans hanging. In
>order to make them stop, I have to kill the process. When
>I do that, the following message gets written to the log --
>[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
>[Microsoft][ODBC SQL Server Driver][Shared Memory]
>ConnectionRead (recv()).
>[Microsoft][ODBC SQL Server Driver][Shared Memory]General
>network error. Check your network documentation.
>-- Anyone have any clue as to what this really means?
>Thanks,
>Margit
>
>>--Original Message--
>>Yes...the dropserver, addserver should clean it up.
>>If you query the system table sysserver, you will probably
>>find that there isn't a server with the srvid = 0. The 0
>is
>>used for the local server. If there is an entry for the
>>local server and srvid is not 0, you can remove it with
>>sp_dropserver. Uisng sp_addserver with 'local' adds the
>>server with the srvid = 0. If you don't specifiy 'local',
>>it's not added with the 0 for srvid.
>>-Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Can't tell you for sure why the jobs randomly hang - I've
seen jobs hang when they attempt to send mail and there are
problems somewhere in the mail configuration. You could run
profiler or a trace to try to track down what specifically
is going on when the jobs hang.
-Sue
On Wed, 08 Oct 2003 08:53:32 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>Sorry for the delay in reply.
>I did not have any problems making changes to any of the
>jobs/maintenance plans either before or after I renamed the server. I
>thought the problem might have been a naming issue so I deleted all the
>maintenance plans and all the jobs I had created and started over. The
>new jobs under the correct server name still exhibit the same bad
>behaviour. There doesn't seem to be any consistency as to where in the
>job stream things fall apart, i.e., which database in the backup job it
>hangs on. On some occassions, the jobs even complete as expected.
>Thanks,
>Margit
>Sue Hoegemeier wrote:
>> Just curious based on the first issue and not sure if it's
>> the issue or not but did you by any chance rename the server
>> after the jobs were created? Do you have any problems
>> changing, modifying jobs?
>> -Sue