PAGE HEADER: moving

Showing posts with label moving. Show all posts

Monday, March 26, 2012

Problem with SQL Query not moving through every record

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

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

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


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

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

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

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

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

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

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

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


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

sql

Wednesday, March 21, 2012

Problem with snapshot replication

I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
You have to make sure the distribution agent is also running, and not
failing with errors.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"captainkt" <nothing@.fake.com> wrote in message
news:OLA7eR$TFHA.2940@.TK2MSFTNGP10.phx.gbl...
I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
sql

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

Monday, March 26, 2012

Problem with SQL Query not moving through every record

Wednesday, March 21, 2012

Problem with snapshot replication

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 moving data from 1 dbase to another.

PAGE HEADER

Blog Archive

About Me