PAGE HEADER

Wednesday, March 7, 2012

problem with query moving data from 1 dbase to another.

I have a large dbase table and am moving the data from 1 table to another.
The problem is that I had to change the datatypes in the destination table so
am getting the failure
Syntax error converting the nvarchar to int.
INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
Select [Data_Item_Log_ID] from
[dbase2].dbo.dataitemlog
Data_Item_Log_ID (destination field) is type integer and the source field
(from another dbase on the same server) is type nvarchar 4. I have several
columns like this and the table has approximately 20k records.
The origin of the source database was a MySQL dbase which I moved to SQL2000
using a driver and access. Thanks.
--
Paul G
Software engineer.If I understand the problem connect, try:
INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
Select CAST([Data_Item_Log_ID] AS int) from
[dbase2].dbo.dataitemlog
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> I have a large dbase table and am moving the data from 1 table to another.
> The problem is that I had to change the datatypes in the destination table so
> am getting the failure
> Syntax error converting the nvarchar to int.
> INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> Select [Data_Item_Log_ID] from
> [dbase2].dbo.dataitemlog
> Data_Item_Log_ID (destination field) is type integer and the source field
> (from another dbase on the same server) is type nvarchar 4. I have several
> columns like this and the table has approximately 20k records.
> The origin of the source database was a MySQL dbase which I moved to SQL2000
> using a driver and access. Thanks.
> --
> Paul G
> Software engineer.|||Thanks will give this a try. Being a C programmer was thinking of using a
cast but did not know how to impliment this with SQL
"Tibor Karaszi" wrote:
> If I understand the problem connect, try:
> INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> Select CAST([Data_Item_Log_ID] AS int) from
> [dbase2].dbo.dataitemlog
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > I have a large dbase table and am moving the data from 1 table to another.
> > The problem is that I had to change the datatypes in the destination table so
> > am getting the failure
> > Syntax error converting the nvarchar to int.
> >
> > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > Select [Data_Item_Log_ID] from
> > [dbase2].dbo.dataitemlog
> > Data_Item_Log_ID (destination field) is type integer and the source field
> > (from another dbase on the same server) is type nvarchar 4. I have several
> > columns like this and the table has approximately 20k records.
> > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > using a driver and access. Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||That's not the problem. You are only changing from an Implicit Cast to an
Explicit Cast.
The problem is that there is some data in the source that can not be
converted to INT.
Try this query:
SELECT *
FROM [dbase2].dbo.dataitemlog
WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
This should produce the records that are causing you grief. Be careful;
this could easily be an ANSI PADDING ON issue where there are extraneous
spaces--those will not be able to convert to numeric.
Sincerely,
Anthony Thomas
"Paul" wrote:
> Thanks will give this a try. Being a C programmer was thinking of using a
> cast but did not know how to impliment this with SQL
> "Tibor Karaszi" wrote:
> > If I understand the problem connect, try:
> >
> > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > Select CAST([Data_Item_Log_ID] AS int) from
> > [dbase2].dbo.dataitemlog
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > I have a large dbase table and am moving the data from 1 table to another.
> > > The problem is that I had to change the datatypes in the destination table so
> > > am getting the failure
> > > Syntax error converting the nvarchar to int.
> > >
> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > Select [Data_Item_Log_ID] from
> > > [dbase2].dbo.dataitemlog
> > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > columns like this and the table has approximately 20k records.
> > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > using a driver and access. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >|||Hi thanks for the response, tried the query and it did find half a dozen or
so records with Null or blank fields in the table, I think the fields that
are causing the problem.
"AnthonyThomas" wrote:
> That's not the problem. You are only changing from an Implicit Cast to an
> Explicit Cast.
> The problem is that there is some data in the source that can not be
> converted to INT.
> Try this query:
> SELECT *
> FROM [dbase2].dbo.dataitemlog
> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> This should produce the records that are causing you grief. Be careful;
> this could easily be an ANSI PADDING ON issue where there are extraneous
> spaces--those will not be able to convert to numeric.
> Sincerely,
>
> Anthony Thomas
>
> "Paul" wrote:
> > Thanks will give this a try. Being a C programmer was thinking of using a
> > cast but did not know how to impliment this with SQL
> >
> > "Tibor Karaszi" wrote:
> >
> > > If I understand the problem connect, try:
> > >
> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > Select CAST([Data_Item_Log_ID] AS int) from
> > > [dbase2].dbo.dataitemlog
> > >
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > > I have a large dbase table and am moving the data from 1 table to another.
> > > > The problem is that I had to change the datatypes in the destination table so
> > > > am getting the failure
> > > > Syntax error converting the nvarchar to int.
> > > >
> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > > Select [Data_Item_Log_ID] from
> > > > [dbase2].dbo.dataitemlog
> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > > columns like this and the table has approximately 20k records.
> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > > using a driver and access. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
> > >|||If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
including decimal, numeric etc. As below shows:
SELECT ISNUMERIC(1.2)
SELECT ISNUMERIC(1e3)
You might want to try instead:
SELECT PATINDEX( '%[^0-9]%', '1.2' )
SELECT PATINDEX( '%[^0-9]%', '1e3' )
SELECT PATINDEX( '%[^0-9]%', '133' )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> Hi thanks for the response, tried the query and it did find half a dozen or
> so records with Null or blank fields in the table, I think the fields that
> are causing the problem.
>
> "AnthonyThomas" wrote:
>> That's not the problem. You are only changing from an Implicit Cast to an
>> Explicit Cast.
>> The problem is that there is some data in the source that can not be
>> converted to INT.
>> Try this query:
>> SELECT *
>> FROM [dbase2].dbo.dataitemlog
>> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
>> This should produce the records that are causing you grief. Be careful;
>> this could easily be an ANSI PADDING ON issue where there are extraneous
>> spaces--those will not be able to convert to numeric.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> "Paul" wrote:
>> > Thanks will give this a try. Being a C programmer was thinking of using a
>> > cast but did not know how to impliment this with SQL
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > If I understand the problem connect, try:
>> > >
>> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
>> > > Select CAST([Data_Item_Log_ID] AS int) from
>> > > [dbase2].dbo.dataitemlog
>> > >
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
>> > > > I have a large dbase table and am moving the data from 1 table to another.
>> > > > The problem is that I had to change the datatypes in the destination table so
>> > > > am getting the failure
>> > > > Syntax error converting the nvarchar to int.
>> > > >
>> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
>> > > > Select [Data_Item_Log_ID] from
>> > > > [dbase2].dbo.dataitemlog
>> > > > Data_Item_Log_ID (destination field) is type integer and the source field
>> > > > (from another dbase on the same server) is type nvarchar 4. I have several
>> > > > columns like this and the table has approximately 20k records.
>> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
>> > > > using a driver and access. Thanks.
>> > > > --
>> > > > Paul G
>> > > > Software engineer.
>> > >
>> > >
>> > >|||The column was set to not allow null so not sure how they got there, did a
MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
'%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
filled. Also not too familiar with Enterprise manager but was wondering if
you know how to connect to an existing database, is this the same as
registering a database?
thanks.
"Tibor Karaszi" wrote:
> If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
> close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> including decimal, numeric etc. As below shows:
> SELECT ISNUMERIC(1.2)
> SELECT ISNUMERIC(1e3)
> You might want to try instead:
> SELECT PATINDEX( '%[^0-9]%', '1.2' )
> SELECT PATINDEX( '%[^0-9]%', '1e3' )
> SELECT PATINDEX( '%[^0-9]%', '133' )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > Hi thanks for the response, tried the query and it did find half a dozen or
> > so records with Null or blank fields in the table, I think the fields that
> > are causing the problem.
> >
> >
> > "AnthonyThomas" wrote:
> >
> >> That's not the problem. You are only changing from an Implicit Cast to an
> >> Explicit Cast.
> >>
> >> The problem is that there is some data in the source that can not be
> >> converted to INT.
> >>
> >> Try this query:
> >>
> >> SELECT *
> >> FROM [dbase2].dbo.dataitemlog
> >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> >>
> >> This should produce the records that are causing you grief. Be careful;
> >> this could easily be an ANSI PADDING ON issue where there are extraneous
> >> spaces--those will not be able to convert to numeric.
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >>
> >> "Paul" wrote:
> >>
> >> > Thanks will give this a try. Being a C programmer was thinking of using a
> >> > cast but did not know how to impliment this with SQL
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> > > If I understand the problem connect, try:
> >> > >
> >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > Select CAST([Data_Item_Log_ID] AS int) from
> >> > > [dbase2].dbo.dataitemlog
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> >> > > > I have a large dbase table and am moving the data from 1 table to another.
> >> > > > The problem is that I had to change the datatypes in the destination table so
> >> > > > am getting the failure
> >> > > > Syntax error converting the nvarchar to int.
> >> > > >
> >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > > Select [Data_Item_Log_ID] from
> >> > > > [dbase2].dbo.dataitemlog
> >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> >> > > > columns like this and the table has approximately 20k records.
> >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> >> > > > using a driver and access. Thanks.
> >> > > > --
> >> > > > Paul G
> >> > > > Software engineer.
> >> > >
> >> > >
> >> > >
>
>|||You register a SQL Server instance, where such an instance can have several databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4777949B-682B-49A3-B17D-C7C2337B4F4C@.microsoft.com...
> The column was set to not allow null so not sure how they got there, did a
> MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
> '%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
> filled. Also not too familiar with Enterprise manager but was wondering if
> you know how to connect to an existing database, is this the same as
> registering a database?
> thanks.
>
> "Tibor Karaszi" wrote:
> > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
is
> > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > including decimal, numeric etc. As below shows:
> >
> > SELECT ISNUMERIC(1.2)
> > SELECT ISNUMERIC(1e3)
> >
> > You might want to try instead:
> >
> > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > SELECT PATINDEX( '%[^0-9]%', '133' )
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > so records with Null or blank fields in the table, I think the fields that
> > > are causing the problem.
> > >
> > >
> > > "AnthonyThomas" wrote:
> > >
> > >> That's not the problem. You are only changing from an Implicit Cast to an
> > >> Explicit Cast.
> > >>
> > >> The problem is that there is some data in the source that can not be
> > >> converted to INT.
> > >>
> > >> Try this query:
> > >>
> > >> SELECT *
> > >> FROM [dbase2].dbo.dataitemlog
> > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > >>
> > >> This should produce the records that are causing you grief. Be careful;
> > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > >> spaces--those will not be able to convert to numeric.
> > >>
> > >> Sincerely,
> > >>
> > >>
> > >> Anthony Thomas
> > >>
> > >>
> > >>
> > >> "Paul" wrote:
> > >>
> > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > >> > cast but did not know how to impliment this with SQL
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> > > If I understand the problem connect, try:
> > >> > >
> > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > >> > > [dbase2].dbo.dataitemlog
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > http://www.karaszi.com/sqlserver/default.asp
> > >> > > http://www.solidqualitylearning.com/
> > >> > >
> > >> > >
> > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > >> > > > The problem is that I had to change the datatypes in the destination table so
> > >> > > > am getting the failure
> > >> > > > Syntax error converting the nvarchar to int.
> > >> > > >
> > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > > Select [Data_Item_Log_ID] from
> > >> > > > [dbase2].dbo.dataitemlog
> > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > >> > > > columns like this and the table has approximately 20k records.
> > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > >> > > > using a driver and access. Thanks.
> > >> > > > --
> > >> > > > Paul G
> > >> > > > Software engineer.
> > >> > >
> > >> > >
> > >> > >
> >
> >
> >|||ok thanks for the information.
"Tibor Karaszi" wrote:
> You register a SQL Server instance, where such an instance can have several databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4777949B-682B-49A3-B17D-C7C2337B4F4C@.microsoft.com...
> > The column was set to not allow null so not sure how they got there, did a
> > MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
> > '%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
> > filled. Also not too familiar with Enterprise manager but was wondering if
> > you know how to connect to an existing database, is this the same as
> > registering a database?
> > thanks.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
> is
> > > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > > including decimal, numeric etc. As below shows:
> > >
> > > SELECT ISNUMERIC(1.2)
> > > SELECT ISNUMERIC(1e3)
> > >
> > > You might want to try instead:
> > >
> > > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > > SELECT PATINDEX( '%[^0-9]%', '133' )
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > > so records with Null or blank fields in the table, I think the fields that
> > > > are causing the problem.
> > > >
> > > >
> > > > "AnthonyThomas" wrote:
> > > >
> > > >> That's not the problem. You are only changing from an Implicit Cast to an
> > > >> Explicit Cast.
> > > >>
> > > >> The problem is that there is some data in the source that can not be
> > > >> converted to INT.
> > > >>
> > > >> Try this query:
> > > >>
> > > >> SELECT *
> > > >> FROM [dbase2].dbo.dataitemlog
> > > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > > >>
> > > >> This should produce the records that are causing you grief. Be careful;
> > > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > > >> spaces--those will not be able to convert to numeric.
> > > >>
> > > >> Sincerely,
> > > >>
> > > >>
> > > >> Anthony Thomas
> > > >>
> > > >>
> > > >>
> > > >> "Paul" wrote:
> > > >>
> > > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > > >> > cast but did not know how to impliment this with SQL
> > > >> >
> > > >> > "Tibor Karaszi" wrote:
> > > >> >
> > > >> > > If I understand the problem connect, try:
> > > >> > >
> > > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > > >> > > [dbase2].dbo.dataitemlog
> > > >> > >
> > > >> > >
> > > >> > > --
> > > >> > > Tibor Karaszi, SQL Server MVP
> > > >> > > http://www.karaszi.com/sqlserver/default.asp
> > > >> > > http://www.solidqualitylearning.com/
> > > >> > >
> > > >> > >
> > > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > > >> > > > The problem is that I had to change the datatypes in the destination table so
> > > >> > > > am getting the failure
> > > >> > > > Syntax error converting the nvarchar to int.
> > > >> > > >
> > > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > >> > > > Select [Data_Item_Log_ID] from
> > > >> > > > [dbase2].dbo.dataitemlog
> > > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > >> > > > columns like this and the table has approximately 20k records.
> > > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > >> > > > using a driver and access. Thanks.
> > > >> > > > --
> > > >> > > > Paul G
> > > >> > > > Software engineer.
> > > >> > >
> > > >> > >
> > > >> > >
> > >
> > >
> > >
>
>|||Tibor:
Thanks for that. The thought did occur to me but I suspected the issue with
NULLS. This is the reasoning: SQL Server can do a successfull implicit cast
from numeric and float data types to integer by truncating the decimal
portion. However, that will not fail the original query, just a logical
failure.
If the columns are marked NOT NULL, then they are not, regardless of what
system the data came from. Your problem is that they are in all likelihood
spaces.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
> close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> including decimal, numeric etc. As below shows:
> SELECT ISNUMERIC(1.2)
> SELECT ISNUMERIC(1e3)
> You might want to try instead:
> SELECT PATINDEX( '%[^0-9]%', '1.2' )
> SELECT PATINDEX( '%[^0-9]%', '1e3' )
> SELECT PATINDEX( '%[^0-9]%', '133' )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > Hi thanks for the response, tried the query and it did find half a dozen or
> > so records with Null or blank fields in the table, I think the fields that
> > are causing the problem.
> >
> >
> > "AnthonyThomas" wrote:
> >
> >> That's not the problem. You are only changing from an Implicit Cast to an
> >> Explicit Cast.
> >>
> >> The problem is that there is some data in the source that can not be
> >> converted to INT.
> >>
> >> Try this query:
> >>
> >> SELECT *
> >> FROM [dbase2].dbo.dataitemlog
> >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> >>
> >> This should produce the records that are causing you grief. Be careful;
> >> this could easily be an ANSI PADDING ON issue where there are extraneous
> >> spaces--those will not be able to convert to numeric.
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >>
> >> "Paul" wrote:
> >>
> >> > Thanks will give this a try. Being a C programmer was thinking of using a
> >> > cast but did not know how to impliment this with SQL
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> > > If I understand the problem connect, try:
> >> > >
> >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > Select CAST([Data_Item_Log_ID] AS int) from
> >> > > [dbase2].dbo.dataitemlog
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> >> > > > I have a large dbase table and am moving the data from 1 table to another.
> >> > > > The problem is that I had to change the datatypes in the destination table so
> >> > > > am getting the failure
> >> > > > Syntax error converting the nvarchar to int.
> >> > > >
> >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > > Select [Data_Item_Log_ID] from
> >> > > > [dbase2].dbo.dataitemlog
> >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> >> > > > columns like this and the table has approximately 20k records.
> >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> >> > > > using a driver and access. Thanks.
> >> > > > --
> >> > > > Paul G
> >> > > > Software engineer.
> >> > >
> >> > >
> >> > >
>
>|||> This is the reasoning: SQL Server can do a successfull implicit cast
> from numeric and float data types to integer by truncating the decimal
> portion. However, that will not fail the original query, just a logical
> failure.
Good point, Anthony.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:16AEFBBD-9FFB-4351-B0B3-112202AC3C1E@.microsoft.com...
> Tibor:
> Thanks for that. The thought did occur to me but I suspected the issue with
> NULLS. This is the reasoning: SQL Server can do a successfull implicit cast
> from numeric and float data types to integer by truncating the decimal
> portion. However, that will not fail the original query, just a logical
> failure.
> If the columns are marked NOT NULL, then they are not, regardless of what
> system the data came from. Your problem is that they are in all likelihood
> spaces.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
is
> > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > including decimal, numeric etc. As below shows:
> >
> > SELECT ISNUMERIC(1.2)
> > SELECT ISNUMERIC(1e3)
> >
> > You might want to try instead:
> >
> > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > SELECT PATINDEX( '%[^0-9]%', '133' )
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > so records with Null or blank fields in the table, I think the fields that
> > > are causing the problem.
> > >
> > >
> > > "AnthonyThomas" wrote:
> > >
> > >> That's not the problem. You are only changing from an Implicit Cast to an
> > >> Explicit Cast.
> > >>
> > >> The problem is that there is some data in the source that can not be
> > >> converted to INT.
> > >>
> > >> Try this query:
> > >>
> > >> SELECT *
> > >> FROM [dbase2].dbo.dataitemlog
> > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > >>
> > >> This should produce the records that are causing you grief. Be careful;
> > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > >> spaces--those will not be able to convert to numeric.
> > >>
> > >> Sincerely,
> > >>
> > >>
> > >> Anthony Thomas
> > >>
> > >>
> > >>
> > >> "Paul" wrote:
> > >>
> > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > >> > cast but did not know how to impliment this with SQL
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> > > If I understand the problem connect, try:
> > >> > >
> > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > >> > > [dbase2].dbo.dataitemlog
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > http://www.karaszi.com/sqlserver/default.asp
> > >> > > http://www.solidqualitylearning.com/
> > >> > >
> > >> > >
> > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > >> > > > The problem is that I had to change the datatypes in the destination table so
> > >> > > > am getting the failure
> > >> > > > Syntax error converting the nvarchar to int.
> > >> > > >
> > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > > Select [Data_Item_Log_ID] from
> > >> > > > [dbase2].dbo.dataitemlog
> > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > >> > > > columns like this and the table has approximately 20k records.
> > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > >> > > > using a driver and access. Thanks.
> > >> > > > --
> > >> > > > Paul G
> > >> > > > Software engineer.
> > >> > >
> > >> > >
> > >> > >
> >
> >
> >

problem with query moving data from 1 dbase to another.

Problem With Query Cost Govenor

Hi,
I'm running this:
DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK
And I'm getting this:
Server: Msg 8649, Level 17, State 1, Line 2
The query has been canceled because the estimated cost of this query (13451)
exceeds the configured threshold of 10800. Contact the system administrator.
When I remove the query governor it runs in about 30 seconds not 10800+.
The stats on this database were manually updated with sp_createstats and
sp_updatestats.
Does anyone have any recommendations?
Thanks,
BryanYou had some reply on this last time you posted:
http://groups.google.com/groups?hl=...lic.sqlserver.*
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <reply@.to.group.com> wrote in message news:%23TgJUrEaFHA.3876@.TK2MSFTNGP12.phx.gbl...

> Hi,
> I'm running this:
> DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK
> And I'm getting this:
> Server: Msg 8649, Level 17, State 1, Line 2
> The query has been canceled because the estimated cost of this query (1345
1)
> exceeds the configured threshold of 10800. Contact the system administrato
r.
> When I remove the query governor it runs in about 30 seconds not 10800+.
> The stats on this database were manually updated with sp_createstats and
> sp_updatestats.
> Does anyone have any recommendations?
> Thanks,
> Bryan
>

Saturday, February 25, 2012

Problem with query and date ranges

I'm trying to create a query that will tell me which requests
took longer than 10 days to move one from particular state to another
state. The query I've created returns the correct requests,
but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.

I'm sure I'm missing something easy, but I can't figure out what it
might be. Any help is appreciated! Thanks,
Myron
-- remove SPAM-KILL from address to reply by email --

DDL for table creation and data population:

CREATE TABLE [dbo].[ReqHistory] (
[Id] [int] NOT NULL ,
[ReqId] [int] NOT NULL ,
[ReqIDStateId] [tinyint] NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RequestStates] (
[ID] [tinyint] NOT NULL ,
[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests] (
[ID] [int] NOT NULL ,
[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusChangeDate] [datetime] NULL ,
[Status] [tinyint] NULL
) ON [PRIMARY]
GO

insert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)
insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)
insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)

insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))
insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))
insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))
insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))
insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))
insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))
insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))
insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))
insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))
insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))
insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))
insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))
insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))
insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))
insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))
insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))
insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))
insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))

insert into RequestStates values(1,'Awaiting CSMC')
insert into RequestStates values(2,'CSMC Review')
insert into RequestStates values(3,'Reject Awaiting CSMC')
insert into RequestStates values(10,'Awaiting MA Review')
insert into RequestStates values(12,'Awaiting FO Review')
insert into RequestStates values(13,'Awaiting IS Review')
insert into RequestStates values(20,'MA Review')
insert into RequestStates values(22,'FO Review')
insert into RequestStates values(23,'IS Review')
insert into RequestStates values(30,'Func Approval')
insert into RequestStates values(31,'Func Approval Complete')
insert into RequestStates values(96,'Resolved')
insert into RequestStates values(97,'Planning')
insert into RequestStates values(98,'Open')
insert into RequestStates values(99,'Closed')

The query that almost works:

select irh.ReqID, irh.MAactiondate, irh.reviewstate,
irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextState
from (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,
irh.ReqIDStateID As IRHState, irs.statetext as ReviewState
from ReqHistory IRH
join requeststates irs on irs.id = irh.ReqIDStateID
where irh.ReqIDStateID in (20, 23)
group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irh
join (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetext
from (select min(actiondate) as actiondate, ReqID,
min(ReqIDStateID) as IRH2State
from ReqHistory
--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,
--but I couldn't make it work
where ReqIDStateID > 23
group by ReqID) as irh2
join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqID
join requests ir on ir.id = irh.ReqID
where irh.MAactiondate + 10 < irh2.Nextactiondate
order by irh.ReqID

The data being returned is:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 96 Resolved

The data that should have been returned:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMCHoly subqueries batman!

Thanks for including the DDL... it made finding an answer to this much
easier. A couple quick notes though... you didn't include and foreign
or primary keys. That combined with the inconsistent naming standards
made it a little difficult to understand the code.

The following code returned what you were expecting. The code assumes
that no two actions can occur at the same exact time. If that
assumption is incorrect then the code will not work correctly in those
situations.

Also, instead of the NOT EXISTS you could of course opt to use a LEFT
OUTER JOIN along with checking for IS NULL on one of the PK columns for
the table (RH). That often gives better performance than NOT EXISTS in
my experience.

HTH,
-Tom.

SELECT MAH.ReqID, MAH.ActionDate, RS.StateText, NA.ActionDate,
NA.ReqIDStateID, NS.StateText
FROM ReqHistory MAH
INNER JOIN ReqHistory NA ON NA.ReqID = MAH.ReqID
AND NA.ActionDate > DATEADD(dy, 10,
MAH.ActionDate)
INNER JOIN RequestStates RS ON RS.ID = MAH.ReqIDStateID
INNER JOIN RequestStates NS ON NS.ID = NA.ReqIDStateID
WHERE MAH.ReqIDStateID IN (20, 23)
AND NOT EXISTS (SELECT *
FROM ReqHistory RH
WHERE RH.ReqID = MAH.ReqID
AND RH.ActionDate > MAH.ActionDate
AND RH.ActionDate < NA.ActionDate)|||"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote:
>Holy subqueries batman!
>Thanks for including the DDL... it made finding an answer to this much
>easier. A couple quick notes though... you didn't include and foreign
>or primary keys. That combined with the inconsistent naming standards
>made it a little difficult to understand the code.
<some snippage
Thanks for the speedy and accurate answer, Tom! Your query looks a
lot more elegant than my monster, and it found rows that my original
was dropping. :)
Myron|||Your basic design is wrong. Time comes in durations and not points --
look at everyone from Zeno to the ISO standards. Also, your data
element names make no sense -- "ReqIDStateId"? if something is a
state, then it is a value and not an indentifier. What is the vague
"ID": floating around? Surely you do not blindly use sequentail
numbering in an RDBMS to mimic a sequential file physical record
number!

CREATE TABLE RequestHistory
( request_nbr INTEGER NOT NULL ,
request_status INTEGER NOT NULL
REFERENCES RequestStatusCodes(request_status)
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
PRIMARY KEY (request_nbr, start_date ));

>>I'm trying to create a query that will tell me which requests took
longer than 10 days to move one from particular state to another state.
<<

Trival with the right design, isn't it?

problem with query

I have the following bit of code

string test0001 = "Select Max(activity_order) from roomactivitylk";

int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = test0001;
max = (int)cmd15.ExecuteScalar();

max = max + 1;

what it does is add 1 to the value max which is taken from the database

however it seems to be set to 0 as everytime it brings back 0 even though the next incrment value should be 2

any suggestions?

cheerts

I am not sure which parts of the code is in a loop. But i guess, the "int max" declaration should be outside of the loop (if it is already not).

Hope this helps.

|||

My guess is u want to perform autoincrement . If i m right i think u may not get it because when there r no records exist check the return value.u may get null if u get null make it as 1 or increment with the max value. Plz reply to me am i rt or wrong

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi

thanks for the replys manmaged to sort it
this is the soltuion

string maxquery = "Select Max(activity_order) from roomactivitylk ";
//cmd14.ExecuteScalar();
int max;
SqlCommand cmd15 = new SqlCommand();
//SqlCommand cmd15 = new SqlCommand(test0001, con);
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteNonQuery();

max = max + 2;
++max;
max++;
ps yes i was tring to increment:-)
cheers!!

Problem with Query

Hi,

Here is the part of a stored procedure

declare @.startDate datetime
declare @.endDate datetime
set @.startDate = '12/1/2007 12:00:00 AM'
set @.endDate = '12/20/2007 12:00:00 AM'

--case1:
--The below query executes fine and displays records (count 50)
select * from Employee
where dtsubmittimestamp BETWEEN @.startDate and @.endDate

--case2:
--The below query executes fine and displays records (count 37)
select * from Employee
where iclientaccid = 51

--case3:
--The below query executes but no records are displayed (0 records)
select * from Employee
where iclientaccid = 51
and dtsubmittimestamp BETWEEN @.startDate and @.endDate

--case4:
--The below query executes but no records are displayed (0 records)
select * from Employee
where dtsubmittimestamp BETWEEN @.startDate and @.endDate
and iclientaccid = 51

I am unable to find out why it doesn't display any records in cases 3 and 4

Please help me. Thanks in advance.

Try...

where (iclientaccid = 51)and (dtsubmittimestampBETWEEN @.startDateand @.endDate)

|||

Your iclientaccid = 51 is not in the range of hardcoded statrtdate and endDate

Satalaj.

Problem with Query

I need to replace the text (III|xII) with (1II|xII)'. For that i created the following query.

SELECT (REPLACE((STUFF(Col1,1,1,'1')),'I','1')) FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

The problem with this query is that it replaces all '|' with '1'. ie, it gives (111|x11).

Could anyone ple show me what's wrong with the query.

All help appreciated.

Thanks,

The following code will give you the flexibility to change any char in the string, at any position, at any length. This function returns max 15 chars (but that’s easy to change)…

CREATE FUNCTION dbo.FixString
(
@.DataString varchar(15),
@.StartPosition smallint,
@.StartLength smallint,
@.SeacrhString varchar(15),
@.ReplaceString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @.Part1 varchar(15),
@.Part2 varchar(15),
@.Part3 varchar(15)

SET @.Part1 = SUBSTRING(@.DataString, 1, @.StartPosition - 1)
SET @.Part2 = REPLACE(SUBSTRING(@.DataString, @.StartPosition, @.StartLength), @.SeacrhString, @.ReplaceString)
SET @.Part3 = SUBSTRING( @.DataString, @.StartPosition + @.StartLength, LEN(@.DataString) - (@.StartPosition + @.StartLength) + 1 )

RETURN @.Part1 + @.Part2 + @.Part3
END
GO

SELECT dbo.FixString(Col1,1,1,'B','1') FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

Happy SQL'n,
Kent Howerter

|||You don't need the REPLACE since it will replace all occurrences of the specified string. STUFF is enough in this case since you are using the length parameter to delete the character at specified position and insert the new one.

Wednesday, March 7, 2012

problem with query moving data from 1 dbase to another.

problem with query moving data from 1 dbase to another.

Problem With Query Cost Govenor

Saturday, February 25, 2012

Problem with query and date ranges

problem with query

Problem with Query

Problem with Query

PAGE HEADER

Blog Archive

About Me