Connecting to sql server 2005

I want to connect to database using sql server 2005 through Visual web developer 2005 express edition . what will be the connection string for this....

keywords under * * are for your own variable.

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ *datafile name *.mdf;Integrated Security=True;User Instance=True"

if it is a database in a local drive

and if you are using a web server ,

connectionString="data source=*ip address * ;user id=userid;password=pass"

|||

The following link might help you..

http://www.connectionstrings.com/?carrier=sqlserver2005

|||

sagar Khatri wrote:

keywords under * * are for your own variable.

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ *datafile name *.mdf;Integrated Security=True;User Instance=True"

if it is a database in a local drive

In fact that is not the right explanation. The above mentioned syntax is used when you use the SQL Server 2005 Express feature of user instances. The above mentioned sample assumes that yxou have a local SQL Server Express instance (configured with the instance name of SQLEXPRESS). User instances are used to temporary attach datafile to SQL Server instances.

sagar Khatri wrote:

and if you are using a web server ,

connectionString="data source=*ip address * ;user id=userid;password=pass"

This has nothing to do with a web server. The above mentioned syntax is used when you have a SQL Server attached database. No matter which middle tier (either an application you wrote or a web server is connecting to the database server)=, you are able to have multiple conenctions from multiple applications to this database, other then the user instance where only one application can create a user instance and have access to the database at a time.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sqlsql

Connecting to SQL Server 2000 from a VB 6.0 app using ADO

How to connect to real ip such as 202.28.94.10 from DCHP ip by use VB 6 .. I don't know about to use connection string ...
My PC is insatalled My SQL Server2000 and has ip 10.98.10.97 that is DHCP ip and another ones is installed the same but that it real ip. Both PC can't to see them together in network

This problem make my first PC can't access database to another one.
Help me plssss...I'm not entirely certain I understood your question. It appears that your SQL server is a DHCP client (and thus it is possible that the IP address will periodically change due to a reboot or a lease renewal (should not happen often, but it is possible).

Is it not possible to connect to the server via its name instead of the IP? Ie, use the DNS server to resolve the IP address?

On the other hand, re-reading your question seems to indicate that you have 2 client workstations, one DHCP and one static. It appears that the static IP client can connect to SQL and the DHCP client cannot. If that is the case, then you appear to have a network issue unrelated to SQL and you must work with the network admin to ensure that connectivity is not being blocked by routing rules.

If I have misunderstood your question, please forgive me.

Sorry, I guess I need a bit more info to help.

Regards,

Hugh Scott

Originally posted by cs423326
How to connect to real ip such as 202.28.94.10 from DCHP ip by use VB 6 .. I don't know about to use connection string ...
My PC is insatalled My SQL Server2000 and has ip 10.98.10.97 that is DHCP ip and another ones is installed the same but that it real ip. Both PC can't to see them together in network

This problem make my first PC can't access database to another one.
Help me plssss...|||Hi hmscott .
What is connection string in VB
My connection string to DHCP ip is "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=G7;Data Source=NUT"

when I will change Data Source from NUT to 10.98.10.97 (This is My DHCP Client IP) it work...
But when i change to Static ip (202.xxx.xxx.xxx) it does not work
How will i do??Please tell me about connection string that can be access to SQL Server..
thx|||Some things to test:

1. Can you Ping the static IP from your workstation (Ping 202.xxx.xxx.xxx).

If not, you need to see your network admin.

2. If you have query analyzer on your client, can you log in to the static IP server?

Again, iff not, you need to see your network admin.

HTH, my understanding gets much fuzzier once you get down to the network layer...

Regards,

Hugh Scott

Originally posted by cs423326
Hi hmscott .
What is connection string in VB
My connection string to DHCP ip is "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=G7;Data Source=NUT"

when I will change Data Source from NUT to 10.98.10.97 (This is My DHCP Client IP) it work...
But when i change to Static ip (202.xxx.xxx.xxx) it does not work
How will i do??Please tell me about connection string that can be access to SQL Server..
thx|||Hi again hmscott.
I ping the static ip from my PC is work..
and I can't use query analyzer log in to static ip server

..my PC (installed SQL Server 2000) and the static ip PC (installed the same) not in the same network ...then Both of them can't connect through SQL Tools.

How can I solve this problem??
and How can I code to connect them with VB?

Please help me again...
Thx.|||Perhaps it would help if you posted the actual error message text. I suggest that you try to set up a registration for the other machine in SQL Enterprise Manager and then copy the text of the error message and post it here|||thx MealinA

This is error message when I tried to Register with SQL Enterprise Manager:

xxx.xxx.xxx.xxx-Login fail for user 'userName'.Reason :Not Associated with a trusted SQL Server Connection;

and

and this is error message when i tried to create DSN :

Connection failed:
SQL State:'28000'
SQL Server Error:18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login fail for user 'user'.Reason :Not Associated with a trusted SQL Server Connection.

Help me plsss...|||See if you can connect with this connection string:

"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=<sa password>;Initial Catalog=G7;Data Source=<ip address>"

If this connects OK then your problem is authentication. I can give you some advice if that is the case...|||Hi MealinA.
I tried to connect my SQL Server with a connection string :
"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=<sa password>;Initial Catalog=G7;Data Source=<ip address>"

but is not work ...
Error :

Runtime error 'xxxxx'
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

How can I solve that??|||did you replace <sa password> with the sa password for your server and <ip address> with the ip address for your server in the connection string before trying it?|||Yes I do,MealinA.
But It does not work..
and if I tried to connect with my user(created) it show the same result..|||On the server if you go (start->run) svrnetcn.exe and make sure only TCP/IP is listed under enabled protocols. Click on TCP/IP then Properties. The default port should be 1433 and the hide server box should be unchecked.

Also in SQL-EM on the server choose the local machine and then go (Tools->SQL Server Configuration Properties). Go to the Security tab and check that Authentication is set to SQL Server and Windows.

On the client go (start->run) cliconfg.exe and make sure that TCP/IP is the only (or at least the first) protocol listed. Click on TCP/IP then Properties. The default port should be 1433.

Tip: (This assumes you have MDAC 2.6 or higher on your CLIENT pc) Something I do is to use the Alias tab to set and alias for the server. Click Add, then enter MySQLServer in the Server alias box. Choose TCP/IP in the Network Libraries list. Under Connection parameters set Server name to <your server ip address> and check Dynamically determine port. Then use MySQLServer in the connection string. This means if the server ip address changes you can run cliconfg and change the alias without needing to change the connectiion string.

If this is all set up then there is no reason why the client should not connect to the server from SQL Server's perspective. If it still will not connect then I would suggest you carefully analyse your network configuration on the computers involved.

Connecting to SQL Server

Any help??

I am a newbie to this...I am trying to right a connection string to a SQL Server DB. I do not have the full blown SQL Server, but just the SQL desktop engine. I put together the following in Web Matrix and it works without a problem. However, when I copy this over into Visual Studio.net I get an error at the "myConnection.Open()" command. Any ideas why it would work in Web Matrix but not in Visual Studio. When I test the connection to the SQL DB in Studio it says that the test completed succesfully so I don't think I am having a problem actually connecting, so I am not sure what the problem is.

Code..............................
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim myConnectionString As String
myConnectionString= "server=Discovery3;database=dbDVD;trusted_Connection=true"

Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')"
Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

End SubPlease qualify "I get an error". We need exact error messages in order to help you.

Terri|||myConnectionString= "server=Discovery3;database=dbDVD;trusted_Connection=true"

Data Source instead of server.
Initial Catalog instead of database
trusted_connection... wtf? this looks a lot like an odbc connection string used in an oledb scenario.

I know you're new, and I'm not making fun of you too much. :)

To learn more about ado.net's connection strings, visit
http://able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider|||Actually, that connection string is fine forStandard Security using SqlConnection.

See alsoSqlConnection.ConnectionString Property for a description of the valid attributes.

Terri|||No offense taken! It probably looks like an ODBC connection because that is where I started my research.

Why would this work in Web Matrix, but not in Visual Studio? You would think if it is wrong it would be wrong in both?

I will give it a shot when I get home.

Thanks|||Again, we need to see the exact error message.

ASP.NET applications run under the ASPNET account if you are not using impersonation. Web Matrix runs under your own account. You either need to turn on impersonation in your web.config file, or you need to give appropriate SQL Server permissions to the ASPNET account.

Terri|||Terri,

Are you serious? Maybe it's muscle and visual memory cuz I swore anytime I ran it with an odbc similar connection string it didn't work for me. Learn something new every day.

I'm still more comfortable with the oledb method personally. It makes a lot more sense to me that way.|||Yes I'm serious, and don't call me Shirley.

"Data Source" and "Server" are interchangeable
"Initial Catalog" and "Database" are interchangeable
"Trusted_Connection=True" and "Integrated Security=SSPI" are interchangeable

I always usehttp://www.connectionstrings.com/ as my primary reference. Personally I can't keep any of it straight and always use that reference.

Terri|||Ok, here is the error message that I get: Line 40 is highlighted

Server Error in '/WebApplication1' Application.
------------------------

Login failed for user 'DISCOVERY3\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DISCOVERY3\ASPNET'.

Source Error:

Line 38: Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
Line 39: myCommand.Connection = myConnection
Line 40: myConnection.Open()
Line 41: myCommand.ExecuteNonQuery()
Line 42: myCommand.Connection.Close()|||You are most probably using a trusted connection to your Sql Database here. You will need to add the 'DISCOVERY3\ASPNET' account to have access to the specific Database that Webapplication1 is using, and grant the account the necessary rights to access (and eventually Insert, Update and Delete) the Tables in this Database. If there are other objects, like Stored Procedures, that the ASPNET account needs to use, you will have to grant rights to EXECUTE these to this account as well.|||Sorry for being new...but when you say add the Discovery3\ASPNET account, where do I add this?|||In sql server, there's something under databases called manager or management. I forget. anyways, in there, you should see login. That's where you want to add that in. I suggest creating a different user, and applying priviledges there.

Terri, I know what you mean. however, with as much sql work that I do.. it's impossible not to remember a good connectionstring sequence, and just muscle memory.sqlsql

Connecting to SQL Mobile within VS-2005

I am getting an error when I run my project within VS2005. I am trying to connect to a SQL Mobile (sdf) file thru code using a connection string. As soon as run my project within the VS2005 environment, I get the following error when the code tries to connect:
"The file is not a valid database file. An internal error has occurred. [,,,Databasename,,]"
The source of the error is: Microsoft SQL Server 2000 Windows CE Edition
Native Error of: 25011
I am am deploying to POcket PC 2003 SE Emulator.
This code worked fine in VS2003 with SQLCE 2.0. But I have now updated the projects to VS2005 and recreated the database sdf file within the VS2005 environment.
Could someone please help me ASAP?
Thanks.

There is no database compatibility between SQL CE 2.0 and SQL Mobile. Database created through one cannot be opened by other. In you case, since you have upgraded the project and re-created the database, you have SQL Mobile database. But your application still has references to SQL CE 2.0 provider. So now your application is trying to open a SQL CE 3.0 Database using SQL CE 2.0 provider; that’s the reason why in your error string you can see

The source of the error is: Microsoft SQL Server 2000 Windows CE Edition
Native Error of: 25011

In you application you need to remove the reference from 2.0 provider and add reference to 3.0 provider.

Thanks

-Mani

Connecting to SQL Express from C# via connection string

I created a SQL Express database from within Vistual Studio. It had a
property ConnectionString, so I grabbed that value and attempted to use it i
n
code. (I did add an @. at the beginning and doubled the literal double quote
symbols.) The code that assigns the connection string and opens the
connection then looked like the following:
SqlConnection cn = new SQLConnection();
cn.COnnectionString = @."Data Source=
.\SQLEXPRESS;AttachDBFilename=""C:\MySolution\MyData.mdf"";Integrated
Security=True;User Instance=True";
cn.Open();
SqlCommand myCommand = new SqlCommand("Use FTTestLocal Create Table Asset
(AssetID int Identity Not Null Primary Key, AssetName varchar(150) Not Null,
Modified TimeStamp)", cn);
int ct = myCommand.ExecuteNonQuery();
When I run the application I get an error when the command is executed
stating "Could not locate entry in sysdatabases for database 'FTTestLocal'.
No entry found with that name. Make sure the name is entered correctly."
Thanks in advance for any help.Your connection string is attaching one database named
C:\MySolution\MyData.mdf and then you are trying to create a table in a
database named FTTestLocal. Either your connection string is wrong or you
are trying to use the wrong database. Perhaps you should explain what
you're trying to do so we can tell which database is appropriate.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steve Wash" <SteveWash@.discussions.microsoft.com> wrote in message
news:E426F5B2-E8E0-4D54-AAE2-949088D2A673@.microsoft.com...
>I created a SQL Express database from within Vistual Studio. It had a
> property ConnectionString, so I grabbed that value and attempted to use it
> in
> code. (I did add an @. at the beginning and doubled the literal double
> quote
> symbols.) The code that assigns the connection string and opens the
> connection then looked like the following:
> SqlConnection cn = new SQLConnection();
> cn.COnnectionString = @."Data Source=
> .\SQLEXPRESS;AttachDBFilename=""C:\MySolution\MyData.mdf"";Integrated
> Security=True;User Instance=True";
> cn.Open();
> SqlCommand myCommand = new SqlCommand("Use FTTestLocal Create Table Asset
> (AssetID int Identity Not Null Primary Key, AssetName varchar(150) Not
> Null,
> Modified TimeStamp)", cn);
> int ct = myCommand.ExecuteNonQuery();
> When I run the application I get an error when the command is executed
> stating "Could not locate entry in sysdatabases for database
> 'FTTestLocal'.
> No entry found with that name. Make sure the name is entered correctly."
> Thanks in advance for any help.
>|||Thanks for your quick review of my problem. I apologize that I made a
type-o, and your solution addresses the type-o. In trying to simplify my
example, I removed a lot of cryptic pathing information, but accidently left
in the "FTTestLocal". The actual code is
ConnectString=@."Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\VS
Projects\Personal Utilities\TableClassBuilder\FTTestLocal.mdf"";Integrated
Security=True;User Instance=True";
The SQL command is "Use FTTestLocal Create Table Asset (AssetID int Identity
Not Null Primary Key, AssetName varchar(150) Not Null, Modified TimeStamp)"
I've also tried
"Use FTTestLocal.mdf Create Table Asset (AssetID int Identity Not Null
Primary Key, AssetName varchar(150) Not Null, Modified TimeStamp)".
Interestingly, the error message did not change at all. It did not seem to
pick up the .mdf in any way.
As for what I'm trying to accomplish, I'm attempting to modify existing
code. Currently the code attaches to an online server running MS SQL 2005
using the previously referenced code. I need to modify it to work as close
as possible to the same way but to instead attach to SQL Express. This way
I
can continue to develop without always needing to go online.|||Try running
SELECT name FROM sys.databases
to get the name of the database. It is probably:
C:\VSProjects\Personal Utilities\TableClassBuilder\FTTestLocal.mdf
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steve Wash" <SteveWash@.discussions.microsoft.com> wrote in message
news:C92EB4DD-7189-487B-9BE9-03BD64B68ACA@.microsoft.com...
> Thanks for your quick review of my problem. I apologize that I made a
> type-o, and your solution addresses the type-o. In trying to simplify my
> example, I removed a lot of cryptic pathing information, but accidently
> left
> in the "FTTestLocal". The actual code is
> ConnectString=@."Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\VS
> Projects\Personal Utilities\TableClassBuilder\FTTestLocal.mdf"";Integrated
> Security=True;User Instance=True";
> The SQL command is "Use FTTestLocal Create Table Asset (AssetID int
> Identity
> Not Null Primary Key, AssetName varchar(150) Not Null, Modified
> TimeStamp)"
> I've also tried
> "Use FTTestLocal.mdf Create Table Asset (AssetID int Identity Not Null
> Primary Key, AssetName varchar(150) Not Null, Modified TimeStamp)".
> Interestingly, the error message did not change at all. It did not seem
> to
> pick up the .mdf in any way.
>
> As for what I'm trying to accomplish, I'm attempting to modify existing
> code. Currently the code attaches to an online server running MS SQL 2005
> using the previously referenced code. I need to modify it to work as
> close
> as possible to the same way but to instead attach to SQL Express. This
> way I
> can continue to develop without always needing to go online.|||That was it. Thanks
"Roger Wolter[MSFT]" wrote:

> Try running
> SELECT name FROM sys.databases
> to get the name of the database. It is probably:
> C:\VSProjects\Personal Utilities\TableClassBuilder\FTTestLocal.mdf
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steve Wash" <SteveWash@.discussions.microsoft.com> wrote in message
> news:C92EB4DD-7189-487B-9BE9-03BD64B68ACA@.microsoft.com...
>
>

Connecting to SQL 6.5 with VB.NET

Hi,

I am trying to connect to a SQL 6.5 database with VB.NET.

Is this possible, and if so, what kind of connection string is used and what mode of data access (OleDB, ODBC, etc.)

I have had several problems doing this and I am starting to wonder if there is anything special that needs to be done or if it is even possible. Thanks in advance!OleDB. Check out http://www.connectionstrings.com/|||Alright, I tried creating an OleDB Connection using Visual Studio. I set up the connection and it connects fine. I can view the tables and it says that it connects to the datasource. But when I try to use the connection in the page I get this error:

System.ArgumentException: The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc). at System.Data.OleDb.OleDbConnectionString.ValidateNotMSDASQL(String progid) at System.Data.OleDb.OleDbConnectionString.ValidateParse() at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl) at System.Data.OleDb.OleDbConnectionString.ParseString(String connectionString) at System.Data.OleDb.OleDbConnection.set_ConnectionString(String value) at ask.testconn.Page_Load(Object sender, EventArgs e) in \\cnsrfdiis01\intranet\ask\testconn.aspx.vb:line 31

Why would it be able to connect in the VS.NET Server Exlporer, but not in a page. I am just trying to open the database and close it. Here is the connection string I am using:

Provider=SQLOLEDB;Persist Security Info=False;User ID=crystal;Initial Catalog=lmsprod;Data Source=10.1.121.22;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AROCAPTEST;Use Encryption for Data=False;Tag with column collation when possible=False

Thanks in advance!|||Ok, I was wrong. I went and checked, and you should be using the ODBC driver. Sorry. Try that and see if you still have the error.

connecting to SQL 2000 Server gives SQL 2005 error message

Hi,

Here is the syntax of the connection string within the web.config file:

<connectionStrings>
<clear />

<addname="MyConnectionString"connectionString="Data Source=000.000.000;Initial Catalog=Database Name;Persist Security Info=True;User ID=XXXXXX;Password=XXXXXX;"providerName="System.Data.SqlClient" />

</connectionStrings>

The test page has a GridView in it which uses the connectionstring fron the web.config file.

Error Message:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Not sure why .NET thinks it's a SQL 2005 server

I can connect to the database via VS 2005, and through Enterprise Manager... just from the ASP.NET page.

Any ideas?

Thanks,
Roland

By default SQ2005 does not have remote connections enable, you need to go to Surface Configuration and the first tap change from pipe to remote connections. That shall fix the problem.

|||

Thanks Al, but the problem is that the server is SQL 2000 and NOT SQL 2005.

So, I'm not sure why .NET thinks, or is getting the information to say that the SQL server is SQL 2005.

So to reiterate the question:

Why do I get a SQL 2005 error while connecting to a SQL 2000 database?

Thanks again,

Roland

|||

Then must be a firewall between or again you can change SQL 2000 to use only pipes, check that and let me know

|||

Why you got a message about MS SQL Server 2005 while the version you have is MS SQL Server 2000?

This is beacuse you are using MS Viusal Studio.NET 2005 which install a SQL Express (lite SQL Server - similar to MSDE) on your machine and by default it will coneect to it.

This SQL Express uses a SQL Server 2005 engine, and that is why you saw "When connecting to SQL Server 2005, ...." message.

Ok, what to do?

Change the default connection string from your IIS.

Open your IIS, then click on your "Virtual Directory" (e.g. your ASP.NET Website) then go to its configuration and change the Server name part in the ConnectionString.

Hope this will help you as it helped me eairler.

Regards,

sqlsql

Connecting to Oracle

I can connect to a oracle database on the development PC using OLE DB, here
is the connection string:
Provider=OraOLEDB.Oracle.1;Data Source=cdbprod
Yet when I Deploy to the test server, it fails. Other applications can
connect to the oracle database on the same test server, but not reporting
services. Can someone give me a working example connection string for Oracle?
--
www.duanelawrence.comI think this might be a issue with setting file access rights to the Oracle
client directory for the RS account. I suspect it is not your connection
string. What error are you getting?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
message news:CAA8D1D1-4299-432A-B6A9-3CE93B4BDECF@.microsoft.com...
> I can connect to a oracle database on the development PC using OLE DB,
here
> is the connection string:
> Provider=OraOLEDB.Oracle.1;Data Source=cdbprod
> Yet when I Deploy to the test server, it fails. Other applications can
> connect to the oracle database on the same test server, but not reporting
> services. Can someone give me a working example connection string for
Oracle?
> --
> www.duanelawrence.com
>|||This is the error I get with a fresh upload from the working one on my
development PC:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'cdbprod'.
(rsErrorOpeningConnection) Get Online Help
The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.
No error information available: REGDB_E_CLASSNOTREG(0x80040154).
"Bruce L-C [MVP]" wrote:
> I think this might be a issue with setting file access rights to the Oracle
> client directory for the RS account. I suspect it is not your connection
> string. What error are you getting?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
> message news:CAA8D1D1-4299-432A-B6A9-3CE93B4BDECF@.microsoft.com...
> > I can connect to a oracle database on the development PC using OLE DB,
> here
> > is the connection string:
> > Provider=OraOLEDB.Oracle.1;Data Source=cdbprod
> >
> > Yet when I Deploy to the test server, it fails. Other applications can
> > connect to the oracle database on the same test server, but not reporting
> > services. Can someone give me a working example connection string for
> Oracle?
> >
> > --
> >
> > www.duanelawrence.com
> >
> >
>
>|||Yeah, this seems to me to be that you need to set permissions on the oracle
directory. Sorry I can't be more specific, I am not using Oracle myself.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
message news:39412D41-650B-43FD-BAEF-E6F57D784209@.microsoft.com...
> This is the error I get with a fresh upload from the working one on my
> development PC:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'cdbprod'.
> (rsErrorOpeningConnection) Get Online Help
> The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.
> No error information available: REGDB_E_CLASSNOTREG(0x80040154).
>
>
> "Bruce L-C [MVP]" wrote:
> > I think this might be a issue with setting file access rights to the
Oracle
> > client directory for the RS account. I suspect it is not your connection
> > string. What error are you getting?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote
in
> > message news:CAA8D1D1-4299-432A-B6A9-3CE93B4BDECF@.microsoft.com...
> > > I can connect to a oracle database on the development PC using OLE DB,
> > here
> > > is the connection string:
> > > Provider=OraOLEDB.Oracle.1;Data Source=cdbprod
> > >
> > > Yet when I Deploy to the test server, it fails. Other applications
can
> > > connect to the oracle database on the same test server, but not
reporting
> > > services. Can someone give me a working example connection string for
> > Oracle?
> > >
> > > --
> > >
> > > www.duanelawrence.com
> > >
> > >
> >
> >
> >|||The real answer is.
We have DTS packages running on the test server, they all use Microsoft
Datalink files (UDL), we opened the one that goes to the Oracle database and
copied the connection string into the shared data source in the report
manager window. The reports are running now, still have not returned, but
they have not errored out in the past 10 minutes.
Duane
"Bruce L-C [MVP]" wrote:
> Yeah, this seems to me to be that you need to set permissions on the oracle
> directory. Sorry I can't be more specific, I am not using Oracle myself.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
> message news:39412D41-650B-43FD-BAEF-E6F57D784209@.microsoft.com...
> > This is the error I get with a fresh upload from the working one on my
> > development PC:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'cdbprod'.
> > (rsErrorOpeningConnection) Get Online Help
> > The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.
> > No error information available: REGDB_E_CLASSNOTREG(0x80040154).
> >
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I think this might be a issue with setting file access rights to the
> Oracle
> > > client directory for the RS account. I suspect it is not your connection
> > > string. What error are you getting?
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote
> in
> > > message news:CAA8D1D1-4299-432A-B6A9-3CE93B4BDECF@.microsoft.com...
> > > > I can connect to a oracle database on the development PC using OLE DB,
> > > here
> > > > is the connection string:
> > > > Provider=OraOLEDB.Oracle.1;Data Source=cdbprod
> > > >
> > > > Yet when I Deploy to the test server, it fails. Other applications
> can
> > > > connect to the oracle database on the same test server, but not
> reporting
> > > > services. Can someone give me a working example connection string for
> > > Oracle?
> > > >
> > > > --
> > > >
> > > > www.duanelawrence.com
> > > >
> > > >
> > >
> > >
> > >
>
>

Connecting to Northwind on SQL Server 2000

I've just installed SQL Server 2000 on my local machine. I'm using the following element in my web.config file for my connection string.

<add key="SqlNWind512" value="server=(local)\141.705.84.745;database=Northwind;
user id=sa;pwd=h3fe8eq7;packet size=512;" /
Unfortunately, I'm getting the following error on my aspx page:

SQL Server does not exist or access denied.

I had no problem writing a connection string to connect to the MSDE installed on my computer, but making a connection to the Northwind database on my newly installed SQL Server 2000 Developer edition has been elusive to me.

I'm sure my IP address is correct. The authentication mode for logging into the database via Enterprise Manager is setup to require a password for sa. Therefore, I assume it's required in the connection string?

Maybe not so coincidentally, while I have been able to open this database through the Server Explorer in VS.NET, I'm not able to access it via Enterprise Mananger (no response, it just hangs) or my aspx pages.

I'm dying here!!! Is my connection string wrong? Any insights would be greatly appreciated!just server=<ip address
forget the (local) bit if it's not local.|||Thank you!|||word to the wise..
Don't put your password to your database online on forums.

h3fe8eq7 can be a very bad thing.

connecting to MSDE using SSPI

Please advise: I have created a simple ASPX page to test if I can connect to MSDE on a Server 2003 system. The connection string that works ok is:

server=WS1;database=northwind;integrated security=false;user id=sa;password=xxx;

but the string that does not work is:

server=WS1;database=northwind;integrated security=SSPI;

which is the string I would like to use for normal web access. This string works fine on my development system (where I use XP Pro and IIS 5.1)

In IIS6 on the "production server" the directory security on the virtual dir is set to allow anonymous access, using user IUSR_WS1 (where WS1 is the name of the system) and to integrated security.

Please help! Thank you

John BI forgot to say the error message I am getting is:

login failed for user 'NTAUTHORITY\NETWORK SERVICE'|||There is a KB for this problem:PRB: "Login Failed" Error Message When You Create a Trusted Data Connection from ASP.NET to SQL Server. See if any of the 3 suggested Resolutions work for you.

To me, the 3rd suggestion seems to be the best course of action. And taking that further, what seems to make the most sense is to add a new database role called "webuser" or something similar, and then add the NT AUTHORITY\NETWORK SERVICE user to that role. This will give you more flexibility if the web app moves to a Windows 2000 machine -- you would just be able to add the ASPNET account as another member of that webuser role.

Terri|||Thank you for your reply. I looked at the article, but I regret to say it does not make much sense to me at this stage. I have bought the MSDE Admin tool to administer MSDE, or could use the osql tool, but am not clear how to:

a. add a new database role (is this adding a "normal" user group)? I dont see any reference to a "database role".

b. add the NTAUTHORITY\NETWORK SERVICE user to that role. I have looked at the list of users and there is no such name. I must be looking in the wrong place.

Excuse my ignorance!

TIA

John B|||You should downloadSQL Server 2000 Books Online. This is a huge download but an essential reference.

I have to admit, I am confused on this issue. I have now come across an article right on this siteRunning ASP.NET 1.1 with IIS 6.0, and part of it covers supporting integrated authentication with SQL Server. The suggestion I gave you was based on something I read elsewhere that seemed to make sense.

Further information on the path I suggested:

The system stored procedure to add a database role is sp_addrole. You'd use it like this:

EXEC sp_addrole 'webuser'

The system stored procedure to add a security account for a Windows user to the current database and enable it to be granted permissions to perform activities in the database is sp_grantdbaccess.

EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE', 'Network Service'

The system stored procedure to add a security account as a member of an existing database role in the current database is sp_addrolemember.

EXEC sp_addrolemember 'webuser', 'Network Service'

Terri|||Thank you. The article you suggested in the last post is very helpful. I have now solved the problem, thanks to an article I found www.cgnit.com/resources/ntauthority_networkservice_error.html

which hits it bang on the nose, and may be useful to others.

It seems to me that all the books, and many of the forums suggest that when you run an ASP.NET app and use anonymous access, the system uses the identity mcxxx\aspnet (where mcxxx is the domain or machine no). But what I have found so far is that under Server 2003, the identity assumed is the mysterious user NTAUTHORITY\NETWORKSERVICE which is NOT found in the list of users, but is apparently a member of the IIS_WPG group, so configuring this group to access the database solves the problem and allows an anonymous user to access records according to the permissions set within MSDE or SQL Server.

Can anyone confirm this is true? Is the ASPNET user in fact NEVER used in Server 2003, but only in W2000 or XP?|||Yes, I can confirm that. I ran into this today, setting up ibuyspy on a Windows2003 server. Here's the quote I found in the machine.config file:
When ASP.NET is running under IIS 6 in native mode, the IIS 6 process model is
used and settings in this section are ignored. Please use the IIS administrative
UI to configure things like process identity and cycling for the IIS
worker process for the desired application

I went ahead and added the IIS_WPG to access the DB I cared about, but you could also setup your own IIS6 AppPool with seperate identity information.

/Brad|||

I cannot but say, its a great thread !!

regards

Connecting to local SQL Server

Hi,

I am having problems connecting to databases on my local SQL Server, express edition from my website.

The following is the connection string i am using:

Database=MyDatabase;Server=COMP\SQLEXPRESS;User ID=sa;

The error I get is "Login failed for sa"

I have tried other users but no luck. Also, I cant seem to create a new user as I don't have administrators rights, any ideas how i can do this?

Any ideas?

did you try it this way

<add name="CommerceTemplate" connectionString="Data Source=Comp.Name\SQLEXPRESS;Initial Catalog=dbname;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />

hope this helps

|||

see

www.connectionstrings.com

for help

|||

Hi,

From your description, it seems that you are using "sa" account to log in your SQLServer, that is to say you have set your SQLServer as "SQL Mixed Authentication mode", right?

Based on the error message, there are two possibilities. First, try to log into your SQLServer by SQLServer Management Studio with the 'sa' account, to see if it can work. If not, I guess the account and password maybe invalid. Second, if you are using Windows Authentication mode, you should also make your connection string looks like:

"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Thanks.

Connecting to an SQL Express Server via IP

Hi Everyone,
I am trying to connect to an SQL Express database via IP. The problem I am having is with the connection string itself. Currently I am using the below:

mysqlconn.ConnectionString = "Data Source=255.255.255.255,1433;Network Library=DBMSSOCN;Initial Catalog=images.mdf"

The problem is the program just freezes up. I double checked the port number and IP address and I know those are correct. I'm using Windows Authentication so I believe I don't need to put my UID and PWORD in there do I?

Can anyone help me with what I am missing

255.255.255.255 is not a valid IP address. To find out the IP address of your server, login to the server and run

ipconfig

and look at the value in the line called "IP Address".

|||I was just putting 255.255.255.255 in there because I didn't want to reveal my actual IP address. I am putting the correct IP in there, I'm just not actually connecting.
|||

Sure, makes sense.

Does the program freeze forever? Does it return any error?

What happens if you run

telnet <ipAddress> 1433

If you are connecting to a SQL Server database, the initial catalog is the name of the database you created earlier and want to use, generally without the ".mdf" suffix.

If you want to use Windows Authentication ("integrated security") you should specify explicitly:

"Integrated Security=true"

The default is false.

|||I think my problem is that I dont have the SQL server allowing remote connections :)

Whoops- sorry about that.

Connecting to a named SQL2000 db instance from vbscript

My previous connection string looked like this:

strConnect = "Provider=SQLOLEDB.1;Password=Password=password;Per sistSecurity Info=True;User ID=TestUser;Initial Catalog=JabilDB;DataSource=corsqlv02;Use Procedure for Prepare=0"

The new connection string (that doesn't work) looks like this:

strConnect = "Provider=SQLOLEDB.1;Password=Password=password;Per sistSecurity Info=True;User ID=TestUser;Initial Catalog=JabilDBDev;DataSource=corsqlv02\JabilDB;Us e Procedure for Prepare=0"

Do i need to upgrade to the latest MDAC ?

Thanks

Digital_fXForget this. I just needed to install MDAC 2.6 and that solved the problem.

Cheers

connecting through C#

When I look at the connection string it's:

Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ProcBuilder.mdf;Integrated Security=True;User Instance=True

This works fine on my machine, but when I try to run it on another computer it tells me:

"An error has occurred while

establishing a connection to the server. When connecting to SQL Server

2005, this failure may be caused by the fact that under the default

settings SQL Server does not allow remote connections."

It should not be trying to connect remotely, it's a local DB. Do I

have to install SQL Express on the machine I'm installing this on for

it to work?

hi,

data is not local to the client machine but to the machine providing the service, the machine with SQL Server (SQLExpress) installed.. and, of course, the relative database files have to be stored on that pc...

so, if you have to connect to a remote SQLExpress instance, you should modify the connection string accordingly to the "remote" SQLExpress instance..
if .\SQExpress works for a local connection, a remote one should "point" to the computer name, thus ComputerName\SQLExpress, and this is a "general" thumb for SQL Server connections...

but you are also dealing User Instance facilities, and http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic8 points out only "local connections" are supported.. you can overcome this hacking the pipe the service is listening on, but you should go for a more "traditional" use of a SQL Server engine..

or, you just install SQLExpress (and your user database(s)) on every each machine and continue using User Instance feature "locally"..

regards

connecting AS400

hi all,
we are running a dts that migrates data from an as400 to sql 2K.
the string data is hebrew (code page 1252).
for that, we have been using a hit odbc:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
untill now we were running the dts from sql 7.0 and that worked well.
the porblem started after copying (and then rewriting) the package to sql 2k
:
when running the dts manually (execute package) it's o.k, but when running
the dts via a job the hebrew characters are being mirrored in the sql 2k.
(e.g, written from left to write instead of right to left).
again, in sql 7 it worked well both ways.
some details:
-in both cases (job and manually) we execute the package from the same serve
r.
-the win login account used for manual executing is identical to the log on
accounts of server service and server agent.
we are using a system dsn.
I have found in a newsgroup a tip to solve that: using a user dsn.
but when I tried that another porblem came up:
when running manualy - it was, again, ok
but when running via a job (or with dtsrun) the server couldn't find the odb
c.
here is the error massage:
DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun
OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Driver Manager] Data source name not found
and no
default driver specified Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0Error Detail Records:Error:
-2147467259 (80004005); Provider Error: 0 (0) Error string: Error
source: Help file: Help context: 0DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
so.. any help will be usefull (regarding the hebrew mirroring or regarding
finding the user dsn)
thanks,
elad.Elad, shalom
I assume you got connected by using Client Access to the AS400.
Look , first of all it depends on lot of things
1) Right click on the server within a package and press "Advance" button.
Under Property name you will find Auto Translate=1 or =0. Try to play with
it.
" " <@.discussions.microsoft.com> wrote in message
news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
> hi all,
> we are running a dts that migrates data from an as400 to sql 2K.
> the string data is hebrew (code page 1252).
> for that, we have been using a hit odbc:
> ODBC OPTIMIZED (READ ONLY) 32 BIT
> VERSION 4.00.06.20
> HIT SOFTWARE, INC
> untill now we were running the dts from sql 7.0 and that worked well.
> the porblem started after copying (and then rewriting) the package to sql
> 2k:
> when running the dts manually (execute package) it's o.k, but when running
> the dts via a job the hebrew characters are being mirrored in the sql 2k.
> (e.g, written from left to write instead of right to left).
> again, in sql 7 it worked well both ways.
> some details:
> -in both cases (job and manually) we execute the package from the same
> server.
> -the win login account used for manual executing is identical to the log
> on
> accounts of server service and server agent.
> we are using a system dsn.
> I have found in a newsgroup a tip to solve that: using a user dsn.
> but when I tried that another porblem came up:
> when running manualy - it was, again, ok
> but when running via a job (or with dtsrun) the server couldn't find the
> odbc.
> here is the error massage:
> DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1DTSRun
> OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
> Error
> string: [Microsoft][ODBC Driver Manager] Data source name not fou
nd and
> no
> default driver specified Error source: Microsoft OLE DB Provider for
> ODBC
> Drivers Help file: Help context: 0Error Detail Records:Error:
> -2147467259 (80004005); Provider Error: 0 (0) Error string: Error
> source: Help file: Help context: 0DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
> so.. any help will be usefull (regarding the hebrew mirroring or regarding
> finding the user dsn)
> thanks,
> elad.
>
>
>
>|||hi Uri,
well, I used the HIT odbc for as400.
here are it's details again:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
and, for that odbc I couldn't find a auto translate property.
I also looked for a download of the client access odbc but couldn't find it.
so... I will thakfull for a link
thanks,
elad.
"Uri Dimant" wrote:

> Elad, shalom
> I assume you got connected by using Client Access to the AS400.
> Look , first of all it depends on lot of things
> 1) Right click on the server within a package and press "Advance" button.
> Under Property name you will find Auto Translate=1 or =0. Try to play with
> it.
>
>
> "_ìò? ùì?_" <@.discussions.microsoft.com> wrote in message
> news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
>
>

connecting AS400

hi all,
we are running a dts that migrates data from an as400 to sql 2K.
the string data is hebrew (code page 1252).
for that, we have been using a hit odbc:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
untill now we were running the dts from sql 7.0 and that worked well.
the porblem started after copying (and then rewriting) the package to sql 2k:
when running the dts manually (execute package) it's o.k, but when running
the dts via a job the hebrew characters are being mirrored in the sql 2k.
(e.g, written from left to write instead of right to left).
again, in sql 7 it worked well both ways.
some details:
-in both cases (job and manually) we execute the package from the same server.
-the win login account used for manual executing is identical to the log on
accounts of server service and server agent.
we are using a system dsn.
I have found in a newsgroup a tip to solve that: using a user dsn.
but when I tried that another porblem came up:
when running manualy - it was, again, ok
but when running via a job (or with dtsrun) the server couldn't find the odbc.
here is the error massage:
DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun
OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0Error Detail Records:Error:
-2147467259 (80004005); Provider Error: 0 (0) Error string: Error
source: Help file: Help context: 0DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
so.. any help will be usefull (regarding the hebrew mirroring or regarding
finding the user dsn)
thanks,
elad.Elad, shalom
I assume you got connected by using Client Access to the AS400.
Look , first of all it depends on lot of things
1) Right click on the server within a package and press "Advance" button.
Under Property name you will find Auto Translate=1 or =0. Try to play with
it.
"àìòã ùìåí" <@.discussions.microsoft.com> wrote in message
news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
> hi all,
> we are running a dts that migrates data from an as400 to sql 2K.
> the string data is hebrew (code page 1252).
> for that, we have been using a hit odbc:
> ODBC OPTIMIZED (READ ONLY) 32 BIT
> VERSION 4.00.06.20
> HIT SOFTWARE, INC
> untill now we were running the dts from sql 7.0 and that worked well.
> the porblem started after copying (and then rewriting) the package to sql
> 2k:
> when running the dts manually (execute package) it's o.k, but when running
> the dts via a job the hebrew characters are being mirrored in the sql 2k.
> (e.g, written from left to write instead of right to left).
> again, in sql 7 it worked well both ways.
> some details:
> -in both cases (job and manually) we execute the package from the same
> server.
> -the win login account used for manual executing is identical to the log
> on
> accounts of server service and server agent.
> we are using a system dsn.
> I have found in a newsgroup a tip to solve that: using a user dsn.
> but when I tried that another porblem came up:
> when running manualy - it was, again, ok
> but when running via a job (or with dtsrun) the server couldn't find the
> odbc.
> here is the error massage:
> DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1DTSRun
> OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
> Error
> string: [Microsoft][ODBC Driver Manager] Data source name not found and
> no
> default driver specified Error source: Microsoft OLE DB Provider for
> ODBC
> Drivers Help file: Help context: 0Error Detail Records:Error:
> -2147467259 (80004005); Provider Error: 0 (0) Error string: Error
> source: Help file: Help context: 0DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
> so.. any help will be usefull (regarding the hebrew mirroring or regarding
> finding the user dsn)
> thanks,
> elad.
>
>
>
>|||hi Uri,
well, I used the HIT odbc for as400.
here are it's details again:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
and, for that odbc I couldn't find a auto translate property.
I also looked for a download of the client access odbc but couldn't find it.
so... I will thakfull for a link
thanks,
elad.
"Uri Dimant" wrote:
> Elad, shalom
> I assume you got connected by using Client Access to the AS400.
> Look , first of all it depends on lot of things
> 1) Right click on the server within a package and press "Advance" button.
> Under Property name you will find Auto Translate=1 or =0. Try to play with
> it.
>
>
> "à ìòã ùìåí" <@.discussions.microsoft.com> wrote in message
> news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
> > hi all,
> > we are running a dts that migrates data from an as400 to sql 2K.
> > the string data is hebrew (code page 1252).
> > for that, we have been using a hit odbc:
> > ODBC OPTIMIZED (READ ONLY) 32 BIT
> > VERSION 4.00.06.20
> > HIT SOFTWARE, INC
> >
> > untill now we were running the dts from sql 7.0 and that worked well.
> > the porblem started after copying (and then rewriting) the package to sql
> > 2k:
> > when running the dts manually (execute package) it's o.k, but when running
> > the dts via a job the hebrew characters are being mirrored in the sql 2k.
> > (e.g, written from left to write instead of right to left).
> > again, in sql 7 it worked well both ways.
> >
> > some details:
> > -in both cases (job and manually) we execute the package from the same
> > server.
> > -the win login account used for manual executing is identical to the log
> > on
> > accounts of server service and server agent.
> > we are using a system dsn.
> >
> > I have found in a newsgroup a tip to solve that: using a user dsn.
> > but when I tried that another porblem came up:
> > when running manualy - it was, again, ok
> > but when running via a job (or with dtsrun) the server couldn't find the
> > odbc.
> > here is the error massage:
> >
> > DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
> > DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
> > DTSStep_DTSExecuteSQLTask_2DTSRun OnStart:
> > DTSStep_DTSDataPumpTask_1DTSRun
> > OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
> > Error
> > string: [Microsoft][ODBC Driver Manager] Data source name not found and
> > no
> > default driver specified Error source: Microsoft OLE DB Provider for
> > ODBC
> > Drivers Help file: Help context: 0Error Detail Records:Error:
> > -2147467259 (80004005); Provider Error: 0 (0) Error string: Error
> > source: Help file: Help context: 0DTSRun OnFinish:
> > DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
> >
> > so.. any help will be usefull (regarding the hebrew mirroring or regarding
> > finding the user dsn)
> >
> > thanks,
> > elad.
> >
> >
> >
> >
> >
> >
> >
>
>

connecting AS400

hi all,
we are running a dts that migrates data from an as400 to sql 2K.
the string data is hebrew (code page 1252).
for that, we have been using a hit odbc:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
untill now we were running the dts from sql 7.0 and that worked well.
the porblem started after copying (and then rewriting) the package to sql 2k:
when running the dts manually (execute package) it's o.k, but when running
the dts via a job the hebrew characters are being mirrored in the sql 2k.
(e.g, written from left to write instead of right to left).
again, in sql 7 it worked well both ways.
some details:
-in both cases (job and manually) we execute the package from the same server.
-the win login account used for manual executing is identical to the log on
accounts of server service and server agent.
we are using a system dsn.
I have found in a newsgroup a tip to solve that: using a user dsn.
but when I tried that another porblem came up:
when running manualy - it was, again, ok
but when running via a job (or with dtsrun) the server couldn't find the odbc.
here is the error massage:
DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun
OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0Error Detail Records:Error:
-2147467259 (80004005); Provider Error: 0 (0) Error string: Error
source: Help file: Help context: 0DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
so.. any help will be usefull (regarding the hebrew mirroring or regarding
finding the user dsn)
thanks,
elad.
Elad, shalom
I assume you got connected by using Client Access to the AS400.
Look , first of all it depends on lot of things
1) Right click on the server within a package and press "Advance" button.
Under Property name you will find Auto Translate=1 or =0. Try to play with
it.
" " <@.discussions.microsoft.com> wrote in message
news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
> hi all,
> we are running a dts that migrates data from an as400 to sql 2K.
> the string data is hebrew (code page 1252).
> for that, we have been using a hit odbc:
> ODBC OPTIMIZED (READ ONLY) 32 BIT
> VERSION 4.00.06.20
> HIT SOFTWARE, INC
> untill now we were running the dts from sql 7.0 and that worked well.
> the porblem started after copying (and then rewriting) the package to sql
> 2k:
> when running the dts manually (execute package) it's o.k, but when running
> the dts via a job the hebrew characters are being mirrored in the sql 2k.
> (e.g, written from left to write instead of right to left).
> again, in sql 7 it worked well both ways.
> some details:
> -in both cases (job and manually) we execute the package from the same
> server.
> -the win login account used for manual executing is identical to the log
> on
> accounts of server service and server agent.
> we are using a system dsn.
> I have found in a newsgroup a tip to solve that: using a user dsn.
> but when I tried that another porblem came up:
> when running manualy - it was, again, ok
> but when running via a job (or with dtsrun) the server couldn't find the
> odbc.
> here is the error massage:
> DTSRun: Loading...DTSRun: Executing...DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1DTSRun
> OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
> Error
> string: [Microsoft][ODBC Driver Manager] Data source name not found and
> no
> default driver specified Error source: Microsoft OLE DB Provider for
> ODBC
> Drivers Help file: Help context: 0Error Detail Records:Error:
> -2147467259 (80004005); Provider Error: 0 (0) Error string: Error
> source: Help file: Help context: 0DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1DTSRun: Package execution complete.NULL
> so.. any help will be usefull (regarding the hebrew mirroring or regarding
> finding the user dsn)
> thanks,
> elad.
>
>
>
>
|||hi Uri,
well, I used the HIT odbc for as400.
here are it's details again:
ODBC OPTIMIZED (READ ONLY) 32 BIT
VERSION 4.00.06.20
HIT SOFTWARE, INC
and, for that odbc I couldn't find a auto translate property.
I also looked for a download of the client access odbc but couldn't find it.
so... I will thakfull for a link
thanks,
elad.
"Uri Dimant" wrote:

> Elad, shalom
> I assume you got connected by using Client Access to the AS400.
> Look , first of all it depends on lot of things
> 1) Right click on the server within a package and press "Advance" button.
> Under Property name you will find Auto Translate=1 or =0. Try to play with
> it.
>
>
> "Xìò? ùì?X" <@.discussions.microsoft.com> wrote in message
> news:12A0B201-3D38-4626-9DD1-155623940393@.microsoft.com...
>
>

connect using perl (solaris platform)

I need to connect to Express edition of sql server installed on my machine, from perl (solaris platform). How should the connection string containing server, port, database etc look like ?

Can any one tell me how to find the *port number* that the local server is using.

|||

hi,

[quote user]

I need to connect to Express edition of sql server installed on my machine, from perl (solaris platform). How should the connection string containing server, port, database etc look like ?

please have a look at connection string samples..

Can any one tell me how to find the *port number* that the local server is using.

you can find the used port via SQL Server Configuration Manager.. select the SQL Server 2005 Network configurations node, which expands in several instances nodes.. select the appropriate instance node.. in the right pane, network protocols will be listed (in order to be available, the desired network protocol must be "enabled")...

select TCP/IP, rx click and access it's properties.. in the IP Addresses tab the used port will be listed.. if dynamic port is set, the instance is configured for dynamic assigned address and this value "could" change at next instance's startup as the instance will try, at it's startup, to get the old used port but it could be relocated to alternative one if the old port has been used (in the meanwhile) by other services.. in case of dynamic assigned ports, the SQL Server Browser, a service listening on port UDP 1434, is required to resolve remote connections..

the SQL Server Browser intercepts incoming connections and, after inspecting the local registry for the dynamic assigned port used by the instance, redirects the remote connection(s) to the appropriate port where the instance is listening on..

regards

|||UsHaving a single instance , use

netstat -a -b at the command prompt and look for the sqlservr.exe process

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

>> you can find the used port via SQL Server Configuration Manager

I could not find Configuration Manager in the management studio... where can I find that.

Thanks,

|||

hi,

it's not in Management Studio.. it's in the Microsoft SQL Server 2005 -> Configuration tools folder..

regards