Executing sql statements

When i execute the following in Sql query anlyzer

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

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

set @.dbname='DB_kms_prv'

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

exec (@.str)

I get error message

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

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

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

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

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

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

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

Simonsql

Executing SQL Statement from flat file

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

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

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

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

Thanks

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

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

Thanks|||

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

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

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

|||

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

thanks for the help

Executing SQL Statement from flat file

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

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

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

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

Thanks

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

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

Thanks|||

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

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

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

|||

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

thanks for the help

executing sql 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 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.

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 multiple query at one time

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = newSqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again an\d agin for each and every query. can iot be done like the given above or can there be some better method?

thanz in advance..

divya

What about creating a static method, passing in the connection and the things that can change in the query, composing query query within the method and executing this thing asynchronously ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Executing more than one query

Hi,

How to execute more than one query consecutively in SqlDataSource like the picture shown below.

you could put multiple statements together by seperating them with a semicolon:

insert blah into blah; insert blah2 into blah2; insert somethingelse into somewhereelse;

|||

Hello my friend,

You should be able to put 1 INSERT/UPDATE/DELETE statement one after the other. Another approach would be to specify a stored procedure to do the INSERT/UPDATE/DELETE and then within the stored procedure have multiple INSERT/UPDATE/DELETE commands. Below is an example SQL script that will create a stored procedure: -

CREATE PROCEDURE usp_InsertClientWithPreference
(
@.ClientName AS VARCHAR(250),
@.ContactNumber AS VARCHAR(20),
@.VacationCountry AS VARCHAR(250)
)

AS

INSERT INTO tblClient (ClientName, ContactNumber)
VALUES (@.ClientName, @.ContactNumber)

UPDATE tblFavourites SET Total = (Total + 1)
WHERE Country = @.VacationCountry

RETURN

Kind regards

Scotty

|||

Thanks sirs..You showed your best quality as always you show

Regards..

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peterHi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:

>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The proble
m
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.
4ax.com...
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
>
problem[vbcol=seagreen]
the[vbcol=seagreen]
time[vbcol=seagreen]
>|||Start by using Profiler to see if you get the same execution plan between th
e executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a cli
ent app issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.g
bl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The probl
em
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
using[vbcol=seagreen]
~4000[vbcol=seagreen]
problem[vbcol=seagreen]
the[vbcol=seagreen]
time[vbcol=seagreen]
>|||If you check Books Online, you will find following for the "Audit Logout" ev
ent:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query ex
ecutes in the same time, the added
time for the logout is the time for the client to process the results before
it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You
might want to post this (with
appropriate details) to an ADO.NET group...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx
.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted take
s
> about the same amount of time for each execution (~2200) the Audit Logout
is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> the executions. Depending on whether
> client app issue.
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> using
> ~4000
> problem
> the
> time
>|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
takes[vbcol=seagreen]
Logout is[vbcol=seagreen]
in[vbcol=seagreen]
between[vbcol=seagreen]
a[vbcol=seagreen]
from[vbcol=seagreen]
4,5[vbcol=seagreen]
>sql

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peter
Hi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:

>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>
|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com... [vbcol=seagreen]
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
problem[vbcol=seagreen]
the[vbcol=seagreen]
time
>
|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>
|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
using[vbcol=seagreen]
~4000[vbcol=seagreen]
problem[vbcol=seagreen]
the[vbcol=seagreen]
time
>
|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> the executions. Depending on whether
> client app issue.
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> using
> ~4000
> problem
> the
> time
>
|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
takes[vbcol=seagreen]
Logout is[vbcol=seagreen]
in[vbcol=seagreen]
between[vbcol=seagreen]
a[vbcol=seagreen]
from[vbcol=seagreen]
4,5
>

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peterHi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
--
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:
>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com...
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
> >Hi all,
> >I've developed an app (C#) that connects to SQL Server 2000 without using
> >thread pooling (using SqlConnection) and I run a query that returns ~4000
> >tuples each time it runs and displays the results in a listview. The
problem
> >is that after I run it 3-4 times the results are read very slowly from
the
> >DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> >it takes more than 2 minutes! What am I doing wrong? Are there any
> >in-between buffers that need to be emptied?
> >
> >Thanks,
> >-peter
> >
>|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> > I've developed an app (C#) that connects to SQL Server 2000 without
using
> > thread pooling (using SqlConnection) and I run a query that returns
~4000
> > tuples each time it runs and displays the results in a listview. The
problem
> > is that after I run it 3-4 times the results are read very slowly from
the
> > DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> > it takes more than 2 minutes! What am I doing wrong? Are there any
> > in-between buffers that need to be emptied?
> >
> > Thanks,
> > -peter
> >
> >
>|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > Start by using Profiler to see if you get the same execution plan between
> the executions. Depending on whether
> > you do or not, you can determine whether this is a SQL Server issue or a
> client app issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > > I've developed an app (C#) that connects to SQL Server 2000 without
> using
> > > thread pooling (using SqlConnection) and I run a query that returns
> ~4000
> > > tuples each time it runs and displays the results in a listview. The
> problem
> > > is that after I run it 3-4 times the results are read very slowly from
> the
> > > DB. The first times the results are displayed in 1-2 secs but the 4,5
> time
> > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > in-between buffers that need to be emptied?
> > >
> > > Thanks,
> > > -peter
> > >
> > >
> >
> >
>|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> > well i checked it with the profiler and while the SQL:BatchCompleted
takes
> > about the same amount of time for each execution (~2200) the Audit
Logout is
> > different and when the program slows down it takes numbers up to ~584263
> > while a normal execution takes up to ~20296.
> >
> > What does this mean?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > > Start by using Profiler to see if you get the same execution plan
between
> > the executions. Depending on whether
> > > you do or not, you can determine whether this is a SQL Server issue or
a
> > client app issue.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> > news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > > Hi all,
> > > > I've developed an app (C#) that connects to SQL Server 2000 without
> > using
> > > > thread pooling (using SqlConnection) and I run a query that returns
> > ~4000
> > > > tuples each time it runs and displays the results in a listview. The
> > problem
> > > > is that after I run it 3-4 times the results are read very slowly
from
> > the
> > > > DB. The first times the results are displayed in 1-2 secs but the
4,5
> > time
> > > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > > in-between buffers that need to be emptied?
> > > >
> > > > Thanks,
> > > > -peter
> > > >
> > > >
> > >
> > >
> >
> >
>

executing analysis services query via openrowset

Can you kindly tell me what settings are required to execute an MDX statement via openrowset.

Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.

The following is the query i am using

SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,

"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,

ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,

ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,

ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe

FROM OpenRowset('MSOLAP.3',

'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',

'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,

NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS

FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&), ([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].[All].UNKNOWNMEMBER)}')

Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?

Warm regards,

Sudhir

I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||

On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005

On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.

Executing an SSIS package containing a Data Mining Query task from a SQL job

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.

There is a comprehensive KB article that may cover your question:

http://support.microsoft.com/kb/918760

|||

Thanks, that has helped.

for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.

this is the only option on the KB article that worked for me.

Thanks.

Executing an application from a query

Hi All,
Is it possible to execute a file or perhaps send a call to a
webpage using an SQL query? Does anyone know of any such feature.
Basically I need to call a webpage(coldfusion script) from my query and
I am wondering if anyone knows of any way that his would be possible.
Thanks a heap in advance for all your help !
Harkirat>> Is it possible to execute a file or perhaps send a call to a webpage
Can you be more specific? SQL Queries are executed on the server while web
pages are displayed on the client. So what exactly do you mean by "calling"
a webpage? Are you trying to display the webpage on the client or somewhere
else? SQL Server has some provisions for generating web pages based on
generated resultsets, but I am not sure that is what you are looking for.
Anith|||Hi Anith,
What I need to do is be able to execute a coldfusion script
via a query. The script can be run via a webpage e.g.
http://mysite.com/CFScript.cfm
So if I could make a 'call' to my webpage via my query that would work.
Also if this is not a possibility would it be possible to execute a
.exe file using a query? That might help too.
Thanks for your reply.
Harkirat
Anith Sen wrote:
> Can you be more specific? SQL Queries are executed on the server while web
> pages are displayed on the client. So what exactly do you mean by "calling
"
> a webpage? Are you trying to display the webpage on the client or somewher
e
> else? SQL Server has some provisions for generating web pages based on
> generated resultsets, but I am not sure that is what you are looking for.
> --
> Anith|||You can use a stored procedure to execute an .exe file using
xp_cmdshell, but there are security issues. I don't think that it;s
the best approach to what you are trying to do, however; what does a
cold fusion script have to do with your data?
Can you provide a little more detail about the business problem you're
trying to solve, and perhaps we can suggest a better alternative.
Stu|||Hi Stu,
My coldfusion script has logic that
updates a table in the database. I need this process to run everytime a
new row is inserted in my table. Hence I was thinking of putting an
insert trigger that calls this coldfusion script.
Can you give an example on how to execute a .exe using a stored
procedure. I could perhaps make a .exe that executes the coldfusion
script which in turn would be called via my trigger.
Thanks.
Harkirat|||Hi
Look at xp_cmdshell in BOL.
If your exe fails, the insert will get rolled back as an error like that can
not be handled in T-SQL. It is not a good idea. Rather write a row in a
queue table, and have something poll the table and then call your exe.
Regards
--
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/
"harry" <i1073@.tamu.edu> wrote in message
news:1124657414.378047.144430@.g44g2000cwa.googlegroups.com...
> Hi Stu,
> My coldfusion script has logic that
> updates a table in the database. I need this process to run everytime a
> new row is inserted in my table. Hence I was thinking of putting an
> insert trigger that calls this coldfusion script.
> Can you give an example on how to execute a .exe using a stored
> procedure. I could perhaps make a .exe that executes the coldfusion
> script which in turn would be called via my trigger.
> Thanks.
> Harkirat
>|||I think your application would perform better if you could keep the
database logic at the dataase level; if it were me, I would probably
simply run the logic in T-SQL as part of the trigger. Why bubble back
up (unless of course your coldfusion script is extremely complicated)?
As for an example, I don't write many triggers, and I disable
xp_cmdshell altogether. Books OnLine is your best bet.
Stu|||Hi Stu,
My coldfusion is indeed complicated thats why I don't wish
to do it in SQL.
Thanks for your help.
Harkirat|||Hi Mike,
I had already thought of that. Seems like my best bet
now.
Thanks for your help.
Harkirat

Executing a Views from a Trigger

Hi,

I am new to using SQL. I want to be able to exucute a query that I
place in a view. I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Thank you.

PhilippeForgot to mention, I am using SQL 2000.

Thank you.|||Hi,

> I am new to using SQL. I want to be able to exucute a query that I
> place in a view.

You don't execute queries inside views. You just get to use the
view as if it were a table.

Why would you want to "execute the query"?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01|||Hi

What exactly do you mean by "call the views every time the data change in
the selected table."?

When the data is added/updated/deleted from the underlying table the View is
automatically updated. (It is just a select statement).

Are you concerned about updating the user interface where the View is used
as a source for a datgrid or something. If so you have to find some way to
tell the Client program to requery the database, perhaps using the View.
when a database update occurs.

--
-Dick Christoph
<solidsna2@.gmail.com> wrote in message
news:1141226630.058373.30490@.e56g2000cwe.googlegro ups.com...
> Hi,
> I am new to using SQL. I want to be able to exucute a query that I
> place in a view. I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01
> Thank you.
> Philippe|||I am working with an application that use SQL to store it's data. What
I am trying to achieve is to execute the query below every hour.

UPDATE Tbl1
SET Tbl1.Flag = '0'
WHERE Tbl1.Flag <> '0'

UPDATE Tbl1
SET Flag = '1'
WHERE Tbl1.Key = (SELECT Tbl2.Key
FROM Tbl2
WHERE Tbl2.Key = Tbl1.Key
AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
BY Tbl2.Key)

DELETE Tbl2
WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate

The Query does exactly what I want it to do. Now I want to launch this
query from the application I was talking about earlyer. So I thought
that if I use a trigger that will be iniated by a script in my
application by changing a value to 1 or 0 every hour. This will then
activate the SQL trigger that will execute the query above. I strore
the query in a views. So that is why I ask how to execute a views with
a trigger.

Maybe this is not the way to go, but I hope you can understand what I
am trying to do.

Thank you again.

Philippe|||Hi

If you want to execute this every hour, this would be better handled in a
scheduled job. Which can be scheduled to run a SQL Statement or stored
procedure every hour.

Although you could schedule the application to run an update query (without
a Trigger or View) every hour, what if the application isn't running all the
time? or if there are muttiple instances of it running?

One point of clarification you cannot execute and Update Query in a View.

-Dick Christoph

--
<solidsna2@.gmail.com> wrote in message
news:1141233438.780742.274780@.t39g2000cwt.googlegr oups.com...
>I am working with an application that use SQL to store it's data. What
> I am trying to achieve is to execute the query below every hour.
> UPDATE Tbl1
> SET Tbl1.Flag = '0'
> WHERE Tbl1.Flag <> '0'
> UPDATE Tbl1
> SET Flag = '1'
> WHERE Tbl1.Key = (SELECT Tbl2.Key
> FROM Tbl2
> WHERE Tbl2.Key = Tbl1.Key
> AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
> BY Tbl2.Key)
> DELETE Tbl2
> WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate
> The Query does exactly what I want it to do. Now I want to launch this
> query from the application I was talking about earlyer. So I thought
> that if I use a trigger that will be iniated by a script in my
> application by changing a value to 1 or 0 every hour. This will then
> activate the SQL trigger that will execute the query above. I strore
> the query in a views. So that is why I ask how to execute a views with
> a trigger.
> Maybe this is not the way to go, but I hope you can understand what I
> am trying to do.
> Thank you again.
> Philippe|||How do I run a SQL statement or stored procedure every hour?

It can not run the the query all the time. I am doing other
manipulation withing my application. The 2 jobs needs to be
synchronise +- 5 min.

Thank you again for the help.

Philippe|||(solidsna2@.gmail.com) writes:
> How do I run a SQL statement or stored procedure every hour?

As Dick said, schedule a job to run from SQL Server Agent.

> It can not run the the query all the time. I am doing other
> manipulation withing my application. The 2 jobs needs to be
> synchronise +- 5 min.

In SQL Agent, a job can have several steps that executed in order. This
may be something for you.

It is difficult from your abstract definition to say whether you are
on the right track at all. If you can describe the underlying business
problem, it is more likely that you will get useful advice.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx