Precompiling checking problem

i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ

Precedence Constraints and Sequence Containers

I have about 12 sequence containers mapped out to execute separately based on a precedence contraint and an expression. These lie at the same level and order in my sequence. Only one of these will execute based on an expression. After any of these executes, I have a consecutive sequence container that I'm attempting to execute. I've set the precedence contraint from all twelve of the prior sequence containers to this single sequence container that I would like to run after any of the 12. My problem is that the package will only allow one of these twelve sequence containers to become a precedence of this second single sequence container at a time. The package will let me graphically attach the precedence constraint from all 12 to this single sequence container, but when the package runs, it fails to follow through to this single sequence container. I'm trying to figure out why this is the case and how I can get what I would like to work -- work. Thanks.

Double-click on one of the precendence constraints going into the single sequence container. Near the bottom you will see a drop down that allows you to set it to an OR condition (The constraints will turn into dotted lines)

This means that that will execute if ANY of its precedence cnstraints are satisfied. If you leave it set to AND, then ALL of the constraints have to be satisfied, which will never happen in your case since the other 11 sequence containers will never execute.

|||

Thanks Dave!

I saw that but wasn't sure how that worked.

Pre execute Failure on Look up task

Hi,

I have an OLE DB Source. The output of OLEDB source is connected to a look up task. The output of the lookup task goes into OLE DB Destination. When executed..

I am getting this error:

DTS.Pipeline: component "Lookup" (2834) failed the pre-execute phase and returned error code 0x8007000E.

Any idea why pre-execute phase failure occurs?

TIN,
Anand

0x8007000E is out of memory error. How big is the reference table? Is Lookup running in Full Cache mode? Can you try running it in Partial Cache mode?|||Hi,

You are probably right. I have a data flow task before this specific task which contains 15 Look Up Tasks. I am presuming that memory is not released when needed.

The reference table is very small. It contains around 220 records.

Also, I have 2 GB memory available on this machine.

-Anandsql

Pre and PostExecute Event handler called more than once

I have a task configured on the post execute event handler of a package expecting this task to be executed only once after the completion of all the other tasks in the package. But I found the task configured on the post execute event of the package getting executed as many times as the number of tasks in the package + 1 . Is there any workaround for this problem?

Yes,

The reason this happens is that events "bubble-up" from a container to all parent containers and are caught by any eventhandlers that exist on those parent containers.

To stop this behaviour you'll need to create an eventhandler scoped to each task's OnPreExecute & OnPostExecute eventhandlers and set [@.System::Propogate]=FALSE. This will stop the event "bubbling-up".

-Jamie

Prcess could not execute 'sp_MSadd_repl_commands27hp'

Hello all,
Our log reader agent is failing at times with the error "The process could
not execute 'sp_MSadd_repl_commands27hp' on 'DistributorServerName'...
We have custom distribution agents that have -CommitBatchSize = 500 and
-CommitBatchThreshold = 1000...
I'm also noticing that our distribution agent cleanup agent progressively
takes longer through our peak. From 10 minutes to close to an hour...
So I'm thinking we are having contention between the distribution db cleanup
and maybe our -CommitBatchSize...
Any recommendations?
Thanks!
Stop the distribution agent and run the log reader agent again. You may need
to enable logging and post the results back here if the condition does not
clear.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
> Hello all,
> Our log reader agent is failing at times with the error "The process could
> not execute 'sp_MSadd_repl_commands27hp' on 'DistributorServerName'...
> We have custom distribution agents that have -CommitBatchSize = 500 and
> -CommitBatchThreshold = 1000...
> I'm also noticing that our distribution agent cleanup agent progressively
> takes longer through our peak. From 10 minutes to close to an hour...
> So I'm thinking we are having contention between the distribution db
> cleanup
> and maybe our -CommitBatchSize...
> Any recommendations?
> Thanks!
|||OK... Here is what is in the log... It goes from working fine to throwing
errors to working fine again:
...
Status: 16384, code: 20007, text: 'No replicated transactions are available.'.
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'DistServerName'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 22020, text: 'Batches were not committed to the
Distributor.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Status: 4096, code: 20024, text: 'Initializing'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'DistServerName'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 2000, text: 'IDistPut Interface has been shut down.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Status: 4096, code: 20024, text: 'Initializing'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
...
Thanks!
Michael
"Hilary Cotter" wrote:

> Stop the distribution agent and run the log reader agent again. You may need
> to enable logging and post the results back here if the condition does not
> clear.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
>
>
|||At the time the below error occured, the "Distribution clean up:
distribution" job was running:
...
Status: 2, code: 1003, text: 'The process could not execute
'sp_MSadd_repl_commands27hp' on 'ANETREPUBVSQL1F'.'.
The process could not execute 'sp_MSadd_repl_commands27hp' on
'DistServerName'.
Status: 2, code: 1003, text: 'Timeout expired'.
Status: 0, code: 22020, text: 'Batches were not committed to the
Distributor.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.
...
As I noticed before, our distribution cleanup takes 30-50 minutes to
complete during peak traffic.
Thanks,
Michael
"Hilary Cotter" wrote:

> Stop the distribution agent and run the log reader agent again. You may need
> to enable logging and post the results back here if the condition does not
> clear.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8A5EB566-D904-4335-ABF8-9BA387671F73@.microsoft.com...
>
>
|||Stop the distribution clean up task until your log reader has processed all
of your commands in the log. Set readbatchsize to 500 and the querytimeout
and log timeout to something large - I would try 300. These settings are for
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:F303B3EA-3B82-4F6B-AF9B-23386530C0F9@.microsoft.com...[vbcol=seagreen]
> At the time the below error occured, the "Distribution clean up:
> distribution" job was running:
> ----
> ...
> Status: 2, code: 1003, text: 'The process could not execute
> 'sp_MSadd_repl_commands27hp' on 'ANETREPUBVSQL1F'.'.
> The process could not execute 'sp_MSadd_repl_commands27hp' on
> 'DistServerName'.
> Status: 2, code: 1003, text: 'Timeout expired'.
> Status: 0, code: 22020, text: 'Batches were not committed to the
> Distributor.'.
> The agent failed with a 'Retry' status. Try to run the agent at a later
> time.
> ...
> ----
> As I noticed before, our distribution cleanup takes 30-50 minutes to
> complete during peak traffic.
> Thanks,
> Michael
>
>
> "Hilary Cotter" wrote:
|||The default Log Reader agent profile has a -ReadBatchSize of 500 and a
-QueryTimeout of 300 already...
So when you say "log timeout to something large - I would try 300", are you
meaning the QueryTimeout? Again, FYI... This is SQL 2000.
Anyway, I upped the QueryTimeout to 600 and I'm still getting the timeout...
Thanks,
Michael
"Hilary Cotter" wrote:

> Stop the distribution clean up task until your log reader has processed all
> of your commands in the log. Set readbatchsize to 500 and the querytimeout
> and log timeout to something large - I would try 300. These settings are for
> your log reader agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:F303B3EA-3B82-4F6B-AF9B-23386530C0F9@.microsoft.com...
>
>
|||OK... I upped the QueryTimout to 1800 and I got the error "The agent is
suspect. No response within last 10 minutes."...
Thanks,
Michael
"BATMAN" wrote:
[vbcol=seagreen]
> The default Log Reader agent profile has a -ReadBatchSize of 500 and a
> -QueryTimeout of 300 already...
> So when you say "log timeout to something large - I would try 300", are you
> meaning the QueryTimeout? Again, FYI... This is SQL 2000.
> Anyway, I upped the QueryTimeout to 600 and I'm still getting the timeout...
> Thanks,
> Michael
>
> "Hilary Cotter" wrote:

Prbblems with a Process.

Hi, i have a store procedure that takes about 20 minutes to execute.
Today i want to execute the same process and it takes more than two
hours and the process doesnt finish.
I restart the server twice, and the problem persist.
What do you recommend to me to check and found the reason that the
process take more time.
Thanks a lot for your help!.
*** Sent via Developersdex http://www.codecomments.com ***Have you tried reindexing the underlying tables or running an UPDATE
STATISTICS on them? That might solve your issue. Do you have regular
maintenance routines setup for this server?
Also, check the execution plan and see if you see anything out of the
ordinary.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"MariaGuzman" wrote:

> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesn4t finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Hi Maria,
Do u have indexes created on that table.Try reindexing.
If the problem persists just recreate ur stored procedure.
Run the Profilor and see what is happening at the back.
or Enable C2 auditing feature to trace all the activities.
HTH
from
Doller|||In addition to checking the execution plan, also blocking can be the cause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message news:eXUUOnsyFHA.3588@.tk2msftngp13.phx.gb
l...
> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesnt finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.codecomments.com ***

Prbblems with a Process.

Hi, i have a store procedure that takes about 20 minutes to execute.
Today i want to execute the same process and it takes more than two
hours and the process doesnt finish.
I restart the server twice, and the problem persist.
What do you recommend to me to check and found the reason that the
process take more time.
Thanks a lot for your help!.
*** Sent via Developersdex http://www.codecomments.com ***
Have you tried reindexing the underlying tables or running an UPDATE
STATISTICS on them? That might solve your issue. Do you have regular
maintenance routines setup for this server?
Also, check the execution plan and see if you see anything out of the
ordinary.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"MariaGuzman" wrote:

> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesn4t finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Hi Maria,
Do u have indexes created on that table.Try reindexing.
If the problem persists just recreate ur stored procedure.
Run the Profilor and see what is happening at the back.
or Enable C2 auditing feature to trace all the activities.
HTH
from
Doller
|||In addition to checking the execution plan, also blocking can be the cause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message news:eXUUOnsyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesnt finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.codecomments.com ***
sql

Prbblems with a Process.

Hi, i have a store procedure that takes about 20 minutes to execute.
Today i want to execute the same process and it takes more than two
hours and the process doesn´t finish.
I restart the server twice, and the problem persist.
What do you recommend to me to check and found the reason that the
process take more time.
Thanks a lot for your help!.
*** Sent via Developersdex http://www.developersdex.com ***Have you tried reindexing the underlying tables or running an UPDATE
STATISTICS on them? That might solve your issue. Do you have regular
maintenance routines setup for this server?
Also, check the execution plan and see if you see anything out of the
ordinary.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"MariaGuzman" wrote:
> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesn4t finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Hi Maria,
Do u have indexes created on that table.Try reindexing.
If the problem persists just recreate ur stored procedure.
Run the Profilor and see what is happening at the back.
or Enable C2 auditing feature to trace all the activities.
HTH
from
Doller|||In addition to checking the execution plan, also blocking can be the cause.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message news:eXUUOnsyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi, i have a store procedure that takes about 20 minutes to execute.
> Today i want to execute the same process and it takes more than two
> hours and the process doesn´t finish.
> I restart the server twice, and the problem persist.
> What do you recommend to me to check and found the reason that the
> process take more time.
> Thanks a lot for your help!.
> *** Sent via Developersdex http://www.developersdex.com ***

PPV's, Loops & Child Packages - Bug or By Design

It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour

Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?

This has just become a big problen for us so any guidance would greatly appreciated.

Paul

I have not come across this problem before. I suggest opening a support issue for this - it will likely need some debugging.

How are you calling the child package multiple times? In a loop?

Donald

|||

Our master package is data driven, from a database.

Dimensions are build / modified in a Master database and then are moved into a number of topic specific datamarts. A dimension like CALENDAR natutally resides in most datamarts, we have a series of packages (one per Dimension) that are reponsible for moving new/changed data to the datamart (they are lightening quick). The child packages take 2 Parent variables SourceConnectionString and TargetConnectionString, these child packages are called from the parent package using the 'Execute Package' task.

This approach has worked well untill we got to serious testing of the system. When the Parent package runs properly it is quite likely that the calendar will move to several places in one execution. The first call to the Child package uses the parent variables correctly, subsequent calls to the child (event if 10 or 15 other packages have been called in the meantime) ignore the new settings of the variables and continue to adress the first datamart setting, consequently we have missing rows, the package does not fail, so the dependancy is met (we have dependacy info in our metadata) and the the Fact build crashes due to missing rows.

This serious stuff for us, we aim to go live at the end of this month

I too may have to hand over the armband....

Paul

|||

I do think you'll need to open a support call for the original issue.

However, I wonder if you could work around this. One way may be to use a different configuration type, such as SQL Server, or registry setting or environment variable.

Your master package could use a script to write out the value to that location and child packages would pick it up from there.

This would be very similar to using the parameter files that other ETL tools use for these scenarios.

Donald

If you do have to hand over the armband, try not to cry about it on TV.

|||

Sorry, my fault.

The orginal version of the package was using Event Handlers, I removed these some time ago, but missed one which was setting an action as complete. So no bug in SSIS just my carlessness.

Just a thought, it would be really nice if icons on the design surface had some indication that and Event Handler was lurking underneath.

Paul

|||

Fantastic idea. You should log it at Microsoft Connect.

It would also be great if there were a visual clue that properties had expressions on them!

-Jamie

PP: XML Variable DataLength returns as 5

Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttributes)
--
5
Question
======= How come I get a value of 5 even tough I passed nothing.Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan
> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttributes

PowerBuilder connect to Access(get an error SQLSTATE = 01S01 )

I am trying to connect Microsoft Access Database from PowerBuilder(ODBC),
when execute a select clause, get an error SQLSTATE = 01S01(Row errors), bu
t
trying open the database from Microsoft Access 2000 tools , the row is very
good. I am puzzled this error. Someone help me to this problem, pleaseHi
This is a SQL Server newsgroup a better place to post this would be a
powerbuilder or Access newsgroup. Searching google for 01S01 returned quite
a
few hits
http://tinyurl.com/79ru9
this one looked promising
http://tinyurl.com/cr6u7
and indicates that is pssibly masking the real error which in this case the
truncation of a string.
John
"guo-feng lui via webservertalk.com" wrote:

> I am trying to connect Microsoft Access Database from PowerBuilder(ODBC
),
> when execute a select clause, get an error SQLSTATE = 01S01(Row errors),
but
> trying open the database from Microsoft Access 2000 tools , the row is ver
y
> good. I am puzzled this error. Someone help me to this problem, please
>sql

Possible to run DTS package on SQL Server 2005

Is it possible to execute (and continue to develop, if necessary) a
SQL Server 2000 DTS package on SQL Server 2005?
Thanks for your help.hi,
vegathena@.gmail.com wrote:
> Is it possible to execute (and continue to develop, if necessary) a
> SQL Server 2000 DTS package on SQL Server 2005?
> Thanks for your help.
please do not mult-ipost, eventually do cross-post...
see answers in microsoft.public.sqlserver.programming and
microsoft.public.sqlserver.msde..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
-- remove DMO to reply

Possible to run DTS package on SQL Server 2005

Is it possible to execute (and continue to develop, if necessary) a
SQL Server 2000 DTS package on SQL Server 2005?
Thanks for your help.
hi,
vegathena@.gmail.com wrote:
> Is it possible to execute (and continue to develop, if necessary) a
> SQL Server 2000 DTS package on SQL Server 2005?
> Thanks for your help.
please do not mult-ipost, eventually do cross-post...
see answers in microsoft.public.sqlserver.programming and
microsoft.public.sqlserver.msde..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
-- remove DMO to reply

Possible Bug with SQl Server 2000

Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.
Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:

>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>
|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:

> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
>
|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
[vbcol=seagreen]
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>

Possible Bug with SQl Server 2000

Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:
>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:
> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
> >Hi,
> >
> >Look at the simple set of SQL statements below: Execute the following on
> >Query Analyzer.
> >
> >declare @.a float
> >declare @.b float
> >set @.a=24353.02821769137
> >set @.b=36459.95
> >exec('select '+@.a+'/'+@.b)
> >
> >Ans=.6679392
> >
> >Now, if I change the value of @.b to 36459.96,
> >I get a value of 0. Integer division is performed just because the value of
> >@.b increased by .01
> >
> >This looks like bug with the internal workings of SQL. This is very
> >dangerous cause basic SQL assumption is that if a value is a float, the
> >division will also be a float division. Integer division on the other hand
> >gives erroneous results in this case.
> >
> >Please solve this problem as soon as possible.
> >
> >-Ling Yu.
> >
> >
>|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>
>>Ling Yu,
>> This is not a bug, but it is surprising, and in my opinion, the query
>>should actually cause a syntax error. I'm glad you raised the question,
>>and I bet other people have been affected by this behavior.
>>Here's why it occurs:
>>The query processor must evaluate the query string 'select '+@.a+'/'+@.b
>>as its first step. When strings are concatenated inside exec(), the +
>>operator is not a T-SQL + operator. The + is applied by the front-end
>>application and only after the entire query string is assembled does
>>that string go to the query processor to be interpreted as T-SQL.
>>Query Analyzer (or the system it uses to prepare queries for the engine)
>>follows different rules than T-SQL to interpret the + that puts strings
>>together within exec(). In T-SQL, the + operation would fail, because
>>it tries to convert 'select ' and '/' into floats and add. But here, @.a
>>and @.b are converted to strings. The way in which they are converted to
>>strings is not obvious, but you can see it if you execute
>>exec('select ' + @.a) and exec('select ' + @.b)
>>What's important to realize is that exec() receives only a string,
>>without any type information about the parameters. Unfortunately,
>>floats are converted to strings by the front-end using at most 6 decimal
>>digits of precision. Since @.a is converted in both cases to the string
>>'24353', how the + in the query (not the concatenation + in the exec) is
>>understood depends on the string @.b converts to. If @.b is converted to
>>the string '36459.9', the addition is performed using decimal
>>arithmetic. If @.b is converted to '36460', the query executed is
>>select 24353/36460, with 0 as the result. The types of @.a and @.b are
>>unknown to the query processor, which sees only the string
>>representations of these numbers.
>>While at first glance, you might think the select query here recognizes
>>that it is receiving floats, it is not. exec() can only receive a
>>string and must interpret the types of numbers from their string
>>representations. A safer approach is to use sp_executesql, with
>>parameters, or to add typecasts in your query string.
>>I hope that helps.
>>
>>PAI wrote:
>>
>>Hi,
>>Look at the simple set of SQL statements below: Execute the following on
>>Query Analyzer.
>>declare @.a float
>>declare @.b float
>>set @.a=24353.02821769137
>>set @.b=36459.95
>>exec('select '+@.a+'/'+@.b)
>>Ans=.6679392
>>Now, if I change the value of @.b to 36459.96,
>>I get a value of 0. Integer division is performed just because the value of
>>@.b increased by .01
>>This looks like bug with the internal workings of SQL. This is very
>>dangerous cause basic SQL assumption is that if a value is a float, the
>>division will also be a float division. Integer division on the other hand
>>gives erroneous results in this case.
>>Please solve this problem as soon as possible.
>>-Ling Yu.
>>
>>