Executing on a different database.

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?

Although I always suggest not using dynamic SQL its the appropiate way to do some adhoc things:

DECLARE @.SQLText VARCHAR(MAX)
DECLARE @.DatabaseName VARCHAR(MAX)

SET @.DatabaseName = 'Mydatabase'
SET @.SQLTEXT = 'USE ' + @.DatabaseName

SELECT @.SQLTEXT = @.SQLTEXT + ' GO ' + ROUTINE_DEFINTION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE <YourFiltercriteriaHere>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

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

ExecuteNonQuery while dataReader still open

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:

 ...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
 ...

Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAMwhy don't you just do that in a procedure? It looks like it's a simple select then insert right?

Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId )
VALUES
SELECT 28, prodID, TypeID From WhereverYouGetThisFrom

GO|||First of all, thanks for answering so fast!

The main reason that is not inside a storedProcedure is requirements. The entire app is not using SP and this has to be done the same way.

LAM|||>>The entire app is not using SP

looks like you have bigger prbs to worry about.
anyway, to answer your q, since the connection is already open with the datareader, you need to open another connection to do the insert.

hth|||you all are making me cross.

command.CommandText = "Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId ) VALUES SELECT 28, prodID, TypeID From WhereverYouGetThisFrom";

Execute statement?

Hi,
I have an ADO component in a delphi program that I use to check a database
for structure changes that are needed. This is basically just a long list o
f
SQL statements. In one instance I need to add a new field if it doesn't
exist, then update the field with values from another field. When I execute
the code, the program says it can't find the new field. If I take the updat
e
statement out it runs fine. Is there a way to either have the alter command
execute before the update command runs? If this were query analyzer I'd jus
t
use the 'GO' statement, is there an equivalent statement I could use? Here
is the SQL statements I'm using:
if not exists (SELECT *
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('MYTABLE')
AND Name = 'NEWFIELD'
)
begin
alter table mytable
add [NEWFIELD] nvarchar(7);
/* this statement below is what is failing */
update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
end;Two roundtrips, the first one to create the column and the second one to
update if everything was ok. Your application can also call OSQL utility to
execute a batch file.
AMB
"Thread77" wrote:

> Hi,
> I have an ADO component in a delphi program that I use to check a database
> for structure changes that are needed. This is basically just a long list
of
> SQL statements. In one instance I need to add a new field if it doesn't
> exist, then update the field with values from another field. When I execu
te
> the code, the program says it can't find the new field. If I take the upd
ate
> statement out it runs fine. Is there a way to either have the alter comma
nd
> execute before the update command runs? If this were query analyzer I'd j
ust
> use the 'GO' statement, is there an equivalent statement I could use? Her
e
> is the SQL statements I'm using:
> if not exists (SELECT *
> FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('MYTABLE')
> AND Name = 'NEWFIELD'
> )
> begin
> alter table mytable
> add [NEWFIELD] nvarchar(7);
> /* this statement below is what is failing */
> update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
> end;|||You can just do what ISQL is doing. Break up your batches (on the same
connection) on a GO (you will have to add it to your query) and send two
distinct commands to the server.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Thread77" <Thread77@.discussions.microsoft.com> wrote in message
news:E2FA4078-BEDC-4D9F-BB35-75A9289E6EC0@.microsoft.com...
> Hi,
> I have an ADO component in a delphi program that I use to check a database
> for structure changes that are needed. This is basically just a long list
> of
> SQL statements. In one instance I need to add a new field if it doesn't
> exist, then update the field with values from another field. When I
> execute
> the code, the program says it can't find the new field. If I take the
> update
> statement out it runs fine. Is there a way to either have the alter
> command
> execute before the update command runs? If this were query analyzer I'd
> just
> use the 'GO' statement, is there an equivalent statement I could use?
> Here
> is the SQL statements I'm using:
> if not exists (SELECT *
> FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('MYTABLE')
> AND Name = 'NEWFIELD'
> )
> begin
> alter table mytable
> add [NEWFIELD] nvarchar(7);
> /* this statement below is what is failing */
> update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
> end;

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie

Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!Shake things up a bit, and try:use test
GO
sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'
GO
create table t ( a integer )
GO
create procedure can_truncate as
truncate table t
RETURN
GO

grant execute on can_truncate to jmartinez-PatP|||I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @.@.VERSION:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)|||Reading through BOL, I found out this:

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?|||You need to run this code after granting rights:

sp_addrolemember 'db_ddladmin', 'jmartinez'|||But then I'm giving the user privileges to basically *destroy* my db..|||Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.|||You might use
Delete
From <TableName>|||You might use
Delete
From <TableName>

The question is about

what this procedure do is to truncate a table on which the user has no access