Executing SSIS Package - SQL 2005 Express

Hi,

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

Thanks

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

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

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

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

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

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

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

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

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

Sorted!

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

Executing SQL Stored Procedures in VB

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

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

Jens K. Suessmeyer

http://www.sqlserver2005.de

Executing SQL Stored Procedures in VB

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

Quote:

Originally Posted by SQLusername

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

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

Quote:

Originally Posted by ck9663

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

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

Executing sql statements

When i execute the following in Sql query anlyzer

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

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

set @.dbname='DB_kms_prv'

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

exec (@.str)

I get error message

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

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

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

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

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

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

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

Simonsql

Executing SQL Statement from flat file

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

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

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

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

Thanks

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

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

Thanks|||

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

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

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

|||

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

thanks for the help

Executing SQL Statement from flat file

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

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

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

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

Thanks

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

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

Thanks|||

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

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

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

|||

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

thanks for the help

Executing SQL Server 2005 stored procedures on Windows 2003

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

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

123.sh sp_my_stored_procedure input_parm1 input_parm2

123.sh sp_different_stored_procedure input_parm1

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

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

Thanks, Jim

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

You can find it here

Adamus

|||

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

Documentation is available here.

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

Executing SQL scripts from files

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

Thanks, Radco

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

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

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

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

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

Thanks

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

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

Thanx
Veeru.SqlCommand.ExecuteNonQuery()

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

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

executing SQL script from Command promt not working

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

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

(uid=sa and pwd=)

and I got the result like this :

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

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

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

thank for the response.

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

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

Executing SQL Package

Hi

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

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

looking for responses.

Thanks
GaneshIt can also be executed from query analyzer.

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

Thanks for your response.

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

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

Thanks,
Ganesh

Executing SQL held in a Text column

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

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

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

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

executing sql file

I want to know,

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

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

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

its working fine but it uses the dos command shell.

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

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

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

Hope for help

Regards,

thanks.

Gurpreet S. Gill

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

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

type in

sqlcmd /? from command prompt for more help

|||

Imtiaz--

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

but i want from SQL Server Managment

Regards,

Thanks.

Gurpreet S. Gill

|||

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

Steps:

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

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

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

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

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

|||

Paul--

Thanks

that`s really gr8.

This is what i want.

Regards,

Thanks.

Gurpreet S. Gill

Executing SQL command when user is connecting

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

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

Executing SQL command when user is connecting

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

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

Executing SQL clause in extended stored procedure

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

EXECuting sps within sps (using return params)

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

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

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

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

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

Hope that will somebody else too...sql

Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

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

Exec ('SP_SpaceUsed '+ @.table)

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

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

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

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

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

Could someone give me a clue

Thanx,

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

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

Simon

executing sp_changeownerdb

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

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

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