exec stored procedure

Hi,

I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!

I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!

The ole db command calls a stored procedure with two input variables:

exec stp_updedgrsholds status, temp_cr_num

from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.

but nothing gets updated in the database.

If I call the stored procedure the following way

exec stp_updedgrsholds 'C', 87

It updates fine! I have tried a number of different way with @. symbols and assignment p_status = @.status

but nothing seems to work.

Any ideas are much appreciated.

Ninder Bassi

If you call the stored proedure and it succeeds, but then data is not updated, have you confused the target of your connections?

What is a data view?

How is the stored procedure being called, what task or component?

What are status and tep_cr_num, that syntax does not make sense. When passing in parameters with OLE-DB, it is usual to use ? as the placeholder, then in the task or component UI, use the "Parameters" screen to map a SSIS variable to the placeholder.

|||

told you i was new to it all!!

the stored procedure is being called from the ole-db command

i have tried the ?'s now and get the following error after mapping them in the "column mappings" tab.

[OLE DB Commmand [3310]]: Columns cannot convert between unicode and non-unicode string data types.

many thanks for the help.

|||

ot it. did a data conversion and the parameters mapped fine. and all worked fine.

many thanks DarrenSQLIS i just needed that nudge in the right direction.

Exception from HRESULT: 0xC0202022

I created a packsge, set up a connection and the connection is tested OK. Then I created a OLE DB Source, gave the created connection and a table. When I click "Preview" button, this is what I got:

Error at DTStask_DTSDataPumpTask_3(OLE DB source[1]): An error occured due to no connection. A data connection is required when requesting metadata.

Additional informatin:

Exception from HRESULT 0xC0202022: (Microsoft.Sqlserver.DTSPipelineWrap)

Please tell me what I did wrong? or have I broken something?

Have you by any chance turned on the "Work Offline" option in the meantime?

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel group
Nothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:

> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel groupNothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:
> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel Pivot Table for AS2005

I try to use the Excel Pivot Table to retrieve the data from AS2005 cube data. I have install the Microsoft OLE DB Provider for Analsysis Services 9.0", but I get the following error message: Insitialzaiton of the data source failed. However, when I use the SQL Server Management Studio, I can browse the cube data. As a result, I know I have permission the access the cube data. Why the Excel Pivot Table is not working for me?

This schenario is pretty common and should be working. There got to be something wrong with components installed.

Try applying the latest version of AS OLEDB provider from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.