Executing SSIS Package - SQL 2005 Express

Hi,

I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.

Thanks

Even I am also trying for the Same. Update me if you get a solution.sql

Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub

If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.

When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.

Can someone suggest how I can get the value 0-6 to my code?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Are possible errors caught within the procedure or your vb code ? How do you know that the procedure is not executed successfully if you get no additional error and why do you think it stops ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Quote:

Originally Posted by SQLusername

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.|||

Quote:

Originally Posted by ck9663

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.

Can you descibe what object locking is and how to remedy it? Also using SQL Server Enterprise Manager where can I edit time-out settings?

Executing sql statements

When i execute the following in Sql query anlyzer

Declare @.dbname varchar(30),
@.str varchar(500),
@.emailID varchar(50)

set @.EmailID='santosh@.yahoo.com'

set @.dbname='DB_kms_prv'

set @.str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +
@.dbname + '.dbo.tblEmployee where officialEmail=' + @.emailID

exec (@.str)

I get error message

The column prefix 'santosh@.yahoo' does not match with a table name or
alias name used in the query.
How to get rid of it..?You need to put single quotes around the address:

set @.str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +
@.dbname + '.dbo.tblEmployee where officialEmail=''' + @.emailID + ''''

If you get syntax errors from dynamic SQL, then just 'SELECT @.sql'
before executing it, so you can see what the statement looks like -
that makes the problem much clearer.

But don't use dynamic SQL at all unless it's absolutely necessary - see
here for all the reasons why to avoid it, and alternative solutions:

http://www.sommarskog.se/dynamic_sql.html

Specifically for your case, see "Getting data from another database" in
this section:

http://www.sommarskog.se/dynamic_sql.html#Dyn_Db

Simonsql

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Server 2005 stored procedures on Windows 2003

I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.

I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.

123.sh sp_my_stored_procedure input_parm1 input_parm2

123.sh sp_different_stored_procedure input_parm1

This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.

How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.

Thanks, Jim

You are looking for an oSql utility that will allow you to write command line SQL executions.

You can find it here

Adamus

|||

For SQL Server 2005, you really should use SQLCmd.exe (osql.exe is for backward compatibility.)

Documentation is available here.

|||If it help I heard about a book, SQL Server 2000 for the Oracle DBA .

Executing SQL scripts from files

Hi, I want to create many files with SQL statements and then I want to write one SQL script file, which executes SQL statement in the other files. How can I do it?

Thanks, Radco

You could do it with Stored Procedures - create a bunch of them - then create one last one, which will execute the others.

If this doesn't do what you want - maybe you could explain your scenario in more detail

|||No it is not what I wanted. I can say to it, that I have a very long script which creates a database for testing, It has more than 2100 lines of SQL statements (mostly INSERT). It begins to be hardly manageable. So I want to split this one script into many files to simplify it. If I want to change something now I need to search 2100 lines of code. If I split it I will only change one smaller file.|||

It is rather not possible to read SQL statement from file and run on SQL server using T-SQL, but you can create your files and next merge them together and run final one. or you can write simple VB application which will read your query and execute them on SQL server.

You can also try to use command line SQL tool osql to run your queries from inside dos batch file.

Thanks

Executing sql scripts from aspx....plz Help!

Hi All,
How to execute the sql scripts (which may be in .sql file or a string) from code behind files?

Thanx
Veeru.SqlCommand.ExecuteNonQuery()

The only problem I can think of is that .SQL scripts generated by Query Analyzer often have the 'GO' keyword in them, which is not a SQL keyword, but rather used for batching in QA.|||True, going to have to parse out GO statement. Some more things needed

SqlCommand comm = New SqlCommand("select * from table", sqlConnection)
comm.CommandType = CommandType.Textsql

executing SQL script from Command promt not working

Hello
I am trying to execute SQl script from command prom like this :

C:\Inetput\wwwroot> osql -U sa -P -i MyComics.sql

(uid=sa and pwd=)

and I got the result like this :

[Shared Memory]SQl Server deos not exist or access denied
[Shared Memory]Connection Open (Connect()).

I already check the SQL server , it's running.
what do I do now?

Thanks in advanceAre you certain that the user name and password is correct? Are you certain that this instance of SQL Server is the main instance and not a named instance?|||Hello

thank for the response.

from my understanding sa is the default user name of SQL Server 2000 and password is blank. I did not set any password for my SQL server.

I'm really new to SQL server, if you have any advice I'll appreciate.|||Try osql -E <other parameters, except user and password
-E uses a trusted connection. Unless you set an sa password while installing SQL Server, SQL Server is set to use Windows authentication. You need to specify Mixed Mode authentication. You can change to mixed mode security in Enterprise Manager. Right click database, select properties, set Authentication to SQL Server and Windows. Make sure you set a good sa password. DO NOT leave it as blank!

Executing SQL Package

Hi

I have created a DTS package in SQL 2005 which will pull the data from oracle and pushes into SQLServer. I am able to execute the package from business intelligence wizard.

Is there anyway to trigger this package manually apart from the wizard?.

looking for responses.

Thanks
GaneshIt can also be executed from query analyzer.

You call it from any forntend tool also.|||Hi

Thanks for your response.

When I create package from Business intelligence wizard, It was created as Package1.dtsx. Now , How can I invoke package1.dtsx from query analyzer?.

As far as I know, In oracle, a package will contain main and a body procedures. We can invoke the main procedure from any command prompt. But I am new to SQL packages. Can you suggest me in this regard.

Thanks,
Ganesh

Executing SQL held in a Text column

Hope someone can give me advice on a problem I'm having with my
current development.
I need to build up very long pieces of SQL, then execute them. The SQL
itself is dependent on the structure of the data in 2 other DBs. I was
using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
realize at the time how big it could grow. Each piece of SQL is just
is single SELECT, but a huge one. I reckon that the size could grow to
arounf 100k characters.
To try to get around the varchar limitation, I'm now accumulating the
SQL in a Text column in a DB table (since I can't use temporary
variables of data type Text), and that's all going fine. What I'm not
sure about is how to execute the SQL once I've finished accumulating
it.
So, I'll have something like this:
'
SELECT myField
FROM myTable
WHERE
this1=that1 AND
this2=that2 AND
:
:
thisN=thatN
'
- held as a value within a Text column. How do I run that query?
Ideally, I'd like to do all of this without breaking into programming
C#, or anything like that. I can do all of the difficult construction
bit in SQL already (to build the query strings), so I just want to do
something like call a stored procedure (I'm not averse to a bit of
complexity in the SP). How would I go about that - or is their an
easier way?
Any advice gratefully received. Last time I asked here, everyone was
very helpful, and it got me over the previous problem, so I'm very
optimistic!
Ronsugnaboris@.gmail.com (Ron) wrote in message news:<93d83728.0504050957.324dbf4a@.posting.goog
le.com>...
> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
I still haven't found a way of doing this. Can anyone help?|||Hi
You may want to look at the undocumented sp_execresultset.
John
"Ron" wrote:

> sugnaboris@.gmail.com (Ron) wrote in message news:<93d83728.0504050957.324d
bf4a@.posting.google.com>...
> I still haven't found a way of doing this. Can anyone help?
>|||sugnaboris@.gmail.com (Ron) wrote in
news:93d83728.0504050957.324dbf4a@.posting.google.com:

> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
Try the sp_executesql system stored procedure.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||Ron,
I have never been able to reach the limit on the EXEC statement, and I've
thrown things like 500k at it. You can concatenate quite a lot of nvarchars
together to do what you need to do:
declare @.buffer1 nvarchar(4000)
...
declare @.buffer100 nvarchar(4000)
exec (@.buffer1+@.buffer2 + ... + @.buffer100)
Of course, splitting a TEXT into NVARCHAR is a whole diferent topic ... post
a new question with how to do that if you have trouble ...
-- Alex Papadimoulis
SQL
"Ron" wrote:

> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
>|||John Bell <JohnBell@.discussions.microsoft.com> wrote in message news:<06BB67E8-16E1-4FFA-A6
3A-070307732CD0@.microsoft.com>...
> Hi
> You may want to look at the undocumented sp_execresultset.
> John
That sounds good, John, Thanks.
I've read a few articles about that stored procedure since you posted,
and I see that there's an xp_ version to this, too.
Just to confirm: I will have a column that holds Text data type
strings, which can be several tens of thousands of characters long.
Each individual value will be a SQL statement, and I want to be able
to execute the SQL statements.
Does that sound feasible with the SP you suggest?
I suppose that the way to use this would be to write a wrapper SP that
does the selection, then calls sp_execresultset, so that I don't see
the varchar(8000) limit from Query Analyzer?|||Thanks, Alex. I think that I can work out how to split the Text value -
but I'll certainly get back onto this newsgroup if it defeats me.
At the moment, I have a pair of nested cursors in the script that
generates the SQL (which is held in the TEXT column). I'm interrogating
some existing databases by looking over some of their objects, and
drilling down to analyze them, so the outer cursor handles tables, and
the inner one handles columns. I suppose that I could chunk the
generated SQL up into different VARCHAR variables as I'm generating;
but I have two misgivings about that:
1) It would contaminate the logic of the generation of the SQL with the
details of how the SQL is to be run, and it feels wrong to mix up those
separate concerns; and
2) I'm not sure if the Query Analyzer limit would apply to running the
EXEC with the concatentation of VARCHARs.
So I think that I'll continue to generate the entire SQL query as a
TEXT value, then read it out and execute it within a stored procedure.
Does that sound OK? It should make the overall process much cleaner,
logically, at the negligible cost of writing a very simple SP. (He
said, before he tried it...)
Thanks again!
Ron|||Thanks very much for your suggestion.
The parameter this SP takes is a unicode string that's too short for
what I need to do, I think. However, I did learn a bit more about
running dynamic SQL while following up on this, so that's been useful!
Ron

executing sql file

I want to know,

is there any method in SQL Server using "SQL Server Management Studio" to execute the .sql file? (Using query)

I know about osql & isql Utilities & i try this also as

EXEC xp_cmdshell 'osql -S vsnet1 -U sa -P sysadm -d aaa -i c:\ACCOUNTS.sql'

its working fine but it uses the dos command shell.

i too try the stored procedures (of others peson`s) like

sp_ExecuteSQLFromFile (i dont want this as it having some limits)

Is there any direct way to execute a .sql file? (as in case of Oracle RUN, START, @. )

Hope for help

Regards,

thanks.

Gurpreet S. Gill

I don't think Management studio provides any other way of executing the .sql file.

The most common way is to use sqlcmd from cmd prompt...

type in

sqlcmd /? from command prompt for more help

|||

Imtiaz--

I cant use the DOS prompt. i know about these commands like sqlcmd, isql, osq

but i want from SQL Server Managment

Regards,

Thanks.

Gurpreet S. Gill

|||

This sounds like it might help you out... you can enable "SQLCMD mode" in SQL Server Management Studio.

Steps:

1) Open SQL Server Management Studio.
2) Open a query window
3) Click the Query menu
4) Click SQLCMD Mode.

Kimberly Tripp does some great demos with SQLCMD. Not sure if it's on a webcast you can watch OnDemand though. Here are some BOL articles you can read.

Editing SQLCMD Scripts with Query Editor
http://msdn2.microsoft.com/en-us/library/ms174187.aspx

SQLCMD Mode
http://msdn2.microsoft.com/en-us/library/ms170411.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Paul--

Thanks

that`s really gr8.

This is what i want.

Regards,

Thanks.

Gurpreet S. Gill

Executing SQL command when user is connecting

I would like to be able to limit users from connecting to the database using
other than the dedicated application. Of course it could be done using
application role, but the applicationd does not support this.
Therefore I have been writing some code to catch the bad guys, but how
should I trigger the code. Something like a trigger on sysprocesses would be
nice, but is not possible.
Any suggestions... ?
No...you'll need to create a job to check however often and execute
the code in the job.
-Sue
On Wed, 2 Jun 2004 22:20:23 +0200, "Ole Wissing"
<nospamtome@.mail.tele.dk> wrote:

>I would like to be able to limit users from connecting to the database using
>other than the dedicated application. Of course it could be done using
>application role, but the applicationd does not support this.
>Therefore I have been writing some code to catch the bad guys, but how
>should I trigger the code. Something like a trigger on sysprocesses would be
>nice, but is not possible.
>Any suggestions... ?
>
sql

Executing SQL command when user is connecting

I would like to be able to limit users from connecting to the database using
other than the dedicated application. Of course it could be done using
application role, but the applicationd does not support this.
Therefore I have been writing some code to catch the bad guys, but how
should I trigger the code. Something like a trigger on sysprocesses would be
nice, but is not possible.
Any suggestions... ?No...you'll need to create a job to check however often and execute
the code in the job.
-Sue
On Wed, 2 Jun 2004 22:20:23 +0200, "Ole Wissing"
<nospamtome@.mail.tele.dk> wrote:

>I would like to be able to limit users from connecting to the database usin
g
>other than the dedicated application. Of course it could be done using
>application role, but the applicationd does not support this.
>Therefore I have been writing some code to catch the bad guys, but how
>should I trigger the code. Something like a trigger on sysprocesses would b
e
>nice, but is not possible.
>Any suggestions... ?
>

Executing SQL clause in extended stored procedure

Hello,
I have a question about the executing of an SQL clause in xp(extended stored
procedure).
I saw the examples about the ODS: xp_gettable_dblib and xp_gettable_odbc.
When I call xp_gettable_dblib, this xp execute the SQL clause in session,
which is different from the session from which it is called(EXEC
xp_gettable_dblib. Of course this is because of the dbopen).
I also saw the extended stored procedure xp_execresultset(xp_repl.dll) ,
which also can execute an SQL clause, but in contrast to xp_gettable_dblib,
this SQL clause is executed in the same session, from which it is called.
I am interested how it is made?
I saw that in xp_repl.dll are imported 5 API procedures from ODBC32.dll. In
this case is ODBC API is used?
I want to write my own xp, using the technology used in xp_execresultset.Since extended stored procedures are deprecated in SQL Server 2005, you
should probably find another solution.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:#zfRaJ6sFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a question about the executing of an SQL clause in xp(extended
stored
> procedure).
> I saw the examples about the ODS: xp_gettable_dblib and xp_gettable_odbc.
> When I call xp_gettable_dblib, this xp execute the SQL clause in session,
> which is different from the session from which it is called(EXEC
> xp_gettable_dblib. Of course this is because of the dbopen).
> I also saw the extended stored procedure xp_execresultset(xp_repl.dll) ,
> which also can execute an SQL clause, but in contrast to
xp_gettable_dblib,
> this SQL clause is executed in the same session, from which it is called.
> I am interested how it is made?
> I saw that in xp_repl.dll are imported 5 API procedures from ODBC32.dll.
In
> this case is ODBC API is used?
> I want to write my own xp, using the technology used in xp_execresultset.
>

EXECuting sps within sps (using return params)

Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @.@.IDENTITY) as input for some of the other procedures.

I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...

CREATE PROCEDURE addTemplateDetail
@.TemplateID int,
@.GroupNameID int,
@.SubGroupNameID int=null,
@.MethodID int,
@.AnalyteID int
AS
DECLARE @.TemplateGroupNameID int
DECLARE @.TemplateMethodID int
SET @.TemplateGroupNameID=(EXEC addTemplateGroupName @.TemplateID, @.GroupNameID)
SET @.TemplateMethodID=(EXEC addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID)
EXEC addTemplateAnalyte(@.TemplateMethodID,@.AnalyteID)

I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.Never mind.
I changed the two lines with return params to:

EXEC @.TemplateGroupNameID=addTemplateGroupName @.TemplateID, @.GroupNameID
EXEC@.TemplateMethodID=addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID

Hope that will somebody else too...sql

Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @.table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
(uncorrect syntax)

Could someone give me a clue

Thanx,

Hennie de Nooijerhdenooijer@.hotmail.com (Hennie de Nooijer) wrote in message news:<191115aa.0311040343.674519a6@.posting.google.com>...
> Hi
> I'm executing SP_SpaceUsed in a stored procedure like this :
> Exec ('SP_SpaceUsed '+ @.table)
> This works great but when i want to execute it for a table in a other
> database i'm running in to troubles. Things i tried is this :
> Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
> syntax)
> Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
> (uncorrect syntax)
> Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
> syntax)
> Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
> (uncorrect syntax)
> Could someone give me a clue
>
> Thanx,
> Hennie de Nooijer

exec('exec ' + @.database + '..sp_spaceused ' + @.table)

Simon

executing sp_changeownerdb

Hi,
I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.
The code fails after executing @.proc1. The errors says that i could not
find proc2.
Here's the code:
CREATE PROC usp_RestoreDB(
@.db_name varchar(10),
@.backup_location varchar(255),
@.login varchar(20)
)
AS
DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
SET @.proc1 = @.db_name + '..sp_fixusers'
SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
RESTORE DATABASE @.db_name
FROM DISK = @.backup_location
WITH REPLACE
Begin
EXEC @.proc1
EXEC @.proc2
ENDTry,
...
exec (@.proc1)
exec (@.proc2)
...
AMB
"Jason" wrote:

> Hi,
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
> The code fails after executing @.proc1. The errors says that i could not
> find proc2.
> Here's the code:
> CREATE PROC usp_RestoreDB(
> @.db_name varchar(10),
> @.backup_location varchar(255),
> @.login varchar(20)
> )
> AS
> DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
> SET @.proc1 = @.db_name + '..sp_fixusers'
> SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
> RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
> Begin
> EXEC @.proc1
> EXEC @.proc2
> END
>|||On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

>Hi,
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>The code fails after executing @.proc1. The errors says that i could not
>find proc2.
>Here's the code:
>CREATE PROC usp_RestoreDB(
>@.db_name varchar(10),
>@.backup_location varchar(255),
>@.login varchar(20)
> )
>AS
>DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
>SET @.proc1 = @.db_name + '..sp_fixusers'
>SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
>RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
>Begin
>EXEC @.proc1
>EXEC @.proc2
>END
Hi Jason,
Try changing the logic for proc2 to
(...)
SET @.proc2 = @.db_name + '..sp_changedbowner'
(...)
EXEC @.proc2 @.loginame = @.login
(...)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

EXECuting SP within another SP

I am trying to call an SP within another SP, but the result being returned
from the inner SP is Null, whereas I should expect it to return a 1 or 0...
Here is the code:
ALTER Proc Returns_RecordPostClean
@.SerialNo int,
@.LineID int,
@.PostCleanDate smalldatetime,
@.PostCleanResult varchar(10),
@.PostCleanText varchar(50),
@.Exothermed tinyint,
@.Turned bit = 0
As
Set NoCount On
Begin Tran
Update Cleans
Set PostCleanDate = @.PostCleanDate,
PostCleanResult = @.PostCleanResult,
PostCleanText = @.PostCleanText,
Exothermed = @.Exothermed,
Turned = @.Turned
Where SerialNo = @.SerialNo
and LineID = @.LineID
Declare @.Status varchar(30)
Declare @.PFE bit
Declare @.Result int
Select @.PFE=O.PFE
From Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID
If @.PostCleanResult = 'Passed'
If @.PFE = 1
Set @.Status = 'PFE Credit Note'
Else
Begin
Set @.Status = 'Complete'
Exec @.Result=Common_UpdateOrderStatus @.LineID
<========= calling 2nd SP
End
Else
Set @.Status = 'Quarantined'
Update OrderDetail
Set Status = @.Status
Where LineID = @.LineID
If @.@.RowCount = 1 and @.Result = 1
Begin
Select @.Result as Success
Commit tran
End
Else
Begin
Select @.Result as Success
rollback tran
End
Set NoCount Off
If I call the 2nd SP outside this SP, it works fine. But for reasons I can't
be bothered going in to, it really needs to be call from within this SP.
This is actually the first time I've asked one SP to call another, so I'm
not sure if I'm missing something..
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Can you post the code of the second sp?
AMB
"CJM" wrote:

> I am trying to call an SP within another SP, but the result being returned
> from the inner SP is Null, whereas I should expect it to return a 1 or 0..
.
> Here is the code:
> ALTER Proc Returns_RecordPostClean
> @.SerialNo int,
> @.LineID int,
> @.PostCleanDate smalldatetime,
> @.PostCleanResult varchar(10),
> @.PostCleanText varchar(50),
> @.Exothermed tinyint,
> @.Turned bit = 0
> As
> Set NoCount On
> Begin Tran
> Update Cleans
> Set PostCleanDate = @.PostCleanDate,
> PostCleanResult = @.PostCleanResult,
> PostCleanText = @.PostCleanText,
> Exothermed = @.Exothermed,
> Turned = @.Turned
> Where SerialNo = @.SerialNo
> and LineID = @.LineID
> Declare @.Status varchar(30)
> Declare @.PFE bit
> Declare @.Result int
> Select @.PFE=O.PFE
> From Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID
> If @.PostCleanResult = 'Passed'
> If @.PFE = 1
> Set @.Status = 'PFE Credit Note'
> Else
> Begin
> Set @.Status = 'Complete'
> Exec @.Result=Common_UpdateOrderStatus @.LineID
> <========= calling 2nd SP
> End
> Else
> Set @.Status = 'Quarantined'
>
> Update OrderDetail
> Set Status = @.Status
> Where LineID = @.LineID
> If @.@.RowCount = 1 and @.Result = 1
> Begin
> Select @.Result as Success
> Commit tran
> End
> Else
> Begin
> Select @.Result as Success
> rollback tran
> End
> Set NoCount Off
>
> If I call the 2nd SP outside this SP, it works fine. But for reasons I can
't
> be bothered going in to, it really needs to be call from within this SP.
> This is actually the first time I've asked one SP to call another, so I'm
> not sure if I'm missing something..
> Thanks
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Sure, here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Proc Common_UpdateOrderStatus
@.LineID int
As
Declare @.Total int, @.Complete int, @.OrderID int
Select
@.OrderID=(Select O.OrderID
from Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID),
@.Total=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID),
@.Complete=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID
and D.Status = 'Complete' or D.Status = 'Cancelled')
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
Select 1 as Success
Else
Select 0 as Success
End
Else
Select 2 As Success
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
> Can you post the code of the second sp?
>
> AMB
>|||CJM wrote:
> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
You need to use a RETURN statement to return a... return value. Do not
use a result set as it will play havok with your application if not
managed carefully.
Instead of:
Select 0
Use:
Return 0
David Gugick
Imceda Software
www.imceda.com|||CJM,
A sp returns a value using the RETURN keyword, mainly to indicate success or
fail (0 - success, another int value diff from 0 to indicate fail).
If you want to return a value with a specific meaning, is better to use an
output parameter.
ALTER Proc Common_UpdateOrderStatus
@.LineID int,
@.op int output
As
...
if @.@.error <> 0 return 1
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
set @.op = 1
Else
set @.op = 0
End
Else
set @.op = 2
return 0
go
declare @.i int
declare @.Result int
Exec @.Result=Common_UpdateOrderStatus @.LineID, @.i int output
print @.Result
print @.i
AMB
"CJM" wrote:

> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
>

Executing sp on other sqlserver

It's possible ?
From SQLServer1, execute sp_addsubscriber of SQLServer2 ?
Thanks,
PePiCKTry Linked servers.
http://www.databasejournal.com/feat...cle.php/3085211
"PePiCK" wrote:

> It's possible ?
> From SQLServer1, execute sp_addsubscriber of SQLServer2 ?
> Thanks,
> PePiCK
>
>

Executing SP inside SP dynamically

I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!In Books OnLine, look up the keywords OUTPUT variable and RETURN.|||Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.|||I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!

Try This

DECLARE @.sql nvarchar(2048)
SET @.sql = ' SET @.count = ( SELECT COUNT(*) FROM table1 )'
DECLARE @.temp int
EXEC sp_executesql @.sql, N'@.count int OUTPUT', @.temp OUTPUT

Jamessql

Executing SP in MSSQL takes forever via JDBC?!

Hi all,
I need to execute a stored procedure in our database server, MS SQL Server
2000 and it takes forever...
I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
My code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
cs.setString(1,"param1");
cs.setString(2,"param2");
long start = System.currentTimeMillis();
ResultSet rs = cs.executeQuery();
System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
ms");
...
Every time I execute this piece of code, it takes between 45-50 seconds...
I have tried to execute my SP from DBVisualizer(an app using the same JDBC
driver) and it is the same result. But, when I execute my SP from Query
Analyzer it takes less than a second?! I have also tried executing the SP
from TOAD for SQL Server, and then the execution time also is less than a
second. Why? What have I missed? Does anybody recognize this problem?
It is not me personally that has wriiten the SP, so I do not really know
what it does and how it looks, but I know that the SP I'm calling is calling
another SP that is creating a couple of temporary tables while it is
executing...
Greatfull for any suggestions...
Cheers
//Anders =)
anders.hedstrom wrote:

> Hi all,
> I need to execute a stored procedure in our database server, MS SQL Server
> 2000 and it takes forever...
> I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
> My code:
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> Connection conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
>
> CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
> cs.setString(1,"param1");
> cs.setString(2,"param2");
Try these two things:
1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
2 -
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
Joe Weinstein at BEA

> long start = System.currentTimeMillis();
> ResultSet rs = cs.executeQuery();
> System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
> ms");
> ...
>
> Every time I execute this piece of code, it takes between 45-50 seconds...
> I have tried to execute my SP from DBVisualizer(an app using the same JDBC
> driver) and it is the same result. But, when I execute my SP from Query
> Analyzer it takes less than a second?! I have also tried executing the SP
> from TOAD for SQL Server, and then the execution time also is less than a
> second. Why? What have I missed? Does anybody recognize this problem?
> It is not me personally that has wriiten the SP, so I do not really know
> what it does and how it looks, but I know that the SP I'm calling is calling
> another SP that is creating a couple of temporary tables while it is
> executing...
> Greatfull for any suggestions...
> Cheers
> //Anders =)
|||
> Try these two things:
> 1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
> 2 -
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
> Joe Weinstein at BEA
Hi Joe,
thanx alot for your tip! This fixed my problem;
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
It would be quite interesting to know why it takes so long to execute the SP
when using CallableStatement and PreparedStatement...
By the way, the lowercase 's' on sendStringParametersAsUnicode made no
difference.
Once again Joe, thanx!!!
Cheers
//Anders =)

Executing SP From a Batch file

I was wondering how I can create a batch file that call a store procedure an
d
put the output on a file.Silver wrote:
> I was wondering how I can create a batch file that call a store
> procedure and put the output on a file.
Try osql.exe - the command-line options are documented in Books Online or by
typing osql /? at the command line.
John.|||Hi,
Call OSQL inside the command prompt
Entries inside the batch file will be
OSQL -Usa -Ppassword -S Servername -d Dbname -Qprocedurename -oc:\output.log
Thanks
Hari
SQL Server MVP
"Silver" <Silver@.discussions.microsoft.com> wrote in message
news:341FE12A-19E6-4A8B-8003-558797A637BF@.microsoft.com...
>I was wondering how I can create a batch file that call a store procedure
>and
> put the output on a file.

Executing SP for all results

I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.comLooping through resultsets with cursors is usually a bad idea and there
is normally a better set-based solution. Can you post your proc, your
select query and some schema so we can advise on a better approach
(maybe some test data & expected results too)?
*mike hodgson*
http://sqlnerd.blogspot.com
Doc Parker wrote:

>I would like to have my stored procedure executed for each item returned by
>a Select query
>
>Ex.
>Declare @.file_id varchar(5)
>Select @.file_id = file_id from GEN where this_value <> ''
>execute sp_mystored_procedure @.file_id
>go
>Currently it only works for one record
>How do I get it to do it for all returned records?
>The top select statement may return 1000 records, but only one pass is made
>through the stored procedure
>Hints Please
>Thanks
>Darrell
>darrellp@.btmcpa.com
>
>
>|||The procedure in question actually runs as part of an exsiting TRIGGER. I am
installing this TRIGGER into a table of a database and want to call it in o
rder to extract data all ready in the table. Otherwise it works great for re
cords currently being added. If I were to use a CURSOR to run this SP it wou
ld be a one time deal. Nothing permanent.
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uWo%23nF6JGHA.340
8@.TK2MSFTNGP12.phx.gbl...
Looping through resultsets with cursors is usually a bad idea and there is n
ormally a better set-based solution. Can you post your proc, your select qu
ery and some schema so we can advise on a better approach (maybe some test d
ata & expected results too)?
mike hodgson
http://sqlnerd.blogspot.com
Doc Parker wrote:
I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.com

executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

executing sp

Hi
I have sp in which i run dbcc inputbuffer.
Can I, and how grant permissions to somebody who
is not in sysadmin fixed server role to execute this sp?Hi,
No it is not possible.
DBCC INPUTBUFFER permissions default to members of the Sysadmin fixed server
role only, who can see any SPID. Other users can see any SPID they own.
Permissions are not transferable.
Thanks
Hari
MCDBA
<roman.ilic@.avtenta.si> wrote in message
news:efd1361.0403160208.7a8a3d9b@.posting.google.com...
> Hi
> I have sp in which i run dbcc inputbuffer.
> Can I, and how grant permissions to somebody who
> is not in sysadmin fixed server role to execute this sp?sql

Executing Simple Statements - Newbie

Hi Gurus,

This is a question that I have for T-sql programmers.

Problem: To execute a statement with aritmetic operators in it.

Database: SQL Server 2000

For e.g. if my string has "5+6+9" I need to execute the string and
obtain a value of 20 in a variable. I read a little bit of
documentation on preparing a statement etc, but before going deeper
there, thought will write you you guys.

Thanks in advance!

Bhaskarsp_executesql is probably the best option:

declare
@.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.s = '5+6+9'
set @.sql = 'set @.i = ' + @.s
exec sp_executesql @.sql, N'@.i int output', @.i = @.i output
select @.i

But in general dynamic SQL can be awkward and there are a number of
important issues to consider - see here for the full story:

http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could do this easily in a front-end application, and
if you have more complex formulae to evaluate then that might be a
better option.

Simon|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

Maybe, but probably not. Or put in another way: this is not something
you normally do in SQL Server. You may still have a very good reason
for wanting to do this, but there is also the possibility that you
are approaching your real business problem incorrectly.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

Maybe, but probably not. Or put in another way: this is not something
you normally do in SQL Server. You may still have a very good reason
for wanting to do this, but there is also the possibility that you
are approaching your real business problem incorrectly.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is the business reason that prompts me to do this. Business would
like to compute a set of say 50 values for about 30 different items. If
I have to hardcode the calculations it will be 50x30 = 1500 items.

Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
would need programmer intervention.

Hence if I had the expression itself in a table and had a way of
calculating it dynamically, it would help.

I will definately explore other methods of doing the same, but my
thought is there is a strong reason to have a functionality like this.

Thanks again
Bkr|||Bkr (keepitliteus@.yahoo.com) writes:
> Here is the business reason that prompts me to do this. Business would
> like to compute a set of say 50 values for about 30 different items. If
> I have to hardcode the calculations it will be 50x30 = 1500 items.
> Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
> would need programmer intervention.
> Hence if I had the expression itself in a table and had a way of
> calculating it dynamically, it would help.

Thanks, that gives me at least a glimpse of the requirements. I'm still
not sure that this is an easy way to.

You could store an expression as @.a + @.b + @.c, and then get that from
the table and put in an variable @.expr. Then you could say:

SELECT @.sql = '@.res = ' + @.expr
EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT',
@.a, @.b, @.c, @.result OUTPUT

If the formula would be changed to @.a + 2 * @.b / @.c it would still
work. But if the formula would become @.a + @.b + @.d / @.e you would have
to change the way you compute the value.

You could parse the string to find out which the values are, but
that is a tedious and tricky business to do in T-SQL. A language like
Perl would be a lot nicer for this sort of work.

One idea that occurred to me is that you should not store the expressions
complete strings, but instead should have a table like:

CREATE TABLE expressiontokens (exprid int NOT NULL,
rowno smallint NOT NULL,
tokens varchar(30) NOT NULL,
CONSTRAINT pk_exprtmers PRIMARY KEY expressionterms(exprid, term))

Then for one expression you could have somehing like

rowno token
1 @.a
2 +
3 @.b
4 *
5 (
6 @.c
7 +
...

@.a, @.b and @.c would then be key values to a table where you would look up
the actual values. In this way would not have to parse the expression at
run-time. (But you would have to parse the expression to store it. If
you were do this in T-SQL, you would still have to build the SQL statement
dynamically.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is the business reason that prompts me to do this. Business would
like to compute a set of say 50 values for about 30 different items. If
I have to hardcode the calculations it will be 50x30 = 1500 items.

Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
would need programmer intervention.

Hence if I had the expression itself in a table and had a way of
calculating it dynamically, it would help.

I will definately explore other methods of doing the same, but my
thought is there is a strong reason to have a functionality like this.

Thanks again
Bkr|||Bkr (keepitliteus@.yahoo.com) writes:
> Here is the business reason that prompts me to do this. Business would
> like to compute a set of say 50 values for about 30 different items. If
> I have to hardcode the calculations it will be 50x30 = 1500 items.
> Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
> would need programmer intervention.
> Hence if I had the expression itself in a table and had a way of
> calculating it dynamically, it would help.

Thanks, that gives me at least a glimpse of the requirements. I'm still
not sure that this is an easy way to.

You could store an expression as @.a + @.b + @.c, and then get that from
the table and put in an variable @.expr. Then you could say:

SELECT @.sql = '@.res = ' + @.expr
EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT',
@.a, @.b, @.c, @.result OUTPUT

If the formula would be changed to @.a + 2 * @.b / @.c it would still
work. But if the formula would become @.a + @.b + @.d / @.e you would have
to change the way you compute the value.

You could parse the string to find out which the values are, but
that is a tedious and tricky business to do in T-SQL. A language like
Perl would be a lot nicer for this sort of work.

One idea that occurred to me is that you should not store the expressions
complete strings, but instead should have a table like:

CREATE TABLE expressiontokens (exprid int NOT NULL,
rowno smallint NOT NULL,
tokens varchar(30) NOT NULL,
CONSTRAINT pk_exprtmers PRIMARY KEY expressionterms(exprid, term))

Then for one expression you could have somehing like

rowno token
1 @.a
2 +
3 @.b
4 *
5 (
6 @.c
7 +
...

@.a, @.b and @.c would then be key values to a table where you would look up
the actual values. In this way would not have to parse the expression at
run-time. (But you would have to parse the expression to store it. If
you were do this in T-SQL, you would still have to build the SQL statement
dynamically.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you!

Initially I thought of creating a table with a column for the prder in
which the formula was to be computed. Later, I thought every formula of
this type can be separated into Numerator and Denominator. So if I have
a table with 2 columns one for Numerator and another for Den. The onus
of giving the right expressions would then rest with the user and make
it easy to maintain and compute too.

Hence this approach. I tried using the code above. But it would not run
for me.
This is the error message that I get.

Server: Msg 170, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '@.'.
(this error is right at the EXEC sp_executesql. If you can throw some
light on it it would be great. I am also reachable at 240-483-3242.

Thanks!|||Thank you!

Initially I thought of creating a table with a column for the prder in
which the formula was to be computed. Later, I thought every formula of
this type can be separated into Numerator and Denominator. So if I have
a table with 2 columns one for Numerator and another for Den. The onus
of giving the right expressions would then rest with the user and make
it easy to maintain and compute too.

Hence this approach. I tried using the code above. But it would not run
for me.
This is the error message that I get.

Server: Msg 170, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '@.'.
(this error is right at the EXEC sp_executesql. If you can throw some
light on it it would be great. I am also reachable at 240-483-3242.

Thanks!|||Bkr (keepitliteus@.yahoo.com) writes:
> Hence this approach. I tried using the code above. But it would not run
> for me.
> This is the error message that I get.
> Server: Msg 170, Level 15, State 1, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near '@.'.

Obviously there should be a SET or SELECT first.

Working with dynamic SQL means that you will have understand the
syntax errors you get - because it's quite easy and go lost and
achieve one. Including something like:

IF @.debug = 1
PRINT @.sql

can often help to spot the problems.

As for mastering dynamic SQL, there is an article on my web site
about it: http://www.sommarskog.se/dynamic_sql.html.

> (this error is right at the EXEC sp_executesql. If you can throw some
> light on it it would be great. I am also reachable at 240-483-3242.

Not that I intend to call, but you should probably have included the
country code as well. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> Hence this approach. I tried using the code above. But it would not run
> for me.
> This is the error message that I get.
> Server: Msg 170, Level 15, State 1, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near '@.'.

Obviously there should be a SET or SELECT first.

Working with dynamic SQL means that you will have understand the
syntax errors you get - because it's quite easy and go lost and
achieve one. Including something like:

IF @.debug = 1
PRINT @.sql

can often help to spot the problems.

As for mastering dynamic SQL, there is an article on my web site
about it: http://www.sommarskog.se/dynamic_sql.html.

> (this error is right at the EXEC sp_executesql. If you can throw some
> light on it it would be great. I am also reachable at 240-483-3242.

Not that I intend to call, but you should probably have included the
country code as well. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||My bad. I am sure you might have guessed where I am from :) The country
code is 011. (United States). That is a very interesting article by
you. My guess is I am writing to a BIG sql server guru. I guess i get
lucky sometimes.

I would definately like to call you if that is okay with you.

Here is the complete code that I am pasting. Not sure where I am going
wrong. Also am planning to buy a book on sql programming. Please have a
look and let me know.
--drop procedure sp_test
create procedure sp_test AS

DECLARE @.debug integer

DECLARE @.a integer
DECLARE @.b integer
DECLARE @.c integer
DECLARE @.expr nvarchar
declare @.result integer
declare @.res integer
declare @.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.a =5
set @.b = 9
set @.c = 11

set @.expr = '@.a + @.b + @.c'

SELECT @.sql = '@.res = ' + @.expr

IF @.debug = 1
PRINT @.sql

EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
IF @.debug = 1
PRINT @.sql

Thanks a lot!!!!!|||My bad. I am sure you might have guessed where I am from :) The country
code is 011. (United States). That is a very interesting article by
you. My guess is I am writing to a BIG sql server guru. I guess i get
lucky sometimes.

I would definately like to call you if that is okay with you.

Here is the complete code that I am pasting. Not sure where I am going
wrong. Also am planning to buy a book on sql programming. Please have a
look and let me know.
--drop procedure sp_test
create procedure sp_test AS

DECLARE @.debug integer

DECLARE @.a integer
DECLARE @.b integer
DECLARE @.c integer
DECLARE @.expr nvarchar
declare @.result integer
declare @.res integer
declare @.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.a =5
set @.b = 9
set @.c = 11

set @.expr = '@.a + @.b + @.c'

SELECT @.sql = '@.res = ' + @.expr

IF @.debug = 1
PRINT @.sql

EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
IF @.debug = 1
PRINT @.sql

Thanks a lot!!!!!|||Bkr (keepitliteus@.yahoo.com) writes:
> My bad. I am sure you might have guessed where I am from :) The country
> code is 011. (United States). That is a very interesting article by
> you. My guess is I am writing to a BIG sql server guru.

Actually, you are posting to a Usenet newsgroup, which are read by an
unknown number of people all over the world.

> Here is the complete code that I am pasting. Not sure where I am going
> wrong. Also am planning to buy a book on sql programming. Please have a
> look and let me know.
> --drop procedure sp_test
> create procedure sp_test AS

Don't call your stored procedures sp_<something>. This prefix is reserved
for system stored procedures, and if Microsoft would ship an sp_test,
you would have a great surprise.

> SELECT @.sql = '@.res = ' + @.expr
> IF @.debug = 1
> PRINT @.sql
> EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
> IF @.debug = 1
> PRINT @.sql

As I said in my previous post "Obviously there should be a SET or SELECT
first." (Also, see Simon's example earlier in the thread.)

Permit to be quite frank: you have a very trivial syntax error. Yes, you
got it from me, but just because it was from me, does not mean that it
is correct. Trivial syntax error crop up all the time when you work
with T-SQL (at least it does when I do :-). If you are not able to
deal with those, when you will have extreme difference of handling
dynamic expressions entered by users (which certainly is advanced usage).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> My bad. I am sure you might have guessed where I am from :) The country
> code is 011. (United States). That is a very interesting article by
> you. My guess is I am writing to a BIG sql server guru.

Actually, you are posting to a Usenet newsgroup, which are read by an
unknown number of people all over the world.

> Here is the complete code that I am pasting. Not sure where I am going
> wrong. Also am planning to buy a book on sql programming. Please have a
> look and let me know.
> --drop procedure sp_test
> create procedure sp_test AS

Don't call your stored procedures sp_<something>. This prefix is reserved
for system stored procedures, and if Microsoft would ship an sp_test,
you would have a great surprise.

> SELECT @.sql = '@.res = ' + @.expr
> IF @.debug = 1
> PRINT @.sql
> EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
> IF @.debug = 1
> PRINT @.sql

As I said in my previous post "Obviously there should be a SET or SELECT
first." (Also, see Simon's example earlier in the thread.)

Permit to be quite frank: you have a very trivial syntax error. Yes, you
got it from me, but just because it was from me, does not mean that it
is correct. Trivial syntax error crop up all the time when you work
with T-SQL (at least it does when I do :-). If you are not able to
deal with those, when you will have extreme difference of handling
dynamic expressions entered by users (which certainly is advanced usage).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

executing set of SPs as joB is hanging..

I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?

Thanks.ANd also my sql server agent had an error few hrs back as

Name:Demo:Sev. 24 Errors
Type: sQL Server event alert
severity:024-FatalError:Hardware error

Thnaks.|||And one more thing is that other jobs were running fine.|||When i investigate in depth i found that there were lots of locks on that table.How can i solve that problem|||how can i forcibly logout a user(sqlserver user) who has locked the table.|||To detect locks look into master.dbo.syslockinfo

i.e. simple script as example

select distinct object_name(rsc_objid) as Table_name,
case rsc_type when 5 then 'page lock' when 6 then 'table lock' end as lock_level ,case req_mode when 5 then 'X' when 8 then 'IX' end as lock_type , case req_ownertype when 1 then 'transaction' when 2 then 'cursor' when 3 then 'session' when 4 then 'ExSession' end as Owner_type, p.last_batch
from master.dbo.syslockinfo s, master.dbo.sysprocesses p
where exists
(select 1 from master.dbo.sysdatabases where s.rsc_dbid=dbid
and name='yourDBNAME') -- put the name of your db here
and rsc_type in (6)-- table level lock
and req_mode in (5) -- exclusive lock
and req_status=1 --granted lock
and s.req_spid=spid
and object_name(rsc_objid) is not null

to kill a process - look up KILL in BOL.

However, before you do anything that drastic - use Profiler to detect what exactly is going on, find what code is causing the performance degradation and act based on that.

simas

executing serial dts

hi,

i am new here and interested in how to :

- execute serial dts in one single action or using vb script.

- change the status (enabled to disabled) and schedule using vb script.

Many dts make me tired when i have to change the status and schedule one by one.

regards.

You don't need to be tired. Read more here:

http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx

Executing Script Files From Transact-SQL

Hi,

I have my create statments for tables, procedures, views, etc in
individual Transact-SQL script files (.sql).

I wnat to write another script file that executes these scripts in the
correct order to create the database.

What is the syntax for executing script files from Transact-SQL?

Thanks, PhilPhil (hp_howell@.hotmail.com) writes:
> I have my create statments for tables, procedures, views, etc in
> individual Transact-SQL script files (.sql).
> I wnat to write another script file that executes these scripts in the
> correct order to create the database.
> What is the syntax for executing script files from Transact-SQL?

There isn't one really. Once the batch has been sent to SQL Server,
the script is executing on the server and not on the machine where you
have the scripts.

You can, though, use xp_cmdshell to fork out and run a script through a
command-line tool like OSQL. Beware then that you are running from a second
connection.

Another alternative is to run the scripts with OSQL from the client machine,
and use ~r to include files. Note that ~r is a command to OSQL, and is not
understood by Query Analyzer or SQL Server.

My personal preference for install scripts is to run them in some client
language (Perl in my case). This does not have to be advanced. Basically
just something which reads the files, passes it to SQL Server through some
API call or through OSQL, and then maybe checks for errors.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Executing rs utility.

Hi,

I have two doubts :

1. The rs Utility has 2 arguments : -u username & -p password.

Is there any way by which we can use encryption/decryption for these parameters ?

2. Is there any way by which we can specify a url which indicates the machine name where the rs utility should be executed.

As rs utility would be present only on the machine where Reporting Services are installed. A way to specify the machine name where the rs utility should be executed would be helpful.

Thanks in advance.

1. No, unless you have custom security extension that knows how to decrypt them

2. The utility takes serverURL parameter.

|||

Thanks for the replies.

1. Even if we have I have a custom security extension, the rs utility should know how to decrypt the username/password. Can you please explain in some detail of how to use custom security extension ?

2. The serverURL parameter which the utility takes is the reportServerURL. I was searching for scenario where in :The Reporting Services (rs utility) are installed on X machine and I want a way to say that execute the rs utility on the X machine from Y machine. Is there any way to do this.

|||

1. A custom security extension (aka Forms-based Authentication) allows you to replace the default SSRS Windows-based security model, as explained in more details here.

2. So, your question is how to get an exe to execute on another box when I start it locally? I don't think you can do this.

Executing result without copying and pasting

I must be overlooking something because I cant figure out how to execute the
results of a query. The query result is over 8000 characters long so I cant
put it in a variable without having more than one and appending them togethe
r
(which caused some other issues when I have tried that).
In this boundaries I have to work within I also cant create the query as a
stored proc and then run it, nor am I able to output to file and then execut
e
the file. I have tried dbcc oututbuffer/inputbuffer, but it has to be
executed from another window/login.
A little background is the original querys output creates another query, and
the output will be different depending on which database the query is ran on
.
The query consists of several cursors. Until this point I have been
copying and pasting the results output into the input and running it. I need
to simplify this for others to run so that it is a one step process.
Thanks for pointing me in the right direction.I *think* I follow what you're saying (let me know
if I'm wrong), but you're having a problem with
a proc creating a secondary query that you can't
run via dynamic sql because the size is too big.
Am I close?
If so, look up the EXECUTE statement in BOL
Here's the snippet to pay close attention to
Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for
dynamic execution. Each string expression can be a mixture of Unicode and
non-Unicode data types.
Although each [N] 'tsql_string' or @.string_variable must be less than 8,000
bytes, the concatenation is performed logically in the SQL Server parser and
never materializes in memory. For example, this statement never produces the
expected 16,000 concatenated character string:
EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')
Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE
statement is executed.
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:12D2E96B-588C-468E-9391-2330015EAA31@.microsoft.com...
> I must be overlooking something because I cant figure out how to execute
the
> results of a query. The query result is over 8000 characters long so I
cant
> put it in a variable without having more than one and appending them
together
> (which caused some other issues when I have tried that).
> In this boundaries I have to work within I also cant create the query as a
> stored proc and then run it, nor am I able to output to file and then
execute
> the file. I have tried dbcc oututbuffer/inputbuffer, but it has to be
> executed from another window/login.
> A little background is the original querys output creates another query,
and
> the output will be different depending on which database the query is ran
on.
> The query consists of several cursors. Until this point I have been
> copying and pasting the results output into the input and running it. I
need
> to simplify this for others to run so that it is a one step process.
> Thanks for pointing me in the right direction.
>|||Right now it is a sql query that is ran, then the results(output) are copied
and pasted into another query window and that query is ran. IT isnt a sp at
this point.
Below is the syntax of the first query
set quoted_identifier off
go
declare @.table_name varchar(50),
@.column_name varchar(50),
@.column_dtype varchar(25)
declare table_names cursor for
select name from sysobjects where type = 'U' and name not in
('dtproperties') And name in (select o.name from sysobjects o inner join
syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
o.name not like 'conflict%' and o.name not like 'msm%')
order by name asc
open table_names
fetch next from table_names
into @.table_name
while @.@.fetch_status = 0
begin
print 'create trigger ' + @.table_name + '_audit_update on ' + @.table_name
print 'for update'
print 'not for replication'
print 'as'
print ''
declare table_columns cursor for
select name,xtype from syscolumns where id =
(select id from sysobjects where name = @.table_name)
open table_columns
fetch next from table_columns
into @.column_name,@.column_dtype
while @.@.fetch_status = 0
begin
if @.column_dtype not in ('35','34','99','61','108')
begin
print 'if update (' + @.column_name + ') and ((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid from inserted'
end
print 'end'
print ''
end
else if @.column_dtype in ('35','99')
begin
print 'if update (' + @.column_name + ')'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
print 'end'
print ''
end
else if @.column_dtype in ('61','108')
begin
print 'if update (' + @.column_name + ') and ((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
print 'or (select top 1 ' + @.column_name + ' from inserted) is
null and (select top 1 ' + @.column_name + ' from deleted) is not null'
print 'or (select top 1 ' + @.column_name + ' from deleted) is
null and (select top 1 ' + @.column_name + ' from inserted) is not null'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
print 'end'
print ''
end
else
begin
print 'if update (' + @.column_name + ')'
print 'begin'
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has been
updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
where r.rowguid = d.rowguid'
print 'end'
print ''
end
fetch next from table_columns into @.column_name,@.column_dtype
end
close table_columns
deallocate table_columns
print 'go'
fetch next from table_names into @.table_name
end
close table_names
deallocate table_names
go
I take this output (a create trigger statement for each table in my
database) and run it against the database. The first query is a script
generator, when its output is ran the triggers are created.|||I kludged together something ugly that may give you an idea of how to go
about getting your result.. Test it out on test system first! You could
get rid of some of the @.sql variables. I only went with so many because I
wasn't sure how big this trigger code could get and I don't have a real spec
to go by.
Also, the code could be rewritten. You don't need to use a cursor and I
think some of this code
could be condensed as well.
Anyway, here's the kludge that will build the triggers and put them into the
database (for what it's worth)
I would experiement with condensing it and cleaning it up.
set quoted_identifier off
go
declare @.table_name varchar(50),
@.column_name varchar(50),
@.column_dtype varchar(25),
@.sql1 varchar(8000),
@.sql2 varchar(8000),
@.sql3 varchar(8000),
@.sql4 varchar(8000),
@.sql5 varchar(8000),
@.sql6 varchar(8000),
@.sql7 varchar(8000),
@.sql8 varchar(8000),
@.sql9 varchar(8000),
@.sql10 varchar(8000),
@.sql11 varchar(8000),
@.sql12 varchar(8000),
@.sql13 varchar(8000)
set @.sql1 = ''
set @.sql2 = ''
set @.sql3 = ''
set @.sql4 = ''
set @.sql5 = ''
set @.sql6 = ''
set @.sql7 = ''
set @.sql8 = ''
set @.sql9 = ''
set @.sql10 = ''
set @.sql11 = ''
set @.sql12 = ''
set @.sql13 = ''
declare table_names cursor for
select name from sysobjects where type = 'U' and name not in
('dtproperties') And name in (select o.name from sysobjects o inner join
syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
o.name not like 'conflict%' and o.name not like 'msm%')
order by name asc
open table_names
fetch next from table_names
into @.table_name
while @.@.fetch_status = 0
begin
set @.sql1 = 'create trigger ' + @.table_name + '_audit_update on ' +
@.table_name
set @.sql1 = @.sql1 + ' for update'
set @.sql1 = @.sql1 + ' not for replication'
set @.sql1 = @.sql1 + ' as'
set @.sql1 = @.sql1 + ' '
declare table_columns cursor for
select name,xtype from syscolumns where id =
(select id from sysobjects where name = @.table_name)
open table_columns
fetch next from table_columns
into @.column_name,@.column_dtype
while @.@.fetch_status = 0
begin
if @.column_dtype not in ('35','34','99','61','108')
begin
set @.sql2 = ' if update (' + @.column_name + ') and ((select top
1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
set @.sql2 = @.sql2 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql2 = @.sql2 + ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql2 = @.sql2 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql3 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id)'
set @.sql3 = @.sql3 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql4 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, req_id)'
set @.sql4 = @.sql4 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
end
else
begin
set @.sql5 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id)'
set @.sql5 = @.sql5 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid from inserted'
end
set @.sql5 = @.sql5 + ' end'
set @.sql5 = @.sql5 + ' '
end
else if @.column_dtype in ('35','99')
begin
set @.sql5 = @.sql5 + ' if update (' + @.column_name + ')'
set @.sql5 = @.sql5 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql6 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql6 = @.sql6 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql7 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id)'
set @.sql7 = @.sql7 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql8 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, req_id)'
set @.sql8 = @.sql8 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
set @.sql9 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id)'
set @.sql9 = @.sql9 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
set @.sql9 = @.sql9 + ' end'
set @.sql9 = @.sql9 + ' '
end
else if @.column_dtype in ('61','108')
begin
set @.sql9 = @.sql9 + ' if update (' + @.column_name + ') and
((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
set @.sql9 = @.sql9 + ' or (select top 1 ' + @.column_name + '
from inserted) is
null and (select top 1 ' + @.column_name + ' from deleted) is not null'
set @.sql9 = @.sql9 + ' or (select top 1 ' + @.column_name + '
from deleted) is
null and (select top 1 ' + @.column_name + ' from inserted) is not null'
set @.sql9 = @.sql9 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql10 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql10 = @.sql10 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql11 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id)'
set @.sql11 = @.sql11 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql12 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, req_id)'
set @.sql12 = @.sql12 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
set @.sql13 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
set @.sql13 = @.sql13 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
set @.sql13 = @.sql13 + ' end'
set @.sql13 = @.sql13 + ' '
end
else
begin
set @.sql13 = @.sql13 + ' if update (' + @.column_name + ')'
set @.sql13 = @.sql13 + ' begin'
set @.sql13 = @.sql13 + ' insert dbo.audittrail (mod_date,
upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
set @.sql13 = @.sql13 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has been
updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
where r.rowguid = d.rowguid'
set @.sql13 = @.sql13 + ' end'
set @.sql13 = @.sql13 + ' '
end
fetch next from table_columns into @.column_name,@.column_dtype
end
close table_columns
deallocate table_columns
print
@.sql1+@.sql2+@.sql3+@.sql4+@.sql5+@.sql6+@.sql
7+@.sql8+@.sql9+@.sql10+@.sql11+@.sql12+@.
sql13
exec(@.sql1+@.sql2+@.sql3+@.sql4+@.sql5+@.sql6
+@.sql7+@.sql8+@.sql9+@.sql10+@.sql11+@.sq
l12+@.sql13)
fetch next from table_names into @.table_name
end
close table_names
deallocate table_names
go
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:23BE3CE1-009E-4382-B0A9-1D834FE54D09@.microsoft.com...
> Right now it is a sql query that is ran, then the results(output) are
copied
> and pasted into another query window and that query is ran. IT isnt a sp
at
> this point.
> Below is the syntax of the first query
> set quoted_identifier off
> go
> declare @.table_name varchar(50),
> @.column_name varchar(50),
> @.column_dtype varchar(25)
> declare table_names cursor for
> select name from sysobjects where type = 'U' and name not in
> ('dtproperties') And name in (select o.name from sysobjects o inner join
> syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
> o.name not like 'conflict%' and o.name not like 'msm%')
> order by name asc
> open table_names
> fetch next from table_names
> into @.table_name
> while @.@.fetch_status = 0
> begin
> print 'create trigger ' + @.table_name + '_audit_update on ' +
@.table_name
> print 'for update'
> print 'not for replication'
> print 'as'
> print ''
> declare table_columns cursor for
> select name,xtype from syscolumns where id =
> (select id from sysobjects where name = @.table_name)
> open table_columns
> fetch next from table_columns
> into @.column_name,@.column_dtype
> while @.@.fetch_status = 0
> begin
> if @.column_dtype not in ('35','34','99','61','108')
> begin
> print 'if update (' + @.column_name + ') and ((select top 1 '
+
> @.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
> deleted))'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid from inserted'
> end
> print 'end'
> print ''
> end
> else if @.column_dtype in ('35','99')
> begin
> print 'if update (' + @.column_name + ')'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
> from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
> @.table_name + ' r where r.rowguid = d.rowguid'
> end
> print 'end'
> print ''
> end
> else if @.column_dtype in ('61','108')
> begin
> print 'if update (' + @.column_name + ') and ((select top 1 '
+
> @.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
> deleted))'
> print 'or (select top 1 ' + @.column_name + ' from inserted)
is
> null and (select top 1 ' + @.column_name + ' from deleted) is not null'
> print 'or (select top 1 ' + @.column_name + ' from deleted) is
> null and (select top 1 ' + @.column_name + ' from inserted) is not null'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
> from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
> @.table_name + ' r where r.rowguid = d.rowguid'
> end
> print 'end'
> print ''
> end
> else
> begin
> print 'if update (' + @.column_name + ')'
> print 'begin'
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has
been
> updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
> where r.rowguid = d.rowguid'
> print 'end'
> print ''
> end
> fetch next from table_columns into @.column_name,@.column_dtype
>
> end
> close table_columns
> deallocate table_columns
> print 'go'
> fetch next from table_names into @.table_name
> end
> close table_names
> deallocate table_names
> go
>
> I take this output (a create trigger statement for each table in my
> database) and run it against the database. The first query is a script
> generator, when its output is ran the triggers are created.