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
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Thursday, March 29, 2012
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 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?
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 :
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
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 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
>
>
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
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 =)
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
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
I have sp in which i run dbcc inputbuffer.
Can I, and how
grant permissions to somebody whois 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 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 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.
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
@.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.
executing querys from a batch file
Someone asked me a curious question.
Can I execute a query to sql server from a bat file? how?
If not is there a simple scripting laguage that this person might use to
drive his process that is similar to a dos bat file?William Kossack (kossackw@.njc.org) writes:
> Someone asked me a curious question.
> Can I execute a query to sql server from a bat file? how?
> If not is there a simple scripting laguage that this person might use to
> drive his process that is similar to a dos bat file?
You can use the command-line tool OSQL for this.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Can you point me to an example or somewhere to look online?
Erland Sommarskog wrote:
>William Kossack (kossackw@.njc.org) writes:
>
>>Someone asked me a curious question.
>>
>>Can I execute a query to sql server from a bat file? how?
>>
>>If not is there a simple scripting laguage that this person might use to
>>drive his process that is similar to a dos bat file?
>>
>>
>You can use the command-line tool OSQL for this.
>
>|||http://msdn.microsoft.com/library/d...mta_01_5zxi.asp
--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"William Kossack" <kossackw@.njc.org> wrote in message
news:11fktjioc9cp78d@.corp.supernews.com...
> Can you point me to an example or somewhere to look online?
> Erland Sommarskog wrote:
>>William Kossack (kossackw@.njc.org) writes:
>>
>>>Someone asked me a curious question.
>>>Can I execute a query to sql server from a bat file? how?
>>>
>>>If not is there a simple scripting laguage that this person might use to
>>>drive his process that is similar to a dos bat file?
>>>
>>
>>You can use the command-line tool OSQL for this.
>>
>>
>>
>|||and
http://msdn.microsoft.com/library/d...mta_01_2q61.asp
--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"William Kossack" <kossackw@.njc.org> wrote in message
news:11fktjioc9cp78d@.corp.supernews.com...
> Can you point me to an example or somewhere to look online?
> Erland Sommarskog wrote:
>>William Kossack (kossackw@.njc.org) writes:
>>
>>>Someone asked me a curious question.
>>>Can I execute a query to sql server from a bat file? how?
>>>
>>>If not is there a simple scripting laguage that this person might use to
>>>drive his process that is similar to a dos bat file?
>>>
>>
>>You can use the command-line tool OSQL for this.
>>
>>
>>
>|||For an example how to embed a SQL script within a batch script so that you end up with only one file look here:
http://dostips.cmdtips.com/DtCodeInterfacing.php
Executing Procedures through LInked Server
I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
|||I have already configured the other server for remote access and reconfigured
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:
> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigured
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
|||Does not work .
"Sue Hoegemeier" wrote:
> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
sql
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
|||I have already configured the other server for remote access and reconfigured
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:
> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigured
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
|||Does not work .
"Sue Hoegemeier" wrote:
> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
sql
Executing Procedures through LInked Server
I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.|||I have already configured the other server for remote access and reconfigure
d
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:
> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigur
ed
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
>|||Does not work .
"Sue Hoegemeier" wrote:
> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
>
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.|||I have already configured the other server for remote access and reconfigure
d
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:
> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigur
ed
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
>|||Does not work .
"Sue Hoegemeier" wrote:
> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
>
Executing procedure for empty result set return
I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]
Subscribe to:
Posts (Atom)