Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with SQL Query not moving through every record

I was wondering if anyone could quickly identify why the query is not parsing through each line in the temp table? I am sure its something stupid and easy, but if anyone has an idea, I would greatly appreciate the help!

My results are the same row repeated exactly the same for the number of rows in the temp table.

Declare @.Topic varchar(150)
Declare @.CustomTitle varchar(150)
Declare @.FullName varchar(100)
Declare @.starttime datetime
Declare @.endtime datetime
select
TOPIC = t.TopicName,
CustomTitle = e.ssCustomTitle,
StartTime = v.StartDateTime,
eFirstName = FirstName,
eLastName = LastName,
eEndTime = ssEndTime
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime
SELECT @.Topic = Topic,
@.CustomTitle = CustomTitle,
@.FullName = eFirstname + ' ' + eLastName,
@.StartTime = starttime,
@.EndTime = eEndTime
From #tmpwork
IF (@.CustomTitle is not null)
IF (not @.CustomTitle = '') --correct problem of ZLS
Begin
set @.Topic = @.CustomTitle
End
SELECT
Topic = @.Topic,
StartTime = @.StartTime,
FullName = @.FullName,
EndTime = @.endtime
INTO #Final
FROM #tmpwork
select * from #Final
drop table #tmpwork
drop table #Final

SELECT @.Topic = Topic,
@.CustomTitle = CustomTitle,
@.FullName = eFirstname + ' ' + eLastName,
@.StartTime = starttime,
@.EndTime = eEndTime
From #tmpwork

... that doesn't give you an error? That's almost surprising. In this case, I'd have used either a loop or cursors to accomplish it.

Actually... you can combine so much of that into just one giant sql call rather than running temp tables and such.

select
TOPIC = IsNull(e.ssCustomTitle, t.TopicName)
StartTime = v.StartDateTime,
FullName = FirstName + ' ' + LastName,
eEndTime = ssEndTime
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
 LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime

seems easier than having that and another temp table just to do one or two things.

look into IIF(expression, true, false) and IsNull(field, replacement) methods to streamline your sql to optimum executions.

books online is also a good source of information.|||Thanks for the help. This forum has saved my butt on countless occasions.

The reason I was going through and creating the second temp table was because I needed it to write the word 'Multiple' if the Topic had to speakers (fullName). So my thought was to simply do the first one where I get just the data I want, and on the second table go through and convert the Data into the format I needed it. Im sure there is a way to convert the data on its way into the first tmp table, however I am not sure of the best way to approach it.|||I ended up making the second temp table to accomplish the task. Perhaps there is an easier way, but this seemed to be the easiest way to do it. FWIW, here is the finished code, maybe it will help someone else as well.

DECLARE @.Topic varchar(100)
DECLARE @.CustomTitle varchar(150)
DECLARE @.starttime datetime
DECLARE @.tmpname varchar(100)
DECLARE @.eventno int
DECLARE @.endtime datetime
select
TOPIC = IsNull(e.ssCustomTitle, t.TopicName),
StartTime = v.StartDateTime,
FullName = FirstName + ' ' + LastName,
EndTime = ssEndTime,
EventNo = e.ssSessionId
INTO #tmpwork
FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
 LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime
Create Table #Final(
 Topic varchar(180),
 StartTime datetime,
 FullName varchar(100) NULL,
 EventNo int,
 EndTime datetime, RoomNo int
)
If (Select Count(*) From #tmpwork) > 0
Begin
While (Select Count(*) From #tmpwork) > 0
Begin
Select @.topic = Topic,
@.starttime = StartTime,
@.tmpname = FullName,
@.eventno = EventNo,
@.endtime = EndTime
From #tmpwork
If (Select Count(*) From #tmpwork Where EventNo = @.eventno) > 1
Insert Into #Final Values(@.topic, @.starttime, 'Multiple', @.eventno, @.endtime, null)
Else
Insert Into #Final Values(@.topic, @.starttime, @.tmpname, @.eventno, @.endtime, null)
Delete #tmpwork Where EventNo = @.eventno
End
End
Select EventNo,
[Time] = SubString(Convert(varchar(20), StartTime), 13, 8),
EndTime = SubString(Convert(varchar(20), EndTime), 13, 8),
Topic,
FullName,
[Date] = Convert(varchar(20), StartTime, 107),
[WeekDay] = Datename(weekday,StartTime)
From #Final
Order By StartTime, EventNo
Drop Table #tmpwork
DROP Table #Final

sql

Problem with sp and uniqueidentifier

Hi,

I use this procedure to add a record to the db (SQL 2005)
i constantly get this error:

Conversion failed when converting from a character string to uniqueidentifier.

This is the sp:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[AddBootloader]

@.BootName nvarchar(50),
@.Version nvarchar(50),
@.CSD nvarchar(50),
@.CreatorID uniqueidentifier,
@.FilePath nvarchar(150),
@.FileSize nvarchar(150)

AS
INSERT INTO dbo.EB_Bootloaders

([Bootname]
,[Version]
,[CSD]
,[FilePath]
,[FileSize]
,[CreatorID])


VALUES

(
@.BootName,
@.Version,
@.CSD,
@.CreatorID,
@.FilePath,
@.FileSize)

And this is the table:

USE [Ebdata]
GO
/****** Object: Table [dbo].[EB_Bootloaders] Script Date: 07/12/2006 10:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EB_Bootloaders](
[ID] [bigint] IDENTITY(1,3) NOT NULL,
[BootName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Version] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CSD] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[FilePath] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,
[FileSize] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_EB_Bootloaders_CreateDate] DEFAULT (getdate()),
[CreatorID] [uniqueidentifier] NOT NULL,
[UpdateDate] [datetime] NULL,
[UpdateUser] [uniqueidentifier] NULL,
CONSTRAINT [PK_EB_Bootloaders_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Ebdata]
GO
ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users] FOREIGN KEY([CreatorID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users1] FOREIGN KEY([UpdateUser])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION

The funny thing is that i copied the sp code from another which runs perfect,
I insert a Guid from an asp.net page.

Hope someone can help me here because im am stucked!

Cheers WimmoHi Wimmo

Aren't your VALUES in your insert statement in the wrong order (@.CreatorID should be last not fourth)?|||set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[AddBootloader]

@.BootName nvarchar(50),
@.Version nvarchar(50),
@.CSD nvarchar(50),
@.CreatorID uniqueidentifier,
@.FilePath nvarchar(150),
@.FileSize nvarchar(150)

AS
INSERT INTO dbo.EB_Bootloaders

([Bootname]
,[Version]
,[CSD]
,[CreatorID]
,[FilePath]
,[FileSize]
)


VALUES

(
@.BootName,
@.Version,
@.CSD,
@.CreatorID,
@.FilePath,
@.FileSize)|||Euh thanx, think i was sleeping!
Glad some people are awake!
Thanx for helping me!

cheers Wim

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
Dan
Hi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are completely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>
|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>
|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com
|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.
|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH
|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks
|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
DanHi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are complet
ely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

Problem with replication

When I try to insert a new record in the subscriber, into a published table, I get a error, saying that it could not execute a stored procedure in the remote server SQLOLEDB. But I when I insert a new record in the publisher, it works correctly, transmitting the changes to the subscriber. What's wrong with it? Both systems are windows 2000 server, with sql server 2000. Thanks.Please give us an idea what type of replication it is?|||- Do you have latest fix installed on the subscriber?
- Does the insertion fire any triggers?|||It is a transactional replication. The error occurs when the subscriber is calling the remote stored procedure in the publisher, which updates the original table.