.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Issue with Connection of Sybase in SSIS and Linked Server in SQL Server

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server
 
Iam having problem connecting with Sybase from SSIS or Link Server of SQL Server 2008.

 I have installed Sybase 12.5.1 OLE DB Driver and have also registered by running command:

 C:\sybase\DataAccess\OLEDB\dll>regsvr32 sybdrvoledb.dll

 We are also able to create System DSN from ODBC Administrator in Windows 7. I am also able to import data in the MS Access. Here is the System DSN I created, successfully.

 I would really appreciate you can help us out.




View Complete Post


More Related Resource Links

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server Express 2005 connection timeout issue.

  
Client: Sql Sever Management Studio Express 2005 (SSMSE) on Vista Server: Sql Server 2005 Express on Windows Server 2008 Standard When I first try to log into SSMSE from the client machine, the login operation times out. When I retry, the login succeeds almost immediately. If I close the app and re-open it, the same thing happens. This appears to be infinitely repeatable. I am able to reproduce this behavior using SQLCMD.exe. Here's the error: "Timeout expired. The timeout elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)" The technical details reference Error Number -2 What may be unique or tricky about this client's network is that it's a mixture of IPv4 and IPv6. I believe the server is configured for both, but honestly the networking side of things is outside of my expertise. Has anyone else experienced a problem like this? I'm looking for some troubleshooting tips and of course a flat-out answer :-). Thank you in advance for your time and expertise - I hope I've broken the problem down to its simplest form. Will.

Linked Server UPDATE issue

  

Hello forum,

I'm doing a simple data load between two linked servers, and everything is working fine until I include an UPDATE statement, that's when I receive the following error message.

OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerName" was unable to begin a distributed transaction.


the Distribute Transaction Coordinator service is running on all machines, and I'm able to do INSERTs between the two servers. Here is a part of the code I'm using. It works fine if I don't include the UPDATE. 


BEGIN TRAN
DECLARE @TempTable TABLE(PDID INT
			,FirstName VARCHAR(MAX)
			,LastName VARCHAR(MAX)
			,Degree 

Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?

  

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.

I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN'

Catch 22 Database Connection Validation Issue w/ BIDS and Production Server

  

I have a rather simple SSIS package that exports data through an ODBC connection from an IBM iSeries database and copies that data to a SQL Server 2005 database.  When I was designing the application, I was able to connect to the iSeries database and store the user ID and password with my BIDS project and copy the data with out any problems.  Great!

When I finished designing and testing the project, I loaded it to the production server.  The final test was to schedule the process so that it would run each night.  I didn't realize it at the time but the password that I had stored with the project in BIDS was removed from the package and various MS documentation stated that I should create a configuration file and load the password from there.  So, I made the necessary changes to the project, adding a configuration file.  The config file is in the Miscellaneous folder within the BIDS project, however, it isn't automatically copied to the bin directory when I build the project.  I have to manually save it to the server, and then after importing the project I have to manually link to the necessary config fields.

This fixed the problem with the server, however, now I can not get BIDS to save my password with the connection string from within the designer.  This is a huge problem.  I am exporting from 21 different da

Linked Server/ LDAP connection help.

  

Hello

I just got assigned to a project to link two servers. The information I am trying to obtain is in a "Lawson LDAP application" and I'm transfering it to a SQL 2008 DB.

I went through the linked server wizard but I dont really understand the depth of it. 

I have the Host name, protocol, port, Base DN, User DN information.

I did some research on the Lawson software that the company is using but what Provider should be selected in the wizard for this type of program.

As you can see I'm quite lost, Any help would be greatly appreciated.


SSIS data flow task not loading all rows from sybase server

  

Hi,

Using a data flow task I am trying to load data from a table in sybase server to sql server. There are approx 10000 rows in the sybase table, however for some reason which I am not able to find out, at random certain no of rows are getting inserted. e.g. in one run 800 rows then 200 rows etc.

Can somebody please guide me on this?



Get the connection status of linked server

  
i am accessing a table from a linked server, before i manipulate that table, i how can i check the connection status of linked server

Sql Server connection issue : I can connect from a web app, but not from a windows app.

  

I have two applications, a win form and a web app. I am using  sql server on a local network. I can open a connection to the sql server from the web app, but the win form app always fails when i try to open a connection. I get a TCP provider error of all sorts. I am using the same connection string for both apps.

Any Idea,

Thanks

 

Additional Info:

Visual Studio 2010, Sql Server 2005,

 


Linked server access issue when hosting application in IIS

  

Hi,

I have two instance of database and connected the other with linked server. When i run the application locally its working fine. It gets valur from linked server tables. After hosting the application in IIS im getting following eror.

The OLE DB provider "SQLNCLI" for linked server "LINK" does not contain the table ""90"."dbo"."table"". The table either does not exist or the current user does not have permissions on that table.

 

Can anyone specify what user should be mapped to solve this issue?


Linked server issue when application hosted in IIS

  

Hi,

I have hosted my application in IIS. Application has two instance of sql server database connected through linked server. After hosting the aplicaiton in IIS im getting the following error.

SCHEMA LOCK permission denied on object.

 

Can anyone help?


Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

Can we import data from Green plum to SQL Server 2005 using OLEDB connection manager in SSIS

  

hi,

actually we are planning to move data from GreenPLum data base to SqlServer 2005 data base using SSIS as ETL tool. What type of connection manager can we use. Is to good to use OLEDB connection manager or ODBC connection manager.

Please give me a reply as soon as possible.

thanks

sashikanth

 


ssis deployment issue - Unable to load custom dll - due to change in Sql server installation

  

Hello all

I had a strange issue in testing my package, after it was deployed to a Test server.

The details of the server

Sql 2005 is installed, in the D:\ drive

I have used a package, where i use a custom DLL to read values. Hence, i added that DLL to the GAC. I was in the assumption that this should complete my proceess of deploying this package.

 

However once i executed the package, i got an error - Could not load the Helper object.

Unable to CAST "system.object"  to namaspace.class.Helper.

 

I reviewed the setting on my macihine, since as part of development inorder to reference the dll i added this to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies location so that i can reference this from BIDS. I checked that location in my test server, i was not present. This location was present in the D: drive ( i assume this was present since i installed my complete SQL Server in the D:\ ). I copied the dLL in that location and added it to GAC from that location. My package still failed with the same error.

 

To resolve this issue, i created the same path in the C:\ folder as C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

assemblies was newly created in C: and i added the dll to GAC from there. Now my package worked.

 

Running SSIS on 64-bit server - dll issue

  

Hi,

We have a SSIS package which refreshes an Excel 2007 file using a script task - references the Microsoft.Office.Interop.Excel.dll

Runs Ok on 32-bit dev PC - returns this error on server: "Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.RefreshExcel()

at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.Main()

--- End of inner exception stack trace ---

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, Cul

Issue with linked server 2008 (and R2) not an issue in 2005

  

Hi,

I have been using SQL 2005 successfully to connect to tables in a 10g Oracle data warehouse. I have been using the 11g client.

I have tried several times to get this working in SQL Server 2008 (and R2) and have tried the 10g and 11g clients...

I do a query like Select top 100 * from DWD..DW.IC_TRAN_PND and I get the error: 

"Invalid data for type "numeric".

 

I have read about the reasons for this but is there a way to make it work like it does in SQL server 2005?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend