[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 ..

[scripts] prompting user

please, how to promt user in mu sql 2005 script? For example, I need user to
confirm some operations.. .

Thx in advance"fireball" <fireball@.onet.kropka.euwrote in message
news:emdkdr$7ro$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need user
to confirm some operations.. .
>

Unless something has changed in T-SQL 2005 that I'm not aware of, you can't.

Quote:

Originally Posted by

Thx in advance
>

|||that is cruel.|||so, I neither I can't call one script from another (from the batch I run),
right?

:(|||"fireball" <fireball@.onet.kropka.euwrote in message
news:eme57k$mnn$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

so, I neither I can't call one script from another (from the batch I run),
right?

No, you can do that.

Quote:

Originally Posted by

>
:(
>

|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need
user to confirm some operations.. .

The answer is you don't. SQL Server is a server application, and
server applications communicate with client applications, not with
end users.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sql is 4gl and asking it to do 3gl operations like prompts and loops
and if then else logic should be illegal although now allowed.

fireball wrote:

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need user to
confirm some operations.. .
>
Thx in advance

|||well,
is that possible at least, to exit script on given condition?|||fireball wrote:

Quote:

Originally Posted by

well,
is that possible at least, to exit script on given condition?

Do you want a return code or not? If you don't want a return code
then you can wirite:
If <conditionReturn
If you want a return code then you need to use a stored proc and inside
the procedure you can write:
If <conditionreturn 100
In your application you can check for status of the called procedure
and take action based on that.|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

is that possible at least, to exit script on given condition?

Depends on how run it. In SQLCMD or OSQL you can exit immediately
by using a RAISERROR with state 127:

RAISERROR('Got a good reson for taking the easy way out now', 16, 127)

This does not work from Query Analyzer or Mgmt Studio.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

RAISERROR('Got a good reson for taking the easy way out now', 16, 127)

Uzytkownik <othellomy@.yahoo.comnapisal w wiadomosci

Quote:

Originally Posted by

If <conditionReturn

all this hepled.

[Reports builder] operator * in a prompt list

Hi all, when i create a reports deploy on the Report server, i usually choose a filter prompted on Run. The lsit containt for example the list of my customers, but the list in empty like i want (like that, user can put a list from excel by copy-paste). But ... , if the want all the customers, what is the " * " operator to select all my records ?

Thanks for help

Erwan, France

Do you mean

In a drop down for a report parameter you want <all> to appear?

[HELP]passwords

for example i will set the password as chicken

http://img515.imageshack.us/my.php?image=chickenqs6.gif

then i press ok and it seems like it works i open it right away and

http://img295.imageshack.us/my.php?image=notchickenao0.gif

^^clearly not chicken......

then,ontop of that when i open the login name is a query script its some 15 character password that everytime u open in script the code changes..

http://img401.imageshack.us/my.php?image=screenhunter04jan062317zu0.gif

any help would be greatly appreciated

this is the normal behaviour. u can not see or know the password once it is been set. What u get while scripting is only a arbitary value. This is for security purpose. By just counting the number of '*' you can not say that it is not Chicken. There is nothing to worry....

Madhu

|||is there a way i can make it set the password i want without encrypting it because i need that password to stay(example chicken) the same if not i cant use it

[help] SSIS File Configuration (XML)

We are deploying our SSIS packages into different folders. For example:
\Test1
\test2
\production

Test1 points to the Test1 database, etc.

So, I configured my SSIS package to use a database connection called dbMAIN.

I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C:\test1\, the same place as my ssis.dtsx file.

I want to copy my dtsx file and my dtsconfig file into \test2 and \production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!

How do I dynamically change it?

I tried to hardcode the location to ".\Global.dtsconfig", but that did not work! Please advise!I found the solution. When you call an executable, you can use the /ConfigFile option. Example:

dtexec.exe /file "d:\myFile.dtsx" /configfile "d:\test1\Test1.dtsconfig"

When myfile.dtsx is run, it will use the test1.dtsconfig file to get all of its configurations.

Hooah!

~Le

[DTS Transformation]

Hello I trying to do some transformation using javascript. I have a table with 40 columns that I need dynamicaly mapped to another table. For example I need to test each Column of tableA if Col1 of TableA looks like an email address map it to TableB email if it looks like a zip map it to Email...

I need to do this with Javascript

if someone could point me in the right direction that would be great

lets say I start small test each col in TableA for an @. sign if I find it map to Email if I can get the idea of how to do that I can do this

thanksok I pretty much got it to work by adding JScript to the ActiveX Transformation Properties. By using pattern matching I was able to get the the data to insert into the correct fields. However one of the problems I am having is when I come across a field with a NULL value. Is there a way to test for NULL. I tried if(v != NULL) but the JScript in the DTS does not seam to know what NULL means?

Thanks for the help

[?] DataSets execution order

Hello,
How to set the fixed order of DataSets execution? For example, I need
DataSet2 to be started only when DataSet1 is finished.
Thanks,
Lucy.If you mark the data source to "Use single transaction", the queries will
have to execute in series (unless you happen to have a custom data provider
that supports transactions spanning multiple connections, which would allow
us to execute them in parallel... but we didn't ship any such data
providers)
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How to set the fixed order of DataSets execution? For example, I need
> DataSet2 to be started only when DataSet1 is finished.
> Thanks,
> Lucy.
>|||Yes I see if I check "Use single transaction", the queries runs
sequentially. But is the way to be sure that query from DataSet1 will start
prior to query from DataSet2?
Thanks,
Lucy
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:uwyZ2DdaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> If you mark the data source to "Use single transaction", the queries will
> have to execute in series (unless you happen to have a custom data
provider
> that supports transactions spanning multiple connections, which would
allow
> us to execute them in parallel... but we didn't ship any such data
> providers)
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > How to set the fixed order of DataSets execution? For example, I need
> > DataSet2 to be started only when DataSet1 is finished.
> >
> > Thanks,
> > Lucy.
> >
> >
>|||When running sequentially, they'll execute in the order they appear in the
RDL file.
(Unfortunately, we don't have anything in the design tool to let you reorder
them. If you need them in a different order, you'll have to hand-edit the
RDL to rearrange them).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OsB0AKdaEHA.3596@.tk2msftngp13.phx.gbl...
> Yes I see if I check "Use single transaction", the queries runs
> sequentially. But is the way to be sure that query from DataSet1 will
start
> prior to query from DataSet2?
> Thanks,
> Lucy
>
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:uwyZ2DdaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> > If you mark the data source to "Use single transaction", the queries
will
> > have to execute in series (unless you happen to have a custom data
> provider
> > that supports transactions spanning multiple connections, which would
> allow
> > us to execute them in parallel... but we didn't ship any such data
> > providers)
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Lusy Crown" <evesq@.uk2.net> wrote in message
> > news:OybWKscaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > How to set the fixed order of DataSets execution? For example, I need
> > > DataSet2 to be started only when DataSet1 is finished.
> > >
> > > Thanks,
> > > Lucy.
> > >
> > >
> >
> >
>

@original and stored procedures

When using @.original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :

<

UpdateParameters><asp:ParameterType="String"Name="ProductType"/><asp:ParameterType="String"Name="OpportunityType"/><asp:ParameterType="Double"Name="Value"/><asp:ParameterType="String"Name="Probability"/><asp:ParameterType="Int32"Name="OpportunityID"/></UpdateParameters>

And my stored procedure like this :

CREATE procedure dbo.UpdateOpportunity

(
@.ProductType int,
@.OpportunityType int,
@.Value money,
@.Probability int,
@.OpportunityID int
)

as

update Opportunity
set ProductType = @.ProductType, OpportunityType = @.OpportunityType,
[Value] = @.Value, Probability = @.Probability
where OpportunityID = @.OpportunityID

Do I need to change both cases of @.OpportunityID in my stored procedure to @.original_OpportunityID for it to work?

Most likely. It also depends on what you have set for the conflictdetection property. If it's compareallvalues, then you'll need to accept @.original_ for each of the columns that you selected via your select statement.

@@SPID and ADODB.Connection : Example

Here an code example code with this problem :
Option Explicit
Private Sub Form_Load()
Dim Connect As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Connect.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=Morgann;Data Source=(local);Use Procedure
for Prepare=1;Auto Translate=True;Packet Size=4096;Application
Name=Graphiplus 11.0.2;Workstation ID=SYLVAIN;Use Encryption for
Data=False;Tag with column collation when possible=False"
Debug.Print Connect.Execute("Select @.@.SPID").Fields(0).Value 'Return 52
Rs.Open "Select * From DEVIS", Connect
Debug.Print Connect.Execute("Select @.@.SPID").Fields(0).Value 'Retuen 53
End Sub
hi Sylvain,
Sylvain Aufrre wrote:
> Here an code example code with this problem :
using a SQL Server trace, you can monitor that performing the SELECT @.@.SPID
statement using a server side cursorlocation (adUseServer, the default
setting) will cause
set @.P2=8
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=1
exec sp_cursoropen @.P1 output, N'SELECT @.@.SPID', @.P2 output, @.P3 output, @.P4
output
select @.P1, @.P2, @.P3, @.P4
that's to say a server cursor operation...
as only 1 statement can be active per connection and the cursor has not been
still completely fetched and released (the recordset is still open), the
successive call to
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value
will force to open an implicit connection to perform the desired operation
(or cancelling the preceding task is required)..
destroying the adodb.recordset will reset the server side cursor situation
to normality
or... changing the connection CursorLocation to client (adUseClient) will
force the OLE DB Cursor Service to be involved, and, as it's known as Client
Cursor Engine, will force the entire result to be transferred not one row at
a time (server side standard) but as a whole, as the Client Cursor Engine
will provide the required storage area at client level int it's own cache,
also providing scroll, filter, sort and search features as it provides a
Rowset in local (client) memory
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
With oCon
.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial
Catalog=pubs;Integrated Security=SSPI;"
.CursorLocation = adUseClient
.Open
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
End With
Dim oRs As ADODB.Recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT @.@.SPID", oCon, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print oRs.Fields(0).Value
oRs.Close
Set oRs = Nothing
Set oRs = New ADODB.Recordset
'oRs.Open "SELECT TOP 1 * FROM authors", oCon, adOpenStatic,
adLockReadOnly, adCmdText
oRs.Open "SELECT @.@.SPID", oCon, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print oRs.Fields(0).Value
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
oRs.Close
Set oRs = Nothing
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
oCon.Close
Set oCon = Nothing
--<--
52
52
52
52
52
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply