Once again, no records returned....

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?

------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@.unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @.unitID
GO
-------------------------------Are you sure that there are records in all 5 tables with the same unitid ? Using inner joins, you are limiting the result set to that scenario.

Jeff|||You are missing the OUTPUT keyword which is required for stored Procs returning none Numeric value. As per the previous post you can only use INNER JOIN if both tables are equal and the ANSI SQL OUTER JOIN limit is four because OUTER JOIN has default NULL condition but that may not work for HTTP applications because HTTP aplications are Stateless. Check out the MSDN link below.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp Hope this helps.

Kind regards,
Gift Peddie|||Found the problem, 1 of the tables didn't have a test value entered in for the unitID. Thanks for the help!sql

OLEDB performance and Script task

Hi enquiring whether is it more efficient to use OLEDB destination to update and extract all records from table A to tabl e B or use script task? And whether OleDB destination perform row by row update? Finally will data integrity be affected if i run delete, update and insert operations in the same data flow but extracting different sets of data each time from the 2 tables?

Unfortunately the answers to your first question is, it depends... Try testing them out for yourself as the speeds will vary by system and by the transforms that you are trying to push the data through.

OleDB destination will NOT preform a row by row UPDATE. The destination transformation is for inserting new rows, to do the update you will use the OLE DB Command Transformation.

Your data integrity should be fine for doing delete update and insert operations in the same data flow. Of course, this will vary depending on what transformations you do, when, how you get your data, etc... Try watching the Kimball video which is linked to on the front page for a good overview of the various ways of handling update / insert. Also, look at the thread for "check to see if a row exists, if so update, else insert" (also stickied on the front page).

|||

Thanks for the quick reply, but using OLE DB Command from what i understand is too expensive, so comparing OLE DB Command and Script task, script task would be a better choice in this aspect, can i say that at first glance?

|||If you have a large number of updates, you should write those updates to a temporary table and then once the data flow is done, issue an Execute SQL task to perform a batch update. That's the *best* option.|||

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

|||

garynkill23 wrote:

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

Yes

OLEDB Error when using Visual FoxPro Driver

Hi all,

I created a package that extracts records from a .DBF file into an SQL Server database. I installed a Visual FoxPro plugin (vfpoledb). When I run the package from my PC, it runs smoothly. But, when I try to run the same package in another PC, it displays a DTS_E_OLEDBERROR "Class not registered" error. Anyone has an idea about what this means? thanks in advance.
Is the OLE DB driver correctly installed in the other PC? If the package runs fine in one PC, then I doubt is a SSIS issue.|||Yes, the OLEDB is installed properly in the other PC. The driver is also installed in the Database Server. Still the problem occurs. I also tried placing and running the SSIS package in the database server but the same problem is encountered. I'm running out of ideas as to what might have caused the problem. What might be the cause(s) of the problem?

Thanks.
|||

Could you try using this provider with some other client?

Thanks,

Bob

OLEDB Error when using Visual FoxPro Driver

Hi all,

I created a package that extracts records from a .DBF file into an SQL Server database. I installed a Visual FoxPro plugin (vfpoledb). When I run the package from my PC, it runs smoothly. But, when I try to run the same package in another PC, it displays a DTS_E_OLEDBERROR "Class not registered" error. Anyone has an idea about what this means? thanks in advance.
Is the OLE DB driver correctly installed in the other PC? If the package runs fine in one PC, then I doubt is a SSIS issue.|||Yes, the OLEDB is installed properly in the other PC. The driver is also installed in the Database Server. Still the problem occurs. I also tried placing and running the SSIS package in the database server but the same problem is encountered. I'm running out of ideas as to what might have caused the problem. What might be the cause(s) of the problem?

Thanks.
|||

Could you try using this provider with some other client?

Thanks,

Bob

OLE Db Destination task in ASYNC_NETWORK_IO wait state

I have established an SSIS dataflow that should move 20,000 records from a source table to a target table. Only new records should be added to the target table – existing records should be ignored. The problem I am reporting occurs when the target table is initially empty (there are no existing records, so everything should come over). I am using the Slowly Changing Dimension task to limit inserts to new records. The tasks in my data flow are:

OLE DB Source è Slowly Changing Dimension è Derived Column è OLE DB Destination

The problem is that the data flow locks up before completing. This occurs if the Data Access Mode of the OLE DB Destination task is “Table or view – fast load”. If I look at the Activity Monitor in SQL Server Management Studio once it has locked up, I can see two processes in the suspended state. Based on the queries, I can identify one of the processes as performing the lookup for the Slowly Changing Dimension task. The other represents the BULK INSERT associated with the OLE DB Destination task.

The Slowly Changing Dimension task is locked waiting for the OLE DB Destination task as determined by looking at the “Blocked By” column in the Activity Monitor. Its wait state is LCK_M_S. The OLE DB Destination task has a wait type of ASYNC_NETWORK_IO.

QUESTIONS: Why is the BULK INSERT in the OLE DB Destination task waiting? What does ASYNC_NETWORK_IO wait state indicate? How do I prevent this from happening?

I am running the SSIS package in SQL Business Intelligence Studio on a workstation against a SQL Server 2005 server. The same situation is seen when I run the package directly on the SQL Server 2005 systems in SQL Business Intelligence Studio rather than on my workstation.

Turn off the table lock option for the OLE-DB Destination. Leaving this on means that the second buffer of data is blocked, specifically the lookup, as when the first passed to the destination the lock was acquired, and now prevents any more lookups happening.

You could also investigate adding a NOLOCK hit to the lookup SQL statement.

|||I had already tried turning off the table lock option, though I failed to mention it. Adding the NOLOCK option onto the query within the Slowly Changing Dimension task did indeed allow the data flow to complete.

Thank you.

Old, deleted Identity values re-used?

If I have an Identity-declared column, the table has lots of rows and I start deleteting records - will any old, deleted identities be re-used (without an explicit re-seed) ?
(I'm talking about numerical Identities, not these GUID things)No. Not without manipulating the IDENTITY column. The exception being that if you TRUNCATE the table, then the seed will reset to the floor value.

Regards,

hmscott|||yeah,it can be used
need to set identity off
insert statement
reset identity on