old sql2k5 folder: import

Showing posts with label import. Show all posts

Friday, March 30, 2012

one more import issue

I am importing a text file with '~' column delimiter and my text has in the file has some format.If i import the data into the sql table all the text is appearing in one line by missing the formart.

(here is the sample data.
empid~empname~emp_txt*
1~jjjj~kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh*
2~3333~gdskhkgjh
ghfdgh
fkjhbdskjhkghdghah*

)

IF i import the above data into a table with emp_txt as text data type
i am getting
kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh
all in one row like:kkkgkhfdhg hhhghg hoghodshgohdsoiahgfhoghojhqgh.But i should get it same as in the notepad.
for that i have changed the datatype of the emp_text column as image in my database.
But when i change the datatype my DTS is giving the following error:

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error,Column3('emp_text',DBTYPE_BYTES),status 2: Error converting value.
Invalid character value for cast specification.In the format file also specify asterisk (*) as a row separator.

Monday, March 26, 2012

On error i want to send error description as mail

I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

You can create a table with the following fields packageid,packagename,taskname,errormessage.

And later write a stored procedure which will insert the data into this table from the package variables. On event handler tab call this stored procedure and pass the system variables "system::executioninstanceguid","system::packagename","system::sourcename","system::errordescription".

Once this is done you can read values from this particular table and assign it to a variable and later on call it in the send mail task.Hope this will help you.

|||

Use OnError Event in Event Handler and system variables such as [System::ErrorCode] and [System::ErrorDescription]

http://msdn2.microsoft.com/en-us/library/ms139744.aspx

|||

As you define above i'm doing such a way

dts.variable("User::vName").value = dts.variable("System::ErrorDescription")

but it is going me error in my script task.

:-(

please provide more specific detail or information.

|||

Hi..

1. In OnError Event Handler, Add a Script Task..

2. Define ReadWriteVariables value in ScriptTask Properties like this

ReadWriteVariables | User::vName,System::ErrorDescription

3. In VSA, edit your script like this..

Dts.Variables("vName").Value = Dts.Variables("ErrorDescription").Value.ToString

HTH

ADConsulting / SQLLeader.com / Daeseong Han

|||

Thanks for you needful help.

:-)

sql

Friday, March 23, 2012

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Hello,

Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?

Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.

With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.

I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.

Thanks in Advance...

Regards
RusszeeHere's the followup answer to my own post...

Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!

"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "

Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx

Tuesday, March 20, 2012

OLEDB error while trying to import MDB data into SQL 2005

Hello,

I am getting an error while trying to import data into SQL 2005 from an existing Access MDB database. When I try this on another workstation there are no problems and the data is imported successfully.

Could someone tell me what to look for or what setting I have incorrect. On the workstation with the problem I had removed SQL 2000 Personal Edition and installed SQL 2005 Standard Edition. Also, I have both Visual 2003 and 2005 installed on this workstation as well.

Thanks

TITLE: SQL Server Import and Export Wizard

Could not set up data flow connections.

The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

ADDITIONAL INFORMATION:

The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({55FFB2C2-E461-4FDB-B3C4-EE4677B80C77})

BUTTONS:

OK

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Error)

Messages

The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
({3DDD68E3-30A4-4513-B0AB-45F340BAB4C0})

- Setting Destination Connection (Stopped)

- Validating (Stopped)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Stopped)

- Copying to [EJTest].[dbo].[Journal] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Did you ever figure out how to fix this. I had the same problem on my laptop, and I had to rebuild my laptop from scratch to fix it. I have another person with the same problem. I would like to fix this with out rebuilding.

Thanks

Michael Robey
mrobey@.worldinsco.com

|||This is a SSIS issue.|||

Yes it is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account.

It should be the same account you are using to run SQL Server. In my case is a standalone server for test/dev purpuse so I don't use Network account. I changed both to Local System. That should fix the issue.

|||

I had the same problem too.

Thanks Carlos, fixed it perfectly.

|||

I had the problem in general. Couldnt import tables from sql2000 / 2005 same and different databases.

Trough Your remark (Thank You) i fiddeld a little with the properties of SSIS and MSSQL Services. if both log on as the built in local system user,it works too. or local system and a Windows user of the machine, thats ok too.

I had posted this problem in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344363&SiteID=1

but never got a reaction from ms

With some combinations you can optionally save the package, with some the wizard throws an error.

A definite clarification from ms about the conditions would be very helpful.

Michael

|||It really solved the problem.
Thank you very much!!!
|||

Better late than never!

Your solution solved my issue as well.

Thank you very much!

|||

It worked for me as well,

I wanted to import a visual foxpro Database to SQL2005, I installed Visual Foxpro oledb (9.0) . but I got the Error message. I tried importing a MSAccess db and it gave me the same error. following up Carlos Instruction, It worked both for msAccess db and Foxpro OleDB

Thanks

|||

Please read the KB article http://support.microsoft.com/kb/913817

The SSIS service does not have to run as the same account as SQL Server instance. What helped you is that you've changed it to run as account with Administrator rights. While this does fixes the problem, it exposes you to unnecessary risks as you are granting the service more priviledges than it really needs.

It is better to fix the real cause as described in the article.

|||

It is working now!!!

THanks

|||Bingo! That did it. Thanks!

OLEDB error while trying to import MDB data into SQL 2005

The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
({3DDD68E3-30A4-4513-B0AB-45F340BAB4C0})

Thanks

Michael Robey
mrobey@.worldinsco.com

|||This is a SSIS issue.|||

Yes it is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account.

|||

I had the same problem too.

Thanks Carlos, fixed it perfectly.

|||

I had the problem in general. Couldnt import tables from sql2000 / 2005 same and different databases.

I had posted this problem in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344363&SiteID=1

but never got a reaction from ms

With some combinations you can optionally save the package, with some the wizard throws an error.

A definite clarification from ms about the conditions would be very helpful.

Michael

|||It really solved the problem.
Thank you very much!!!
|||

Better late than never!

Your solution solved my issue as well.

Thank you very much!

|||

It worked for me as well,

Thanks

|||

Please read the KB article http://support.microsoft.com/kb/913817

It is better to fix the real cause as described in the article.

|||

It is working now!!!

THanks

|||Bingo! That did it. Thanks!

OLEDB error while trying to import MDB data into SQL 2005

Hello,
I am getting an error while trying to import data into SQL 2005 from an existing Access MDB database. When I try this on another workstation there are no problems and the data is imported successfully.
Could someone tell me what to look for or what setting I have incorrect. On the workstation with the problem I had removed SQL 2000 Personal Edition and installed SQL 2005 Standard Edition. Also, I have both Visual 2003 and 2005 installed on this workstation as well.
Thanks
TITLE: SQL Server Import and Export Wizard
Could not set up data flow connections.
The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
ADDITIONAL INFORMATION:
The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({55FFB2C2-E461-4FDB-B3C4-EE4677B80C77})

BUTTONS:
OK
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)

Messages

The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
({3DDD68E3-30A4-4513-B0AB-45F340BAB4C0})

- Setting Destination Connection (Stopped)

- Validating (Stopped)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Stopped)

- Copying to [EJTest].[dbo].[Journal] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Thanks

Michael Robey
mrobey@.worldinsco.com

|||This is a SSIS issue.|||

Yes it is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account.

|||

I had the same problem too.

Thanks Carlos, fixed it perfectly.

|||

I had the problem in general. Couldnt import tables from sql2000 / 2005 same and different databases.

I had posted this problem in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344363&SiteID=1

but never got a reaction from ms

With some combinations you can optionally save the package, with some the wizard throws an error.

A definite clarification from ms about the conditions would be very helpful.

Michael

|||It really solved the problem.
Thank you very much!!!|||

Better late than never!

Your solution solved my issue as well.

Thank you very much!

|||

It worked for me as well,

Thanks

|||

Please read the KB article http://support.microsoft.com/kb/913817

It is better to fix the real cause as described in the article.

|||

It is working now!!!

THanks

|||Bingo! That did it. Thanks!

OLEDB error while trying to import MDB data into SQL 2005

The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
({3DDD68E3-30A4-4513-B0AB-45F340BAB4C0})

Thanks

Michael Robey
mrobey@.worldinsco.com

|||This is a SSIS issue.|||

Yes it is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account.

|||

I had the same problem too.

Thanks Carlos, fixed it perfectly.

|||

I had the problem in general. Couldnt import tables from sql2000 / 2005 same and different databases.

I had posted this problem in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344363&SiteID=1

but never got a reaction from ms

With some combinations you can optionally save the package, with some the wizard throws an error.

A definite clarification from ms about the conditions would be very helpful.

Michael

|||It really solved the problem.
Thank you very much!!!
|||

Better late than never!

Your solution solved my issue as well.

Thank you very much!

|||

It worked for me as well,

Thanks

|||

Please read the KB article http://support.microsoft.com/kb/913817

It is better to fix the real cause as described in the article.

|||

It is working now!!!

THanks

|||Bingo! That did it. Thanks!

Monday, March 12, 2012

OLE/DB provider returned message: Invalid authorization specification

Hello,
I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:
insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;Password=pw d;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]') as a
I'm getting the following error:
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.
Thanx!
CHU! Eric
Hello Eric,
I have reproduced the issue on my side. Based on my research, the following
command works well:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
Therefore, I recommend you perform the following commands:
1. Make sure the Authentication Mode of MSDE is mixed mode.
The following article is for your reference:
INFO: MSDE Security and Authentication
http://support.microsoft.com/default...en-us;325022#3
2. Run the following command to test:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as a
Note:
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Sophie,
Thank you! It works fine now

"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
> or
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
>
> Therefore, I recommend you perform the following commands:
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
> The following article is for your reference:
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/default...en-us;325022#3
>
> 2. Run the following command to test:
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
> Or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as
> a
>
> Note:
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

OLE/DB provider returned message: Invalid authorization specification

Hello,
I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:
insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;P
assword=pwd;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]'
) as a
I'm getting the following error:
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.
Thanx!
CHU! EricHello Eric,
I have reproduced the issue on my side. Based on my research, the following
command works well:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdein
stance';'sa';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
Therefore, I recommend you perform the following commands:
1. Make sure the Authentication Mode of MSDE is mixed mode.
The following article is for your reference:
INFO: MSDE Security and Authentication
http://support.microsoft.com/defaul...;en-us;325022#3
2. Run the following command to test:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]
') as a
Note:
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Sophie,
Thank you! It works fine now

"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdein
stance';'sa';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
> or
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=test
;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as
a
>
> Therefore, I recommend you perform the following commands:
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
> The following article is for your reference:
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/defaul...;en-us;325022#3
>
> 2. Run the following command to test:
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
> Or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb
;UID=sa;PWD=password;Database=data
ba
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[table
B]') as
> a
>
> Note:
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

Friday, March 9, 2012

OLE DB Source Editor

hi,

I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.

"Exec Site_Address"

I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.

I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ?

Please help me to find a solution.

I will also paste the procedure code that i have used.

Create procedure Site_Address

begin

create table #Data_For_Site_Address_Table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(20),

CreatedBy varchar(20)

)

-- tblcrdsiteaddress

insert into #Data_For_Site_Address_Table

select distinct * from

(select

(select top 1 fsu.ser_num

from fs_unit fsu

where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num

order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,

CASE

WHEN ca.country like 'Luxembourg' THEN 'LU'

WHEN ca.country like 'Deutschland' THEN 'DE'

WHEN ca.country like 'Austria' THEN 'AT'

WHEN ca.country like 'Czech Republic' THEN 'CZ'

WHEN ca.country like 'Denmark' THEN 'DK'

WHEN ca.country like 'CHINA' THEN 'CN'

WHEN ca.country like 'CROATIA' THEN 'HR'

WHEN ca.country like 'Egypt' THEN 'EG'

WHEN ca.country like 'Germany' THEN 'DE'

WHEN ca.country like 'Hungary' THEN 'HU'

WHEN ca.country like 'Jordan' THEN 'JO'

WHEN ca.country like 'Korea, Republic Of' THEN 'KR'

WHEN ca.country like 'Poland' THEN 'PL'

WHEN ca.country like 'Switzerland' THEN 'CH'

WHEN ca.country like 'United Kingdom' THEN 'GB'

ELSE '- N/A -' END AS CountryID, CA.CreatedBy

from custaddr ca

) al

where unitid is not null

Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,

TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]

From #Data_For_Site_Address_Table TT

END

Thanks.

Rgds,
Meher Krishna.V

I just did a quick search in the forum and found a couple threads dealing with SP being used inside of an OLE DB Source:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ole+db+source+procedure&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

This, particularly, seems to talk about same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161617&SiteID=1

I hope it helps

|||

Hopefully this should explain the problem:

Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)

-Jamie

|||

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

|||

Rafael Salas wrote:

I knew I have read about a solution somewhere; I just could not remember the exact place.
Good stuff Jamie!

Well it wasn't at the above link cos I only wrote it about an hour ago

-Jamie

|||So, probably I dreamed about it. |||

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

|||

meher666 wrote:

thanks Jaime... that was great.
The problem is solved when i used a function......
But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.
Any clue how to use it ?

No, in some circumstances I just don't think it can be done. Where did I suggest SET FMTONLY?

-Jamie

|||

okieee.... i am sorry again.... i thought it was you suggested using that statement. It was somebody else.

Thanks for your help.

Monday, February 20, 2012

Old MDAC changes, 2.1 - 2.5

We have a large project that is importing msado21.tlb. We want to import least msado25.tlb to access the ADODB::_Stream object, but are unsure what exactly this entails. I've noticed no problems testing that change, but I cannot find any changelog for the MDAC upgrade. Is anyone aware of, or can point me to, a list of changes (fixes, modifications, caveats, etc) between MDAC 2.1 and MDAC 2.5? The binary msado file is always much newer, of course, but we want to link with the older type libraries to maintain maximum compatibility.

Any help is appreciated, I'm at a dead end!

Some of the MDAC 2.5 changes are discussed here: http://msdn2.microsoft.com/en-us/library/ms810823.aspx#mda_comp

You can look at the readme for 2.5 RTM which discusses the changes for that version.
See the download for MDAC 2.5 and find MDAC readme html file in the package. http://www.microsoft.com/downloads/details.aspx?FamilyID=83e8f178-94c9-4e7d-b0b6-a8a94c4eb912&DisplayLang=en

Friday, March 30, 2012

one more import issue

Monday, March 26, 2012

On error i want to send error description as mail

Friday, March 23, 2012

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Tuesday, March 20, 2012

OLEDB error while trying to import MDB data into SQL 2005

OLEDB error while trying to import MDB data into SQL 2005

OLEDB error while trying to import MDB data into SQL 2005

OLEDB error while trying to import MDB data into SQL 2005

Monday, March 12, 2012

OLE/DB provider returned message: Invalid authorization specification

OLE/DB provider returned message: Invalid authorization specification

Friday, March 9, 2012

OLE DB Source Editor

Monday, February 20, 2012

Old MDAC changes, 2.1 - 2.5

old sql2k5 folder

Blog Archive

About Me