Problems Linking to Oracle Server

Has anyone successfully linked to an Linux Oracle server running 9i? We are unable to get the link working.....have installe dvarious drivers and still get connection refused. Any thoughts?

If this is a Linked Server issue I would try the SQL Server Engine forum.

Donald

Problems linking Access to SQL replicated tables

Hello!
I was wondering if anyone could help me.
I am running an Access application that uses linked tables to connect to a SQL server.
Ever since I turned on replication on those tables, I cannot add records to the table using regular Access forms (and, for that matter, I cannot do it by just double-clicking on the table from Access and trying to enter records).

Any help will be greatly appreciated!!!!!Have you also refreshed those links i.e.(deleted the links and recreated them in Access) since you "turned on replication on those tables" (or not)?|||Yes, I have deleted the link and recreated it after turning on replication, but I still get the same error every time I add a record.|||You are able to add a row via query analyzer or EM, and the table is not a read only subscribed table you are attempting to add rows to? (just asking the obvious things)
If all that is in order you might check to see if the generated link schemas include columns that the system automatically populates with data. If there are some (replication related columns) or others such as identity, GUID, timestamp, etc. columns (that are automatically populated), try removing them from the Access link schemas (not the base tables) then recreate your link(s) and attempt a test row insert. If that does not work try creating a view (test insertion in QA / EM), then create a linked table to the view and test its behavior.sql

Problems installing SQL Server 2005 Mobile on device

I have a Fujitsu-Siemens LOOX 520 running WM 5 version 5.1.70 on which I am trying to create a Server 2005 Mobile database.

I have installed the following files on the Pocket PC after a hard reset to make sure the unit was in its vanilla state:

NETCFv2.wm.armv4i.cab

sqlce30.dev.ENU.ppc.wce5.armv4i.CAB

sqlce30.ppc.wce5.armv4i.CAB

sqlce30.repl.ppc.wce5.armv4i.CAB

All the files appeared to succesfully install. Connecting to the device in VS2005 works with no problems. Creating a small test app in VB.NET and running it on the device works no problems, so the device is satisfactorily connected to VS2005.

My problem is this:

When I try to create an SQL Server 2005 Mobile database on the device using Add Connection under the Data Sources window in Visual Studio 2005 Professional (RTM version), I get the following error:

"Microsoft SQL Server 2005 Mobile Edition and/or .NET Compact Framework v2.0 is not properly installed on your mobile device or the device is not responding."

Trying the same thing in Microsoft SQL Server Management Studio gives the same error, but a little bit of extra information:

"Microsoft SQL Server 2005 Mobile Edition and/or .NET Compact Framework v2.0 is not properly installed on your mobile device or the device is not responding. (SQL Server Mobile Edition ADO.NET Data Provider)"

As far as I can see, everything IS installed OK, so why is VS2005 not working?

Thanks for any help.

JB

A few others have reported this as well - creating the SQL Mobile database on device, directly from VS2005 seems a little flaky right now. My advice would be to create the SQL Mobile database on your development (desktop) machine and then add it to your smart device project as "Content" for deployment along with your CF app. You *can* create the database directly on device from SQL Server 2005 Management Studio if you have SQL Server 2005 installed - this does work well.

- Darren Shaffer

|||

Darren
Thanks very much indeed for your reply.
I discovered that installing a different set of files on the device actually solved the problem - specifically I installed the CE equivalents of the files that I listed previously. This seemed to sort the problem out - certainly installing those versions allowed the creation of an SQL mobile database on the device and locally on the desktop machine without any problems.
Incidentally, being something of a newbie to all this, I share the frustrations of Dave_AC with regards to the loss of simplicity of creating a database on the Desktop and being able to easily transfer it to the device without having to use SQL Server 2005 etc etc which I am completely new to. ADO.NET and Active Sync seemed to be a nice straightforward, relatively simple way of managing a single user database from desktop to device. Things seems a LOT more complicated now! I appreciate there is a tremendous amount of functionality and power that was not there before, but losing that simplicity seems to be a somewhat retrograde step in some ways.

(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163919&SiteID=1)
In that post, I see that you mentioned a webcast which you presented - -"I'm not entirely clear on why you need this - I build a *LOT* of CF apps for clients that include SQL CE and SQL Mobile and have never had a need to work with the .sdf file from a desktop app. If you are trying to find an effective way to load the initial database with data, my recent webcast shows how to do that using nothing but SQL Server 2005 Management Studio."
Where can we access that webcast? And are there other webcasts available that will help new users to get their heads round all this?

Thanks again for your help.

JB

|||

Darren

I stress that I truly am a Newbie in the midst of some serious Code Gods, so if I sound like I don't have a clue what I'm talking about - it's because I don't!

Cheers!

JB

|||

Here is the link to the on-demand webcast:

http://www.msreadiness.com/ws_abstract.asp?eid=15003229

I guess I still struggle to understand the need to create a SQL Mobile database from desktop .NET code when it can be created:

1. using query analyzer on device

2. on the desktop from VS2005

3. on the desktop from SS2005

4. in code on device

5. as a byproduct of a merge replication with AddOption.CreateDatabase enabled

Maybe I've been doing this for so long that I'm not seeing what it looks like to someone coming into it fresh. In any case, all of the CF MVPs are encouraging Microsoft to expand the reach of SQL Mobile. It would make a fabulous ClickOnce database option for Smart Clients.

-Darren

|||

Thanks for the link, Darren.

I'm not actually suggesting that I want to create an SQL mobile database from desktop .NET code - I understand how to create mobile databases via VS2005 or SS2005 which either reside on the desktop or on the device. That works, and works very well.

The difficulty for me comes in creating a simple single user database on the desktop which can be accessed by desktop code - for example an Access database like I used when programming with VS2003 VB.NET - and then having a simple way of synchronising the database on the mobile device with that database, purely for single user capability. In fact, for simple applications, you don't even want to be able to sync the database - you just want to be able to easily move the database back and forward from the desktop to the device, and be able to access that database from both locations ie. do some work on database on PC, then copy over to device and use there with no requirement to take data back from the device to the PC - really really simple. I write apps in which I actually don't WANT the user to be able to modify the desktop database from the device database. All the editing is done on the PC version, and then just copied to the devices for use in the field. Its a simple one-way process.

Previously I would have used RAPI and Active sync to move the database to the device with conversion being done automatically.

So in the past, you could make an Access database, then copy it over to the PPC and use the IntheHand wrapper to access the converted file on the pocket PC - and I'm sure you are aware of all this! All nice and straightforward - but with the new WM5 stuff and with SQL Server 2005 mobile, and SQL Server 2005, it seems that you can't do that as easily. You have to use replication, configure IIS for publication of data, and get into a whole load of stuff to do something which in the past was reasonably simple. Or do you? That's what I am trying to discover...

It may be that I am completely missing the point.. Remember, I've only had the VS2005 stuff for about a week now, and Christmas has inconveniently imposed itself on my programming time - and there's a lot to learn!

I'm not knocking all the SQL Mobile stuff - it looks superb once you have got your head round it - but for a newbie, it is pretty imposing! I guess I am just supremely thick.

Are there any tutorials or walkthroughs which explain this stuff at my dullard level?

Thanks again for your input.

JB

Problems installing SQL Server 2000

i have recently formated my laptop, and ever since i can't get SQL Server 2000 running correcly

The install seems to go ok, but when i open Enterprise Manager, and select databases, theres nothing there and i get an error saying the connection has stopped

When i try to install Service Pack 3 i get the following error:

Error running script: sp3_serv_uni.sql(1)

does any 1 know what this means

i've uninstalled many times but i still get the same errors

What am i doing wrong?What OS are you running on your laptop?

What service pack?

What version SQL are you running (Developer? MSDE?)?

You can go to your %SYSTEMROOT% directory and look for sqlstp.log and sqlsp.log. These are the installation log files. Any errors that SQL encounters during install will be documented there. One of the two files increments by 1 each time you attempt the install (but I can't remember which one it is).

The one time that I had trouble installing SQL Server was in a clustered environment, trying to install SQL on a virtual server. It turned out that I had to update the MDAC before trying to install SQL; you may consider updating your MDAC (but that's only a wild guess).

Regards,

hmscott

i have recently formated my laptop, and ever since i can't get SQL Server 2000 running correcly

The install seems to go ok, but when i open Enterprise Manager, and select databases, theres nothing there and i get an error saying the connection has stopped

When i try to install Service Pack 3 i get the following error:

Error running script: sp3_serv_uni.sql(1)

does any 1 know what this means

i've uninstalled many times but i still get the same errors

What am i doing wrong?|||thanks it ok now i've cracked it

Problems instaling IIS & Notification Service

I am trying to install SQL server 2005 Developer Edition on a stand alone PC running XP (service pack 2). I have the below error messages preventing me installing Notification Services and Intergration Services ( looks like the same problem for both ). I followed the advice to check the 'Distributed TransactionCoordinator' in services, all setting were correct. I stopped amd restarted the service and selected "retry' to no avail. Any ideas anyone?

Messages:

TITLE: Microsoft SQL Server 2005 Setup

Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2147024894

Error message: The system cannot find the file specified.

Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80070002

BUTTONS:

&Abort

&Retry

&Ignore

TITLE: Microsoft SQL Server 2005 Setup

Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894

Error message: The system cannot find the file specified.

Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80070002

BUTTONS:

&Abort

&Retry

&Ignore

Thanks in advance

ADG

can you try to reinstall .NET Framework 2.0 and then try the setup again?

You can find x86 vesrion of .NET 2.0 redistributable at: http://www.microsoft.com/downloads/details.aspx?FamilyID=0856EACB-4362-4B0D-8EDD-AAB15C5E04F5&displaylang=en

The page also contains links to x64 and ia64 versions at the bottom.

|||

Many thanks Jin

I downloaded .net 2.0 as suggested, and selected repair. Service loaded fine afterwards

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Problems in Report Presentation

Hello:
We are running SQL Server 2000 RS. We are just starting to deploy reports
and we are running into a strange problem.
We have given users access to their reports via various Windows security
groups. When an Sysadmin runs a report, it's runs fine and displays
everything in a timely fashion.
When a regular user runs a report, there is a significant delay while the
Report is Being Generated icon spins. Finally, the report displays but some
graphics are not displayed and are replaced by a box with a red X.
This behavior is consistent on any user who is not a sysadmin.
Any suggestions on how to correct this problem would be appreciated.
ThanksBrennan,
Do the images exist on a location where the group to which the user's
belong does not have permission?
I bet if you set the permission on the image to 'Everyone'/Anonymous
you'll get the images in the report.
Andy Potter

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.sql

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE :spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRefvarchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priorityvarchar(1),
@.i_str_SLAclassvarchar(10),
@.i_str_CustTypevarchar(15),
@.i_str_ModuleNamevarchar(40),
@.i_str_ProductNamevarchar(40),
@.i_str_ServerPlatformvarchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
(CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
(SELECT@.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROMSLAMatrix,
Priority
WHERESLA_Priority=Priority.Priority
ANDSLA_Priority=@.i_str_Priority
ANDSLA_Class=@.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
(CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
(SELECT@.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHEREContactSeqNum=@.i_str_ContactID
ANDContacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GO
At the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems exporting to Excel

I am getting the following error message when I try to export to mulitple sheets (running 2000)

Excel Rendering Extension : Width of excel cell in the excel sheet exceeded the max limit of 1726.5 points.

If I take each the matrix out, it works ok. If I put the matrix back in, and take out the table it also works ok, it is only when I try to go both.

Does the matrix or table have a very wide column? Is there a large horizontal space between them? The Excel has to map the structures to cells in Excel. These cells have maximum widths that cannot be exceeded.

If you post your RDL, I might be able to offer more specific guidance.

|||How do I post the rdl.

Problems exporting to Excel

I am getting the following error message when I try to export to mulitple sheets (running 2000)

Excel Rendering Extension : Width of excel cell in the excel sheet exceeded the max limit of 1726.5 points.

If I take each the matrix out, it works ok. If I put the matrix back in, and take out the table it also works ok, it is only when I try to go both.

Does the matrix or table have a very wide column? Is there a large horizontal space between them? The Excel has to map the structures to cells in Excel. These cells have maximum widths that cannot be exceeded.

If you post your RDL, I might be able to offer more specific guidance.

|||How do I post the rdl.sql

Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)sql

Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)

Problems executing Database Engine Tuning Advisor

We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either

Event does not reference any tables.

Or

Statement does not reference any tables.

Anyone have any idea why this would happen?

PLease explain the process how you are trying analyze the SP using DTA?

ALso may try from another machine to see whether its a problem of tools on this machine.

|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.

Also, if we log the execution and use the trace log to do the analysis we get the same.

It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".|||this happens against any stored procedure that DTA analyzes, why?

Problems executing Database Engine Tuning Advisor

We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either

Event does not reference any tables.

Or

Statement does not reference any tables.

Anyone have any idea why this would happen?

PLease explain the process how you are trying analyze the SP using DTA?

ALso may try from another machine to see whether its a problem of tools on this machine.

|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.

Also, if we log the execution and use the trace log to do the analysis we get the same.

It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".|||this happens against any stored procedure that DTA analyzes, why?

Problems executing Database Engine Tuning Advisor

We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either

Event does not reference any tables.

Or

Statement does not reference any tables.

Anyone have any idea why this would happen?

PLease explain the process how you are trying analyze the SP using DTA?

ALso may try from another machine to see whether its a problem of tools on this machine.

|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.

Also, if we log the execution and use the trace log to do the analysis we get the same.

It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".
|||this happens against any stored procedure that DTA analyzes, why?
sql

problems connection to Informix database

Hi,

I have a MS SQL 7 running on one machine and want to connect to a Informix database on another machine.

But I always get the error
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'ind_new_id' with incorrect bookmark ordinal 0.

I have installed the newest version of "Microsoft Data Access Components" and I have the latest Informix odbc driver

When I try to test the connection in the ODBC Data Source Administrator the test returns fine.

Anyone knows what my problem might be?One of the post in this forum refers
The problem is caused because most providers including SQL Server OLE DB provider do
not support index seek.

The 'index as access path' should not be checked when creating a linked server using
the SQL OLEDB. There is a provider level option which is read by the SQL Server from
the following registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\SQLOLEDB\IndexAs
Accesspath. Please check whether the value of the above registry key is 0. If it
isn't , please change it to 0 and try the remote query again.

If it doesn't work, I suggest you rebuild a linked server using the SQL OLEDB without
checking 'index as access path'.