How to execute a store procesure using a link server to oracle

Is it possible to execute a store procedure via a link server to oracle.
I tried these two options but none of them worked.
select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
Deepak VermaDverma,
Can you execute the sp directly as with SQL Server?
EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
i.e.,
EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
HTH
Jerry
"dverma" <dverma@.discussions.microsoft.com> wrote in message
news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
> Is it possible to execute a store procedure via a link server to oracle.
> I tried these two options but none of them worked.
> select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
> select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
>
> --
> Deepak Verma|||If this linked server were a sql server then there is no problem, I would
like to know specific answer against a ORACLE database.
Deepak Verma
"Jerry Spivey" wrote:

> Dverma,
> Can you execute the sp directly as with SQL Server?
> EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
> i.e.,
> EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
> HTH
> Jerry
> "dverma" <dverma@.discussions.microsoft.com> wrote in message
> news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
>
>

How to execute a SSIS package from VS 6.0?

Dear all,

I wrote this post in another category but the answer did not satisfied so that I'd like to know if really anyone has ever used or experienced with this possibility. No by .Net language rather than Vb 6.0 or ASP or even instanciacing DMO library.

Thanks a lot for your support,

According this link http://support.microsoft.com/?kbid=817248 it seems possible but I haven't idea if possible keeping in mind that it has been made by yourself no for others... So that, I mean, these assemblies comes along with Sql Server 2005 installation.

sql

How to execute a SSIS dtsx package from an asp.net 2.0 application?

I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried -

 Dim app As New Application() ' ' Load package from file system ' Dim package As Package = app.LoadPackage("c:\ssis\Package.dtsx", Nothing) 'package.ImportConfigurationFile("c:\ExamplePackage.dtsConfig") 'Dim vars As Variables = package.Variables 'vars("MyVariable").Value = "value from c#" Dim result As DTSExecResult = package.Execute() lblResult.Text = "Package Execution results: {0} " & result.ToString()

All I get is a message 'Failure'.

Does anyone have an example of how to do this?

Your requirement is different and you are doing in wrong way. The mentioned code run the package on the same machine.

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1801974&SiteID=1

|||

ok I've gone for option number 3 on your first link which uses a webservice to execute the package and then I can run the webservice through my asp.net page.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

This is my webservice - the example taken from that page,

<WebMethod()> _Public Function LaunchPackage( _ByVal sourceTypeAs String, _ByVal sourceLocationAs String, _ByVal packageNameAs String)As Integer'DTSExecResultDim packagePathAs String Dim myPackageAs PackageDim integrationServicesAs New Application' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName)Select Case sourceTypeCase"file"' Package is stored as a file. ' Add extension if not present.If String.IsNullOrEmpty(Path.GetExtension(packagePath))Then packagePath =String.Concat(packagePath,".dtsx")End If If File.Exists(packagePath)Then myPackage = integrationServices.LoadPackage(packagePath,Nothing)Else Throw New ApplicationException( _"Invalid file location: " & packagePath)End If Case"sql"' Package is stored in MSDB. ' Combine logical path and package name.If integrationServices.ExistsOnSqlServer(packagePath,".",String.Empty,String.Empty)Then myPackage = integrationServices.LoadFromSqlServer( _ packageName,"(local)",String.Empty,String.Empty,Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case"dts"' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB.If integrationServices.ExistsOnDtsServer(packagePath,".")Then myPackage = integrationServices.LoadFromDtsServer(packagePath,"localhost",Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are'file', 'sql', and 'dts'.")End Select Return myPackage.Execute()

and my asp.net page

Protected Sub btnBuildDW_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnBuildDW.Click ExecutePackage()End Sub Protected Sub ExecutePackage()Dim launchPackageServiceAs New SCCBuildDW.SCCBuildDWDim packageResultAs Integer Try packageResult = launchPackageService.LaunchPackage("file","c:\ssis","Package")Catch exAs Exception' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException lblResult.Text ="The following exception occurred: " & ex.MessageEnd Try lblResult.Text = packageResult.ToString'CType(packageResult, PackageExecutionResult).ToString ' Console.ReadKey()End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelledEnd Enum

I'm getting PackageFailed. The webservice runs and returns 1 - which must be PackageFailed.

Any ideas what I'm doing wrong? The webservice is using windows authentication and the folder the package is in has Everyone Full Control.

|||

I've made some progress. It was failing due to permissions, I've altered these and can now execute a package containing a stored procedure. However my original package runs a stored procedure and then an Analysis Services Task. This still fails - I've checked the user is a member of the role that has access to run the task.

Is there something else I need to do?

|||

I'm still stuck on this - can anyone offer any help?

|||

I have this working now on my local machine. As soon as I move it to the live server I get PackageFailed when I attempt to run it. Has anyone experienced similar problems?

Given the amount of information on the internet I'm beginning to think I'm the only one who wants to execute SSIS in ASP.NET!

|||

i have similar issues too. maybe running ssis dtsx package using web services is not a good idea at all. especially if we run into so many authentication issues.

what about using sql server agent to run ssis package!

|||

I'm now using a stored procedure to execute the package and this works without problems.

Details of my code can be found here -http://forums.asp.net/p/1149972/1873190.aspx#1873190

|||

I'm also having the same problems you have described. I don't have the option of executing from a stored procedure.

I noticed that when I use the <identity impersonate="true"> and specified the username and password, that I got much further, but the package still fails.

I'm guessing that I'll probably end up adjusting the permissions on certain directories and so forth until this works.

|||

Hi all,

this might be useful.

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1518271

I tried using this and it works.

How to Execute a SQL (.sql) script from within VB?

Hello,

I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.

SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.

Anyone have a suggestion (besides reformatting evey like and hard-coding it?)

Thanks a ton!read it into a string, and execute it as normal.|||You can read the SQL into a string and give it to a SqlCommand class for ExecuteNonQuery.

One tip: the "GO" batch delimiter is not T-SQL, it is a signal to Query Analyzer. Many script generation tools will place the "GO" delimiter into script files to tell QA where to split up the commands. You'll need to parse the script and execute individual batches as you encounter GO batch delimiters.sql

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a selectcommand on button click?

Hello friends

Can anyone tell me the means by which I can populate a label or a datagrid by executing a selectcommand statement on the form of my webpage. And what is the best way to retrieve data from a SQL datasource, is it by using separate sqldatasources for each different parts of a page that requires different data from different tables in the same database or by using a tableadapter to retrieve the data for the best performance?

Thanks for replying for my previous questions. It was helpful and great in my program implementation.

Thank you

Hi

When you bind the sqldatasource to datagrid selectcommand will be automatically executed if you call databind().

As for difference of SqlDataSource and tableadapter it largely depends on the implementation you could take a look at:

DataSets vs. Collections

How to execute a saved package?

Sorry for such a simple question -

I ran the Import data wizard and chose to save the package to sql server. Now I would like to execute that package I saved again, but I can't find where to do it in the management studio. Help!

Open SQL Server Management Studio - Select "Integration Services" in the Connect option.
Expand "Stored Packages" - Expand "MSDB".

You will find your saved package there.

Thanks,
Loonysan

How to execute a Package?

Hallo

I created my first Package and i am not able to deploy it in my SQL server.

I created a Development Utility and in the OutputPath i got a *.dtsx and a *.SSISDevelopmentManifest file.
When i run the packaage (*.dtsx) it works but when i try to install the package with *.SSISDevelopmentManifest it just genereates a folder in the selected Folder
(...\Microsoft SQL Server\90\DTS\Packages\) and no more.

What am i missing?

All the "Deployment" does is copy the *.dtsx files to the file system or MSDB on the target SQL server. You apparently selected "file system". It did exactly what it was suppose do.

Now you need to connect to the "Integration Services" on the server and look under "File System" and you will see the SSIS packages.|||

Hi soanfu,

When using the Deployment Utility, a directory is created in the Microsoft SQL Server\90\DTS\Packages\ folder. This happens regardless of where your package gets deployed. If the package gets deployed to the file system, the dtsx file will be copied into this directory. If you deploy to SQL Server, the directory is created but remains empty.

As someone else pointed out, connecting to the Integration Services instance will allow you to view all packages deployed to a server - both file system and SQL Server. To connect, open SQL Server Management Services. In the Object Explorer, click the Connect button. Select Integration Services and log in. You should be able to view packages stored to the SQL Server under the Stored Packages\MSDB folder.

Hope this helps,
Andy

|||

Thanks for the tip!

I didn’t know that there is a “Integration Services".

But know I have one more question.

In “Sql Server 2000” we could program the execution of DTS Packages with the option Tasks. How can I do it in SQL Server 2005?

|||You mean, to schedule it? You could use SQL Server Agent for that.

How to execute a dynamic SQL with integer parameter for stored procedure?

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.

Error: Unterminated String Constant.

What is the problem?

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By '+ @.SortExpression+') as Row_Count,Rank() Over (Order By '+ @.SortExpression+') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between '+@.startRowIndex+' and '+ @.maxRowIndex+' ';
Exec(@.strSQL);

Can you please try:

Where Row_Count Between ' +Convert(Varchar,@.startRowIndex)+' and '+Convert(Varchar,@.maxRowIndex)+''

|||

Try

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By ' + @.SortExpression + ') as Row_Count,Rank() Over (Order By ' + @.SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between ' +CONVERT(VARCHAR(10),@.startRowIndex) -- the integer needs to be converted to varchar for the + to be a concatenate!
+ ' and ' +CONVERT(VARCHAR(10),@.maxRowIndex) + ' ';
PRINT @.strSQL -- Comment this out once the TSQL is generated correctly
Exec(@.strSQL);

|||

It works! I was converting it to an integer with the convert function. I didn't realized I had to convert it as a string..

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florencelee
If you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:

> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>
|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?

>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp[vbcol=seagreen]
>table. The #Temp table should be defined before.
>"florencelee" wrote:
the
>.
>
|||See my other reply...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> then process the temp
> the

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florenceleeIf you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:
> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?
>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp
>table. The #Temp table should be defined before.
>"florencelee" wrote:
>> Hi,
>> I have 3 string
>> select @.string = 'SELECT'
>> select @.string2 ='colname'
>> select @.string3 ='tablename'
>> select @.finalstring = @.string + @.string2 + @.string3
>> inside the user define function, we can not put in
>> exec command, how we run this dynamic sql command in
the
>> user define function?
>> thank you.
>> regards,
>> florencelee
>.
>|||See my other reply...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> >--Original Message--
> >If you want the output of this dynamic SQL in a table,
> >you can use "insert into #Temp exec(@.finalstring)" and
> then process the temp
> >table. The #Temp table should be defined before.
> >
> >"florencelee" wrote:
> >
> >> Hi,
> >> I have 3 string
> >>
> >> select @.string = 'SELECT'
> >> select @.string2 ='colname'
> >> select @.string3 ='tablename'
> >>
> >> select @.finalstring = @.string + @.string2 + @.string3
> >>
> >> inside the user define function, we can not put in
> >> exec command, how we run this dynamic sql command in
> the
> >> user define function?
> >>
> >> thank you.
> >> regards,
> >>
> >> florencelee
> >>
> >.
> >sql

How to execute a DTS Via a stored procedure

Hi I have created a DTS package, it runs well, but i would like to execute it in a storeprocedure

Is this possible and if so where do i start from, I have looked all over for it tho every where i look creates a DTS from a store procedure.

I need desperate help

Thanks in advanvcehere is an excellent solution for you

http://p2p.wrox.com/topic.asp?TOPIC_ID=16647

How to execute a DTS Package from inside a job

Good day!

Just wanted to ask how can I execute a DTS package from inside a scheduled job.

I have 3 DTS Package, so instead of scheduling the 3 package separately...I created a job to execute the 3 packages in a single scheduled time.

My syntax inside the command window is:
EXEC DTSPackageName

But its not working.
Any help is greatly appreciated.
Thanks.you can rightclick the package and schedule it.

or go to run --> dtsrunui --> advanced and generate the code to be put into the SQL Job. Do this for each package and then just copy and paste each of the code into the job steps.|||Hi!

I just did what you instructed me. But a syntax error occurred.
This is what i've pasted in the job step.

DTSRun /S "SQL2K-VBP" /N "FFPJLC_TO_CMS" /G "{7DE00045-4E3C-445D-A0B7-BA95370EB072}" /W "0" /E

Thanks for taking the time to answer my questions.

God bless. :)|||the job step is operating system command

run the dtsrunui and schedule the package, it will create the job automatically with the package you choose. then look into the job step to see how it is coded.|||DTSRun /S "SQL2K-VBP" /N "FFPJLC_TO_CMS" /G "{7DE00045-4E3C-445D-A0B7-BA95370EB072}" /W "0" /E

God bless. :)
Look up DTSRun in Books OnLine. When you set up the job step, make it an "operating system command". Here is an example (/E means trusted login
i.e. Windows authentication):

DTSRun /S ServerName /N "DTS Job Name" /E|||Hi!

Thank you so much guys for helping me out about this DTS Package scheduling. The job is working well now.

God bless. :) ;)

How to Execute a DiffGram

This article on MSDN:

"Microsoft SQL Server 9.0 Technical Articles, XML Options in Microsoft SQL Server 2005"

states that it is possible to execute a DiffGram using SqlXmlCommand. However, when I execute this code:

static string NorthwindConnString = "Provider=SQLOLEDB;Server=NJSQL06;database=Interlink_GISLink90;Integrated Security=SSPI";
SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.CommandStream = new FileStream(@."C:\Development\SQLXML\ApplyDiffGram\ApplyDiffGram\bin\Debug\DiffGramFile.xml", FileMode.Open, FileAccess.Read);
cmd.CommandType = SqlXmlCommandType.DiffGram;
cmd.SchemaPath = @."C:\Development\SQLXML\ApplyDiffGram\ApplyDiffGram\bin\Debug\XSDTypedSchema.xsd";
cmd.ExecuteNonQuery();

I get this error message:

"Empty update, no updatable rows found"

My DiffGram and mapping-schema are pasted below. Does anyone know what I'm doing wrong?

--Nancy

This is my DiffGram:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="XSDTypedSchema.xsd">
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<Interlink_GISLink90DataSet>
<GISVessel diffgr:id="GISVessel1" msdata:rowOrder="0" diffgr:hasChanges="modified">
<Vessel_id>2578</Vessel_id>
<Name>SHENG FENG</Name>
<DOT_Number>2578</DOT_Number>
<gb_DB_ID>71</gb_DB_ID>
<gb_DBID_PK>2578</gb_DBID_PK>
<gb_DB_OWNER_ID>156</gb_DB_OWNER_ID>
<Logical_Cancel_Value>0</Logical_Cancel_Value>
<GB_DB_TimeStamp>2002-04-24T14:11:00.943Z</GB_DB_TimeStamp>
<Alias_Name>SHENG FENG </Alias_Name>
<Year_Built>1995</Year_Built>
<Ex_Name>SHENG FENG</Ex_Name>
<Freight_Code>SHFEN</Freight_Code>
<Vessel_State>C</Vessel_State>
<Comments>John Adams</Comments>
<Ownership_Type_id>4</Ownership_Type_id>
</GISVessel>
</Interlink_GISLink90DataSet>
<diffgr:before>
<GISVessel diffgr:id="GISVessel1" msdata:rowOrder="0">
<Vessel_id>2578</Vessel_id>
<Name>SHENG FENG</Name>
<DOT_Number>2578</DOT_Number>
<gb_DB_ID>71</gb_DB_ID>
<gb_DBID_PK>2578</gb_DBID_PK>
<gb_DB_OWNER_ID>156</gb_DB_OWNER_ID>
<Logical_Cancel_Value>0</Logical_Cancel_Value>
<GB_DB_TimeStamp>2000-01-12T10:09:00.943Z</GB_DB_TimeStamp>
<Alias_Name>SHENG FENG </Alias_Name>
<Year_Built>1995</Year_Built>
<Ex_Name>SHENG FENG</Ex_Name>
<Freight_Code>SHFEN</Freight_Code>
<Vessel_State>C</Vessel_State>
<Comments>Edmund Burke</Comments>
<Ownership_Type_id>4</Ownership_Type_id>
</GISVessel>
</diffgr:before>
</diffgr:diffgram>
</ROOT>

This is my mapping schema inside XSDTypedSchema.xsd:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="GISVessel">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Vessel_id" type="xs:int" />
<xs:element name="Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Lloyds_No">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="7" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="DOT_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="16" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Call_Sign">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="9" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_OWNER_ID" type="xs:int" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" />
<xs:element name="Alias_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Net_Tonnage_mt" type="xs:double" />
<xs:element minOccurs="0" name="Gross_Tonnage_mt" type="xs:double" />
<xs:element minOccurs="0" name="Registry_Flag">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Vessel_Registration_Date" type="xs:dateTime" />
<xs:element minOccurs="0" name="Vessel_Registry_Location_id" type="xs:int" />
<xs:element minOccurs="0" name="Place_Built_Country_id" type="xs:int" />
<xs:element minOccurs="0" name="Year_Built" type="xs:int" />
<xs:element minOccurs="0" name="Official_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="12" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Ex_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Vessel_Type">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="IMO_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="12" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Hull_Number" type="xs:int" />
<xs:element minOccurs="0" name="Building_Yard_id" type="xs:int" />
<xs:element minOccurs="0" name="Freight_Code">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_State">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Registry_Country_id" type="xs:int" />
<xs:element minOccurs="0" name="Comments">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="500" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Ownership_Type_id" type="xs:int" />
<xs:element minOccurs="0" name="GVA_Hull_Id" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="GISVoyage">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Voyage_id" type="xs:int" />
<xs:element name="Vessel_id" type="xs:int" />
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_OWNER_ID" type="xs:int" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" />
<xs:element minOccurs="0" name="Approved_Date" type="xs:dateTime" />
<xs:element name="Last_Update_Date" type="xs:dateTime" />
<xs:element minOccurs="0" name="Last_Update_SecUser_id" type="xs:int" />
<xs:element name="Completed_For_Replication">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_Alias_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Sch_Proforma_id" type="xs:int" />
<xs:element name="Alternate_Ship_type" type="xs:boolean" />
<xs:element name="Voyage_Number_Retired" type="xs:int" />
<xs:element minOccurs="0" name="Rotation_id" type="xs:int" />
<xs:element minOccurs="0" name="Sch_Proforma_GUID">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TBA" type="xs:boolean" />
<xs:element minOccurs="0" name="Special_Loader_Grid_id" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="GISVoyage_Port_Call">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Voyage_Port_Call_id" type="xs:int" />
<xs:element name="Voyage_id" type="xs:int" />
<xs:element name="Sequence_Number" type="xs:short" />
<xs:element name="Port_id" type="xs:int" />
<xs:element minOccurs="0" name="Actual_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Actual_Departure_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Estimated_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Estimated_Departure_Time" type="xs:dateTime" />
<xs:element name="Bypassed_Canceled" type="xs:boolean" />
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_TimeStamp" type="xs:dateTime" />
<xs:element name="Is_Import_Call" type="xs:boolean" />
<xs:element name="Is_Export_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Cut_Off_Date" type="xs:dateTime" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element minOccurs="0" name="Voyage_Export_Suffix">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Voyage_Import_Suffix">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Commercial_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Commercial_Departure_Time" type="xs:dateTime" />
<xs:element name="Extra_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Sch_Proforma_Port_id" type="xs:int" />
<xs:element minOccurs="0" name="Buffer_Remaining_Mins" type="xs:int" />
<xs:element minOccurs="0" name="Bypassed_Reason">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Delete_Type">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Display_Sequence_Number" type="xs:short" />
<xs:element name="Is_Transit_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Export_Documentation_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Export_DTX_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Import_Documentation_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Import_DTX_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Voyage_Number_Change" type="xs:boolean" />
</xs:sequence>
</xs:complexType>
<xs:unique msdata:PrimaryKey="true" name="Constraint1">
<xs:selector xpath=".//GISVessel" />
<xs:field xpath="Vessel_id" />
</xs:unique>
<xs:unique msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true" name="GISVoyage_Constraint1">
<xs:selector xpath=".//GISVoyage" />
<xs:field xpath="Voyage_id" />
</xs:unique>
<xs:unique msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true" name="GISVoyage_Port_Call_Constraint1">
<xs:selector xpath=".//GISVoyage_Port_Call" />
<xs:field xpath="Voyage_Port_Call_id" />
</xs:unique>
</xs:element>
<xs:annotation>
<xs:appinfo>
<msdata:Relationship name="X_VOYG_VSSL_Vessel_id" msdata:parent="GISVessel" msdata:child="GISVoyage" msdata:parentkey="Vessel_id" msdata:childkey="Vessel_id" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" />
<msdata:Relationship name="X_PCAL_VOYG_Voyage_id" msdata:parent="GISVoyage" msdata:child="GISVoyage_Port_Call" msdata:parentkey="Voyage_id" msdata:childkey="Voyage_id" />
</xs:appinfo>
</xs:annotation>
</xs:schema>

None of your elements appear to be mapped to any tables. use the "msdata:relation" annotation to map an element to a table.|||

This error message means that you have specified a row to update, but that the row to be updated could not be found in the database. So the "before" image matched 0 rows in the database. This means either the data in the database has changed, or there are some conversion issues between the data in the diffgram and the data in the database that is causing the rows not to match.

You can try to use SQL Query analyzer to capture the SQL and see which column (or columns) are not matching, or you can try to modify the diffgram to see which colum is causing the problem.

I have a feeling that the problem may be the datetime column. One thing to try is to modify the schema to be:

<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" msdata:datatype="dateTime"/>

This will let SQLXML know to do a datetime specific comparison on the SQL Server.

Hope this helps,

Todd

How to execute a "batch file" using SQL Server 2005?

Hi all,

As the topic describes, I'd like to run a batch file using sql server.
How do i do it?

Thanks in advanceOne method is with a SQL Server agent CmdExec job step. Another is via
xp_cmdshell. If you choose to use xp_cmdshell, be sure you fully understand
the security implications and don't grant direct execute permissions on the
proc.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1135346052.206276.14220@.o13g2000cwo.googlegro ups.com...
> Hi all,
> As the topic describes, I'd like to run a batch file using sql server.
> How do i do it?
> Thanks in advance

how to execute (using sp_executesql or other...) heterogeneous dyn

use SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in dynamic query
"herve maillarda" wrote:

> Hi,
> I need to run execute an heterogeneous dynamic query (I build the query
> using nvarchar variable).
> Code sample :
> CREATE PROCEDURE COPY_DATA (@.ServerName nvarchar(20), @.DbName
> nvarchar(20),@.TableName nvarchar(20), @.DateDeb DateTime, @.DateFin
> DateTime) AS
> -- Build SQL Query --
> Set @.Ch_SQL = +"INSERT INTO " + @.ServerName + "." + @.DbName + ".dbo." +
> @.TableName + " ( Date_Heure, Valeur )"
> Set @.Ch_SQL =@.Ch_SQL + " SELECT Date_Heure,Valeur FROM " + @.DbName +
> ".dbo." + @.TableName + " WHERE (Date_Heure >= CONVERT(DATETIME, '" +
> CAST(@.DateDeb AS nvarchar(20)) + "', 102) AND Date_Heure <=
> CONVERT(DATETIME, '" + CAST(@.DateFin AS nvarchar(20)) +"', 102))"
> -- Execute query
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> exec sp_executesql @.Ch_SQL
> GO
>
> I've alway an error coming from SQL server..."You must set ANSI_NULLS
> and ANSI_WARNINGS"...
> If I try the query from the Analyzer it works ...
> How can I do ?
> Thanks for your help...
> Herve.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>Won't work...
I still have the same error message coming from the Query Analyzer
"[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
connection."
I've try to add the options in my query (as you told me) :
Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO " +
@.ServerName ...
Do I miss something ?
thanks for your help.
Herve MAILLARD
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi
Do you have triggers that have not had the settings correctly defined?
http://groups-beta.google.com/group...br />
915f7f68
John
Herve MAILLARD wrote:
> Won't work...
> I still have the same error message coming from the Query Analyzer
> "[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
> require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
> connection."
> I've try to add the options in my query (as you told me) :
> Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO "
+
> @.ServerName ...
> Do I miss something ?
> thanks for your help.
>
> Herve MAILLARD
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!sql

How to execute

I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.

Can someone please give me a basic DTEXEC example?

Thanks in advance !Moving to the SSIS forum|||

cheers Louis.

To the OP, perhaps this will help:

Online Beginner Resources
http://blogs.conchango.com/jamiethomson/archive/2007/01/30/SSIS_3A00_-Online-Beginner-Resources.aspx

-Jamie

How to Execute

My Question:

If we execute Query (Text Format), Im getting Insert statements in result set

Right Now; I'm doing, Copy and Paste the result set in Query window and again running the query to insert into Table.

Is there any way, to execute it Dynamically(instead of copy & paste)

can Any one help with this.

Look into this query:

SET NOCOUNT ON

USE PUBS
GO

Drop Table ##Temp1
GO
Create Table ##Temp1 ( X Varchar (22), Name Varchar (30), Z Varchar (30))

Insert into ##Temp1
select 'select count(*) from ' AS X, Name AS Y, 'Insert into ##1' AS Z
from sysobjects where xtype = 'u'
order by name

-- SELECT * FROM ##Temp1

Drop Table ##1
go
Create Table ##1(Table_Name Varchar(50), No_Of_Rows Int)

select Z + char(10) + 'Select '+ '"' + A.Name + '" ' + 'As Table_Name, ' +
'count(*) As No_of_Rows from '+ A.Name
from sysobjects A , ##Temp1 B
where A.Name = B.Name and xtype = 'U'
order by A.NameEXECUTE should execute your SQL statement

How to exectue sql Function in asp.net

I want to exectue sql user define function in my asp.net application
I have one user define function in sql server 2000 that give good logic for my database access now i want to execute that function in asp.net

check this article