opening up odbc data source in the query query inside of the server manager

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.

Thanks in advance

Daniel Buchanan.

If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

You can set up linked servers on one of the SQL Servers and join the data.

http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx

hth

BobP

Opening SqlConnection - VB.NET

I used to use rdo in VB6 and now I'm trying to figure out how to use the SqlDataReader class in VB.NET. I want to use an ODBC data source to specify the connection info. I used to give the rdoConnection object a connect string that looked something like this:
"DSN=[data source name];UID=[sql server user];PWD=[pwd]"
I don't understand the connect string given in all the examples I've found (nor does it seem to work on my system...)

mySqlConnection =New SqlConnection("server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind")
Does anyone have any ideas? I'm open to explanations as well as solutions :)
Thanks,
jdm

Already tried to look at: http://www.connectionstrings.com/ ?|||jsmiller, are you receiving an error message?
Can you shell out to DOS (EDIT: i.e. acommand prompt, sheesh my age is showing), and run this command:
osql -L
This will tell you the name of the SQL Servers to which you haveaccess. Whatever is returned is what should go into the server=part of the connection string. If you are still having trouble,please paste your connection string and error message and we will tryto help you through it.
|||

Success! The thing is that I don't want to specify a server name but rather a System DSN... So I think based on a little more research (thanks connectionstrings.com), I don't want to use a sqlConnection but an odbcConnection. Here is my latest attempt:

Dim myOdbcConnectionAs OdbcConnection

Dim myOdbcCommandAs OdbcCommand

Dim myOdbcDataReaderAs OdbcDataReader

myOdbcConnection =New OdbcConnection("DSN=DSN;User Id=sa;Password=PWD")

myodbccommand =New OdbcCommand(SQLString, myOdbcConnection)

myOdbcConnection.Open()

myOdbcDataReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection)
The program breaks on the myOdbcConnection.Open line, with an error
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
So if I add "Trusted_Connection=true" to the connect string everything seems to work out. At this point, my question is what does the trusted connection variable really mean? Am I making a security mistake?
I'm new to this so any comments welcome.
Thanks!
jdm

Open Sessions using MS Access as Front End

I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?

I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.

Any help would be appreciated.What happens if you kill your Access client or re-boot the client PC? If memory serves, ODBC Manager uses a cached connection and will hold a connection past a close. The idea is that you might want to re-connect very soon and by holding a connection the ODBC Manager can save a bit of time on the second connection.|||Originally posted by Paul Young
What happens if you kill your Access client or re-boot the client PC? If memory serves, ODBC Manager uses a cached connection and will hold a connection past a close. The idea is that you might want to re-connect very soon and by holding a connection the ODBC Manager can save a bit of time on the second connection.

If I close the Access client and reboot the machine the session will disappear, but there are times the session won't dissappear and that session becomes an orphan/ghost session . This ghost session then keeps a lock onto the table.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=myDSNn

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

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

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

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

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

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

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.