Hi,
I'm making a transactional replication.
I get the following error:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: SQL4 (Data source); Error number: 4819)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
Anyone that knows how to solve the problem?
Thanks in advance
Anders
your collation on your publication database is different from your collation
on the subscriber database.
To solve your problem use the same collation on both databases.
"Anders Johansson" <anders.johansson@.nospam.se> wrote in message
news:OQ9CtRuJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm making a transactional replication.
> I get the following error:
> Could not bulk insert. Bulk data stream was incorrectly specified as
sorted.
> (Source: SQL4 (Data source); Error number: 4819)
> ----
--
> --
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> ----
--
> --
> Anyone that knows how to solve the problem?
> Thanks in advance
> Anders
>
|||Hi Hilary!
The publication database had a different collation than the subscriber.
But I didnt change the whole subscriber collation - just the article that
went wrong. (There was an alternative in the Publication properties).
Thanks!
"Hilary Cotter" <hilaryk@.att.net> skrev i meddelandet
news:%23tQRaguJEHA.3944@.tk2msftngp13.phx.gbl...
> your collation on your publication database is different from your
collation
> on the subscriber database.
> To solve your problem use the same collation on both databases.
> "Anders Johansson" <anders.johansson@.nospam.se> wrote in message
> news:OQ9CtRuJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> sorted.
> ----
> --
> ----
> --
>
|||damn, why didn't I think of that. Good idea!
"Anders Johansson" <anders.johansson@.nospam.se> wrote in message
news:uYQGUquJEHA.3380@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary!
> The publication database had a different collation than the subscriber.
> But I didnt change the whole subscriber collation - just the article that
> went wrong. (There was an alternative in the Publication properties).
> Thanks!
> "Hilary Cotter" <hilaryk@.att.net> skrev i meddelandet
> news:%23tQRaguJEHA.3944@.tk2msftngp13.phx.gbl...
> collation
> ----
> ----
>
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Friday, March 23, 2012
error when inserting same data into database
hello,
im having a problem with my sql statement. i have an insert statement
that inserts new "comments" into my database, but every so often, the
data that needs to be inserted is the same "comment" that is already in
the database. this produces an error. i want the statement to add a
new comment if it's not there, and to override or skip over the
comments that already exist in the database. here's my code:
'Insert New Comments Into Comments
strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
Commentator ) " & _
"SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments: " & Err.DESCRIPTION
Err.Clear
End If
'Insert Comments Into Comments_History
strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
CommentDate, Commentator ) " & _
"SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments History: " & Err.DESCRIPTION
Err.Clear
End If
any help would be outstanding!
thanks!!!
rickWhat is the error you are getting? The most likely suspect is that you are
inserting duplicate values into a column with a primary key or unique index
defined. Logically, it would seem that [Order], [CommentDate] would be the
primary key, but it's a crazy world and the database developer may have
placed the key on [Comment].
<RSummersJr@.gmail.com> wrote in message
news:1135025948.470130.281510@.o13g2000cwo.googlegroups.com...
> hello,
> im having a problem with my sql statement. i have an insert statement
> that inserts new "comments" into my database, but every so often, the
> data that needs to be inserted is the same "comment" that is already in
> the database. this produces an error. i want the statement to add a
> new comment if it's not there, and to override or skip over the
> comments that already exist in the database. here's my code:
> 'Insert New Comments Into Comments
> strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
> Commentator ) " & _
> "SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
> Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments: " & Err.DESCRIPTION
> Err.Clear
> End If
> 'Insert Comments Into Comments_History
> strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
> CommentDate, Commentator ) " & _
> "SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
> Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments History: " & Err.DESCRIPTION
> Err.Clear
> End If
>
> any help would be outstanding!
> thanks!!!
> rick
>|||well...what i think is happening is that the person is trying to
re-save the same comments, and then the insert statement produces an
error because there are two of the same comments in the database...the
error is:
Insert Comments History: The changes you requested to the table were
not successful because they would create duplicate values in the index,
primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to
permit duplicate entries and try again.
I want the comment to override the existing comment instead of adding
the same comment again. any help would be great...thanks!
rick|||So you want your INSERT to become an UPDATE, right?
You need to prevent an insert if the key already exists and instead update
the values. Please post DDL and sample data to get better help.
ML
http://milambda.blogspot.com/|||Ask whomover designed the database what primary keys, unique indexes or
constraints are defined on the table. Without that information, it's hard
for a developer to code against it.
<RSummersJr@.gmail.com> wrote in message
news:1135030402.665994.163870@.g47g2000cwa.googlegroups.com...
> well...what i think is happening is that the person is trying to
> re-save the same comments, and then the insert statement produces an
> error because there are two of the same comments in the database...the
> error is:
> Insert Comments History: The changes you requested to the table were
> not successful because they would create duplicate values in the index,
> primary key, or relationship. Change the data in the field or fields
> that contain duplicate data, remove the index, or redefine the index to
> permit duplicate entries and try again.
> I want the comment to override the existing comment instead of adding
> the same comment again. any help would be great...thanks!
> rick
>
im having a problem with my sql statement. i have an insert statement
that inserts new "comments" into my database, but every so often, the
data that needs to be inserted is the same "comment" that is already in
the database. this produces an error. i want the statement to add a
new comment if it's not there, and to override or skip over the
comments that already exist in the database. here's my code:
'Insert New Comments Into Comments
strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
Commentator ) " & _
"SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments: " & Err.DESCRIPTION
Err.Clear
End If
'Insert Comments Into Comments_History
strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
CommentDate, Commentator ) " & _
"SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments History: " & Err.DESCRIPTION
Err.Clear
End If
any help would be outstanding!
thanks!!!
rickWhat is the error you are getting? The most likely suspect is that you are
inserting duplicate values into a column with a primary key or unique index
defined. Logically, it would seem that [Order], [CommentDate] would be the
primary key, but it's a crazy world and the database developer may have
placed the key on [Comment].
<RSummersJr@.gmail.com> wrote in message
news:1135025948.470130.281510@.o13g2000cwo.googlegroups.com...
> hello,
> im having a problem with my sql statement. i have an insert statement
> that inserts new "comments" into my database, but every so often, the
> data that needs to be inserted is the same "comment" that is already in
> the database. this produces an error. i want the statement to add a
> new comment if it's not there, and to override or skip over the
> comments that already exist in the database. here's my code:
> 'Insert New Comments Into Comments
> strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
> Commentator ) " & _
> "SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
> Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments: " & Err.DESCRIPTION
> Err.Clear
> End If
> 'Insert Comments Into Comments_History
> strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
> CommentDate, Commentator ) " & _
> "SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
> Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments History: " & Err.DESCRIPTION
> Err.Clear
> End If
>
> any help would be outstanding!
> thanks!!!
> rick
>|||well...what i think is happening is that the person is trying to
re-save the same comments, and then the insert statement produces an
error because there are two of the same comments in the database...the
error is:
Insert Comments History: The changes you requested to the table were
not successful because they would create duplicate values in the index,
primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to
permit duplicate entries and try again.
I want the comment to override the existing comment instead of adding
the same comment again. any help would be great...thanks!
rick|||So you want your INSERT to become an UPDATE, right?
You need to prevent an insert if the key already exists and instead update
the values. Please post DDL and sample data to get better help.
ML
http://milambda.blogspot.com/|||Ask whomover designed the database what primary keys, unique indexes or
constraints are defined on the table. Without that information, it's hard
for a developer to code against it.
<RSummersJr@.gmail.com> wrote in message
news:1135030402.665994.163870@.g47g2000cwa.googlegroups.com...
> well...what i think is happening is that the person is trying to
> re-save the same comments, and then the insert statement produces an
> error because there are two of the same comments in the database...the
> error is:
> Insert Comments History: The changes you requested to the table were
> not successful because they would create duplicate values in the index,
> primary key, or relationship. Change the data in the field or fields
> that contain duplicate data, remove the index, or redefine the index to
> permit duplicate entries and try again.
> I want the comment to override the existing comment instead of adding
> the same comment again. any help would be great...thanks!
> rick
>
Error when inserting datetime in german SQL Server 2000
Hello!
I have a program that are using a stored procedure to insert a row in
an SQL Server. During several years this program has worked fine using
an English version of SQL server. Now a customer wants to use a german
SQL server, but it doesn't work. The row is never inserted. I get the
following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
Konvertieren von Datentyp varchar in datetime.
Now, if I change the language setting to english in the "SQL Server
Login properties for 'sa'" it all works. If I restore the language
setting to german in the "SQL Server Login properties for 'sa'", it
all continues to work until I re-boot the PC. When I have re-booted, I
once gain end up with the error that I have described before.
It seems strange to me that SQL Server doesn't behave in a consistent
way. Sometimes it works with german language settings, sometimes it
doesn't.
Any explanations would be very appriciated.
BR / PatrikThis is because dateformat changes according to language settings.
date format considerations while insert and update:
You can SET DATEFORMAT and modify code as follows.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@.dt)) ;
select * from test;
go
OR other method would be to use explicit coversion of the
date using CONVERT function.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
insert into test values(convert(datetime,@.dt, 103)) ;
select * from test;
go
--
-Vishal
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Patrik,
Yes, language and localizations are a problem to deal with. You might find
this topic helpful:
Writing International Transact-SQL Statements in the MSDN at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp
You can also manage this by setting the language of the connection that is
transmitting data. For this use: SET LANGUAGE xxx for a connection or use
sp_defaultlanguage to set the default for a login.
Russell Fields
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no confusion
for people *or* the software.
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Only YYYYMMDD without any separators works always:
SET DATEFORMAT dmy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
SET DATEFORMAT mdy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
I can see that you as an American are not that used to dealing with date
formats as I as a European am ;-)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uame7U9PDHA.2768@.tk2msftngp13.phx.gbl...
> Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no
confusion
> for people *or* the software.
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0307010514.b59427d@.posting.google.com...
> > Hello!
> >
> > I have a program that are using a stored procedure to insert a row in
> > an SQL Server. During several years this program has worked fine using
> > an English version of SQL server. Now a customer wants to use a german
> > SQL server, but it doesn't work. The row is never inserted. I get the
> > following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> > Konvertieren von Datentyp varchar in datetime.
> >
> > Now, if I change the language setting to english in the "SQL Server
> > Login properties for 'sa'" it all works. If I restore the language
> > setting to german in the "SQL Server Login properties for 'sa'", it
> > all continues to work until I re-boot the PC. When I have re-booted, I
> > once gain end up with the error that I have described before.
> >
> > It seems strange to me that SQL Server doesn't behave in a consistent
> > way. Sometimes it works with german language settings, sometimes it
> > doesn't.
> >
> > Any explanations would be very appriciated.
> >
> > BR / Patrik
>|||> I can see that you as an American are not that used to dealing with date
> formats as I as a European am ;-)
Hey, don't call me an American... I'm a Canuck, just ask Tom...
:-Psql
I have a program that are using a stored procedure to insert a row in
an SQL Server. During several years this program has worked fine using
an English version of SQL server. Now a customer wants to use a german
SQL server, but it doesn't work. The row is never inserted. I get the
following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
Konvertieren von Datentyp varchar in datetime.
Now, if I change the language setting to english in the "SQL Server
Login properties for 'sa'" it all works. If I restore the language
setting to german in the "SQL Server Login properties for 'sa'", it
all continues to work until I re-boot the PC. When I have re-booted, I
once gain end up with the error that I have described before.
It seems strange to me that SQL Server doesn't behave in a consistent
way. Sometimes it works with german language settings, sometimes it
doesn't.
Any explanations would be very appriciated.
BR / PatrikThis is because dateformat changes according to language settings.
date format considerations while insert and update:
You can SET DATEFORMAT and modify code as follows.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@.dt)) ;
select * from test;
go
OR other method would be to use explicit coversion of the
date using CONVERT function.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
insert into test values(convert(datetime,@.dt, 103)) ;
select * from test;
go
--
-Vishal
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Patrik,
Yes, language and localizations are a problem to deal with. You might find
this topic helpful:
Writing International Transact-SQL Statements in the MSDN at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp
You can also manage this by setting the language of the connection that is
transmitting data. For this use: SET LANGUAGE xxx for a connection or use
sp_defaultlanguage to set the default for a login.
Russell Fields
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no confusion
for people *or* the software.
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Only YYYYMMDD without any separators works always:
SET DATEFORMAT dmy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
SET DATEFORMAT mdy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
I can see that you as an American are not that used to dealing with date
formats as I as a European am ;-)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uame7U9PDHA.2768@.tk2msftngp13.phx.gbl...
> Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no
confusion
> for people *or* the software.
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0307010514.b59427d@.posting.google.com...
> > Hello!
> >
> > I have a program that are using a stored procedure to insert a row in
> > an SQL Server. During several years this program has worked fine using
> > an English version of SQL server. Now a customer wants to use a german
> > SQL server, but it doesn't work. The row is never inserted. I get the
> > following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> > Konvertieren von Datentyp varchar in datetime.
> >
> > Now, if I change the language setting to english in the "SQL Server
> > Login properties for 'sa'" it all works. If I restore the language
> > setting to german in the "SQL Server Login properties for 'sa'", it
> > all continues to work until I re-boot the PC. When I have re-booted, I
> > once gain end up with the error that I have described before.
> >
> > It seems strange to me that SQL Server doesn't behave in a consistent
> > way. Sometimes it works with german language settings, sometimes it
> > doesn't.
> >
> > Any explanations would be very appriciated.
> >
> > BR / Patrik
>|||> I can see that you as an American are not that used to dealing with date
> formats as I as a European am ;-)
Hey, don't call me an American... I'm a Canuck, just ask Tom...
:-Psql
Monday, March 12, 2012
Error when cast Varchar to decimal
I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))
What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>
|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>
|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]
|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>
|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))
What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>
|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>
|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]
|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>
|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Error when cast Varchar to decimal
I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left
and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left[/vbco
l]
and[vbcol=seagreen]
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
>
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric
*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left
and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left[/vbco
l]
and[vbcol=seagreen]
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
>
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric
*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Error when cast Varchar to decimal
I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > I'm writing a script with an insert statment to insert records from one
> > database to another but I have to change the type first. How can I find
> out
> > more about what can be causing this or is it something wrong with my
> syntax?
> > The field [patient_weight] is a varchar(256) stripped of all spaces left
> and
> > right.
> > Thanks, Alpha
> >
> > cast([patient_weight] as decimal(10,2))
> >
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
> > What result do you get here?
> >
> > SELECT COUNT(*) FROM [source table name]
> > WHERE ISNUMERIC(patient_weight) = 0
> >
> > --
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > I'm writing a script with an insert statment to insert records from
one
> > > database to another but I have to change the type first. How can I
find
> > out
> > > more about what can be causing this or is it something wrong with my
> > syntax?
> > > The field [patient_weight] is a varchar(256) stripped of all spaces
left
> > and
> > > right.
> > > Thanks, Alpha
> > >
> > > cast([patient_weight] as decimal(10,2))
> > >
> >
> >
> >|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> > I got 4. I looked at the table and they're all '0'(zero) except these 4.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > What result do you get here?
> > >
> > > SELECT COUNT(*) FROM [source table name]
> > > WHERE ISNUMERIC(patient_weight) = 0
> > >
> > > --
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > > I'm writing a script with an insert statment to insert records from
> one
> > > > database to another but I have to change the type first. How can I
> find
> > > out
> > > > more about what can be causing this or is it something wrong with my
> > > syntax?
> > > > The field [patient_weight] is a varchar(256) stripped of all spaces
> left
> > > and
> > > > right.
> > > > Thanks, Alpha
> > > >
> > > > cast([patient_weight] as decimal(10,2))
> > > >
> > >
> > >
> > >
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> > Thank you that works but I don't understadn what happend here. So these 4
> > rows has a '0'(zero) char in it with CR. So how does this query able to
> > select it? ISNUMERIC cast it to 0 (zero in numerica) right?
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > I'm writing a script with an insert statment to insert records from one
> > database to another but I have to change the type first. How can I find
> out
> > more about what can be causing this or is it something wrong with my
> syntax?
> > The field [patient_weight] is a varchar(256) stripped of all spaces left
> and
> > right.
> > Thanks, Alpha
> >
> > cast([patient_weight] as decimal(10,2))
> >
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
> > What result do you get here?
> >
> > SELECT COUNT(*) FROM [source table name]
> > WHERE ISNUMERIC(patient_weight) = 0
> >
> > --
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > I'm writing a script with an insert statment to insert records from
one
> > > database to another but I have to change the type first. How can I
find
> > out
> > > more about what can be causing this or is it something wrong with my
> > syntax?
> > > The field [patient_weight] is a varchar(256) stripped of all spaces
left
> > and
> > > right.
> > > Thanks, Alpha
> > >
> > > cast([patient_weight] as decimal(10,2))
> > >
> >
> >
> >|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> > I got 4. I looked at the table and they're all '0'(zero) except these 4.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > What result do you get here?
> > >
> > > SELECT COUNT(*) FROM [source table name]
> > > WHERE ISNUMERIC(patient_weight) = 0
> > >
> > > --
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > > I'm writing a script with an insert statment to insert records from
> one
> > > > database to another but I have to change the type first. How can I
> find
> > > out
> > > > more about what can be causing this or is it something wrong with my
> > > syntax?
> > > > The field [patient_weight] is a varchar(256) stripped of all spaces
> left
> > > and
> > > > right.
> > > > Thanks, Alpha
> > > >
> > > > cast([patient_weight] as decimal(10,2))
> > > >
> > >
> > >
> > >
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> > Thank you that works but I don't understadn what happend here. So these 4
> > rows has a '0'(zero) char in it with CR. So how does this query able to
> > select it? ISNUMERIC cast it to 0 (zero in numerica) right?
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Wednesday, March 7, 2012
error using dbms.sql
Hello:
I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
Procedure created.
No errors.
SQL> exec readtable('state_relation')
BEGIN readtable('state_relation'); END;
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "CHADBA.READTABLE", line 78
ORA-06512: at line
The code is the following:
set serveroutput on size 1000000
CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS
v_tablename varchar2(30);
v_tablekey varchar2(30);
fccid number;
name varchar2(30);
state varchar2(2);
strsql varchar2(1000);
numrows number;
averageleng number;
bytes number;
countreg number;
countbytes number;
cursor1 INTEGER;
cursor2 INTEGER;
ignore INTEGER;
BEGIN
-- OPEN CURSOR
cursor1 := dbms_sql.open_cursor;
-- PREPARE PARSE
DBMS_SQL.PARSE(cursor1,
'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
DBMS_SQL.native);
-- use bind variables? No
-- use query ? yes
-- Define colmna
DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
-- execute
ignore := DBMS_SQL.EXECUTE(cursor1);
-- Prepare cursor for read and insert in other table
cursor2 := DBMS_SQL.OPEN_CURSOR;
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '
|| 'v_tablekey l'
|| 'v_tablename m'
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
-- Parse cursor2
DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);
-- fetch_rows
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);
ignore := DBMS_SQL.EXECUTE(cursor2);
ELSE
-- No more rows to read
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor1);
DBMS_SQL.CLOSE_CURSOR(cursor2);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cursor1) THEN
DBMS_SQL.CLOSE_CURSOR(cursor1);
END IF;
IF DBMS_SQL.IS_OPEN(cursor2) THEN
DBMS_SQL.CLOSE_CURSOR(cursor2);
END IF;
RAISE;
END readtable;
/
show errors;
Can some body help me? Thanks in advanceRemove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.
Here is the correct stmt :
DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);
Originally posted by nelari
Hello:
I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
Procedure created.
No errors.
SQL> exec readtable('state_relation')
BEGIN readtable('state_relation'); END;
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "CHADBA.READTABLE", line 78
ORA-06512: at line
The code is the following:
set serveroutput on size 1000000
CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS
v_tablename varchar2(30);
v_tablekey varchar2(30);
fccid number;
name varchar2(30);
state varchar2(2);
strsql varchar2(1000);
numrows number;
averageleng number;
bytes number;
countreg number;
countbytes number;
cursor1 INTEGER;
cursor2 INTEGER;
ignore INTEGER;
BEGIN
-- OPEN CURSOR
cursor1 := dbms_sql.open_cursor;
-- PREPARE PARSE
DBMS_SQL.PARSE(cursor1,
'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
DBMS_SQL.native);
-- use bind variables? No
-- use query ? yes
-- Define colmna
DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
-- execute
ignore := DBMS_SQL.EXECUTE(cursor1);
-- Prepare cursor for read and insert in other table
cursor2 := DBMS_SQL.OPEN_CURSOR;
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '
|| 'v_tablekey l'
|| 'v_tablename m'
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
-- Parse cursor2
DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);
-- fetch_rows
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);
ignore := DBMS_SQL.EXECUTE(cursor2);
ELSE
-- No more rows to read
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor1);
DBMS_SQL.CLOSE_CURSOR(cursor2);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cursor1) THEN
DBMS_SQL.CLOSE_CURSOR(cursor1);
END IF;
IF DBMS_SQL.IS_OPEN(cursor2) THEN
DBMS_SQL.CLOSE_CURSOR(cursor2);
END IF;
RAISE;
END readtable;
/
show errors;
Can some body help me? Thanks in advance|||Originally posted by dbmadcap
Remove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.
Here is the correct stmt :
DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);
Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.|||You have v_tablekey and v_tablename inside quotes. Change it as below :
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '|| v_tablekey || ' l '
|| ' , '|| v_tablename || ' m '
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
Also as a humble suggestion, try to form the string in fewer lines so that it will make easy for you to read & debug
Originally posted by nelari
Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.
I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
Procedure created.
No errors.
SQL> exec readtable('state_relation')
BEGIN readtable('state_relation'); END;
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "CHADBA.READTABLE", line 78
ORA-06512: at line
The code is the following:
set serveroutput on size 1000000
CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS
v_tablename varchar2(30);
v_tablekey varchar2(30);
fccid number;
name varchar2(30);
state varchar2(2);
strsql varchar2(1000);
numrows number;
averageleng number;
bytes number;
countreg number;
countbytes number;
cursor1 INTEGER;
cursor2 INTEGER;
ignore INTEGER;
BEGIN
-- OPEN CURSOR
cursor1 := dbms_sql.open_cursor;
-- PREPARE PARSE
DBMS_SQL.PARSE(cursor1,
'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
DBMS_SQL.native);
-- use bind variables? No
-- use query ? yes
-- Define colmna
DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
-- execute
ignore := DBMS_SQL.EXECUTE(cursor1);
-- Prepare cursor for read and insert in other table
cursor2 := DBMS_SQL.OPEN_CURSOR;
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '
|| 'v_tablekey l'
|| 'v_tablename m'
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
-- Parse cursor2
DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);
-- fetch_rows
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);
ignore := DBMS_SQL.EXECUTE(cursor2);
ELSE
-- No more rows to read
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor1);
DBMS_SQL.CLOSE_CURSOR(cursor2);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cursor1) THEN
DBMS_SQL.CLOSE_CURSOR(cursor1);
END IF;
IF DBMS_SQL.IS_OPEN(cursor2) THEN
DBMS_SQL.CLOSE_CURSOR(cursor2);
END IF;
RAISE;
END readtable;
/
show errors;
Can some body help me? Thanks in advanceRemove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.
Here is the correct stmt :
DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);
Originally posted by nelari
Hello:
I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
Procedure created.
No errors.
SQL> exec readtable('state_relation')
BEGIN readtable('state_relation'); END;
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "CHADBA.READTABLE", line 78
ORA-06512: at line
The code is the following:
set serveroutput on size 1000000
CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS
v_tablename varchar2(30);
v_tablekey varchar2(30);
fccid number;
name varchar2(30);
state varchar2(2);
strsql varchar2(1000);
numrows number;
averageleng number;
bytes number;
countreg number;
countbytes number;
cursor1 INTEGER;
cursor2 INTEGER;
ignore INTEGER;
BEGIN
-- OPEN CURSOR
cursor1 := dbms_sql.open_cursor;
-- PREPARE PARSE
DBMS_SQL.PARSE(cursor1,
'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
DBMS_SQL.native);
-- use bind variables? No
-- use query ? yes
-- Define colmna
DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
-- execute
ignore := DBMS_SQL.EXECUTE(cursor1);
-- Prepare cursor for read and insert in other table
cursor2 := DBMS_SQL.OPEN_CURSOR;
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '
|| 'v_tablekey l'
|| 'v_tablename m'
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
-- Parse cursor2
DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);
-- fetch_rows
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);
ignore := DBMS_SQL.EXECUTE(cursor2);
ELSE
-- No more rows to read
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor1);
DBMS_SQL.CLOSE_CURSOR(cursor2);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cursor1) THEN
DBMS_SQL.CLOSE_CURSOR(cursor1);
END IF;
IF DBMS_SQL.IS_OPEN(cursor2) THEN
DBMS_SQL.CLOSE_CURSOR(cursor2);
END IF;
RAISE;
END readtable;
/
show errors;
Can some body help me? Thanks in advance|||Originally posted by dbmadcap
Remove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.
Here is the correct stmt :
DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);
Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.|||You have v_tablekey and v_tablename inside quotes. Change it as below :
strsql := ' SELECT DISTINCT '
|| ' l.bill_fccid '
|| ' l.name'
|| ' count(l.svcing_st) '
|| ' from '|| v_tablekey || ' l '
|| ' , '|| v_tablename || ' m '
|| ' where '
|| 'l.bill_fccid'
|| ' = '
|| 'm.bill_fccid'
|| 'group by '
|| ' l.bill_fccid'
|| 'l.name';
Also as a humble suggestion, try to form the string in fewer lines so that it will make easy for you to read & debug
Originally posted by nelari
Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.
Sunday, February 26, 2012
ERROR USING BULK INSERT
Hi
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
Error Updating DB2 on MVS-MF from a Linked Server
Need to be able to run update queries on DB2 on IBM MF from a Linked Server. Select and Insert queries work but Update and Delete queries don't. DB2 connect is installed and ODBC System dsn's are created for DEV and Production DB2 environments.
The ODBC drivers can be selected when running Imports/Exports but can't be specified through a linked server.
Any Ideas?
Tom...
Linked Servers do not directly support ODBC drivers, but you can plug in ODBC drivers by using a OLE DB to ODBC bridge technology 'Microsoft OLE DB Provider for ODBC (MSDASQL)'. This componentships with MDAC but is not available on 64-bit at this time.
Although option would be to use Microsoft's DB2 OLE DB Provider directly with Linked Server available for download on http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.Error updating AS400 table
SQL2000 SP3a
I have a statement as follows which attempts to insert data into an AS400
table using a linked server.
[b]SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRAN
INSERT OPENQUERY(LS_HMVCASW1,
'SELECT
FIELD
FROM
WARDOUR1.PCUPDDTA.KENNY
WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
SELECT
'X'
COMMIT TRAN
SET XACT_ABORT OFF
GO[/b]
I get the following error:
[b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
returned 0x80004005: The provider did not give any information about the
error.].[/b]
Journaling is set on on the AS400 tables. As far as I can make out, the
provider supports distributed transactions - question is how? The user in
the LS has correct permissions on AS400.
Any help much appreciated.The error does imply Access Denied. Use your AS400 query interface, log on
as the user this job is running under, and manually test the SQL
Jeff
"Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> SQL2000 SP3a
> I have a statement as follows which attempts to insert data into an AS400
> table using a linked server.
> [b]SET XACT_ABORT ON
> GO
> BEGIN DISTRIBUTED TRAN
> INSERT OPENQUERY(LS_HMVCASW1,
> 'SELECT
> FIELD
> FROM
> WARDOUR1.PCUPDDTA.KENNY
> WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> SELECT
> 'X'
> COMMIT TRAN
> SET XACT_ABORT OFF
> GO[/b]
> I get the following error:
> [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> returned 0x80004005: The provider did not give any information about the
> error.].[/b]
>
> Journaling is set on on the AS400 tables. As far as I can make out, the
> provider supports distributed transactions - question is how? The user in
> the LS has correct permissions on AS400.
> Any help much appreciated.|||I cannot log into the green screen using this profile as it has been set up
to boot you off immediately you log on, but I can link to the table from
Access using the same system DSN and user profile, and I can add/edit data
through that, so that to me means permissions are ok.
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>|||After applying a Client Access SP, I've got it working ... sometimes. If I
remove the BEGIN TRAN and COMMIT TRAN it works. Leave those in and I get :
[b]The operation could not be performed because the OLE DB provider
'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[/b]
But if I stop and restart SQL, the TRAN stuff then works.. but only once,
which is bizarre. If I run it a second time it errors again. The field I'm
updating doesn't need to be unique.
Any ideas?
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>
I have a statement as follows which attempts to insert data into an AS400
table using a linked server.
[b]SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRAN
INSERT OPENQUERY(LS_HMVCASW1,
'SELECT
FIELD
FROM
WARDOUR1.PCUPDDTA.KENNY
WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
SELECT
'X'
COMMIT TRAN
SET XACT_ABORT OFF
GO[/b]
I get the following error:
[b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
returned 0x80004005: The provider did not give any information about the
error.].[/b]
Journaling is set on on the AS400 tables. As far as I can make out, the
provider supports distributed transactions - question is how? The user in
the LS has correct permissions on AS400.
Any help much appreciated.The error does imply Access Denied. Use your AS400 query interface, log on
as the user this job is running under, and manually test the SQL
Jeff
"Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> SQL2000 SP3a
> I have a statement as follows which attempts to insert data into an AS400
> table using a linked server.
> [b]SET XACT_ABORT ON
> GO
> BEGIN DISTRIBUTED TRAN
> INSERT OPENQUERY(LS_HMVCASW1,
> 'SELECT
> FIELD
> FROM
> WARDOUR1.PCUPDDTA.KENNY
> WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> SELECT
> 'X'
> COMMIT TRAN
> SET XACT_ABORT OFF
> GO[/b]
> I get the following error:
> [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> returned 0x80004005: The provider did not give any information about the
> error.].[/b]
>
> Journaling is set on on the AS400 tables. As far as I can make out, the
> provider supports distributed transactions - question is how? The user in
> the LS has correct permissions on AS400.
> Any help much appreciated.|||I cannot log into the green screen using this profile as it has been set up
to boot you off immediately you log on, but I can link to the table from
Access using the same system DSN and user profile, and I can add/edit data
through that, so that to me means permissions are ok.
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>|||After applying a Client Access SP, I've got it working ... sometimes. If I
remove the BEGIN TRAN and COMMIT TRAN it works. Leave those in and I get :
[b]The operation could not be performed because the OLE DB provider
'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[/b]
But if I stop and restart SQL, the TRAN stuff then works.. but only once,
which is bizarre. If I run it a second time it errors again. The field I'm
updating doesn't need to be unique.
Any ideas?
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>
Friday, February 24, 2012
Error trying to Insert into Table -
Hello...
I have a stored procedure that is failing to insert into a table. When I
manually run the INSERT statement in SQL QA, I receive the following error
message:
"Server: Msg 208, Level 16, State 1, Procedure
ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
I ran a query against the SysObjects table and there is not a record of the
"tsvw.." object..but there is one for the "ins_C525744..." referenced in the
first part of the error message.
This seems to be related to our replication setup...but I am unsure how to
resolve or actually find the problem.
I looked at the Stored Procedures list in the database and I can't find one
named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
If anyone has any advice or suggestions I'd appreciate it.
thanks
- dw
Is this table published for merge replication or a subscriber to a merge
publication?
If so, your replication deployment has not been successful. I would advise
you to drop your subscription and recreate it.
If not, run this script to scrub your subscription database:
http://groups-beta.google.com/group/...a?dmode=source
"dw" <dw@.discussions.microsoft.com> wrote in message
news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> Hello...
> I have a stored procedure that is failing to insert into a table. When I
> manually run the INSERT statement in SQL QA, I receive the following error
> message:
> "Server: Msg 208, Level 16, State 1, Procedure
> ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
> Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
> I ran a query against the SysObjects table and there is not a record of
the
> "tsvw.." object..but there is one for the "ins_C525744..." referenced in
the
> first part of the error message.
> This seems to be related to our replication setup...but I am unsure how to
> resolve or actually find the problem.
> I looked at the Stored Procedures list in the database and I can't find
one
> named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
> If anyone has any advice or suggestions I'd appreciate it.
> thanks
> - dw
|||Actually, it isn't a table...as it turns out...it is a reference to an
Insert Trigger on a table that is part of a Merge Replication.
I discovered this last night...there were two Insert Triggers on this table
(which is part of a merge publication). I deleted the one that was causing
the error and now everything works (even my replication/subscriptions) again.
- dw
"Hilary Cotter" wrote:
> Is this table published for merge replication or a subscriber to a merge
> publication?
> If so, your replication deployment has not been successful. I would advise
> you to drop your subscription and recreate it.
> If not, run this script to scrub your subscription database:
> http://groups-beta.google.com/group/...a?dmode=source
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> the
> the
> one
>
>
I have a stored procedure that is failing to insert into a table. When I
manually run the INSERT statement in SQL QA, I receive the following error
message:
"Server: Msg 208, Level 16, State 1, Procedure
ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
I ran a query against the SysObjects table and there is not a record of the
"tsvw.." object..but there is one for the "ins_C525744..." referenced in the
first part of the error message.
This seems to be related to our replication setup...but I am unsure how to
resolve or actually find the problem.
I looked at the Stored Procedures list in the database and I can't find one
named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
If anyone has any advice or suggestions I'd appreciate it.
thanks
- dw
Is this table published for merge replication or a subscriber to a merge
publication?
If so, your replication deployment has not been successful. I would advise
you to drop your subscription and recreate it.
If not, run this script to scrub your subscription database:
http://groups-beta.google.com/group/...a?dmode=source
"dw" <dw@.discussions.microsoft.com> wrote in message
news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> Hello...
> I have a stored procedure that is failing to insert into a table. When I
> manually run the INSERT statement in SQL QA, I receive the following error
> message:
> "Server: Msg 208, Level 16, State 1, Procedure
> ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
> Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
> I ran a query against the SysObjects table and there is not a record of
the
> "tsvw.." object..but there is one for the "ins_C525744..." referenced in
the
> first part of the error message.
> This seems to be related to our replication setup...but I am unsure how to
> resolve or actually find the problem.
> I looked at the Stored Procedures list in the database and I can't find
one
> named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
> If anyone has any advice or suggestions I'd appreciate it.
> thanks
> - dw
|||Actually, it isn't a table...as it turns out...it is a reference to an
Insert Trigger on a table that is part of a Merge Replication.
I discovered this last night...there were two Insert Triggers on this table
(which is part of a merge publication). I deleted the one that was causing
the error and now everything works (even my replication/subscriptions) again.
- dw
"Hilary Cotter" wrote:
> Is this table published for merge replication or a subscriber to a merge
> publication?
> If so, your replication deployment has not been successful. I would advise
> you to drop your subscription and recreate it.
> If not, run this script to scrub your subscription database:
> http://groups-beta.google.com/group/...a?dmode=source
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> the
> the
> one
>
>
Friday, February 17, 2012
Error SQL server when try to select or delete or add new
when i try to get data or insert new from some table.
SQL show this message error
Server: Msg 3624, Level 20, State 1, Line 1
Location: q:\SPHINX\NTDBMS\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 2116
Connection Broken
-
how to fix this problem ? These table have around 2,900,000 records.
thanks.
Unfortunately, that seems like some form of data corruption. Try running DBCC CHECKDB and DBCC CHECKTABLE to see if you can locate the inconsistency errors. It may be that you'll have to perform a restore from a backup or perhaps a REPAIR_REBUILD will solve the problem.
Check Books Online for further information on the commands.
HTH!
error sql 2000
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
Line 53
Cannot insert the value NULL into column '', table '';
column does not allow nulls. INSERT fails.
The statement has been terminated.HI,
You might get this error if you have an invalid database owner. Execute the
below script to verify,
SELECT name, SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Incase if you have any invalid users , execute sp_changedbowner (refer books
online) stored procedure.
Thanks
Hari
MCDBA
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Are you running sp_helpdb with a database name or without? If the latter,
does it work if you explicitly set a database name that you have access to?
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.
Line 53
Cannot insert the value NULL into column '', table '';
column does not allow nulls. INSERT fails.
The statement has been terminated.HI,
You might get this error if you have an invalid database owner. Execute the
below script to verify,
SELECT name, SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Incase if you have any invalid users , execute sp_changedbowner (refer books
online) stored procedure.
Thanks
Hari
MCDBA
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Are you running sp_helpdb with a database name or without? If the latter,
does it work if you explicitly set a database name that you have access to?
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.
Wednesday, February 15, 2012
Error saving diagram
I'm trying to save a diagram but get this error:
"Cannot insert the value NULL into column 'version',
table 'Hernan.dbo.dtproperties'; column does not allows
nulls. INSERT fails.
The statement has been terminated
The 'dt_adduserobject' procedure attempted to return a
status of NULL, wich is not allowed. A status of 0 will be
returned instead."
can you help me ?
cannot save any diagram.
thanks in advance,
Hernanwithout the table DLL I'm afraid there's not much help for you.
Did you try to add a column with a null value and make it PK?
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan|||my bad.
I thought you had this error on a user table.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:Of2NBtRYDHA.1900@.TK2MSFTNGP10.phx.gbl...
> without the table DLL I'm afraid there's not much help for you.
> Did you try to add a column with a null value and make it PK?
>
> "Hernan" <hvaldes@.frisa.com> wrote in message
> news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> > I'm trying to save a diagram but get this error:
> >
> > "Cannot insert the value NULL into column 'version',
> > table 'Hernan.dbo.dtproperties'; column does not allows
> > nulls. INSERT fails.
> > The statement has been terminated
> > The 'dt_adduserobject' procedure attempted to return a
> > status of NULL, wich is not allowed. A status of 0 will be
> > returned instead."
> >
> > can you help me ?
> > cannot save any diagram.
> >
> > thanks in advance,
> > Hernan
>|||Hernan,
Whats the SQL Server version? The below workaround is mentioned in
BooksOnLine for this error:
ALTER TABLE dbo.dtproperties ADD uvalue NVARCHAR(255) NULL
GO
IF EXISTS(SELECT * FROM dbo.dtproperties) EXEC('UPDATE dbo.dtproperties SET
uvalue = CONVERT(NVARCHAR(255), value)')
GO
If it still doesnt help, read on.
>> cannot save any diagram.
Does that mean you dont have any database diagrams? If so, try dropping the
dtproperties table.This table is recreated automatically every time you try
to save a database diagram.You have to unmark the table as a system
object.Since this is undocumented, the usual warnings exist:
dbo.sp_msUnmarkschemaobject dtproperties
drop table dtproperties
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan
"Cannot insert the value NULL into column 'version',
table 'Hernan.dbo.dtproperties'; column does not allows
nulls. INSERT fails.
The statement has been terminated
The 'dt_adduserobject' procedure attempted to return a
status of NULL, wich is not allowed. A status of 0 will be
returned instead."
can you help me ?
cannot save any diagram.
thanks in advance,
Hernanwithout the table DLL I'm afraid there's not much help for you.
Did you try to add a column with a null value and make it PK?
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan|||my bad.
I thought you had this error on a user table.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:Of2NBtRYDHA.1900@.TK2MSFTNGP10.phx.gbl...
> without the table DLL I'm afraid there's not much help for you.
> Did you try to add a column with a null value and make it PK?
>
> "Hernan" <hvaldes@.frisa.com> wrote in message
> news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> > I'm trying to save a diagram but get this error:
> >
> > "Cannot insert the value NULL into column 'version',
> > table 'Hernan.dbo.dtproperties'; column does not allows
> > nulls. INSERT fails.
> > The statement has been terminated
> > The 'dt_adduserobject' procedure attempted to return a
> > status of NULL, wich is not allowed. A status of 0 will be
> > returned instead."
> >
> > can you help me ?
> > cannot save any diagram.
> >
> > thanks in advance,
> > Hernan
>|||Hernan,
Whats the SQL Server version? The below workaround is mentioned in
BooksOnLine for this error:
ALTER TABLE dbo.dtproperties ADD uvalue NVARCHAR(255) NULL
GO
IF EXISTS(SELECT * FROM dbo.dtproperties) EXEC('UPDATE dbo.dtproperties SET
uvalue = CONVERT(NVARCHAR(255), value)')
GO
If it still doesnt help, read on.
>> cannot save any diagram.
Does that mean you dont have any database diagrams? If so, try dropping the
dtproperties table.This table is recreated automatically every time you try
to save a database diagram.You have to unmark the table as a system
object.Since this is undocumented, the usual warnings exist:
dbo.sp_msUnmarkschemaobject dtproperties
drop table dtproperties
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan
Subscribe to:
Posts (Atom)