Odd: Inserts and updates happen before running code in debugger

Hi,

Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.

Anyone else encountered this?The VS 2005 Development Server caused the problem.

On changing to IIS 5 for execution, when debugging, the ado.net code only executed when I had stepped through it. Therefore if I stopped debugging before I stepped through the ado.net code, the database was not updated. However, in using VS 2005 Development Server, the database was already updated, even before I had stepped through the first line of code!

Can someone try this finding out?|||On some apps, I continue to see database changes when no ado.net code has been run in the debugger. This is using IIS or the built in server.

On others, no database changes take place when no ado.net code has been run in the debugger. There must be a bug.

Odd Unicode problem

I use Server2000 and ADO connection and Unicode.
I have fields type of nvarchar.
And when I do the search ADO creates the SQL statement like this:
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
(Caught from Profiler)
And this result nothing.
but when I delete the N from the second parameter:
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
before the Unicode variable, it works (work - means result the correct
records)!
and to make it more odd
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
works too.
I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
Management Studio Express and there are same results.
Have anyone any idea what is wrong?
Regards,
Tomasz RutkowskiHi Tom
My guess is that your data is not what you expect
If you update the data
BEGIN TRANSACTION
UPDATE dbo.AB_EntityTreeNodeLink
SET EntityTitle = N'?'
WHERE EntityTitle = '?'
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
ROLLBACK TRANSACTION
Does it work?
John
"TomX" wrote:
> I use Server2000 and ADO connection and Unicode.
> I have fields type of nvarchar.
> And when I do the search ADO creates the SQL statement like this:
> exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
> dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
> ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
> ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
> nvarchar(120),@.P3 smallint', 0, N'?', 7
> (Caught from Profiler)
> And this result nothing.
> but when I delete the N from the second parameter:
> ...., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
> before the Unicode variable, it works (work - means result the correct
> records)!
> and to make it more odd
> ..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
> works too.
> I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
> Management Studio Express and there are same results.
> Have anyone any idea what is wrong?
> Regards,
> Tomasz Rutkowski
>
>
>

Odd timing problem MSAccess to SS2005

I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.

The issue is there is one field in the Access table that must be put into a different SS2005 fact table.

When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.

Has anyone ever bumped into a situation like this?

This could be because of a number of things, but I think the most likely are probably that the slow table is heavily indexed or has foreign keys being enforced. Also, make sure you're using fastload.|||

[banging head against desk]

Fast load ... that was it. Doggone it. Thanks for indulging a goof.

Odd Timeout

Hi everyone,

I'm getting a weird timeout issue. We have a procedure that can take anywhere from 20 sec - 1:30 to execute. I have coded the connection string to time out after 10 minutes and I coded the command object to timeout at 10 minutes. But for some reason this app still times out at 30 seconds.

Is there some other area I need to look at to make sure this timeout doesn't occur? This is an old app that I inherited. It is done in .net 1.1 and SQL 2000. Any thoughts would be great.

Hi,

It seems you should try to change the executionTimeout parameter in the httpRuntime tag inmachine.config, i.e.

<httpRuntime executionTimeout="90" ... />

More information to see,http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/cpgenref/html/gngrfhttpruntimesection.asp

Thanks.

odd things...I''m totally stuck

Hi all of you,

I've got a .dtproj project along with 10 dtsx packages. Up to here everything is fine, every package have its own source files, sql destinations and so on..

Issue comes when I try create a flat file source connection for the eleven one.

I get this message:

The component has detected potential metadata corruption during validation.
Error at Data Flow Task [Flat File Source [1]]: Failed to find any non-special error column in output "Flat File Source Error Output" (3).

It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?

Otherwise if I'm gonna to open a new .dtproj project separately no problem at all, I mean, appears the suitable window, "Connection Manager", "Column", "Error output" on the left and so on..

It's very strange. The rest of the packages, the same situation. Fortunately, all of them are stored on the server.

I don't get the point at all. Could you please tell me what the hell is happening?

Thanks in advance and regards,

amazing...after closing the project and re-opening the bloody eleven package appears without nothing... tasks, everything is lost.. only a white surface..

Is it possible that it has became a rotten project?

|||

Sorry. "Work offline"......

sql

odd TEXT type field problem

Hi,
There is a db with 200GB, with ~160 GB data in it. Most of the data was a
TEXT type field in a table. That field has been dropped and added back with
default NULL but it appears I still have ~160 GB of data.
How is that possible?
updatestats would help?
The table in question has a two field composite clustered index, but not on
the TEXT type field.
The truth is the whole thing is very fragmented. Defragmenting the clustered
index would place exclusive lock on the table?
Your help would be appreciated,
JanosYou can try running DBCC CLEANTABLE on the table that had the TEXT column.
Look up syntax in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Janos Horanszky" <kob_uki@.hotmail.com> wrote in message
news:emmBIbZEFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There is a db with 200GB, with ~160 GB data in it. Most of the data was a
> TEXT type field in a table. That field has been dropped and added back
with
> default NULL but it appears I still have ~160 GB of data.
> How is that possible?
> updatestats would help?
> The table in question has a two field composite clustered index, but not
on
> the TEXT type field.
> The truth is the whole thing is very fragmented. Defragmenting the
clustered
> index would place exclusive lock on the table?
> Your help would be appreciated,
> Janos
>|||I appreciate Adam, I will try it on our test environment, I let you know
asap.
Janos
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eDFFq6dEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> You can try running DBCC CLEANTABLE on the table that had the TEXT column.
> Look up syntax in BOL.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Janos Horanszky" <kob_uki@.hotmail.com> wrote in message
> news:emmBIbZEFHA.3368@.TK2MSFTNGP10.phx.gbl...
> with
> on
> clustered
>

Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

I had this line in a very, very large stored procedure
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1
Restored:
and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
Incorrect Syntax near â'Râ'.(-2147217900)
Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
So I changed the code to this and it works now:
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
Begin
Update #PY_Tbl Set After1987=1
End
Restored:
Any ideas why?Steve,
SQL Server syntax is in the process of changing and becoming more ANSI.
That means that occasionally an assumption that 2000 was willing to make,
2005 is unwilling to make. The new approved syntax for ensuring that a
command is terminated is to close with ;
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1;
Restored:
FWIW,
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>I had this line in a very, very large stored procedure
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1
> Restored:
> and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> Incorrect Syntax near "R".(-2147217900)
> Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> So I changed the code to this and it works now:
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> Begin
> Update #PY_Tbl Set After1987=1
> End
> Restored:
> Any ideas why?
>|||That look promising...
But unfortunately the ";" character did not fix the syntax error.
And keep in mind that this is during run-time of the SPROC and not compiling
into the DB.
"Russell Fields" wrote:
> Steve,
> SQL Server syntax is in the process of changing and becoming more ANSI.
> That means that occasionally an assumption that 2000 was willing to make,
> 2005 is unwilling to make. The new approved syntax for ensuring that a
> command is terminated is to close with ;
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> Restored:
> FWIW,
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >I had this line in a very, very large stored procedure
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >
> > Restored:
> >
> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >
> > Incorrect Syntax near "R".(-2147217900)
> >
> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> >
> > So I changed the code to this and it works now:
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0)
> > Begin
> > Update #PY_Tbl Set After1987=1
> > End
> >
> > Restored:
> >
> > Any ideas why?
> >
>
>|||Can you provide a repro? Your code returned an *expected* error (no such table...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||I do not understand what you want me to supply? This error is easily
duplicated on the production server.
"Tibor Karaszi" wrote:
> Can you provide a repro? Your code returned an *expected* error (no such table...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>|||Steve,
OK, I missed the boat. Which probably means that there is something going
on earlier that is at the root of the problem. My repro (comment out
whichever inserts you do not want to test) had no error on 2005, with or
without the semi-colon.
create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
insert into #Py_Tbl values(55, 1986,7,2)
insert into #Py_Tbl values(66, 1985,7,2)
insert into #Py_Tbl values(77, 1986,7,0)
insert into #Py_Tbl values(77, 1987,0,0)
insert into #Py_Tbl values(77, 1987,0,2)
If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
update #PY_Tbl Set After1987=1
Restored:
SELECT * from #PY_Tbl
drop table #Py_Tbl
What next?
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not
> compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near
>> > "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||The SPROC is over 1000 lines. If calculates a persons pension.
The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
I'm feeling it's a true parsing bug in SQL - but I've got no way to
reproduce it in a smaller fashion.
"Russell Fields" wrote:
> Steve,
> OK, I missed the boat. Which probably means that there is something going
> on earlier that is at the root of the problem. My repro (comment out
> whichever inserts you do not want to test) had no error on 2005, with or
> without the semi-colon.
> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
> insert into #Py_Tbl values(55, 1986,7,2)
> insert into #Py_Tbl values(66, 1985,7,2)
> insert into #Py_Tbl values(77, 1986,7,0)
> insert into #Py_Tbl values(77, 1987,0,0)
> insert into #Py_Tbl values(77, 1987,0,2)
> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> update #PY_Tbl Set After1987=1
> Restored:
> SELECT * from #PY_Tbl
> drop table #Py_Tbl
> What next?
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> > That look promising...
> >
> > But unfortunately the ";" character did not fix the syntax error.
> >
> > And keep in mind that this is during run-time of the SPROC and not
> > compiling
> > into the DB.
> >
> > "Russell Fields" wrote:
> >
> >> Steve,
> >>
> >> SQL Server syntax is in the process of changing and becoming more ANSI.
> >> That means that occasionally an assumption that 2000 was willing to make,
> >> 2005 is unwilling to make. The new approved syntax for ensuring that a
> >> command is terminated is to close with ;
> >>
> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> and
> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> >>
> >> Restored:
> >>
> >> FWIW,
> >> RLF
> >>
> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >> >I had this line in a very, very large stored procedure
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >> >
> >> > Restored:
> >> >
> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >> >
> >> > Incorrect Syntax near "R".(-2147217900)
> >> >
> >> > Changing the label RESTORED: to XYZ: make the error change to: ...near
> >> > "X"
> >> >
> >> > So I changed the code to this and it works now:
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0)
> >> > Begin
> >> > Update #PY_Tbl Set After1987=1
> >> > End
> >> >
> >> > Restored:
> >> >
> >> > Any ideas why?
> >> >
> >>
> >>
> >>
>
>|||Steve,
I certainly cannot argue with you about that possibility. And I understand
that it may not be worth pursuing any further.
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6BA773B3-EF55-4B52-A379-B4961D856905@.microsoft.com...
> The SPROC is over 1000 lines. If calculates a persons pension.
> The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
> I'm feeling it's a true parsing bug in SQL - but I've got no way to
> reproduce it in a smaller fashion.
> "Russell Fields" wrote:
>> Steve,
>> OK, I missed the boat. Which probably means that there is something
>> going
>> on earlier that is at the root of the problem. My repro (comment out
>> whichever inserts you do not want to test) had no error on 2005, with or
>> without the semi-colon.
>> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
>> insert into #Py_Tbl values(55, 1986,7,2)
>> insert into #Py_Tbl values(66, 1985,7,2)
>> insert into #Py_Tbl values(77, 1986,7,0)
>> insert into #Py_Tbl values(77, 1987,0,0)
>> insert into #Py_Tbl values(77, 1987,0,2)
>> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0)
>> update #PY_Tbl Set After1987=1
>> Restored:
>> SELECT * from #PY_Tbl
>> drop table #Py_Tbl
>> What next?
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
>> > That look promising...
>> >
>> > But unfortunately the ";" character did not fix the syntax error.
>> >
>> > And keep in mind that this is during run-time of the SPROC and not
>> > compiling
>> > into the DB.
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Steve,
>> >>
>> >> SQL Server syntax is in the process of changing and becoming more
>> >> ANSI.
>> >> That means that occasionally an assumption that 2000 was willing to
>> >> make,
>> >> 2005 is unwilling to make. The new approved syntax for ensuring that
>> >> a
>> >> command is terminated is to close with ;
>> >>
>> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> >> and
>> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> >>
>> >> Restored:
>> >>
>> >> FWIW,
>> >> RLF
>> >>
>> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >> >I had this line in a very, very large stored procedure
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >> >
>> >> > Restored:
>> >> >
>> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this
>> >> > error:
>> >> >
>> >> > Incorrect Syntax near "R".(-2147217900)
>> >> >
>> >> > Changing the label RESTORED: to XYZ: make the error change to:
>> >> > ...near
>> >> > "X"
>> >> >
>> >> > So I changed the code to this and it works now:
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0)
>> >> > Begin
>> >> > Update #PY_Tbl Set After1987=1
>> >> > End
>> >> >
>> >> > Restored:
>> >> >
>> >> > Any ideas why?
>> >> >
>> >>
>> >>
>> >>
>>|||>I do not understand what you want me to supply?
Something we could execute that gives us the same error message. I see from your discussion with
Russell that it isn't that easy, though. A parsing bug seems probable, and I guess that if you want
to pursue this you would have to open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:FCE7ED20-94C8-4008-8484-AF93D1953941@.microsoft.com...
>I do not understand what you want me to supply? This error is easily
> duplicated on the production server.
> "Tibor Karaszi" wrote:
>> Can you provide a repro? Your code returned an *expected* error (no such table...).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>|||On Tue, 21 Aug 2007 13:46:03 -0700, Steve Z
<SteveZ@.discussions.microsoft.com> wrote:
>I had this line in a very, very large stored procedure
>If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>PenHrs<>0) Update #PY_Tbl Set After1987=1
>Restored:
What is the *next* statement?
J.

Odd syntax error with shape queries SQL2000/Win2003

Hi,

I am running SQL Server 2000 SP3 on Windows Server 2003 and since recently
have a strange problem executing shape queries from COM+ components using
ADO.

Until 4 days ago, they worked, then from one moment to the next (I must have
changed something, but I have no clue what other than restoring a 1.2 GB
database) they started failing with this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation

I have no problem executing non-shape queries, it is just the shape queries
that fail.

Any clues what may have gone wrong? Or how I can fix it?

Cheers,
Rsa Myh> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Syntax error or access violation

And apparently this had nothing to do with Win2003 or SQL Server or any
software at all... Just with a mistake in a setting of my own software...
The datasource string for the shape data source was invalid....

I'm hanging my head in shame..

Odd Sum() problem

Hi everyone, first time posting here.

I am having an odd problem with Crystal Reports XI. I have a small database table in Oracle, only has 8 records in it. I want to do a grand total for each column in the database (adding up all 8 records).

But when I use the Sum(fieldname) command, I get a number that is way off. The other commands like Maximum(fieldname) return the value I expect. But Sum is way off.

I am using the columns in a different part of the report, and the report is broken down into sections that match the different records of this database. What I need is a grand total for each column for the whole report.

I even tried a running total, and it returns the same number that is more than twice what it should be. There has to be something I am missing.

Thanks for the help. I am pretty new to .net and programming so I might have used the wrong terms for something.Is the detail section hidden or supressed? If so, have you checked to see if you are selecting distinct records? When you run te report what is the record count in the lower right corner of the screen? It should only be 8.

odd stored procedure behavior

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.

Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.

Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.

There's some joins, but the main table has maybe 20 million rows.

This is sql server 2000.

Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.

The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.

Why?

brianbrianlanning (brianlanning@.gmail.com) writes:

Quote:

Originally Posted by

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.
>
Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.
>
Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.
>...
Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.
>
The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.
>
Why?

When the optimizer compiles the query plan, it works the stored procedure
as a whole. Thus it has no knowledge of the values of local variables.
Instead it applies standarad assumption which for BETWEEN is 20%, if
memory serves.

However, the optimizer does look at the parameter values, and use
these as guidance. When it knwos both end of the period, it can
estimate more exactly how many rows you will select. Note however
that this plan is cached, and if you the next time call the procedure
with an interval of, say, two years, the same plan will be used,
although that plan may not be good for this longer interval.

What you also could try is:

datecol BETWEEN @.startdate AND dateadd(DAY, 30, @.startday)

Hopefully, the optimizer understands the condition and can act
accordingly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Odd SQLTrigger behavior

I have three tables I am using, aspnet_Users, Stories,CustomizedStory. Stories andCustomizedStory are related via a foreign key StoryID. I've setup the tables so that when I delete aStory row it cascade deletes the corresponding row from CustomizedStory. Each CustomizedStory row has a reference to UserID from aspnet_Users.Since, I didn't want to mess with the table definition by adding a cascadedelete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:

ALTERTRIGGER[dbo].[DeleteCustomizedStories]

ON[dbo].[aspnet_Users]
FOR DELETE
AS
BEGIN
DELETE FROMdbo.Story
WHEREStoryID=
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserID =
(SELECTUserIDFROMdeleted))
END

The problem I am having is that it deletes all of theCustomizedStory rows as specified by the cascading option, but doesn't deletethe Story rows. I can't seem to understandwhy this is happening, especially when Iexplicitly told it to delete story rows.

The behavior is a bit weird, I must admit. Your DELETE-query is wrong though, which may cause problems.

DELETE FROMdbo.Story
WHEREStoryIDIN
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserIDIN
(SELECTUserIDFROMdeleted))

or

DELETE s FROMdbo.Storys
INNER JOINdbo.CustomizedStory c ON s.StoryID=c.StoryID
INNER JOINdeleted d ON c.UserID=d.UserID

Odd SqlException

I'm using .NET 2.0

I designed my BLL to work as follows:

'Update existing company <System.ComponentModel.DataObjectMethodAttribute _ (System.ComponentModel.DataObjectMethodType.Update,True)> _Public Function UpdateCompany(ByVal Original_CompanyIDAs Int64, ...)As Boolean'Get current recordDim CompaniesAs biodata.CompaniesDataTable = CompaniesAdapter.GetCompanyByID(Original_CompanyID)'If no record exists, Update failsIf Companies.Count = 0Then Throw New ApplicationException("Company does not exist.")End If Dim CompanyAs biodata.CompaniesRow = Companies(0)'Set all the values to load into the database Company.CompanyName = CompanyName ...Dim RowsAffectedAs Integer = CompaniesAdapter.Update(Company)Return (RowsAffected = 1)End Function

Recently I deleted a column in my SQL database. In order to account for this change, I deleted the same column in my DAL. Then, because of the obvious compile errors that would occur if I didn't, I deleted all references to that column in my BLL. My DAL automatically creates the Update and Insert statements. Now, the error I'm getting is as follows:

Exception Details:System.Data.SqlClient.SqlException: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The statement has been terminated.

Source Error:

Line 18409: System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")> _Line 18410: Public Overloads Overridable Function Update(ByVal dataRow As System.Data.DataRow) As IntegerLine 18411: Return Me.Adapter.Update(New System.Data.DataRow() {dataRow})Line 18412: End FunctionLine 18413:

Stack Trace:

[SqlException (0x80131904): Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.The statement has been terminated.] System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +200 System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +104 System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +3042 System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows) +345 biodataTableAdapters.CompaniesTableAdapter.Update(DataRow dataRow) in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\67b7db20\aea00f88\App_Code.nf1apfb7.1.vb:18411 CompaniesBLL.UpdateCompany(Int64 Original_CompanyID, String CompanyName, String Division, String Website, Nullable`1 CompanyInactive, String CompanyInactiveReason, Nullable`1 EmployeeCount, Nullable`1 YearFounded, Nullable`1 YearStartedBiobased, Nullable`1 NAICS, String Address1, String Address2, String City, String StateCode, String Territory, String CountryCode, String Zip, String CompanyNotes, String Prefix, String FName, String LName, String Suffix, String Title, String Email, String Phone1, String PhoneType1, String Phone2, String PhoneType2, String Phone3, String PhoneType3, String UpdateType, String UpdatedBy, Int64 SecurityLevel, Int64[] Flags) in F:\App_Code\BLL\CompaniesBLL.vb:376

Now, this exception makes no sense to me because the DAL should automatically fix the update and insert statements itself, and I never directly access those statements. The error is occuring at CompaniesAdapter.Update(Company) in my code above. Any ideas?

Thanks,
BSY E-718

Turn on SQL Profiler... and watch the actual query that is being executed... that might point you in the right direction.

Odd sqldatasource insert behavior

I have a sqldatasource (code listed below) whose insert Paramaters are control parameters. My aspx page has a textbox and a submit button. the button onclick runs the sqdatasource1.insert.

What I get is every other insert inserts the text in textbox2 and every other insert enters nothing for the namecust value. I have a required field validator which correctly prevents submission if textbox2 is empty.

How do I fix this?

:<code>

<asp:PanelID="Panel1"runat="server"Height="50px"Width="548px">

<asp:ButtonID="Button1"runat="server"Text="New Prospect"ValidationGroup="insertCust"/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"

ErrorMessage="Prospect Name can not be blank"ValidationGroup="insertCust"></asp:RequiredFieldValidator>

<asp:TextBoxID="TextBox2"runat="server"Width="330px"ValidationGroup="insertCust"></asp:TextBox></asp:Panel>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

SelectCommand="SELECT DISTINCT CODETERR FROM dbo.F_arcus() AS F_arcus_1 WHERE (DATEINAC = 0) AND (rtrim(CODETERR) <>'')">

</asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

InsertCommand="INSERT INTO dbo.BudgetProspects(NameCust, CodeTerr) VALUES (@.Namecust, @.codeterr)"

SelectCommand="SELECT CustomerID, NameCust FROM dbo.BudgetProspects WHERE (CodeTerr = @.codeterr)"

UpdateCommand="UPDATE dbo.BudgetProspects SET NameCust = @.namecust">

<UpdateParameters>

<asp:ParameterName="namecust"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</SelectParameters>

<InsertParameters>

<asp:ControlParameterControlID="textbox2"Name="Namecust"PropertyName="text"/>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</InsertParameters>

</asp:SqlDataSource>

</code>

codebehind button_click:

<code>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

IfNot TextBox2.TextIsNothingThen

SqlDataSource1.Insert()

TextBox2.Text =""

EndIf

EndSub

</code>

Never mind, complete stupidity on my part the altenate row style had white background and white text.

Odd SQL Server behavior

Hello everyone,
It seems like I've got a runaway SQL Server process.
Almost without fail, when I'm connected to the Internet
via my cable-modem, the SQL Server process starts to
consume 97-100% of the CPU resources on my machine after
about 45 minutes to 1 hour from the time the process is
started. Not only does it consume almost all my CPU's
power, but the number of packets sent out through the
Ethernet cable grows exponentially to gigantic proportions:
prior to SQL Server going nuts, I see the number of
packages SENT and RECEIVED in the order of a few thousand,
but afterwards the number of SENT packages skyrockets to
several MILLION, and keeps going and going.
When this happens, I cannot stop the SQL Server service,
nor can I kill the process. Only solution I have found up
until now is to reboot.
Any ideas? Could this be some sort of a SQL Server virus?
About my system:
Compaq Evo N600c
Tons of free space; 768MB RAM
Windows 2000 Server SP3
SQL Server 2000 SP3
Please send replies to:
jgiosone AT sofwerks.comThis is symptomatic of the Slammer worm but you are not vulnerable if you
have SP3 installed. You might double-check that SP3 is actually installed
with the following:
SELECT SERVERPROPERTY('ProductLevel')
Also, check to see if you have other unpatched instances on your machine.
See <http://www.microsoft.com/sql/downloads/securitytools.asp> for
information on the SQL Critical Update tool.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jose P. Gisone" <jgisone@.sofwerks.com> wrote in message
news:448d01c3e45b$38ee6410$a301280a@.phx.gbl...
> Hello everyone,
> It seems like I've got a runaway SQL Server process.
> Almost without fail, when I'm connected to the Internet
> via my cable-modem, the SQL Server process starts to
> consume 97-100% of the CPU resources on my machine after
> about 45 minutes to 1 hour from the time the process is
> started. Not only does it consume almost all my CPU's
> power, but the number of packets sent out through the
> Ethernet cable grows exponentially to gigantic proportions:
> prior to SQL Server going nuts, I see the number of
> packages SENT and RECEIVED in the order of a few thousand,
> but afterwards the number of SENT packages skyrockets to
> several MILLION, and keeps going and going.
> When this happens, I cannot stop the SQL Server service,
> nor can I kill the process. Only solution I have found up
> until now is to reboot.
> Any ideas? Could this be some sort of a SQL Server virus?
> About my system:
> Compaq Evo N600c
> Tons of free space; 768MB RAM
> Windows 2000 Server SP3
> SQL Server 2000 SP3
>
> Please send replies to:
> jgiosone AT sofwerks.com

Odd SQL Server behavior

Hello everyone,
It seems like I've got a runaway SQL Server process.
Almost without fail, when I'm connected to the Internet
via my cable-modem, the SQL Server process starts to
consume 97-100% of the CPU resources on my machine after
about 45 minutes to 1 hour from the time the process is
started. Not only does it consume almost all my CPU's
power, but the number of packets sent out through the
Ethernet cable grows exponentially to gigantic proportions:
prior to SQL Server going nuts, I see the number of
packages SENT and RECEIVED in the order of a few thousand,
but afterwards the number of SENT packages skyrockets to
several MILLION, and keeps going and going.
When this happens, I cannot stop the SQL Server service,
nor can I kill the process. Only solution I have found up
until now is to reboot.
Any ideas? Could this be some sort of a SQL Server virus?
About my system:
Compaq Evo N600c
Tons of free space; 768MB RAM
Windows 2000 Server SP3
SQL Server 2000 SP3
Please send replies to:
jgiosone AT sofwerks.comThis is symptomatic of the Slammer worm but you are not vulnerable if you
have SP3 installed. You might double-check that SP3 is actually installed
with the following:
SELECT SERVERPROPERTY('ProductLevel')
Also, check to see if you have other unpatched instances on your machine.
See <http://www.microsoft.com/sql/downlo...curitytools.asp> for
information on the SQL Critical Update tool.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jose P. Gisone" <jgisone@.sofwerks.com> wrote in message
news:448d01c3e45b$38ee6410$a301280a@.phx.gbl...

quote:

---

> Hello everyone,
> It seems like I've got a runaway SQL Server process.
> Almost without fail, when I'm connected to the Internet
> via my cable-modem, the SQL Server process starts to
> consume 97-100% of the CPU resources on my machine after
> about 45 minutes to 1 hour from the time the process is
> started. Not only does it consume almost all my CPU's
> power, but the number of packets sent out through the
> Ethernet cable grows exponentially to gigantic proportions:
> prior to SQL Server going nuts, I see the number of
> packages SENT and RECEIVED in the order of a few thousand,
> but afterwards the number of SENT packages skyrockets to
> several MILLION, and keeps going and going.
> When this happens, I cannot stop the SQL Server service,
> nor can I kill the process. Only solution I have found up
> until now is to reboot.
> Any ideas? Could this be some sort of a SQL Server virus?
> About my system:
> Compaq Evo N600c
> Tons of free space; 768MB RAM
> Windows 2000 Server SP3
> SQL Server 2000 SP3
>
> Please send replies to:
> jgiosone AT sofwerks.com

---

sql

Odd Sql Problem

If I run this sql statement:

SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched

I get a result like this:

04017.0023 zN/S
04023.0559 0Ready
04127.0311 aJan 21 2005
etc

So far so good...

Now I want to remove the left hand letter in that status column,
and I try nesting the query, like this:

select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb

Now I get:
04017.0023 N/S
04023.0559 Ready
04127.0311 an 21 2005

For some reason, it removes 2 characters when there is a date contained in the string. Any body have any ideas?Post the Table DDL|||CREATE TABLE [dbo].[rptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[product] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tons] [real] NOT NULL ,
[status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bill_to_numb] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[request_date] [smalldatetime] NULL ,
[LoadNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchedID] [bigint] NULL ,
[Sched_Ord_Status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO|||Not for me...the whole thing doesn't make sense though...

Do you have a real world example?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE [dbo].[myrptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) ,
[status] [varchar] (13)
)
GO

INSERT INTO myrptCS_Sched (Order_Numb, status)
SELECT '04017.0023', 'N/S' UNION ALL
SELECT '04023.0559', 'Ready' UNION ALL
SELECT '04127.0311', 'Jan 21 2005'
GO

SELECT order_numb
, RIGHT(MAX(stat),LEN(MAX(stat))-1) as Status
FROM (
SELECT order_numb
, stat = CASE WHEN status = 'N/S' THEN 'zN/S'
WHEN status = 'ready' THEN '0Ready'
ELSE cast('a' + status as varchar(13))
END
FROM myrptCS_Sched
) s
GROUP BY order_numb
GO

SET NOCOUNT OFF
DROP TABLE myrptCS_Sched
GO|||The table is a base recordset for a report. It gets truncated and repopulated hourly. The table shows the production status of each item on an order, so an order number can occur multiple times on the report (once for each item).
For each item, there is one of three statuses(?):
N/S = Not Scheduled
Ready = Product is already in inventory
Date = The date that production is scheduled for the item.

Now I've been asked to show an order summary on the report, and let the users drill into the detail only if they need to. I decided to add a column to the table (Sched_Ord_Status), and go back and populate that column after the rest of the table has been populated. I need it to show the "worst case" status for the order. N/S being worst, and Ready being best. If everything on the order is Ready, except for one item, which is N/S, then the order status is N/S.|||If it helps at all, the initial sql I posted is part of this statement:

update rptCs_Sched set sched_ord_status = d.Status
from rptcs_sched inner join (
select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb) d on rptcs_sched.order_numb = d.order_numb|||Well did you cut and paste my code to see that the sympton you describe is not happening with the code I posted?

Noe how about the DDL for the other table and some DML to populate it with some sample data like I did...

Odd SQL Performance problem

I can't post DDL for this, but perhaps some of you may have experience
with this and will know what's happening.
I have the following problem:
INSERT TableName
SELECT * FROM ViewName
ViewName is a view that returns about 500,000 rows. TableName is a
materialized, cached representation of that data because the view is
relatively complex. I'm hesitant to use an Indexed View because I want
control over how and when my materialized copy gets updated.
I stopped the above statement after it executed for nearly 24 hours.
The following set of statements only took a few minutes to complete
successfully:
SELECT * INTO #X FROM ViewName
INSERT TableName
SELECT * FROM #X
Any ideas as to why the second set of statements would execute so much
more quickly than the first? I assume that the first statement is
trying to sort the data as it selects it to do the clustered index
inserts, thus executing multiple times on smaller sets of data, whereas
the second set of statements executes the view, stores the data and
then has a much simpler result set to sort. Are there any types of
hints that I could provide the processor to speed execution of the
first set of statements?
-AlanDid you look to see what the bottleneck was? Was there blocking, were the
disks or cpu pegged or was it just sitting there? Have you checked the
estimated plans for each to see what the differences were?
Andrew J. Kelly SQL MVP
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1145127196.691104.246480@.z34g2000cwc.googlegroups.com...
>I can't post DDL for this, but perhaps some of you may have experience
> with this and will know what's happening.
> I have the following problem:
> INSERT TableName
> SELECT * FROM ViewName
> ViewName is a view that returns about 500,000 rows. TableName is a
> materialized, cached representation of that data because the view is
> relatively complex. I'm hesitant to use an Indexed View because I want
> control over how and when my materialized copy gets updated.
> I stopped the above statement after it executed for nearly 24 hours.
> The following set of statements only took a few minutes to complete
> successfully:
> SELECT * INTO #X FROM ViewName
> INSERT TableName
> SELECT * FROM #X
> Any ideas as to why the second set of statements would execute so much
> more quickly than the first? I assume that the first statement is
> trying to sort the data as it selects it to do the clustered index
> inserts, thus executing multiple times on smaller sets of data, whereas
> the second set of statements executes the view, stores the data and
> then has a much simpler result set to sort. Are there any types of
> hints that I could provide the processor to speed execution of the
> first set of statements?
> -Alan
>|||all of the following options are untested :)
insert into tablename select * from (select * from viewname) as a
or
insert into tablename exec('select * from viewname')
P.S: Regarding the second option... I don't know if this kind of an execute
statement works.. never came across a situation where I had to use this :)|||Check the indexes on the target table.
If the data your inserting has primary key values (or other clustered
index values) that are all over the place (not in relative sequence) or
require insertion somewhere other than the end of the table, you might
run into a lot of page splits.
If that target table is large and the inserted data can't fit into the
indexes free space (fillfactor), it can take forever.
If this is the case, you might be better of dropping the clustered
index...insert the data...recreate the clustered index.
Hope this helps...John|||I think it's more related to the processor trying to batch and insert
blocks of results from the source view. One thing I've noticed is that
when running sp_who2 I seem to catch the spid shifting between SELECT,
INSERT and EXECUTE. I was kindof surprised to see EXECUTE in there.
Yes, it's exactly as you saw it INSERT x SELECT * FROM y -- No triggers
or anything.
My main reason for reporting this is here on USENET that I think it's
something that should be optimized in the query processor/optimizer
within SQL Server.
-Alan
retlaw wrote:
> Check the indexes on the target table.
> If the data your inserting has primary key values (or other clustered
> index values) that are all over the place (not in relative sequence) or
> require insertion somewhere other than the end of the table, you might
> run into a lot of page splits.
> If that target table is large and the inserted data can't fit into the
> indexes free space (fillfactor), it can take forever.
> If this is the case, you might be better of dropping the clustered
> index...insert the data...recreate the clustered index.
> Hope this helps...John|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> insert into tablename exec('select * from viewname')
> P.S: Regarding the second option... I don't know if this kind of an
> execute statement works.. never came across a situation where I had to
> use this :)
Yes, it works, but I'd be surprised if it makes any changes to performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Odd SQL IN usage?

Hi guys,

Got an odd SQL string that I need to produce that is most probably simple to
construct but with it being hot in our office, I simply can't get my head
around it...!!

Its based around an online emailing facility whereby multiple hotels can be
emailed via a single application. Users within the application have access
rights to email only specific hotels.

The tables are laid out like this (irrelevant columns left out)...

CampaignID, CampaignName, CampaignHotelIDs
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2

UserID, UserName, UserHotelIDAccess
1 Test User 1,6,7
2 Test User 2,7

Now on the stats page I want to give users access to view ONLY sent
campaigns to which they have access to view, I was considering the IN SQL
statement to achieve something like this...

'WHERE CampaignHotelIDs IN UserHotelIDAcess'

...but that doesn't want to work, can anyone give me any ideas to get this
working within just a single SQL query?

Cheers, @.sh@.sh wrote:

Quote:

Originally Posted by

Hi guys,
>
Got an odd SQL string that I need to produce that is most probably simple to
construct but with it being hot in our office, I simply can't get my head
around it...!!
>
Its based around an online emailing facility whereby multiple hotels can be
emailed via a single application. Users within the application have access
rights to email only specific hotels.
>
The tables are laid out like this (irrelevant columns left out)...
>
CampaignID, CampaignName, CampaignHotelIDs
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2
>
UserID, UserName, UserHotelIDAccess
1 Test User 1,6,7
2 Test User 2,7
>
Now on the stats page I want to give users access to view ONLY sent
campaigns to which they have access to view, I was considering the IN SQL
statement to achieve something like this...
>
'WHERE CampaignHotelIDs IN UserHotelIDAcess'
>
...but that doesn't want to work, can anyone give me any ideas to get this
working within just a single SQL query?

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Your columns "UserHotelIDAccess" and "CampaignHotelIDs" are in violation
of 1NF (First Normal Form) "A cell must be atomic." I.e., there must
only be one item per column. If your data was like this:

CampaignID, CampaignName, CampaignHotelIDs
1 Test Campaign 1
1 Test Campaign 4
1 Test Campaign 5
1 Test Campaign 7
1 Test Campaign 9
2 Test Campaign2 1
2 Test Campaign2 2

UserID, UserName, UserHotelIDAccess
1 Test User 1
1 Test User 6
1 Test User 7
2 Test User 2
2 Test User 7

Your WHERE clause would work like this:

WHERE CampaignHotelIDs = UserHotelIDAccess
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK15H4echKqOuFEgEQJqNgCePdnBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZhjJ5pZ1UFV
=kzX+
--END PGP SIGNATURE--|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Got an odd SQL string that I need to produce that is most probably
simple to construct but with it being hot in our office, I simply can't
get my head around it...!!

It must also have been hot in the office when this was desiged:

Quote:

Originally Posted by

CampaignID, CampaignName, CampaignHotelIDs
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2
>
UserID, UserName, UserHotelIDAccess
1 Test User 1,6,7
2 Test User 2,7

If you are on SQL 2000, this is very painful to work with. If you are on
SQL 2005, it's slightly less painful.

Before I go, which version of SQL Server do you actually have?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You can immediately improve your database by not allowing the moron
that crammed everything into a single column to write code until he can
explain Normal Forms. I would also inspect everything he has done and
pull it out. That kind of fundamental error tells you what kind of
programmer you have.|||Thanks for your reply, I see what you're suggesting but its too late to
change the overall structure of the table now ;o(

"MGFoster" <me@.privacy.comwrote in message
news:tOerg.596$vO.300@.newsread4.news.pas.earthlink .net...

Quote:

Originally Posted by

>
Your columns "UserHotelIDAccess" and "CampaignHotelIDs" are in violation
of 1NF (First Normal Form) "A cell must be atomic." I.e., there must
only be one item per column. If your data was like this:
>
CampaignID, CampaignName, CampaignHotelIDs
1 Test Campaign 1
1 Test Campaign 4
1 Test Campaign 5
1 Test Campaign 7
1 Test Campaign 9
2 Test Campaign2 1
2 Test Campaign2 2
>
UserID, UserName, UserHotelIDAccess
1 Test User 1
1 Test User 6
1 Test User 7
2 Test User 2
2 Test User 7
>
Your WHERE clause would work like this:
>
WHERE CampaignHotelIDs = UserHotelIDAccess
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
>
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
>
iQA/AwUBRK15H4echKqOuFEgEQJqNgCePdnBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZhjJ5pZ1UFV
=kzX+
--END PGP SIGNATURE--

|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message

Quote:

Originally Posted by

news:Xns97F91FCEE7C9Yazorman@.127.0.0.1...
It must also have been hot in the office when this was desiged:

Well it seemed a good idea at the time, and infact works within the
application itself brilliantly and efficiently without numerous hits on the
DB - however this one element now is proving tricky

We're using SQL 7 so based on what you've said, I guess you can't help ;o)

Cheers, @.sh|||On Fri, 7 Jul 2006 10:46:12 +0100, @.sh wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
>news:Xns97F91FCEE7C9Yazorman@.127.0.0.1...
>It must also have been hot in the office when this was desiged:

>
>Well it seemed a good idea at the time, and infact works within the
>application itself brilliantly and efficiently without numerous hits on the
>DB - however this one element now is proving tricky
>
>We're using SQL 7 so based on what you've said, I guess you can't help ;o)

Hi @.sh,

Oh, Erland certainly can help you. Just check out his site:
http://www.sommarskog.se/arrays-in-sql.html#SQL7
--
Hugo Kornelis, SQL Server MVP|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
>news:Xns97F91FCEE7C9Yazorman@.127.0.0.1...
>It must also have been hot in the office when this was desiged:

>
Well it seemed a good idea at the time, and infact works within the
application itself brilliantly and efficiently without numerous hits on
the DB - however this one element now is proving tricky
>
We're using SQL 7 so based on what you've said, I guess you can't help
;o)

SQL 7? That will not make it even less painful. :-)

There are two approaches. One is to run a cursor over the rows and
for each row call a stored procedure that unpacks the row into a
table, so that you can run the queries the proper way. You can find
examples of such procedures on my web site:
http://www.sommarskog.se/arrays-in-sql.html#SQL7.

I would in such case such suggest that you put this code in the trigger
on this table, so that you always can work on properly desinged tables.
Overall, you should strive of changing the database design to move away
from this structure. Yes, I see that you in other post said that it's
too late to change the design, but I disagree. Unless the product already
has a declared end of life, it's never too late to change a flat-out
incorrect design like this one.

The other approach is a query similar to:

WHERE ',' + UserHotelIDAcess + ',' LIKE
'%,' + CampaignHotelIDs + ',%'

This theme has some variations, see
http://www.sommarskog.se/arrays-in-sql.html#realslow. All of them are
painfully slow, and could prove unworkable if your data has any volumes.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Odd Snapshot Behavior

I'm encountering some behavior in a Snapshot publication I just can't figure
out.
I have a publication consisting of 124 tables. These tables are all lookup
tables so the data isstatic and so are the respcetive table schemas.
I dropped all subscriptions to this publication, dropped the publication,
and disabled replication. I then configured the server for replication. I
recreated the publication with the required articles. I created a new
subscriotion and chose to create the snapshot immediately.
The snapshot gets created just fine, but when the distribution agent
attempts to apply the snapshot, I get the following error:
Cannot drop the table 'dbo.ReportTypeLookup' because it is being used for
replication
The odd thing is that I'm not trying to drop that table at all!! As soon as
I remove that particular command from MSrepl_commands, another error occurs,
only this time it's trying to drop another table.
I am 100% sure no one is trying to drop any tables in production...
If anyone has any insight into this I would truly appreciate it.
Thank You!!
It sounds like the subscriber has been previously set up as a publisher of
these tables. Try running sp_removedbreplication on the subscriber which
should reset the replication flags at the article level and then
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

odd server behavior

Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
AramidIt could be that the auto update stats kicked in, or that the data was
readily available in cache. Do check in Profiler to see if there are any
recompilations. Also capture the execution plan in Profiler, and compare
with the one you are getting in Query Analyzer. It would also help if you
had posted the stored procedure code, so that we can point out any issues.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"aramid" <aramid@.hotmail.com> wrote in message
news:v5s371p46il11k82jn9tene1701fmtad2j@.
4ax.com...
Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
Aramidsql