floating point exception - unexplainable - even after SP4 still ge

I am in the process of translating Access 97 Databases into SQL, and working
through many very complex interrelated queries which work fine in Access. On
translating many queries all work fine apart from when I get to the top leve
l
query which effectively nests many level of queries. On trying to display
this top level view I get the horrible
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.
Now I have tried to narrow the problem down, even removed any floating point
datatypes from the view but I still get the problem.
I now have a view which works if I join 3 tables but on trying to join 4
tables gives the exception. It does not matter which table I miss out...
Am I hitting some limit of SQL which does not exist in Access 97? Or is
these a nasty bug floating around which cannot handle nested views of a
certain level...
Could some clever person please could come back to this posting urgently
with when there will be another fix for this problem, or email me at
getalifestyle@.easyget.bizHi
The fix as described in
http://support.microsoft.com/defaul...kb;en-us;892840 is not
included in SP4, so you need to get a seperate hotfix for it from Microsoft.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Giergiel" wrote:

> I am in the process of translating Access 97 Databases into SQL, and worki
ng
> through many very complex interrelated queries which work fine in Access.
On
> translating many queries all work fine apart from when I get to the top le
vel
> query which effectively nests many level of queries. On trying to display
> this top level view I get the horrible
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current transacti
on
> is canceled.
> Now I have tried to narrow the problem down, even removed any floating poi
nt
> datatypes from the view but I still get the problem.
> I now have a view which works if I join 3 tables but on trying to join 4
> tables gives the exception. It does not matter which table I miss out...
> Am I hitting some limit of SQL which does not exist in Access 97? Or is
> these a nasty bug floating around which cannot handle nested views of a
> certain level...
> Could some clever person please could come back to this posting urgently
> with when there will be another fix for this problem, or email me at
> getalifestyle@.easyget.biz
>|||Hi
Can you please post the SQL Script here, so that we can try to give u a
solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

Flat File Data Source with variable number of delimited columns

I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).

Is it possible to use a single flat file data source that will work with all of the different width files?
No.

The only thing you can do is read in each line as one big record and then maybe use substrings or something to pick apart the files.

Flat File Data Flow

any suggestions on dealing with a flat file in the format below. I only want to process the data columns in the middle of the file and want to ignore all other rows. This was a very simple task in DTS with a small amount of VBScript in the transformation but it doesn't seem as straightforward in SSIS. thanks

......... file example ......

start-of-file

header1

header2

...

start-of-data

col0|col1|col2|col3|....

col0|col1|col2|col3|....

col0|col1|col2|col3|....

end-of-data

end-of-file

Two steps.

Read the file in first as one big text string (for each row) and pass them through a Conditional Split transformation to filter off each row that you don't want. Then hook it to a flat file destination.

Then use another flat file source against the file just created to do your column parsing. Work with it as needed from there.

Searching this forum will also yield other options (substrings, etc...) that you can try.|||thanks phil. thats helps

Flat file CSV problem

Hi all,

I hope someone can help with a problem i'm having.
I want to process a large number of CSV files into various tables in an SQL database.
The CSV file contains entries on a row by row basis relating to specific events (indicated by an eventID in column 0).
Eventually i think i want to be using a conditional split to process each row seperately depending on the eventID but before i get this far i am having a problem with the source data.

Each event can have varying amounts of columns filled in in the CSV file. And each CSV file can have multiple event types in it.

The flat file manager seems to merge a number of different rows into one within the preview pane. It seems to ignore the end of row delimiter of CrLf.

Can anyone please help me to sort this so that each row is on its own and will allow me to pass the structured data set to a conditional split task?

Many thanks in advance,

GrantOk,

I seem to have managed to set all the data for each row into one component and have a script to extract the EventID to one output column and the remaining parameters to a second column. I need to perform a check on the EventID by passing it and another variable into a stored procedure. How do i go about doing this in the data flow section? is it possible or do i have to look at using the control flow section for this?

Thanks,

Grant|||

Grant,

What kind of check does that procedure performed? Keep in mind that any operation you define in the dataflow will be performed in row by row basis; so a call to a procedure in a data flow will be executed as many times as rows you have in the file. Since you already succeed on separating the eventID from the rest I would try to use a conditional split transformation based on the EventID value and then to performed specific transformations/checks to every data pipeline.

Rafael Salas

|||Hi Rafael,

Thanks for the response. The stored procedure i was talking about returns a value based on the event ID. Having thought about what you said i can set this value manually after the conditional split has been carried out and i know what the event ID is. Does that sound more plausible?

I am trying to rewrite a windows application that processes these files, initially this used a stored procedure to process each row of data. The main stored procedure calls other sub procedures within it. The problem was that the stored procedure itself was getting very unwieldly with a large number of if and nested if statement which meant that the addition of new events was complex and time consuming. Using the SSIS package a believe i can make this a much easier process to manage.

Is it still possible to call a stored procedure in the dataflow task once the conditional split has been performed? i realise that there may be other stored procedures required once i have my data row?

I have just attempted to run some SQL code on a per row basis and have discovered that i cannot seem to user variables or parameters in the OLE DB Command task. How would i go about either returning a value dependant on if the current eventID exists in another table in the database or indeed; how to insert data into a specific table if it doesn't exist.
After i do that then i can get on to processing the event data to the table where this is stored. I hope that makes sense.

Many thanks,

Grant|||

Wow a lot of questions!

First to all let me clarify that there is nothing wrong with having OLE DB Command tasks in your Dataflow; it is just that as personal pratice I try to use bulk operations against the DB when possible.

If what you are trying to use thr stored procedures for is to check if a row exists in a table; you can use the lookup transformation in your dataflow; then use the error output as your insert pipeline and the output as the 'already exists' kind of pipeline (or just not use it if you want to ignore them). The lookup can be also returned other columns from your lookup table if that is what you need

Rafael Salas

|||I tend to ask a lot of them yes :)

I looked into the lookup transformation which i can see how i would use the error output etc. I was then using an OLEDB destinbation to insert rows to the table. The problem with the OLEDB destination is that i cannot loop back to the lookup. With an oledb command would i script the insert command and then be able to loop back to the lookup transformation.
E.g.

Should this be ok to enter as a SQL Command:

if (select count(*) from SerialPartRev Where SerialPartRev.SerialNo = ? and SerialPartRev.PartNo = ?) = 0
begin
Insert into SerialPartRev (SerialNo, PartNo, RevisionNo)
Values(?,?,?)
end

The reason i want to do this is so that the SerialPartRev table is update automatically. I require to do the same thing with a Username table. The big problem is how to check again for the entry before processing further. As i have found looping back to the lookup isn't possible due to it only accepting one input path.

I'm quite happy to accept that i am doing this wrong and that maybe someone could suggest another process for implementing this.

Cheers,

Grant|||Rafael,

I have been reading you're previous post again and whilst i understand what you are saying about using the output and error output in the pipeline depending on if the row exists but one question still remains. Firstly i have to say that there will be a couple of instances where i have to check if data from the columns in the flat file exists in SQL tables. Regardless of if i have to insert the row manually or if it already exists after both of these operations the output still has to go to the one conditional split task. Effectively it splits the path in two and then rejoins after carrying out the necessary functionality. Is this possible without the use of scripting? I believe i can achieve this via a script although it will mean exposing a password in plain taxt so that the database connection string will work properly.

Thank you,

Grant|||Actually, forget it. I have managed to call the stored procedures form the OLE DB commands. I have no idea what i was doing wrong previously but it seems to be working now.

Thanks for you're help on this matter it was most appreciated. I'm sure i'll have more questions in time.

Cheers,

Grant

FIX: Connection Error: (provider: Shared Memory Provider, error: 0 - No process is on the other

Hello,

Please bear with me as I am no Sql Server guru, but am getting this error that is preventing me from continuing with my development work. I am the only developer on my team running Sql Server 2005 and it has been working just fine for the last week. I opened the Management Studio this morning, just like every other morning and got this error:

TITLE: Connect to Server

Cannot connect to (local).

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Of course the help page says no help is available. I have googled myself sick and still have not found a way so resolve this...has anyone else had this error and/or a fix? I am using XP Pro SP2...any help would be greatly appreciated!

Thanks!
Steph

This error usually indicates that the server closed the connection, e.g. during login verification.

The best is to check for any messages from SQL Server correlated to the time of the failure - either in the Application EventLog or in the ERRORLOG file (by default located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory). There is a chance they provide more information to identify the root cause of the failure.

|||Did not see any weird error message may cause this problem.|||Try and connecting to the SQL Server box using the DAC (Dedicated Admin Connection) e.g if your server name is "SERVER" enter "ADMIN:Server" in Server name and click connect.|||

Not sure if you have resolved this or not. I had the same error trying to connect to a new SQL Server 2005 installation with the management studio from a remote machine and discovered that the named pipes protocol was diasabled in the SQL Server network configuration protocols. I enabled it and restarted SQL Server and the problem was resolved.

Good Luck

Lane

|||

I checked the SQL Server log (message 2 above) and realised there were too many connections opened. The error description was verbose. Closed them and it worked.

Cheers

|||

yaah!!!! i have done same and it worked.. but after opening 5 or 6 connection, m getting the same error message again.....

Cheers!!

|||Another mistake that can cause this error is incorrect credentials in a connection string.|||

execute sp_configure and increment the conexion

|||reboot your db server...|||

The reason this message appears is because of the web.config connectionString. Rather than using the default authentication details of Integrated Security=True:

connectionString="Data Source=STL-COPELAND\SQLSERVER2005;Initial Catalog=RJ;Integrated Security=True"

Use "User ID=sa Password=test" for example in the connection string:

connectionString="Data Source=STL-COPELAND\SQLSERVER2005;Initial Catalog=RJ;User ID=sa Password=test"

Rod Copeland

|||

I spent a couple hourse working on this issue until i realised my SQL Server Agent Was stopped. It wasn't set to auto start to reboots didn't help. Oddly all my win32 apps would work fine and asp.net apps running from other machines could get the SQL data but a asp.net 2.0 app running off localhost would throw this error.

Ben

|||

I enable the Protocols "Named Pipes" and it works.

Thanks Lane.

|||After a while like 5 hours that I tried to solve this problem Thanks Lane I found your post... & Now it works|||I received this error after dropping connections while detaching a database. I restarted the SQL server service as well as agent and IIS. nothing seemed to fix it. I then logged into SQL Server under another admin login. I looked at the properties under my usual login and found that the default database was not set, which seemed strange. I reset the default database and logged back in under my account. Not quite sure what SQL Server did, but now it's fixed.

Fishing the DTS Knowledge Pool

I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).

I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.

Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
Try the *real* DTS knowledge pool: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

This forum is limited to SSIS.

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First time, replication

Why does the replication process adds a rowguid, a
uniqueidentifier, column in most of my tables? Is this a
default/necessary behaviour? It is causing some conflicts,
so could this be suppress? TIA
That's a normal behavior for merge replication.
Merge replication requires a universal unique way to identify each row.
So that's why it adds a rowguid to every table involved in a publication
unless this table already possesses a column of such type
"John" <anonymous@.discussions.microsoft.com> a crit dans le message de
news: 10ea01c50fc4$61538f80$a401280a@.phx.gbl...
> Why does the replication process adds a rowguid, a
> uniqueidentifier, column in most of my tables? Is this a
> default/necessary behaviour? It is causing some conflicts,
> so could this be suppress? TIA