multiple rows insert: March 2012

Friday, March 30, 2012

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!

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.

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.

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

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,
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

How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@.listOnly bit=0, @.maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@.dbName sysname, @.listOnly bit=0,
@.maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @.dbName

Declare @.execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @.dbName
If (@.@.FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @.dbName + ''''
Return -1
End

While(@.@.FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @.dbName
Print Char(13)
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @.execstr, N'@.listOnly bit, @.maxFrag Decimal',
@.listOnly, @.maxFrag
Fetch crDbs INTO @.dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@.netzero.comI believe if you change:
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
to
Set @.execstr = '[' + @.dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.|||Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***|||Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@.execstr).

SET @.execstr = 'USE ' + @.dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@.listOnly) + ',' + RTRIM(@.maxFrag)
EXEC (@.execstr)|||Got it. Had to change to this

Set @.execstr = @.dbName + '..sp_RebuildIndexesSub'
Exec @.execstr @.listOnly, @.maxFrag

thanks
Sunit|||You are right. Your code is much cleaner :)

How to exec SQL user defined function?

Hi,
How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write
EXEC nameofstored procedure abc (@.abc is the parameter passed).. But How to run a SQL function ?
Thanks

assume function GetUser takes ID as input parameter , write the following in query analyzer and execute

BEGIN
DECLARE @.ID int
set @.ID = 45
SELECT dbo.GetUser(@.ID) AS 'User Name'
END

|||You need to either use it in a stored procedure and then call thestored procedure, or use it in some other statement, such as a SELECTstatement:
SELECT MyFunc(someField) FROM MyTable
There's no way to call it directly as you want.
Don
sql

How to 'exec sp_who' into #temp_table ?

How to 'exec sp_who' into #temp_table ?
Thanks !
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:#RKHZjQcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>
CREATE TABLE #temp_table (
col1..
..
coln)
INSERT #temp_table
exec sp_who
Note the temp table's columns need to match the result set from the sp_who
sproc. Also, you might like sp_who2 better. It gives more information.
Rick Sawtell
MCT, MCSD, MCDBA
|||See if this helps:
http://support.microsoft.com/newsgro...-us&sloc=en-us
AMB
"SOHO" wrote:

> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>
|||Thanks to all of you!!
Problems been solved.
Thanks !
"SOHO" <hkwin2000@.hotmail.com> glsD:%23RKHZjQcFHA.2128@.TK2MSFTNGP15.phx .gbl...
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>

How to 'exec sp_who' into #temp_table ?

How to 'exec sp_who' into #temp_table ?
--
Thanks !"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:#RKHZjQcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>
CREATE TABLE #temp_table (
col1..
..
coln)
INSERT #temp_table
exec sp_who
Note the temp table's columns need to match the result set from the sp_who
sproc. Also, you might like sp_who2 better. It gives more information.
Rick Sawtell
MCT, MCSD, MCDBA|||See if this helps:
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=6954e8d3-69eb-49c0-ac86-cae4cc92cc86&sloc=en-us&sloc=en-us
AMB
"SOHO" wrote:
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>|||Thanks to all of you!!
Problems been solved.
--
Thanks !
"SOHO" <hkwin2000@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23RKHZjQcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>

How to 'exec sp_who' into #temp_table ?

How to 'exec sp_who' into #temp_table ?
Thanks !"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:#RKHZjQcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>
CREATE TABLE #temp_table (
col1..
.
coln)
INSERT #temp_table
exec sp_who
Note the temp table's columns need to match the result set from the sp_who
sproc. Also, you might like sp_who2 better. It gives more information.
Rick Sawtell
MCT, MCSD, MCDBA|||See if this helps:
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"SOHO" wrote:

> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>|||Thanks to all of you!!
Problems been solved.
Thanks !
"SOHO" <hkwin2000@.hotmail.com> glsD:%23RKHZjQcFHA.2128@.TK2MSFTNGP15.phx.gbl...[vbc
ol=seagreen]
> How to 'exec sp_who' into #temp_table ?
>
> --
> Thanks !
>
>[/vbcol]

how to exec a stored procedure

hi,

how do I exec stored procedure that accept parameter and return a single value?

here is example of report

stu_id = ******

stu_name = ****

subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject

thks,

You cannot call a stored procedure per row if you mean that with your mentioned design, you would have to get all the information within one procedure to display it in the bound table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Charles,

Have you tried using a user defined function in place of the stored procedure?

Simone

|||

A potentially better performing alternative to a user defined function would probably be a derived table containing the marks for each student by subject. You would then join on the table.

Something like

select stu_id, stu_name, subject, mark

from students s

left outer join (select stu_id, subject, marks from marks) m on m.stu_id = s.stu_id

Of course you would need to summarize the marks into a table...

cheers,

Andrew

How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using dynamic S
QL.
http://www.sommarskog.se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL = 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message news:1155862466.120172.203660@.i3g2000cwc.goog
legroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
>
> Sorry for newbie question =]
> Thank you.
>sql

How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using =dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html=20
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL =3D 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message =news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL =3D 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> > Sorry for newbie question =3D]
> Thank you.
>
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The exact same way. (Just a little =issue with quotes...)
I suggest that you review this article =before embarking into using dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTestAS BEGIN
=DECLARE @.SQL nvarchar(100)
=SET @.SQL =3D 'SELECT ''Hello, World.'''
=EXECUTE sp_executesql @.SQL
=ENDGO
EXECUTE #MyTest
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Cylix" =wrote in message news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com=...> =For example,> I have a variable @.SQL =3D 'Select ''Hello =World'''> How can I execute this @.SQL in stored procedure?> > Sorry for =newbie question =3D]> Thank you.>

--=_NextPart_000_0BBE_01C6C228.19951980--

How to excute package in sequence

How to excute package in sequence . i have an requirement like this the package should execute in a sequence

1.PKG1

2.PKG2

3.PKG3

and in some case PKG1,.PKG2,.PKG3 shoule run parallel.

Thanks & Regards

Jegan.T

Jegant wrote:

How to excute package in sequence . i have an requirement like this the package should execute in a sequence

1.PKG1

2.PKG2

3.PKG3

You know what the Execute Package Task is right? Well you can control the execution of these 3 packages from another package.

1. Put 3 Execute Package tasks into a new package. Point them at your 3 packages

2. Put a precedence constraint from Execute Package Task for PKG1 to Execute Package Task for PKG2

3. Put a precedence constraint from Execute Package Task for PKG2 to Execute Package Task for PKG3

Jegant wrote:

and in some case PKG1,.PKG2,.PKG3 shoule run parallel.

Well in this case...

1. Put 3 Execute Package tasks into a new package. Point them at your 3 packages

2. Drop an empty sequence container onto your new package

3. Drag a precedence constraint from teh sequence container to each of the three Execute Package Tasks.

-Jamie

|||

Hi Jamie

Thanks for your reply . ill try this and get back to you.

Jegan.T

how to excluded the intersection of 2 dimensions ?

Hi,

I have the following simplified problem.

I have a fact table and two dimension tables, colors and weekdays.

I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays ?

They query tool doesn't give you really the options.

Any suggestions ?

Constantijn Enders

You can do it in MDX, but only a few query tools will allow you to construct queries like this. I think vendors tend to refer to this capability as 'asymmetic sets'; I can only think of one tool I've seen recently that did this (Intelligencia - http://www.it-workplace.co.uk/i4wfeatures.aspx) but I'm sure that if you hunt around there will be others. Does anyone else know of one?

Here's an MDX query to prove it's possible:

select

measures.[internet sales amount] on 0,

except(

[Date].[Day Name].[Day Name].members

[Product].[Color].[Color].members

,{([Date].[Day Name].&[2], [Product].[Color].&[Red])}

) on 1

from [Adventure Works]

HTH,

Chris

How to exclude zero values when sorting?

I have a datagrid with a "sort" field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can't figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?This isn't pretty but why don't you give it a go:
SELECT * FROM mytable WHERE myfield > 0 ORDER BY myfield ASC
UNION SELECT * FROM mytable WHERE myfield = 0

Regards
Fredr!k|||Unfortunately, this isn't valid syntax becuase ORDER BY must be at the end of the query. I get "Incorrect syntax near the keyword 'UNION'." when I try

SELECT * FROM Photo WHERE PhotoOrder > 0 ORDER BY PhotoOrder ASC UNION SELECT * FROM Photo WHERE PhotoOrder = 0|||To use the UNION operator in SQL Server all your Data types must be the same and the same order in both tables, but UNION is restrictive because it performs an Implicit DISTINCT by eliminating DUPLICATES. So if eliminating duplicates is not important try UNION ALL, if it still fails the it is INNER JOIN if both tables are equal or OUTER JOIN if they are not equal. Hope this helps.

Kind regards,
Gift Peddie|||I'm confused - how is this relevant to my question?|||I got what I wanted with
"ORDER BY IsNull(PhotoORDER, 1000)"

Unfortunately, zeroes will still sort first, but I can NULLify them on data entry|||You might try:


ORDER BY
 CASE WHEN ISNULL(PhotoOrder,0) = 0 THEN 2 ELSE 1 END,
 CASE WHEN ISNULL(PhotoOrder,0) <> 0 THEN PhotoOrder

Terri|||I was only replying your UNION error not your original post. I will try to be clear in the future.

Kind regards,
Gift Peddiesql

how to exclude time portion of datefield in a textbox?

Hello,
The datasourece in my report contains a date field, and the time portion of
this date field is showing up. Is there a format function that I can use in
the textbox or do I need to remove the time portion at the datasource level?
If there is some kind of formatting I can do in the textbox - what does that
look like? What is the syntax?
Thanks,
RichIn the textbox properties, format tab, format code, use 'd'. The format
codes are from .Net you can also use the format function if you need to
concatenate. The elipsi button shows more options.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> Hello,
> The datasourece in my report contains a date field, and the time portion
> of
> this date field is showing up. Is there a format function that I can use
> in
> the textbox or do I need to remove the time portion at the datasource
> level?
> If there is some kind of formatting I can do in the textbox - what does
> that
> look like? What is the syntax?
> Thanks,
> Rich|||Thanks. I will give that a try. Although, I did tweak the datasource a
little and added this which also does the trick (just not at the report level
is the thing):
convert(char(8), s.CurExpireDate, 1) CurExpireDate
which returns say - 10/18/06 format
where CurExpireDate was the datefield in my datasource.
May I ask what the syntax looks like using 'd' in the report textbox?
= ...Fields!CurExpireDate.Value
"Steve MunLeeuw" wrote:
> In the textbox properties, format tab, format code, use 'd'. The format
> codes are from .Net you can also use the format function if you need to
> concatenate. The elipsi button shows more options.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> > Hello,
> >
> > The datasourece in my report contains a date field, and the time portion
> > of
> > this date field is showing up. Is there a format function that I can use
> > in
> > the textbox or do I need to remove the time portion at the datasource
> > level?
> >
> > If there is some kind of formatting I can do in the textbox - what does
> > that
> > look like? What is the syntax?
> >
> > Thanks,
> > Rich
>
>|||The expression in the textbox would the the same:
=Fields!CurExpireDate.Value
the textbox is applying the formatting to the contents of the textbox.
If you wanted to concatenate strings then the expression would look
something like this:
= "Generated on " & Format(Globals!ExecutionTime, "d")
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> Thanks. I will give that a try. Although, I did tweak the datasource a
> little and added this which also does the trick (just not at the report
> level
> is the thing):
> convert(char(8), s.CurExpireDate, 1) CurExpireDate
> which returns say - 10/18/06 format
> where CurExpireDate was the datefield in my datasource.
> May I ask what the syntax looks like using 'd' in the report textbox?
> = ...Fields!CurExpireDate.Value
>
>
> "Steve MunLeeuw" wrote:
>> In the textbox properties, format tab, format code, use 'd'. The format
>> codes are from .Net you can also use the format function if you need to
>> concatenate. The elipsi button shows more options.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
>> > Hello,
>> >
>> > The datasourece in my report contains a date field, and the time
>> > portion
>> > of
>> > this date field is showing up. Is there a format function that I can
>> > use
>> > in
>> > the textbox or do I need to remove the time portion at the datasource
>> > level?
>> >
>> > If there is some kind of formatting I can do in the textbox - what does
>> > that
>> > look like? What is the syntax?
>> >
>> > Thanks,
>> > Rich
>>|||Thank you for this information.
Rich
"Steve MunLeeuw" wrote:
> The expression in the textbox would the the same:
> =Fields!CurExpireDate.Value
> the textbox is applying the formatting to the contents of the textbox.
> If you wanted to concatenate strings then the expression would look
> something like this:
> = "Generated on " & Format(Globals!ExecutionTime, "d")
> Steve MunLeeuw
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> > Thanks. I will give that a try. Although, I did tweak the datasource a
> > little and added this which also does the trick (just not at the report
> > level
> > is the thing):
> >
> > convert(char(8), s.CurExpireDate, 1) CurExpireDate
> >
> > which returns say - 10/18/06 format
> >
> > where CurExpireDate was the datefield in my datasource.
> >
> > May I ask what the syntax looks like using 'd' in the report textbox?
> >
> > = ...Fields!CurExpireDate.Value
> >
> >
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> >> In the textbox properties, format tab, format code, use 'd'. The format
> >> codes are from .Net you can also use the format function if you need to
> >> concatenate. The elipsi button shows more options.
> >>
> >> Steve MunLeeuw
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> >> > Hello,
> >> >
> >> > The datasourece in my report contains a date field, and the time
> >> > portion
> >> > of
> >> > this date field is showing up. Is there a format function that I can
> >> > use
> >> > in
> >> > the textbox or do I need to remove the time portion at the datasource
> >> > level?
> >> >
> >> > If there is some kind of formatting I can do in the textbox - what does
> >> > that
> >> > look like? What is the syntax?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>

How to exclude the collation property when scripting a table in SQL Server 2005

In Sequel Server 2000 when I used to script the creation of a table, it was
possible to go to the scripting options in SQL Query Analyser and switch off
the Include Collation statements.
However, I cannot seem to find this setting in SQL Server 2005 - can anyone
point me in the right direction as I do not want to include these statements
when I create a CREATE TABLE script.
Thanks in advance
AndyHi Andrew
If you use the scripting wizard there are configurable options that by
default do not include collations. Try right clicking on the management
branch rather than the table and choose the General Scripts option.
John
"Andrew Houghton" wrote:
> In Sequel Server 2000 when I used to script the creation of a table, it was
> possible to go to the scripting options in SQL Query Analyser and switch off
> the Include Collation statements.
> However, I cannot seem to find this setting in SQL Server 2005 - can anyone
> point me in the right direction as I do not want to include these statements
> when I create a CREATE TABLE script.
> Thanks in advance
> Andy
>
>

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

sql

How to exclude from NTBACKUP SQL server?

Hello All
On my server when I use ntbackup, i recieve error:
MSDEWriter has reported a VSS error 0x800423f4.
Its problem known by Microsoft, workaround is:
http://support.microsoft.com/kb/828481/ (but it now work for me)
So question is - how may I exclude from ntbackup checklist SQL server?
Thanks
--
Best Regards
Denis Laskov
Arcnet Ltd, Israel
MCSE 2003Hi
Is this not a case of when you set up the backup to exclude the directory
where the data and log files exist, but include the directory where the
backup of the data has been located?
John
"Denis Laskov" wrote:

> Hello All
> On my server when I use ntbackup, i recieve error:
> MSDEWriter has reported a VSS error 0x800423f4.
> Its problem known by Microsoft, workaround is:
> http://support.microsoft.com/kb/828481/ (but it now work for me)
> So question is - how may I exclude from ntbackup checklist SQL server?
> Thanks
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003|||Not sure I'am understand You
I need option to uncheck SQL from ntbackup list of object, but it's hidden
or something.
Is there any option to backup selected folders and System State without
backing up SQL server?
thx
--
Best Regards
Denis Laskov
Arcnet Ltd, Israel
MCSE 2003
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Is this not a case of when you set up the backup to exclude the directory
> where the data and log files exist, but include the directory where the
> backup of the data has been located?
> John
> "Denis Laskov" wrote:
>|||Hi Denis
I don't only have XP to go on at the moment, but when you set up the backup
you can select options to backup selected files/folders, this will give you
are tree view and you check what you wish to backup. Prior to your job
running you will need to backup the databases possibly using osql and a
script that issues a BACKUP command.
If you are issuing your NTBACKUP command from a batch file then you can
specify the files to backup in the selection file "@.bks file name", for more
on this look at the windows help by issuing the command NTBACKUP /?
John
"Denis Laskov" wrote:
[vbcol=seagreen]
> Not sure I'am understand You
> I need option to uncheck SQL from ntbackup list of object, but it's hidden
> or something.
> Is there any option to backup selected folders and System State without
> backing up SQL server?
> thx
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003
>
> "John Bell" wrote:
>|||Hi John
Thanks for Your paition.
Here is Screenshot of ntbackup window:
http://selfmade.fromru.com/images/error.JPG
I cannot find here SQL server selection.
SQL have it's own Maintanance plan Backup so I don't need it in regular
backup.
Best Regards
Denis Laskov
Arcnet Ltd, Israel
MCSE 2003
"John Bell" wrote:
[vbcol=seagreen]
> Hi Denis
> I don't only have XP to go on at the moment, but when you set up the backu
p
> you can select options to backup selected files/folders, this will give yo
u
> are tree view and you check what you wish to backup. Prior to your job
> running you will need to backup the databases possibly using osql and a
> script that issues a BACKUP command.
> If you are issuing your NTBACKUP command from a batch file then you can
> specify the files to backup in the selection file "@.bks file name", for mo
re
> on this look at the windows help by issuing the command NTBACKUP /?
>
> John
> "Denis Laskov" wrote:
>|||Hello
First thing you should do is to apply Service pack 1 on windows 2003. This
will resolve your problem (http://support.microsoft.com/kb/828481/ ). Well,
it did on my server. There is no more "Reverting to non-shadow copy backup
mode" error. NTbackup can now make a snapshot af all databases and make a
backup. If you don't want to apply SP1 then you have to exclude all SQL
databases from NTbackup (C:\program files\microsoft SQL server\mssql\data is
default location if I remember right) and change their recovery model to
simple. You won't see SQL like you see Exchange in NTbackup tree, you need
to exclude folders where your databases are located.
Don't forget to backup your databases from SQL (SQL jobs, maintenance plans)
and include those backups in NT backup.
Tom
"Denis Laskov" <DenisLaskov@.discussions.microsoft.com> wrote in message
news:5D9210D0-CB5F-492C-B108-4411C48623FC@.microsoft.com...[vbcol=seagreen]
> Hi John
> Thanks for Your paition.
> Here is Screenshot of ntbackup window:
> http://selfmade.fromru.com/images/error.JPG
> I cannot find here SQL server selection.
> SQL have it's own Maintanance plan Backup so I don't need it in regular
> backup.
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003
>
> "John Bell" wrote:
>|||Hi Denis
To add to Tom's reply, currently you don't seem to have anything selected.
If you want all the C drive to be backed up check next to the C drive, if yo
u
only want part of the drive to be backed up then expand the C drive by
clicking the '+' sign next to it and select what you want at that level.
Expand each level to be more granular.
John
"Denis Laskov" wrote:
[vbcol=seagreen]
> Hi John
> Thanks for Your paition.
> Here is Screenshot of ntbackup window:
> http://selfmade.fromru.com/images/error.JPG
> I cannot find here SQL server selection.
> SQL have it's own Maintanance plan Backup so I don't need it in regular
> backup.
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003
>
> "John Bell" wrote:
>|||John Hi
Thx for your pation
Please review my answer to Tom with Screenshot of D: drive - MS SqL folder.
Thanks
--
Best Regards
Denis Laskov
Arcnet Ltd, Israel
MCSE 2003
"John Bell" wrote:
[vbcol=seagreen]
> Hi Denis
> To add to Tom's reply, currently you don't seem to have anything selected.
> If you want all the C drive to be backed up check next to the C drive, if
you
> only want part of the drive to be backed up then expand the C drive by
> clicking the '+' sign next to it and select what you want at that level.
> Expand each level to be more granular.
> John
> "Denis Laskov" wrote:
>|||What happens if you select "Microsoft SQL Server" folder to select it and
then click on it again do clear it off? Is it gray again or is it cleared?
But that won't help you with http://support.microsoft.com/kb/828481/
problem. If any of the databases od D drive is not in simple recovery model
than NTbackup will switch to non-shadow copy backup mode. That means that if
you have any system files or any other open files on D drive, they will
probably won't be backed up and you will get errors in backup log.
Tom
"Denis Laskov" <DenisLaskov@.discussions.microsoft.com> wrote in message
news:C50FE487-A583-49B7-B024-8F8445D5C65D@.microsoft.com...[vbcol=seagreen]
> John Hi
> Thx for your pation
> Please review my answer to Tom with Screenshot of D: drive - MS SqL
> folder.
> Thanks
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003
>
> "John Bell" wrote:
>|||Hi
This may be a permissions thing, although if you go through the backup
wizard and choose the advanced options after selecting the backup device,
there is an option to disable shadow copy backup, which may be the issue if
not selected.
John
"Denis Laskov" wrote:
[vbcol=seagreen]
> John Hi
> Thx for your pation
> Please review my answer to Tom with Screenshot of D: drive - MS SqL folder
.
> Thanks
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd, Israel
> MCSE 2003
>
> "John Bell" wrote:
>

Friday, March 30, 2012

Blog Archive

About Me