bcp within batch file

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!DBA (kaylisse@.yahoo.com) writes:
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?

The return status for a program called from a batch file is in
%ERRORLEVEL%, so this is the variable you should check.

I seem to recall that BCP does not always set this variable as one
may desire. It does set it, if the password is wrong. But I believe
it does not set %errorlevel% if some rows does not load.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks like most interesting bcp errors will set %errorlevel% to 1. An empty
input file, however, doesn't set the errorlevel. You can take action in a
..CMD file like this:

bcp <table> [in|out] <filespec> [switches]
if %errorlevel% 1 goto <label>
<normal processing steps here
:<label> echo something BAD happened to your BCP!
<steps to do something about it here
I didn't test it but I believe if you set the maxerrors switch, you won't
get the non-zero errorlevel unless you actually exceed that threshold. You
might want to test this yourself.

FYI - in our .CMD scripts, if I want to simply fail the job after the error,
I usually do this:

bcp <stuff>
if %errorlevel% 1 goto BCP_FAILED

and I don't bother using a BCP_FAILED label anywhere. Searching for it, the
job runs right past the end and aborts. You'll see a message saying "Can't
find label BCP_FAILED" or something similar as part of the job status report
if you run this through SQL Executive and, by convention here, that's the
diagnostic for the job.

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!|||Hi

%ERRORLEVEL% will be 0 when a succesful import has been performed. If it
fails then it will return 1 (on my tests!).

John

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -T
Please post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is
I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here'
s
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:
> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp and trigger: missing data in bcp out file

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.

But I got missing data in bcp out file like this:

Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063

778145 296494 2007-01-29 21:25:47.063
*/

1. trigger.sql
CREATE TABLE [FERUpdate] (
[id] [int] NOT NULL ,
[fid] [int] NOT NULL ,
[sid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgFERUpdate on FER For Insert,Update as
insert into FERUpdate(id,fid,sid) select ins.id, ins.fid,ins.sid from
inserted ins

2. bcp.bat
--
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"

bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt

isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
--
--

I have been struggling with this for these two days. Your any helps
are appreciated, Please help me out!! Thanks!!!(danceli@.gmail.com) writes:

Quote:

Originally Posted by

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.
>
But I got missing data in bcp out file like this:
>
Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063
>
778145 296494 2007-01-29 21:25:47.063
*/

What numbers are these?

Quote:

Originally Posted by

2. bcp.bat
--
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"
>
bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt
>
isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
--

How often do you run this?
What is the meaning if the <-1 things?

And how do you conclude that the data is missing? There is no
ORDER BY clause in your SELECT, so the missing rows may be elsewhere
in the file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Either you query is wrong or you did the DELETE operation before the
bcp out command.

BCP

Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:

> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?

BCP

Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?

BCP

Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:

> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express
> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
encuentra la información de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
> encuentra la información de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE => @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320
-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INI
T,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batch Updation

I have the scenario of selecting values from more than one tables, also while updating the values to DB from Datagrid some changes may or may not be made in grod

Then check the values alreay existing table to the grid values now i had written in OPENXML inside a Stored procedure,the SP have if statements, INSERT,UPDATE for one table or more than one table

which one is best openxml or the adapter.update is best,

Also how can achieve more than one t-sql operation in adapter.update

the SP is below

/*

To UPDATE And INSERT the Asset Values and Asset Status, and to INSERT Asset Change History

For the Assets which are Value and Status Changed

*/

Create procedure AssetValueStatusAdjustment_InsertUpdate

@.XMLString text
,@.LastModifiedUserID varchar(10)
,@.LastModifiedDate varchar(10)
,@.CultureID varchar(5)
As
BEGIN
SET NOCOUNT ON
Declare @.InventoryHistoryID bigint
Declare @.handle int
Declare @.XMLStatusName nvarchar(50)
Declare @.TableStatusName nvarchar(50)
Declare @.Error int

EXEC sp_xml_preparedocument @.handle OUTPUT, @.XMLString

-- This is For INSERT the Details of the Value Adjustment Assets into the Table
-- Inventory_ValueAdjustment

INSERT INTO Inventory_ValueAdjustment
(InventoryID
,PostDate
,PreAdjustmentOriginalCost
,PreAdjustmentNetValue
,AdjustmentAmount
,Comment
,GLTemplateID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryID
,XMLAdjustmentColumns.PostDate
,XMLAdjustmentColumns.PreAdjustmentOriginalCost
,XMLAdjustmentColumns.PreAdjustmentNetValue
,XMLAdjustmentColumns.AdjustmentAmount
,XMLAdjustmentColumns.Comment
,XMLAdjustmentColumns.GLTemplateID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
PostDate datetime,
PreAdjustmentOriginalCost decimal(18,2),
PreAdjustmentNetValue decimal(18,2),
AdjustmentAmount decimal(18,2),
Comment nvarchar(200),
GLTemplateID int)XMLAdjustmentColumns

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- UPDATE the ValueAdjustment to Inventory_Profile

UPDATE
Inventory_Profile
SET
NetValue=XMLAdjustmentColumns.NetValue
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate

From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
NetValue decimal(19,2))XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

--INSERT a ValueAdjustment Change History Record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table
AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- Check Wheather the Status has been Changed or not
SELECT
@.XMLStatusName = XMLAdjustmentColumns.InventoryStatusName
,@.TableStatusName = Inventory_Status_CnfgLocale.InventoryStatusName

FROM
Inventory_Status_Cnfg

INNER JOIN
Inventory_Status_CnfgLocale ON Inventory_Status_Cnfg.InventoryStatusID=Inventory_Status_CnfgLocale.InventoryStatusID

INNER JOIN
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Table

WITH(InventoryStatusName nvarchar(100)
,InventoryStatusID tinyint
,CultureID varchar(5))XMLAdjustmentColumns

ON
Inventory_Status_CnfgLocale.InventoryStatusID=XMLAdjustmentColumns.InventoryStatusID

AND
Inventory_Status_CnfgLocale.CultureID=@.CultureID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- If New Status is not matched with the existing Status Name then

IF(@.XMLStatusName <> @.TableStatusName)

BEGIN --To Insert Status Change Record

--UPDATE the New Status Changed Value to Inventory_Profile

UPDATE
Inventory_Profile
SET
InventoryStatusID = XMLAdjustmentColumns.InventoryStatusId
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate

FROM
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Tavle

WITH(InventoryStatusID tinyint
,InventoryID bigint)XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

--Select the Maximum available InventoryHistoryID to avoid primary key violation
SELECT
@.InventoryHistoryID=COUNT(InventoryHistoryID)
FROM
Inventory_History

SET @.InventoryHistoryID = @.InventoryHistoryID+1

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- INSERT a status change history record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
@.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table

AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

END

GOTO NextStatement --dont want to Update the Status GOTO Next expression

NextStatement:

ErrorCatch:
RETURN @.Error

EXEC sp_xml_removedocument @.handle
END
Go

/*

This Stored Procedure Will Insert two tables as Inventory_Profile, Inventory_History and

Update TaxDepreciation_Profile by OPENXML BULK UPDATE CONCEPT

-
The @.xmlString Parameter is used to get the value as XML Nodes From the Non Transaction

Class

-

*/

guideme please

SqlDataAdapter has an UpdateBatchSize property that will batch updates together before sending them to the server. Value 0 should send as much across as possible. In addition you can use a Transaction to ensure that your updates happen in a consistent manner.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatebatchsize.aspx

In this way you could use the dataset and SqlDataAdapter to acheive what you are doing with your stored procedure. This will probably reduce the amount of code and get your business logic into c#.

Batch updates with SQL CLR

Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?

Here's what I'm currently doing:
I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly:
- sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records);
- performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated);
- updating the table when each group has been processed.

This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.

Any ideas on how I can improve performance of my updating?

Thanks in advance,

Graham

If I understand correctly, you're doing record by record calculation. So all required info is present within the record. If I would try to solve this I would have started with a CLR function, not a SP. So my statement would look something like:

Update BigTable Set CalcField = CLRFunc.ToughCalc(fieldX, fieldY, fieldZ)

I'm not sure if this would be faster, but it would be set based (not all functions will produce set bases solutions)

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done bu
t
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!
Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>
|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...[vbcol=seagreen]
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done but
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>
|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...[vbcol=seagreen]
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!
|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>
|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...[vbcol=seagreen]
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

batch update in stored procedure

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @.userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?

CREATE PROCEDURE dbo.MySP
@.userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@.userIDs)

IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFFOriginally posted by gyuan
Obviously, UserID has datatype int.

Obviousley it doesn't...

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Use the UDF that's posted in there|||You could use dynamic query - sp_executesql or execute it.|||Originally posted by Brett Kaiser
Use the UDF that's posted in there

Brett,

I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.|||Cut and paste the following code in to QA and run it...

USE Northwind
GO

CREATE FUNCTION CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN
DECLARE @.i int, @.c varchar(100);
SELECT @.Str = @.Str + ',', @.i = 1, @.c = '';
WHILE @.i <= LEN(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN ISNUMERIC(@.c)=1 THEN @.c ELSE Null END
, RTRIM(LTRIM(@.c))
, CASE WHEN ISDATE(@.c)=1 THEN @.c ELSE Null END)
SET @.c = ''
END
ELSE

SET @.c = @.c + substring(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
GO

DECLARE @.userIDs varchar(100)
SELECT @.userIDs = '101,102,103'

SELECT *
FROM myTable99
WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@.userIDs))
GO

DROP FUNCTION CSVTable
DROP TABLE myTable99
GO

Thank you Dr. Cross Join

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859|||I run it and get the result I need. So now do I need to add the function to the stored procedure?|||Just make you're WHERE Clause look like mine...

Just need to make sure the udf is in the same enviroment where you release the procedure...

Get it?|||Yes, it works. That is a great function. Thank you, Brett.

By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?|||Yeah, go to the link and give thanks to Jeff..

Use dynamic sql as a last resort...and never in application code...

I use it for admin support...but it's not released...

Batch update and trigger

Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit

BATCH Update ?

Hi all,

we need to update many single cells with individual MDX Update Statements.
We're doing this with ADOMD now (C# Project) in a loop.
In order to save roundtrips and put things in one transaction
we considered using the <Batch> Element of XMLA.
I could'nt find a example how to use this with MDX Commands.
this doesnt work:
<Batch>
<Command>
<Statement>
UPDATE ..
</Statement>
</Command>
<Command>
<Statement>
UPDATE..
</Statement>
</Command>
</Batch>

The Batch element at line 7, column 22 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Do you have any hints ?
BTW: what happend to www.xmla.org, its down?
Wher can I find a complete schema file for XMLA?
a lot of questions...
Thanks a lot,
mik

You can update multiple cell values in a single Update statement seperated by commas.

For example:

UPDATE CUBE [Cube1] SET

(USA, Sales) = 100 USE_EQUAL_ALLOCATION,

(Canada, Sales) = 50 USE_EQUAL_ALLOCATION

You can also find some information on XML/A at

http://msdn2.microsoft.com/de-de/library/ms186604.aspx

Batch T-SQL Scripts

In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @. plus the path and file name. So, if I have a script called E:\CreateTable.sql, I can simply enter @.CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:\CreateTable.sql file (there may be one or more commands).

Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?

Thanks,

hmscottI don't know of a way to do it from a script in either Query Analyzer or OSQL, but you can open a new file in Query Analyzer and execute it that way and you can use the ED command in OSQL to allow you to incorporate the file (via the editor) there too.

-PatP|||Not that I've seen...the closest is

File>Open>filename.sql

[CTRL]+E

And Whore-acle is so much more painful in SQL+...I guess each has it's own pluses...

Did you ever use mask.sql?

Oh, and osql could run in a command line pretty easily...just make sure you redirect the output...

But why bother, unless you're releasing a script to production...|||Thnx guys. Just wishful thinking...