Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.

Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.sql

Invoking MigrationWizardConsole

I have a couple of questions concerning invoking the application MigrationWizardConsole.exe. Can this application be invoked directly or is invoked from the GUI app, MigrationWizard.exe? My other question is if the MigrationWizardConsole can be invoked directly what are the parameters?

Thanks

Ron Cotton

MigrationWizardConsole is used only by setup during upgrades. It should not be invoked by users.|||

Sasha,

Thanks for the information...Do you have any suggestions concerning automating the migration of SQL 2000 databases to SQL 2005?

Thanks

Ron C

Inventory Stock, Triggers vs Views/SP

Hello..

I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?

1st Design

PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price

PURCHASE TABLE
PurchaseID
Date
...etc...

PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...

and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE

2nd Design

PRODUCT TABLE
ItemID
ItemName
Price
...etc...

INVENTORY TABLE
ItemID
QtyBegin
...etc...

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...

and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ......

And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.

According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

Thanks a lot.Hi

I would suggest to have design 2 as my option. Performance without reliability is of no use. There is no meaning in giving a wrong information to the user.

In the first design, you are storing the stock of each item whenever there is a movement of the product, either inward or outward. As per your design, let us assume, initially you are having a product, Product A, with no stock. Now you make a purchase on 01/23/2007 for quantity 50. Now the QtyOnHand will have value 50. Suppose you make a sales on 01/25/2007 for 30, as per your design you will update the QtyonHand field, which will now become 20. Now, if I need to get the stock of Product A on 01/04/2007, I cannot use the value in the field QtyonHand, which will be wrong. In this case it will be 20. But actually it must be 50. So we need to calculate the stock by adding the inwards and deducting the outwards. Also if you allow backdate billing, stock updation will become a serious issue.

In the second design, I accept that the performance will be lesser than the first one. But by careful designing that can also be solved. For eg, having a seperate table which contains a date field, Product key and QtyonHand fields. For each purchase, sales and other product movement transactions, you can update this table for each day. There can be several other methods as well. If you come across please intimate me as well.

with regards

Abdur Raoof M|||For what it's worth...
No matter which solution you choose, don't forget to leave room to account for stock transactions that are not
related to sales and receipts, such as damage or shrink.
Invariably, Beginning inventory-sales+purchases<>actual ending inventory.|||@.Abdul Raoof

I very much agree that the 2nd one is far more reliable, but I am still wondering how much more do I have to pay for the reliability? I have done the 2nd one before, and the difference to load the 'item' table without calculating qty compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec.

And thanks for minding me the problem of the 1st design, never thought of that before.

@.RedNeckGeek
I think I have included Adjustment Table if that's what you mean?
Beginning inventory-sales+purchases-adjustment = actual ending inventory :)

Thank you Guys.

Anyone? Please... feel free to comment...

Invalid Object Name?

Hi

I am developing a windows application that connects to a sql 2000 server. I have created a stored procedure and I am trying to execute the stored procedure in the query analyzer.

Here is the code for the stored procedure

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

Select count([@.dlname].[Auditors working code])
from [@.dlname]
where [Auditors working code] = @.CB
GO

Here is the code that I am attempting to use in the query analyzer

Declare @.dlname as nvarchar(25)
set @.dlname = 'SWMC-OP-02-2005'
Declare @.CB as nvarchar(10)
set @.CB = 'CB'
Execute CountofComebacks @.dlname, @.CB

I get the following error in the query analyzer

Server: Msg 208, Level 16, State 1, Procedure CountofComebacks, Line 9
Invalid object name'@.dlname'.

Any assistance would be greatly appreciated.

thanks

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)
GO

If you want to know more about how this works google for Dynamic SQL. There are disadvantages in using this approach too. there are plenty of articles over the net that xplain about "Dynamic SQL".

|||

First let me say thank you for your response.

That being said I am still getting an error when attempting to execute the sp in the query analyzer.

Here is the code in the query analyzer

Declare @.dlname as nvarchar
Set @.dlname = 'SWMCOP022005'
Declare @.CB as nvarchar
Set @.CB = 'CB'
Declare @.NumberofCBs as integer
Execute CountofComebacks @.dlname, @.CB, @.NumberofCBs

Here is the sp

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10),
@.NumberofCBs as int
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)

return @.NumberofCBs
GO

and here is the error I receive in the query analyzer

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
The 'CountofComebacks' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Is this because of the syntax error on line 2?

thanks

|||

throw in an ISNULL function.

SET @.sql = 'Select ISNULL(count([' + @.dlname + '].[Auditors working code]),0) from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

sql

invalid object name..(my error message)

i'm working on an application using vs 2005, sql server2000, with c# asp.net

i can access many tables in my db that the dbo is the dbowner for them, but when i access few tables that the owner for them is dswebwork, i recieved an error says, invalid object name tbluser...which tbluser is table name...this is the error message in details....

Invalid object name 'tblUsers'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'tblUsers'.

Source Error:

Line 57: string passWord = txtPassword.Text;Line 58:Line 59: Users users = new Users(Constants.DB_CONNECTION,Line 60: userName, passWord);Line 61:

Source File:e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs Line:59

Stack Trace:

[SqlException (0x80131904): Invalid object name 'tblUsers'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52 System.Data.SqlClient.SqlDataReader.get_MetaData() +130 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +307 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +151 WebWorksBO.DBElements.BaseDataSQLClient.FillDataset(DataSet dsToFill) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\BaseDataSQLClient.cs:97 WebWorksBO.DBElements.dbUsers..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\dbUsers.cs:38 WebWorksBO.AppElements.Users..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\AppElements\Users.cs:370 LoginMaster.LoginUser() in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:59 LoginMaster.imgbtnOK_Click(Object sender, ImageClickEventArgs e) in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:46 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +102 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +141 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

so..i hope to help me...i need to deploy this project soon...

Append the owner to the table name, when you use it or change the owner to dbo

Select * from dswebwork.tblUsers

INVALID OBJECT NAME Error

Hi All,
I am using ASP and SQL Server 2000 for an application. The problem that i am facing is that I get INVALID OBJECT NAME error. to be precise...
"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name '<table name>'"
the application runs for some days without any issues then suddenly it starts showing the error. When the error starts coming.. it appears in all the screens that were running without any issues just moments back. The only solution which works is to log o
ut of the application and login again.
Please suggest what could be the cause of the error...
If you need any more information just let me know...
Regards
Jaspreet
When you get the errors, check the connections to see what
database context is being used by the web clients. You can
capture this information in many ways such as with Profiler,
sp_who, sp_who2, querying sysprocesses, checking the Current
Activity node in Enterprise Manager, etc. It could be that
the database context is changing. You could also try fully
qualifying the object with database.owner.object in whatever
code you are using.
-Sue
On Sat, 5 Jun 2004 05:26:02 -0700, "Jaspreet"
<jaspreets@.ggn.hcltech.com> wrote:

>Hi All,
>I am using ASP and SQL Server 2000 for an application. The problem that i am facing is that I get INVALID OBJECT NAME error. to be precise...
>"Error Type:
>Microsoft OLE DB Provider for SQL Server (0x80040E37)
>Invalid object name '<table name>'"
>the application runs for some days without any issues then suddenly it starts showing the error. When the error starts coming.. it appears in all the screens that were running without any issues just moments back. The only solution which works is to log
out of the application and login again.
>Please suggest what could be the cause of the error...
>If you need any more information just let me know...
>Regards
>Jaspreet
|||Sue,
Thanx for your suggestion... I'll definitely check the context next time
the error occurs.
In my code, I have used database.owner.object in all the asp pages. Can
you give me an idea why this error could come.. so that in future I'd
take care of this issue.
Regards
Jaspreet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||It's hard to say without knowing your app but you may want
to check the default database for the user this happens
with. Also If you change database context in any of the
stored procedures, queries, etc (e.g. with USE
SomeDatabase).
-Sue
On Tue, 08 Jun 2004 21:57:04 -0700, Jaspreet Sethi
<jaspreets@.ggn.hcltech.com> wrote:

>Sue,
>Thanx for your suggestion... I'll definitely check the context next time
>the error occurs.
>In my code, I have used database.owner.object in all the asp pages. Can
>you give me an idea why this error could come.. so that in future I'd
>take care of this issue.
>Regards
>Jaspreet
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!

INVALID OBJECT NAME Error

Hi All,
I am using ASP and SQL Server 2000 for an application. The problem that i am
facing is that I get INVALID OBJECT NAME error. to be precise...
"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name '<table name>'"
the application runs for some days without any issues then suddenly it start
s showing the error. When the error starts coming.. it appears in all the sc
reens that were running without any issues just moments back. The only solut
ion which works is to log o
ut of the application and login again.
Please suggest what could be the cause of the error...
If you need any more information just let me know...
Regards
JaspreetWhen you get the errors, check the connections to see what
database context is being used by the web clients. You can
capture this information in many ways such as with Profiler,
sp_who, sp_who2, querying sysprocesses, checking the Current
Activity node in Enterprise Manager, etc. It could be that
the database context is changing. You could also try fully
qualifying the object with database.owner.object in whatever
code you are using.
-Sue
On Sat, 5 Jun 2004 05:26:02 -0700, "Jaspreet"
<jaspreets@.ggn.hcltech.com> wrote:

>Hi All,
>I am using ASP and SQL Server 2000 for an application. The problem that i a
m facing is that I get INVALID OBJECT NAME error. to be precise...
>"Error Type:
>Microsoft OLE DB Provider for SQL Server (0x80040E37)
>Invalid object name '<table name>'"
>the application runs for some days without any issues then suddenly it starts showi
ng the error. When the error starts coming.. it appears in all the screens that were
running without any issues just moments back. The only solution which works is to l
og
out of the application and login again.
>Please suggest what could be the cause of the error...
>If you need any more information just let me know...
>Regards
>Jaspreet|||Sue,
Thanx for your suggestion... I'll definitely check the context next time
the error occurs.
In my code, I have used database.owner.object in all the asp pages. Can
you give me an idea why this error could come.. so that in future I'd
take care of this issue.
Regards
Jaspreet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||It's hard to say without knowing your app but you may want
to check the default database for the user this happens
with. Also If you change database context in any of the
stored procedures, queries, etc (e.g. with USE
SomeDatabase).
-Sue
On Tue, 08 Jun 2004 21:57:04 -0700, Jaspreet Sethi
<jaspreets@.ggn.hcltech.com> wrote:

>Sue,
>Thanx for your suggestion... I'll definitely check the context next time
>the error occurs.
>In my code, I have used database.owner.object in all the asp pages. Can
>you give me an idea why this error could come.. so that in future I'd
>take care of this issue.
>Regards
>Jaspreet
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!

Invalid Object Name

Hi,
My application uses VB6 and Sql-Server 2000.
I cant understand why the error
INVALID OBJECT NAME Run-time error '-2147217865 (80040e37)'
appears only sometimes and not always. For example, a select instruction is executed inside a loop without any problem 1 thousand times, then when I try to execute it 1 thousand times and one, it fails.


Have you got any suggestions?

Ive read a lot of posts on Internet Forums, without finding any solutions.

In the mail I attach, you can see that the application stops at 79%, after having executed many times the select instruction without any problem!

Thank you very much for your help!

Bye,

EmanuelaWhat attachment?

Just post the code here...but my guess is you're building dynamic sql|||Without seeing it I too am guessing dynamic SQL, but I reckon the problem lies in the length/size of a variable you've declared. Varchar(50) for example|||Here is the code:

Dim Con As ADODB.Connection

Set Con = New ADODB.Connection
Con.ConnectionString = "driver={SQL Server};server=OIVN303;uid=NIS_adm;pwd=nisdb04;dat abase=ZET"
Con.ConnectionTimeout = 3000
Con.Open

Set CreaConnessioneDB = Con

Dim Rs3 As ADODB.Recordset

Set Rs3 = New ADODB.Recordset
Rs3.CursorType = adOpenKeyset
Rs3.LockType = adLockOptimistic
Rs3.ActiveConnection = Con

Sql3 = "SELECT * T_NUM_E1_OMC_PER_OFFICE
Rs3.Open Sql3
If Not Rs3.EOF Then
Num_Tot_TRX = Rs3(0)
Num_Min_E1_GSM = Rs3(1)
End If
Rs3.Close

Set rs3 = nothing

...

Sorry, no attachment.

Thanks very much to everybody can help me!,
Bye,
Emanuela|||Sql3 = "SELECT * T_NUM_E1_OMC_PER_OFFICE

SURELY this is wrong.
No FROM and no closing quote (")?!|||Ok, I made a mistake during copy and paste:
Obviously the correct statement is:
Sql3 = "SELECT * FROM T_NUM_E1_OMC_PER_OFFICE"

A part from this, can you help me?|||That's it?

No Predicates?

Why not use Stored Procedures in any case?|||CreaConnessioneDB

Another typo?

Fancy pasting this loop you speak of that's causing the problem?sql

Invalid Locator de-referenced

In our application we use three database(access, sql server and oracle) to store configurations. We use a program to copy the configuration from one database to another. We use ODBC and MFC recordsets to do the copy. We recently updated the application and changed the the table schema's. Before we had two tables which each had a schema of one long as key and one image field. Now we have one table with one long as key and three image fields. Now when we copy to sql server from access we get the error " Invalid locator de-refrenced" when we have large data (about 1 MB) in one image field. Before the change in schema's we did not have this problem with an image field with a large amout of data. We are using the sql server odbc driver.

Jim,

I believe this error message is raised as a result of writetext request to the server. Is it possible for you to try SQL Native Client ODBC driver. This is the most up to date SQL Server ODBC driver. You can download Microsoft SQL Server Native Client from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Thanks

Waseem

|||

I am using the SQL server ODBC driver 2000.85.1117.00 and also got the Invalid Locator de-referenced when I copied data containing the big image and text. I used the SQLPrepare, SQLBinderParam, SQLParam and SQLPutData. The error I got likes:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Our application is Unicode based and we used the SQL_C_WCHAR and SQL_C_BINARY in bind parameter but the sql type in the server is text and image.

Any idea?

I downloaded the SQL Native Client, it is even worse since I got an exception for un-recognized type.

Appreicate any helps

workingonsql

|||

having the same problem found a solution said "passing SQL_LEN_DATA_AT_EXEC(250000) as the
nullindicator to SQLBindParameter and then put the data in 250000 byte chunks".

but not sure what its means, do you know?

Invalid Locator de-referenced

In our application we use three database(access, sql server and oracle) to store configurations. We use a program to copy the configuration from one database to another. We use ODBC and MFC recordsets to do the copy. We recently updated the application and changed the the table schema's. Before we had two tables which each had a schema of one long as key and one image field. Now we have one table with one long as key and three image fields. Now when we copy to sql server from access we get the error " Invalid locator de-refrenced" when we have large data (about 1 MB) in one image field. Before the change in schema's we did not have this problem with an image field with a large amout of data. We are using the sql server odbc driver.

Jim,

I believe this error message is raised as a result of writetext request to the server. Is it possible for you to try SQL Native Client ODBC driver. This is the most up to date SQL Server ODBC driver. You can download Microsoft SQL Server Native Client from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Thanks

Waseem

|||

I am using the SQL server ODBC driver 2000.85.1117.00 and also got the Invalid Locator de-referenced when I copied data containing the big image and text. I used the SQLPrepare, SQLBinderParam, SQLParam and SQLPutData. The error I got likes:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Our application is Unicode based and we used the SQL_C_WCHAR and SQL_C_BINARY in bind parameter but the sql type in the server is text and image.

Any idea?

I downloaded the SQL Native Client, it is even worse since I got an exception for un-recognized type.

Appreicate any helps

workingonsql

|||

having the same problem found a solution said "passing SQL_LEN_DATA_AT_EXEC(250000) as the
nullindicator to SQLBindParameter and then put the data in 250000 byte chunks".

but not sure what its means, do you know?

Invalid Locator de-referenced

In our application we use three database(access, sql server and oracle) to store configurations. We use a program to copy the configuration from one database to another. We use ODBC and MFC recordsets to do the copy. We recently updated the application and changed the the table schema's. Before we had two tables which each had a schema of one long as key and one image field. Now we have one table with one long as key and three image fields. Now when we copy to sql server from access we get the error " Invalid locator de-refrenced" when we have large data (about 1 MB) in one image field. Before the change in schema's we did not have this problem with an image field with a large amout of data. We are using the sql server odbc driver.

Jim,

I believe this error message is raised as a result of writetext request to the server. Is it possible for you to try SQL Native Client ODBC driver. This is the most up to date SQL Server ODBC driver. You can download Microsoft SQL Server Native Client from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Thanks

Waseem

|||

I am using the SQL server ODBC driver 2000.85.1117.00 and also got the Invalid Locator de-referenced when I copied data containing the big image and text. I used the SQLPrepare, SQLBinderParam, SQLParam and SQLPutData. The error I got likes:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Our application is Unicode based and we used the SQL_C_WCHAR and SQL_C_BINARY in bind parameter but the sql type in the server is text and image.

Any idea?

I downloaded the SQL Native Client, it is even worse since I got an exception for un-recognized type.

Appreicate any helps

workingonsql

|||

having the same problem found a solution said "passing SQL_LEN_DATA_AT_EXEC(250000) as the
nullindicator to SQLBindParameter and then put the data in 250000 byte chunks".

but not sure what its means, do you know?

sql

Invalid IP address exception

I'm attempting to deploy an application that uses SQL Server 2005 Express as a back end. I installed the application on 6 or 7 machines, and on two of those machines I'm getting an error message I just don't understand. When I try to connect to the database, I'm getting "System Exception: An invalid IP address was specified.". I'm using the <machine name>\<instance name> to log into the server and I can ping the associated IP address. What's even more confusing is that I can log onto this instance from any of the other machines with no problem. I'm assuming the problem isn't with the SQL instance but a setting I'm missing on the machine, but for the life of me I can't find anything. Any help would be greatly appreciated.

Chris

Can you check this blog and see if there is any alias defined on the specific machine?

http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx

Invalid Export DLL or export format

I have read a lot about this error and all the suggestions put in place in order to have it working to no avail.We have a Visual Basic 6 application and we are able to export reports to Word,PDF or EXCEl through Report viewer, however for sometime we have had problems exporting to PDF on Terminal Server 2000, now that we upgraded to TS 2003 none of the exports work,exporting to pdf or excel gives an error as discribed in the subject and to word just doesn't happen, no error is thrown.

I have made sure I have all the appropriate dll in c\windows\crystal. the only thing that strikes me as strange is, I have found a post informing we should have all of those dll files under c\program files\common files\crystal decisions\2.0\bin and there is the need to have files in MergeModules as well, however those folders do not exist on the TS where Crystal Reports 8.5 was installed.

I have crystal installed on my own pc and i have all of those folders but since exporting locally on our pcs is not the problem but on the Terminal Server I can't connect if that could be the problem, but person that I helping with this refuses to create those folders because he thinks they are not needed.

Would somebody know are those folders that are getting created when Crystal is istalled and if i should just create them myself.

Hope somebody can help because i am really stuck.

ThanksYou need to create Setup from the Package and Deployment wiward and add all the necessary files/dlls so that they will be copied to the corresponding directories in the system where set up is run

Invalid cursor state

[Microsoft][ODBC SQL Server Driver]Invalid cursor state
Having a few of these reported in our application logs as it calls a sql
server stored proc stemming from ODBC that references SQLFetch
The stored proc does not use a cursorPlease exhibit some patience and don't post the same question multiple
times.
What on earth does your stored procedure do? Should we guess? Do you have
any problems when you call the stored procedure directly (without using the
app)? How about if you use a different connection method (e.g. OLEDB
instead of ODBC)?
"Hassan" <hassan@.test.com> wrote in message
news:OUHYKlXUIHA.5160@.TK2MSFTNGP05.phx.gbl...
> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor|||Hi Hassan
My guess is that it is a DTC issue, but as Aaron points out all three of the
posts you have made since April 2007 for this give very little information.
John
"Hassan" wrote:
> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor
>

Invalid cursor state

[Microsoft][ODBC SQL Server Driver]Invalid cursor state
Having a few of these reported in our application logs as it calls a sql
server stored proc stemming from ODBC that references SQLFetch
The stored proc does not use a cursor
Please exhibit some patience and don't post the same question multiple
times.
What on earth does your stored procedure do? Should we guess? Do you have
any problems when you call the stored procedure directly (without using the
app)? How about if you use a different connection method (e.g. OLEDB
instead of ODBC)?
"Hassan" <hassan@.test.com> wrote in message
news:OUHYKlXUIHA.5160@.TK2MSFTNGP05.phx.gbl...
> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor
|||Hi Hassan
My guess is that it is a DTC issue, but as Aaron points out all three of the
posts you have made since April 2007 for this give very little information.
John
"Hassan" wrote:

> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor
>

Invalid cast when trying to use SQLXMLBulkload

I am trying to move data between two servers through my application by outputting the data as XML and then importing it later. I am using .WriteXML and .WriteXMLSchema in my .NET application to create the files, but I get "invalid cast Exception" when trying to BulkLoad them in using SQLXMLBulkLoad4. I even adjust the XSD file to add the sqlatatype in there for the DateTime fields. Is there another field I am breaking on. I looked at the docs and I thinbk my GUIDs are OK since they do not have the {} on them.

Example file: XSD file

<?xml version="1.0" standalone="yes"?>
<xschema id="STG_Additional_Labs" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
<xs:element name="STG_Additional_Labs" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xsequence>
<xs:element name="FacilityID" type="xs:int" minOccurs="0" />
<xs:element name="Comment" type="xstring" minOccurs="0" />
<xs:element name="Deleted" type="xs:boolean" minOccurs="0" />
<xs:element name="Lab_Date" type="xsateTime" sqlatatype="dateTime" minOccurs="0" />
<xs:element name="Lab_Name" type="xstring" minOccurs="0" />
<xs:element name="Lab_Time" type="xstring" minOccurs="0" />
<xs:element name="Lab_Value" type="xstring" minOccurs="0" />
<xs:element name="PIID" type="xs:int" minOccurs="0" />
<xs:element name="rowguid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="RowIDGuid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="SaveDateTime" type="xsateTime" sqlatatype="dateTime" minOccurs="0" />
<xs:element name="VersionGuid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="PatientID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>

Example file: XML file

<?xml version="1.0" standalone="yes"?>
<STG_Additional_Labs>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-30T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Subst</Lab_Name>
<Lab_Value>trace</Lab_Value>
<PIID>14</PIID>
<rowguid>03cc9264-8829-464f-b01d-2b18ee4ccdfb</rowguid>
<RowIDGuid>ff9e4e59-6716-46d4-bfcb-61777ed8cf5d</RowIDGuid>
<SaveDateTime>2007-05-30T10:18:24.52-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-19T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Sub</Lab_Name>
<Lab_Value>&lt;0.25/ neg</Lab_Value>
<PIID>14</PIID>
<rowguid>876ca5f9-5c0f-4a74-bf03-2e86260ca2a1</rowguid>
<RowIDGuid>45c45895-8008-40ed-a779-c478d476c15b</RowIDGuid>
<SaveDateTime>2007-05-19T10:13:53.857-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
</STG_Additional_Labs>Ack! Sorry about the emoticons!|||Actually, further work on this problem seemed to reveal that the Cast error was actually due to the connection string in my application being wrong (although it is used for all the other connections in the application). The error now coming back is this:

<?xml version="1.0"?><Result State="FAILED"><Error><HResult>0x80004005</HResult><Description>
<![CDATA[Reference to undeclared namespace prefix: 'sql'.
]]></Description><Source>Schema mapping</Source><Type>FATAL</Type></Error></Result>

What would be the proper way to specify the SQL namespace in the top of the file?|||I have essentially resolved my problem by not trying to output the XML data to then read it in later. I am now using the SQLBulkLoad classes to pump the data from a DataTable to the destination SQL server. It would be nice however to have a method in the SQLReader/Writer that can write out a SQL friendly XSD file, so that it can be used later by the SQLXMLBulkLoad class.

Invalid buffer received from client.

I'm having seriouos trouble after installing SP4. I have an application writ
ten in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after upgrading
to SP4, the application totally crashed while trying to access the database.
It gives an error saying a
severe error has occued on the server. The
event log on the server has the following logged:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
For more information, see Help and Support Center at http://go.microsoft.com/fwlin
k/events.asp.
I'm sure it's SP4 because after I reinstalled SQL Server with SP3, everythin
g worked fine again. The server is running Windows 2003 SP1.
Anyone else having this problem or have a solution?Sp4 reduces the network packet size to 32767. Consider adjusting your app to
avoid the error.
<quote>
In SP4, the maximum value for the network packet size option (set using
sp_configure) is 32767. This is slightly less than half the previous maximum
of 65536. During upgrade, existing values larger than 32767 will
automatically be adjusted to 32767. If a script attempts to use sp_configure
to set a value larger than 32767 but less than or equal to 65536, the value
will also be set to 32767. Setting the network packet size to a value larger
than 65536 results in an error.
</quote>
There's used to be a bug even when the network packet size was set to
65535...
http://support.microsoft.com/kb/875411
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42813312@.duster.adelaide.on.net...
> I'm having seriouos trouble after installing SP4. I have an application
> written in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after
> upgrading to SP4, the application totally crashed while trying to access
> the database. It gives an error saying a severe error has occued on the
> server. The event log on the server has the following logged:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> I'm sure it's SP4 because after I reinstalled SQL Server with SP3,
> everything worked fine again. The server is running Windows 2003 SP1.
> Anyone else having this problem or have a solution?|||Thanks oj...
Do you have an example of how to change the packet size to 32767 - is it thr
ough the connection string? At the moment, it has packet size set to 4096.
oj wrote:
> Sp4 reduces the network packet size to 32767. Consider adjusting your app
to
> avoid the error.
> <quote>
> In SP4, the maximum value for the network packet size option (set using
> sp_configure) is 32767. This is slightly less than half the previous maxim
um
> of 65536. During upgrade, existing values larger than 32767 will
> automatically be adjusted to 32767. If a script attempts to use sp_configu
re
> to set a value larger than 32767 but less than or equal to 65536, the valu
e
> will also be set to 32767. Setting the network packet size to a value larg
er
> than 65536 results in an error.
> </quote>
> There's used to be a bug even when the network packet size was set to
> 65535...
> http://support.microsoft.com/kb/875411
>|||Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
According to the article, you might want to limit each of your insert batch
to 32767 rows.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42817d1b$1@.duster.adelaide.on.net...
> Thanks oj...
> Do you have an example of how to change the packet size to 32767 - is it
> through the connection string? At the moment, it has packet size set to
> 4096.
> oj wrote:|||I tried what you suggested but with no vail.
Here's my connection string:
UID=user;PWD=password;Initial Catalog=DatabaseName;Data Source=ServerName;Pa
cket Size=4096;
I tried the packet size 2048, 1024, 768 but they all return the same error.
Any help is greatly appreciated!
oj wrote:
> Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
> According to the article, you might want to limit each of your insert batc
h
> to 32767 rows.
>|||Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
and be sure the length is not exceeding the allowable for the specified
type.
If you post your code (.Net call + sql), someone will take a closer look.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:428293aa$1@.duster.adelaide.on.net...
>I tried what you suggested but with no vail.
> Here's my connection string:
> UID=user;PWD=password;Initial Catalog=DatabaseName;Data
> Source=ServerName;Packet Size=4096;
> I tried the packet size 2048, 1024, 768 but they all return the same
> error.
> Any help is greatly appreciated!
> oj wrote:|||Thanks oj... I changed my code as you suggested and it is now working! :)
But now it runs extremely slow and frequently timeout. Any solution to this
problem as well?
oj wrote:
> Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
> and be sure the length is not exceeding the allowable for the specified
> type.
> If you post your code (.Net call + sql), someone will take a closer look.
>|||Double check your sql to make sure proper indexes installed. Check out these
to see if they help:
http://support.microsoft.com/?id=308049
http://support.microsoft.com/kb/224587/
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282bc9e@.duster.adelaide.on.net...
> Thanks oj... I changed my code as you suggested and it is now working! :)
> But now it runs extremely slow and frequently timeout. Any solution to
> this problem as well?
>
> oj wrote:|||Thanks oj... It's working fine now...
The problem was caused by creating an SQL parameter using DbType instead of
SqlDbType. I was trying to create a generic data provider that connects to b
oth Oracle and SQL Server. Now I think I have to map the individual DbType t
o SqlDbType.
Anyway, thanks for your help!! Much appreciated!
oj wrote:
> Double check your sql to make sure proper indexes installed. Check out the
se
> to see if they help:
> http://support.microsoft.com/?id=308049
> http://support.microsoft.com/kb/224587/
>|||You're very welcome.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282ce51@.duster.adelaide.on.net...
> Thanks oj... It's working fine now...
> The problem was caused by creating an SQL parameter using DbType instead
> of SqlDbType. I was trying to create a generic data provider that connects
> to both Oracle and SQL Server. Now I think I have to map the individual
> DbType to SqlDbType.
> Anyway, thanks for your help!! Much appreciated!
> oj wrote:

Invalid attribute/identifier error

HI ,
My application is working fine with SQL Server 2000 ; but when I
change the back end to Oracle 9i it gives an error "Invalid
attribute/identifier" .
Any help would be appreciated .
Thanks ,
Sujay
Hi
SQL Server and Oracle use different dialects of SQL so it is highly unlikely
that the application will work without significant change, even if you have
used ANSI standard SQL the features may not always be fully supported by
either system.
You do not say how far you the application has got, if it is not connecting
then the connection string may be incorrect, if you can track it down to a
specific statement, then run the query in SQL*Plus to see what happens.
John
<ghosh.sujay@.gmail.com> wrote in message
news:1110899405.355947.41090@.f14g2000cwb.googlegro ups.com...
> HI ,
> My application is working fine with SQL Server 2000 ; but when I
> change the back end to Oracle 9i it gives an error "Invalid
> attribute/identifier" .
> Any help would be appreciated .
> Thanks ,
> Sujay
>

Invalid attribute/identifier error

HI ,
My application runs fine when I am using SQL Server 2000 as its db, but
it gives a message "Invalid attribute/identifier error" when the db is
Oracle .
Any help in this matter would be appreciated .
Thanks ,
Sujay
Sujay Ghosh
Bangalore, INDIA
Email : sujayg_mfc@.yahoo.com
Mobile : 9845467789
Could you provide more detail? Like when the error message happens.
"Sujay Ghosh" <sujayg_mfc@.yahoo.com> wrote in message
news:775763E3-2218-4754-88C6-384CC144AA80@.microsoft.com...
> HI ,
> My application runs fine when I am using SQL Server 2000 as its db,
but
> it gives a message "Invalid attribute/identifier error" when the db is
> Oracle .
> Any help in this matter would be appreciated .
> Thanks ,
> Sujay
> --
> Sujay Ghosh
> Bangalore, INDIA
> Email : sujayg_mfc@.yahoo.com
> Mobile : 9845467789
>
|||Hi ,
Fixed the problem , actually some columns were missing in the referred
procedure .
Thanks ,
Sujay
"Bei Zhu [MSFT]" wrote:

> Could you provide more detail? Like when the error message happens.
> "Sujay Ghosh" <sujayg_mfc@.yahoo.com> wrote in message
> news:775763E3-2218-4754-88C6-384CC144AA80@.microsoft.com...
> but
>
>