Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker
"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
sqlsql
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Tuesday, March 27, 2012
Consistency of row when reading with READ UNCOMMITTED
Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
--
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
--
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
Consistency of row when reading with READ UNCOMMITTED
Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
Sunday, March 25, 2012
conntectionString Question Please
I'm having problems connecting Efficon's Advanced DataGrid to the database in DNN3.
Can someone help me with the syntax for the connection string?
Thanks in advance,
ScottTry http://www.connectionstrings.com for help on connection strings.
|||Resolution:
Connection Sting
Provder=SQLOLEDB
Trusted_Connection= yes (trusted_connection=true did not work)
sqlsql
Can someone help me with the syntax for the connection string?
Thanks in advance,
ScottTry http://www.connectionstrings.com for help on connection strings.
|||Resolution:
Connection Sting
Provder=SQLOLEDB
Trusted_Connection= yes (trusted_connection=true did not work)
sqlsql
Connectivty problem from NON domain users
I have a problem esteblishing a connection to a SQL server with a SQL user,
while I am logged to the computer with a local computer.
Any domain account can has no problem.
What can ause this?
Thanks in advance,
Ido Friedman
What tool are you connecting from?
How are you trying to connect?
Do you know for sure that you are trying to connect via SQL Authentication?
Have you tried hitting the Data Sources (ODBC) applet within the Control
Panel and creating a test connection?
Keith Kratochvil
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>I have a problem esteblishing a connection to a SQL server with a SQL user,
> while I am logged to the computer with a local computer.
> Any domain account can has no problem.
> What can ause this?
> Thanks in advance,
> Ido Friedman
|||Do you have mixed mode authentication enabled?
To enable...
SQL 2005
In SQL Server Management Studio Object Explorer, right-click your server,
and then click Properties.
On the Security page, under Server authentication, select the new server
authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK, to acknowledge the
need to restart SQL Server.
SQL 2000
Expand a server group.
Right-click a server, and then click Properties.
Click the Security tab.
Under Authentication, click SQL Server and Windows.
Under Audit level, select the level at which user accesses to Microsoft SQL
ServerT are recorded in the SQL Server error log:
None causes no auditing to be performed.
Success causes only successful login attempts to be audited.
Failure causes only failed login attempts to be audited.
All causes successful and failed login attempts to be audited.
Hope that helps...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>I have a problem esteblishing a connection to a SQL server with a SQL user,
> while I am logged to the computer with a local computer.
> Any domain account can has no problem.
> What can ause this?
> Thanks in advance,
> Ido Friedman
|||Thanks for your reply,
I am using the following ASP page:
The page is running in IIS under the local user IUser_computername.
When I run the same code with a domain account it successeds.
Thanks in advance,
Ido Friedman
<%
Dim dataSource
On Error Resume Next
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA
SOURCE=servername";UID=user;PWD=user;DATABASE=nort hwind"
If err.number = 0 Then
Response.Write(cnn.State)
cnn.Close
Else
Response.Write( err.Description )
End If
Response.End
%>
"Warren Brunk" wrote:
> Do you have mixed mode authentication enabled?
> To enable...
> SQL 2005
> In SQL Server Management Studio Object Explorer, right-click your server,
> and then click Properties.
> On the Security page, under Server authentication, select the new server
> authentication mode, and then click OK.
> In the SQL Server Management Studio dialog box, click OK, to acknowledge the
> need to restart SQL Server.
>
> SQL 2000
> Expand a server group.
>
> Right-click a server, and then click Properties.
>
> Click the Security tab.
>
> Under Authentication, click SQL Server and Windows.
>
> Under Audit level, select the level at which user accesses to Microsoft? SQL
> ServerT are recorded in the SQL Server error log:
> None causes no auditing to be performed.
>
> Success causes only successful login attempts to be audited.
>
> Failure causes only failed login attempts to be audited.
>
> All causes successful and failed login attempts to be audited.
>
> Hope that helps...
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
> news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>
>
while I am logged to the computer with a local computer.
Any domain account can has no problem.
What can ause this?
Thanks in advance,
Ido Friedman
What tool are you connecting from?
How are you trying to connect?
Do you know for sure that you are trying to connect via SQL Authentication?
Have you tried hitting the Data Sources (ODBC) applet within the Control
Panel and creating a test connection?
Keith Kratochvil
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>I have a problem esteblishing a connection to a SQL server with a SQL user,
> while I am logged to the computer with a local computer.
> Any domain account can has no problem.
> What can ause this?
> Thanks in advance,
> Ido Friedman
|||Do you have mixed mode authentication enabled?
To enable...
SQL 2005
In SQL Server Management Studio Object Explorer, right-click your server,
and then click Properties.
On the Security page, under Server authentication, select the new server
authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK, to acknowledge the
need to restart SQL Server.
SQL 2000
Expand a server group.
Right-click a server, and then click Properties.
Click the Security tab.
Under Authentication, click SQL Server and Windows.
Under Audit level, select the level at which user accesses to Microsoft SQL
ServerT are recorded in the SQL Server error log:
None causes no auditing to be performed.
Success causes only successful login attempts to be audited.
Failure causes only failed login attempts to be audited.
All causes successful and failed login attempts to be audited.
Hope that helps...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>I have a problem esteblishing a connection to a SQL server with a SQL user,
> while I am logged to the computer with a local computer.
> Any domain account can has no problem.
> What can ause this?
> Thanks in advance,
> Ido Friedman
|||Thanks for your reply,
I am using the following ASP page:
The page is running in IIS under the local user IUser_computername.
When I run the same code with a domain account it successeds.
Thanks in advance,
Ido Friedman
<%
Dim dataSource
On Error Resume Next
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA
SOURCE=servername";UID=user;PWD=user;DATABASE=nort hwind"
If err.number = 0 Then
Response.Write(cnn.State)
cnn.Close
Else
Response.Write( err.Description )
End If
Response.End
%>
"Warren Brunk" wrote:
> Do you have mixed mode authentication enabled?
> To enable...
> SQL 2005
> In SQL Server Management Studio Object Explorer, right-click your server,
> and then click Properties.
> On the Security page, under Server authentication, select the new server
> authentication mode, and then click OK.
> In the SQL Server Management Studio dialog box, click OK, to acknowledge the
> need to restart SQL Server.
>
> SQL 2000
> Expand a server group.
>
> Right-click a server, and then click Properties.
>
> Click the Security tab.
>
> Under Authentication, click SQL Server and Windows.
>
> Under Audit level, select the level at which user accesses to Microsoft? SQL
> ServerT are recorded in the SQL Server error log:
> None causes no auditing to be performed.
>
> Success causes only successful login attempts to be audited.
>
> Failure causes only failed login attempts to be audited.
>
> All causes successful and failed login attempts to be audited.
>
> Hope that helps...
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
> news:934F22F5-2624-49B2-AC35-1970C1AE876D@.microsoft.com...
>
>
Connectivity Issues in AMO
Hi,
I am using AMO to connect to SSAS 2005 and programmatically create objects. currently I am specifying only the ServerName in the connection string. Please see the code below:
<code>
Server SSASServer = new Microsoft.AnalysisServices.Server();
SSASServer.Connect("DataSource=" + ServerName);
</code>
The above code works fine and I am able to connect. Now I want to provide the instance name of the server and also the server name. because tomorrow we will have multiple instances of Analysis server running on the same server. I read somewhere that we need to specify ServerName\InstanceName.
SSASServer.Connect(ServerName + "\\" + InstanceName);
So I tried the above, with both the serverName and InstanceName being the same for now. (currently only one instance is running). but that is not working, whenever I do that I get the error: I also removed the string "DataSoruce=", still I get the same error.
{"A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running."}
1. How to identify the ServerName and the InstanceName?
2. How to specify them in the connection string?
3. Currently I am using Windows Integrated Authentication, how do i specify username and password to connect to SSAS 2005.
Please do provide any information possible.
Thanks and Regards
Vijay R
Connection to the default instance could only be made by using ServerName.
Anything you specify after the '\' will be considered as an instance name. meaning client will try to find an AS named instance with such name.
In your experiment above, it will try to find a named instance and will fail because you dont have it installed.
Get your named instance installed and connect to it using ServerName\InstanceName where InstanceName you provide during setup of Analysis Server.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
Thanks a lot Melomed,
That certainly helped me.
But what about specfying the Username and password for connection.
That is if I do not want to use Windows Integrated Authentication for SSAS in AMO.
Regards
Vijay R
|||
When using TCP/IP to connect to Analysis Server , Windows authentication is your only choice.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Melomed,
I was succesful in implementing and executing teh AMO based app that creates Cubes, Dimensions, MeasureGroups, Measures and Partitions.
but I still have the problem of connecting to an named instance of the SSAS server. I now have a named instance of SSAS.
I am also able to conect to it using the BI studio /Visual Studio 2005. After connecting the summary/properties display ServerName\InstanceName.
but when i try this programmatically using AMO, it throws the below error. (the same one!)
{"A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running."}
Here is the code:
string tempConnstring = "DataSource=" + SSASServerName + @."\" + SSASInstanceName;
SSASServer.Connect(tempConnstring);
-
Please do help me as to how I should be doing this?
Thanks and Regards
Vijay R
|||It is not "DataSource="
But rather "Data Source="
You are missing a space there.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, March 22, 2012
Connectivity Issue with SQL Server 2005
Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
Connectivity Issue with SQL Server 2005
Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
--
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
--
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
--
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
--
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
Connectivity Issue with SQL Server 2005
Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
BUTTONS:
OK
"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegro ups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
BUTTONS:
OK
"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegro ups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David
Connectivity issue
I am using the connection string as
"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TMS_CHECK_CONVERSION;Data Source=PHILDCAP4Q\VENDOR" in
specified named instance is installed on the Respective machine.
It is giving the following error.,
Sqlserver does not exist or access denied.,
Please suggest ...?
Are you using Windows authentication on your SQL Server instance? If yes, is your SQL Server a member server in a domain?|||Yes I am using windows authentication only also sql server is a member server in the domain.
still I am getting the same error.
Any help is appericated.
|||Is this SQL Server 2000 or 2005? If it's SS2005, is your sql browser running? What are the protocols enabled on the server? Any firewall issue? You'd better follow the instruction here and give us more details. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1
|||
Named instance of SQL server is installed on Windows 2003 server.
And we are trying to connect to .Net with the follwoing connection string.,
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>
we are getting the error as "SQL Server does not exist or access denied.
The information
[1] Client side:
What is the connection string in you app or DSN? (please specify)
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>
If client fails to connect, what is the client error messages? (please specify)
Sql Server does not exist or access denied
Is the client remote or local to the SQL server machine? [Remote | Local]
Remote
Can you ping your server? [YES | NO ]
In cmd.exe console, type “ping -a <server_name>”.
Yes.
Can you telnet to your SQL Server? [YES | NO, please specify the error message ]
In cmd.exe console, type “telnet <server name> port, where port can be 135, 445 or sql_server_tcp_port. If your cmd.exe console turns into a complete black screen with a cursor flushing on top left corner, you are connected. Type ctrl+’[‘ to bring up telnet prompt and type “quit” <enter>.
Yes
What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] Or/And, what is your client application? [SQL Management Studio | SQL Profiler | Visual Studio | Other (please specify).
MDAC Ver 2.8
Is your client computer in the same domain as the Server computer? (Same domain | Different domains | WorkGroup)
Yes
What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes].
TCP/IP and Named Pipes
Do you have aliases configured that match the server name portion of your connection string? If so, please check if it is correct. You can use cliconfg.exe (SS 2000) or SQL Server Configuration Manager (SS 2005) to configure the alias.
No alias
Do you select force encryption on server and/or client?
No, theser are unchecked on both client server
[2] Server side:
What is the MS SQL version? [ SQL Server 2005 | SQL Sever 2005 ]
Sql server 2000 sp 4.
What is the SKU of MS SQL? [Enterprise | Standard | Workgroup | Express (or MSDE) | other (please specify)].
Enterprise
What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. Use SQL Server Configuration Manager to configure it and check ERRORLOG or event log to confirm.
TCP/IP and Named Pipes
Does the server start successfully? [YES | NO] If not what is the error messages in the SQL server ERRORLOG?
Yes.
If SQL Server is a named instance, is the SQL browser enabled? [YES | NO]
We are using sql server 2000, sp4.
What is the account that the SQL Server is running under?[Local System | Network Service | Domain Account]
Local system
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES | NO | not applicable]
Don't know how to check this., Whether this is applicable for sql server 2000 also.,
Do you make firewall exception for SQL Browser UDP port 1434? [YES | NO | not applicable ]
Don't know how to check this., Whether this is applicable for sql server 2000 also.,
[3] Platform:
What is the OS version? [Windows XPSP2 | Windows 2003 | Windows 2000 | Windows 98 | others (please specify ) ].
Windows server 2003
Do you have third party antivirus, anti-spareware software installed? [Symantec | Norton | other (please specify)].
Yes. But I am not sure which is installed on customer's place.,
Thanks a lot.,
Any Help is really appriciated.
|||Chugupta,
"Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES | NO | not applicable]
Don't know how to check this., Whether this is applicable for sql server 2000 also.,
Do you make firewall exception for SQL Browser UDP port 1434? [YES | NO | not applicable ]
Don't know how to check this., Whether this is applicable for sql server 2000 also.,
"
You need to check both for your SQL2000 server. For SQL2000, you still need to make sure that your tcp port (Use ERRORLOG to check the value of the TCP port that your server is listening on) and UDP 1434 port are not blocked by the firewall. Mostlikely, they are blocked by firewall setting.
The following KB can give you a clue.
http://support.microsoft.com/?kbid=914277&SD=tech
|||
The most astonish thing here is., we are able to connect through delphi code and we are doing database operations. But when we are trying to connect the sql server through .Net it is throwing an exception sql server does not exist or access denied.
|||Can you post the exact connection string? I hope you are not using <>.
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>; Data Source= <Sql Server Installed Machine Name>\<Sql server instance Name>
|||The connection string which I am using is
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTest; Data Source= ServerPC\TestInstance
DBTest is the database name
ServerPC is the system where the sql server is installed
TestInstance is the name instance of sql server on ServerPc
Sql server is installed on Windows 2003 Server.
Using the above conncetion string I am able to connect the sql server using Delphi but not with .Net.,
When I am trying to connect through VB.Net it is throwing an exception "SQL Server does not exist or access denied.
|||Thanks for confirmation. I'm not sure how Delphi works, but can you make sure UDP port 1434 is not blocked? See the above post by Nan.
Connectivity in between SQL Server and DB2
I would like to know whether a connection can established in between SQL
Server and DB2. If yes how?Hi!!
This may help you(Pls note that I have not tested this)
http://groups.google.com/group/micr...b72d654e19e7a51
and
http://support.microsoft.com/kb/q222937/
--
Thanks,
Umesh
"VBB" wrote:
> I would like to know whether a connection can established in between SQL
> Server and DB2. If yes how?
>
Server and DB2. If yes how?Hi!!
This may help you(Pls note that I have not tested this)
http://groups.google.com/group/micr...b72d654e19e7a51
and
http://support.microsoft.com/kb/q222937/
--
Thanks,
Umesh
"VBB" wrote:
> I would like to know whether a connection can established in between SQL
> Server and DB2. If yes how?
>
Connectivity from Client to Server
Hai,
I am often getting the message
"SQL Server does not exist or access denied (Connection Open (Connect())"
when connecting from the client sql 2000 to the server and at sometimes it gets connected and it still exists/persists for some time.
though i checked the user rights, client server network utility where tcp/ip is the first protocol.
Give a (me) way to connect to the server, though the user iam trying to connect works in the Query Analyser in the Server
here,
vijayanand.gI know this a post from a previous post. I would try sql profiler and monitor the connections to see if the server is rejecting you or are you not getting there at all. You can use vnc or terminal services to connect to the server (for a good connection) and monitor your connection status.
Let me know.|||Hai,
Though i connect from the profiler the Server gives an error
here,
vijayanand.g|||Is the server behind any firewall?
Originally posted by g_vijayanand
Hai,
Though i connect from the profiler the Server gives an error
here,
vijayanand.g|||You need to execute the profiler either on the sql server or through a remote connection (using vnc or terminal services). Then try to connect on your machine and see what the profiler shows.|||Sorry - I just received your emails - I only have access to my email at home. Basically, you will need to find a machine that has access to the sql server (or login to the actual sql server) and run the profiler and see if at least the server sees that you are attempting to hit it.|||Hai,
The profiler does not connect and trace the required samples.
here,
vijayanand.g|||Hai,
I tried to connect from other machine in sql enterprise manager and query analyser for the specified user, it connects - but through my login in the domain it does not connect.
For the past 1 week iam trying to connect from a XP Machine , it does not connect, i tried to connect from a Windows 98 machine it connects, from a machine that has been logged as an administrator it connects, now i've changed my machines OS to Windows NT Server - now also not connecting .
here,
vijayanand.g|||Hai,
Connected to the Server. It was due to the entry that did not exist for the specified machine in the Hosts file or LmHosts file for the specified machine name.
The connectivity came when the connection was tested with the server by registering the Client by giving the ip address of the machine.
here,
vijayanand.g
I am often getting the message
"SQL Server does not exist or access denied (Connection Open (Connect())"
when connecting from the client sql 2000 to the server and at sometimes it gets connected and it still exists/persists for some time.
though i checked the user rights, client server network utility where tcp/ip is the first protocol.
Give a (me) way to connect to the server, though the user iam trying to connect works in the Query Analyser in the Server
here,
vijayanand.gI know this a post from a previous post. I would try sql profiler and monitor the connections to see if the server is rejecting you or are you not getting there at all. You can use vnc or terminal services to connect to the server (for a good connection) and monitor your connection status.
Let me know.|||Hai,
Though i connect from the profiler the Server gives an error
here,
vijayanand.g|||Is the server behind any firewall?
Originally posted by g_vijayanand
Hai,
Though i connect from the profiler the Server gives an error
here,
vijayanand.g|||You need to execute the profiler either on the sql server or through a remote connection (using vnc or terminal services). Then try to connect on your machine and see what the profiler shows.|||Sorry - I just received your emails - I only have access to my email at home. Basically, you will need to find a machine that has access to the sql server (or login to the actual sql server) and run the profiler and see if at least the server sees that you are attempting to hit it.|||Hai,
The profiler does not connect and trace the required samples.
here,
vijayanand.g|||Hai,
I tried to connect from other machine in sql enterprise manager and query analyser for the specified user, it connects - but through my login in the domain it does not connect.
For the past 1 week iam trying to connect from a XP Machine , it does not connect, i tried to connect from a Windows 98 machine it connects, from a machine that has been logged as an administrator it connects, now i've changed my machines OS to Windows NT Server - now also not connecting .
here,
vijayanand.g|||Hai,
Connected to the Server. It was due to the entry that did not exist for the specified machine in the Hosts file or LmHosts file for the specified machine name.
The connectivity came when the connection was tested with the server by registering the Client by giving the ip address of the machine.
here,
vijayanand.g
Connectivity Book
Hi,
Can someone please guide for a book that speaks in depth about connectivity issues and bugs related to SQL server connection objects.
I am also looking for a good getting started(and in depth) guide/resourse for following components....
MDAC Architecture & Installation
SQL Server Profiler.................................................................................................. 5
SQL Server Security................................................................................................. 7
Indexes..................................................................................................................... 8
Network Libraries and Connectivity........................................................................... 9
Linked Servers........................................................................................................ 11
Microsoft JET Basics........................................................................................................ 1
Access and Jet.................................................................................................................. 2
JET Provider 3.51 vs. 4.0.................................................................................................. 2
Unrecognized Database Format with Data Control............................................................. 3
Access ODBC driver and ASP – Just say No!.................................................................. 3
JET Installation.................................................................................................................. 3
............................................................................................................................. 1
Cursors; Location and Type.............................................................................................. 2
Objectives................................................................................................................. 2
What is a cursor?...................................................................................................... 2
Simple ADO Code.................................................................................................... 3
Default Values........................................................................................................... 3
FireHose Cursor....................................................................................................... 4
CursorType Property Values..................................................................................... 4
Static Cursor............................................................................................................. 5
Keyset Cursor........................................................................................................... 5
Dynamic Cursor........................................................................................................ 6
Limitations of CursorType......................................................................................... 6
CursorLocation......................................................................................................... 7
Server Side Recordsets............................................................................................. 7
Client Side Recordsets.............................................................................................. 8
Questions to ask........................................................................................................ 9
Guidelines............................................................................................................... 10
Knowledge Base Articles........................................................................................ 10
Resources............................................................................................................... 10
COM Basics....................................................................................................................... 1
COM – A Binary Component Architecture........................................................................ 4
COM Defined....................................................................................................... 4
Summary of the limitations of traditional object oriented techniques......................... 4
How COM Works............................................................................................................ 5
Using COM Objects............................................................................................. 5
Objects and classes................................................................................................... 7
Objects and interfaces............................................................................................... 7
Interface – a Contract................................................................................................ 7
Interfaces.......................................................................................................................... 8
An Array of Function Pointers................................................................................... 8
Identifying an Interface............................................................................................... 8
Interface Types......................................................................................................... 8
IUnknown................................................................................................................. 9
The COM Activation Process.......................................................................................... 10
Activation Steps for in-process COM component................................................ 10
Activation Steps for local COM component......................................................... 11
Creating a COM Client................................................................................................... 12
Declaring your COM object Variables............................................................................. 13
Creating COM Objects................................................................................................... 13
Destroying COM Objects............................................................................................... 15
Ensuring the “correct” COM Object is Created................................................................ 15
Lab - Creating a COM Client in Visual Basic............................................................... 15
Creating an In-Process COM Component....................................................................... 17
Creating Properties.......................................................................................................... 18
Creating Methods............................................................................................................ 19
The Instancing Property for ActiveX DLL’s..................................................................... 20
MTSTransactionMode Property...................................................................................... 22
Creating a Local Server COM Component...................................................................... 24
Component Versioning in Visual Basic............................................................................. 25
Components in COM+.................................................................................................... 29
COM+ Surrogate.................................................................................................... 29
COM+ Object context............................................................................................ 29
Just In Time (JIT) activation............................................................................................. 29
JIT Activation and state................................................................................................... 31
Demonstration: JIT Deactivation.................................................................................. 31
Demonstration: JIT Activation...................................................................................... 32
Active X Dlls created in Visual Basic that are enlisted in MTS / COM+ Applications34
ADO Basics – COM Automation object model................................................................ 1
COM Automation object model........................................................................................ 2
ADO Basics...................................................................................................................... 3
ADO Objects................................................................................................................... 4
ADO – Command Object................................................................................................. 9
ADO –Recordset Object................................................................................................ 10
ADO Recordsets – ways of obtaining.............................................................................. 13
ADO Objects – Data Access Summary........................................................................... 15
ADO Collections –.......................................................................................................... 16
ADO – Updating & Locking........................................................................................... 22
ADO – Using Stored Procedures.................................................................................... 23
This is just a list of things i would like to learn!!
Thankyou
Jay
Book online and KB have good info. I am not aware of any book dedicated to sql connectivity issue yet.|||Thanks Nan,
I am trying to go inside the working of couple of things like database connection process and connection object behaviour in distributed systems in general.
I do appriciate your reply.
Thankyou,
Jay
Connectivity between vb 6.0 and Sql server 2000 (data base is in Server)
Hai all
I am sudhir
I am facing problem to establish connection between vb 6.0 and ms sql
server 2000, ie data base is in Server. I want to make conneciton
between them.
please help me.
Advance thank you alll
' Basic ADO
Dim strConnectionString as String
Dim myConn as ADODB.Connection
Dim myRS as ADODB.Recordset
' This uses the SA username, but you can create / use another
strConnectionString = "Provider=SQLOLEDB.1;Password=MySAPassword;Dat a
Source=MyServerName;User ID=SA;Initial Catalog=MyDBName;"
Set myConn = New ADODB.Connection
myConn.Open strConnectionString
set myRS = myConn.Execute "SELECT MyCol From MyTable"
If not myRS.EOF then
myRS.MoveFirst
While Not myRS.EOF
' Read stuff out of the recordset
Msgbox mrRS!MyCol
mrRS.MoveNext
Wend
End If
Jamie Clancy
Ulysses Systems, London
"sudhir9992003@.gmail.com" wrote:
> Hai all
> I am sudhir
> I am facing problem to establish connection between vb 6.0 and ms sql
> server 2000, ie data base is in Server. I want to make conneciton
> between them.
> please help me.
> Advance thank you alll
>
I am sudhir
I am facing problem to establish connection between vb 6.0 and ms sql
server 2000, ie data base is in Server. I want to make conneciton
between them.
please help me.
Advance thank you alll
' Basic ADO
Dim strConnectionString as String
Dim myConn as ADODB.Connection
Dim myRS as ADODB.Recordset
' This uses the SA username, but you can create / use another
strConnectionString = "Provider=SQLOLEDB.1;Password=MySAPassword;Dat a
Source=MyServerName;User ID=SA;Initial Catalog=MyDBName;"
Set myConn = New ADODB.Connection
myConn.Open strConnectionString
set myRS = myConn.Execute "SELECT MyCol From MyTable"
If not myRS.EOF then
myRS.MoveFirst
While Not myRS.EOF
' Read stuff out of the recordset
Msgbox mrRS!MyCol
mrRS.MoveNext
Wend
End If
Jamie Clancy
Ulysses Systems, London
"sudhir9992003@.gmail.com" wrote:
> Hai all
> I am sudhir
> I am facing problem to establish connection between vb 6.0 and ms sql
> server 2000, ie data base is in Server. I want to make conneciton
> between them.
> please help me.
> Advance thank you alll
>
ConnectionTimeout
I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
Vlad
objConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
Vlad
objConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
ConnectionTimeout
I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
VladobjConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
VladobjConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
ConnectionString property not initialized
I am getting an error message that says that my connection string has not been intialized I have initialized it.
Dim AirliquidiConnAsNew SqlClient.SqlConnection(ConfigurationManager.AppSettings("AirliquidiDatabase"))
Any suggestions??
Check out this article on using the connectionStrings element in the configuration file. Good luck!
http://weblogs.asp.net/jgaylord/archive/2005/05/12/406639.aspx
|||with that line you've only created a connection instance, but have not initialized it. You'll need to actually use it before it will work now.
here's what you need to do. You need to add a "using" statement to the code and handle the data retrieval inside of it.
Dim connString As String = _
ConfigurationManager.ConnectionStrings(connStringName).ConnectionString'Create a SqlConnection instance
Using myConnection As New SqlConnection(connString)
'Specify the SQL query
Const sql As String = "SELECT * FROM Customers"'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSet
Dim myDataSet As New DataSet'Create a SqlDataAdapter instance
Dim myAdapter As New SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)'Bind the DataSet to the GridView
gvCustomers.DataSource = myDataSet
gvCustomers.DataBind()'Close the connection
myConnection.Close()
End Using
You can visit http://aspnet.4guysfromrolla.com/articles/110905-1.aspx for a more thorough explanation (the page I got this code from)
This is what I have. Would I be putting a using statement in the Page_Load?
ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.ClickDim connAirliquidiAsNew SqlClient.SqlConnection(ConfigurationManager.AppSettings("AirliquidiDatabase"))
Dim cmdInsertLoginAsNew SqlClient.SqlCommand("Insert into Logins(ID, Username, LoginDate) values (@.ID, @.Username,@.LoginDate)", connAirliquidi)Dim activeUserAsString
Dim IDAs Guid = System.Guid.NewGuidDim cmdCheckUserCredentialsAsNew SqlClient.SqlCommand("select * From Usernames where(username=@.username and password=@.password)", connAirliquidi)
'If user did not fill in a username or password
If txtUsername.Text =""Or txtPassword.Text =""Then
Message("Please fill in a username/password")Else
Try
connAirliquidi.Open()
cmdCheckUserCredentials.Parameters.AddWithValue("@.Username", Trim(txtUsername.Text))cmdCheckUserCredentials.Parameters.AddWithValue("@.password", Trim(txtPassword.Text))
SelectCase activeUserCaseTrue
cmdInsertLogin.Parameters.AddWithValue("@.ID", ID)cmdInsertLogin.Parameters.AddWithValue("@.Username", Trim(txtUsername.Text))
cmdInsertLogin.Parameters.AddWithValue("@.LoginDate",Date.Now())Session("Username") = Trim(txtUsername.Text)
FormsAuthentication.SetAuthCookie(txtUsername.Text,False)Session("ID") = ID.ToString
Response.Redirect("ALSI.aspx?ID={" & ID.ToString &"}")CaseFalse
Message("Inactive user")CaseElse
Message("Invalid Username and/or Password")EndSelect
Catch exAs ExceptionMessage("Error authenticating user. Please try again.")
Finally
connAirliquidi.Close()
EndTry
EndIf
EndSub
|||you have to open the connection before you use the sql command. moveconnAirliquidi.Open() directly below this line
Dim connAirliquidiAsNew SqlClient.SqlConnection(ConfigurationManager.AppSettings("AirliquidiDatabase"))
which also means you'll have to move the connAirliquidi.close() down to directly above the End Sub line.
|||
I did that and I am still getting the same error message.
|||Can you post a snap from your web.config file where you've actually declared the connection string ?|||<configuration><appSettings>
<addkey="customerservice"value="abetha@.airsep.com"/><addkey="smtpserver"value=""/>
<addkey="AirliquidiConn"value="Airliquidi id=**;data source=SEPSQL;persist security info=True;initial catalog=Usernames;password=****"/></appSettings>
<connectionStrings><addname="AirliquidiConn"connectionString="Data Source=SEPSQL;Database=Airliquidi;User ID=**;Password=*****"
providerName="System.Data.SqlClient" />
</connectionStrings>
||| Correct me if I'm wrong but in your earlier posts you've written the name of your connection string to be "AirliquidiDatabase" and you've set it to be used from appSettings. Now, the snap you've posted is not having a connection string with the name you might be using in your code. Both the connection strings that you've set in your config file are named "AirliquidiConn".
Thank you for catching that. I have changed it to AirliquidiConn and I am still getting the same error.
ConnectionString Property
I am having trouble initializing my connection. This is the code:
DimDBConnPhone As NewSqlConnection(ConfigurationManager.AppSettings("DBConnPhone"))
Dim DBConnClient As NewSqlConnection(ConfigurationManager.AppSettings("DBConnClient"))
Dim Sqlcomm1 As New SqlCommand
Dim Sqlcomm2 As New SqlCommand
DBConnPhone.Open()
DBConnClient.Open()
Once I start debugging, it stops and give me the error "The ConnectionString Property was not initialized" Any suggestions?
If you want to retrieve connection strings, you should use ConfigurationManager.ConnectionStrings property, not ConfigurationManager.AppSettings. So the code changes to:
Dim DBConnPhone As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnPhone"))
Dim DBConnClient As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnClient"))
...
|||I did that and it said that Value of type 'System.Configuration.ConnectionStringSettings' cannot be converted to 'String'|||Dim DBConnClient As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnClient").ConnectionString)|||I didn't get any errors with that, but I also did not see where the newrecord was inserted into the table. I got to check my code some more.Thanks
ConnectionString nightmare
I'm having a hard time making a connection work in my ASP.Net page and would be most appreciative of any help. Here two different code snipets and the errors they generate:
ATTEMPT 1:
Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; database=WebDataUtility; uid=AVILA-4400\ASPNET; pwd=;")
cnWebDataUtility.Open()
ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'. Reason: Not associated with a trusted SQL Server connection.
ATTEMPT 2:
Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; Database=WebDataUtility; Integrated Security=SSPI")
cnWebDataUtility.Open()
ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'.
BACKGROUND INFO:
I recently installed SQL Server Developer Edition. During that installation it did not allow me to select anything but "Windows Login" (I am not sure of the exact wording) for authentication, I believe it was. I would expect this to mean, in terms of the connectionstring, "Integrated Security=SSPI." I have tried making this connection in Visual Studio .NET and it works, but when I copy the contents of the ConnectString property in the Properties window in VS to my ConnectionString in the code above, I get errors that it does not recognize a "provider" property, and so on.
How frustrating. If anyone can help me understand this mess, I'd be very grateful.
Thank you,
Paul.I've never seen a connection string with double-quotes around the 'server=' portion of the connection string (probably just a typo?). Nor have I ever seen the server name prepended to the 'uid'. Try:
server=AVILA-4400; database=WebDataUtility; uid=ASPNET; pwd=yourPassword;
I assume that "ASPNET" is the Database Login and you've omitted the password on purpose?
Tuesday, March 20, 2012
connectionstring gets populated unwillingly
Hi,
I hav eplaced an expression for the flat file connection as below
@.[User::FileDirectory] + @.[User::FileName]
This is supposed to be used instead of the ConnectionString property of the flat file connection.
You can see that I have created two variables.
The variable
@.[User::FileDirectory] is set to the directory. i.e. I have hardcoded the path to it and assigned it to this variable.
The variable @.[User::FileName] is picked up automatically.
The question is:
When I go to the properties of the flat file connection, I delete the value inside the connectionstring property becuase there is now the expression which is set to the connectionstring.
But when I come back to this property then I am not sure why the connectionstring property gets populated with the directory that I hardcoded to the variable.
Many thanks
arkiboys wrote:
Hi,
I hav eplaced an expression for the flat file connection as below@.[User::FileDirectory] + @.[User::FileName]
This is supposed to be used instead of the ConnectionString property of the flat file connection.
You can see that I have created two variables.
The variable
@.[User::FileDirectory] is set to the directory. i.e. I have hardcoded the path to it and assigned it to this variable.The variable @.[User::FileName] is picked up automatically.
The question is:
When I go to the properties of the flat file connection, I delete the value inside the connectionstring property becuase there is now the expression which is set to the connectionstring.
But when I come back to this property then I am not sure why the connectionstring property gets populated with the directory that I hardcoded to the variable.Many thanks
This is by design. The connectionstring property is picking up the value from the expression. Don't worry about it. It will be correct at runtime.|||
I'm not sure if this is correct because in the connectionstring property it shows:
\\gblond088s0b\D$\ApplData\CEM\WorkingTemp\
and in the expressions it shows:
@.[User::FileDirectory] + @.[User::FileName]Please note that in the variable design I have manually assigned the variable @.[User::FileDirectory] to the path above
Thanks
|||I forgot to mention that it gives the following error:
Error: 2007-07-26 18:10:05.39
Code: 0xC020200E
Source: Data Flow Task Flat File Source [1]
Description: Cannot open the datafile "\\gblond088s0b\D$\ApplData\CEM\WorkingTemp\".
End Error
so basically, it is not seeing the filename. it only sees the directoryname
Thanks
|||
I forgot to mention that it gives the following error:
Error: 2007-07-26 18:10:05.39
Code: 0xC020200E
Source: Data Flow Task Flat File Source [1]
Description: Cannot open the datafile "\\gblond088s0b\D$\ApplData\CEM\WorkingTemp\".
End Errorso basically, it is not seeing the filename. it only sees the directoryname
Thanks
Right. You need to give the Filename variable a default value. And if, during runtime, it still cannot find the data file, then you aren't populating the Filename variable correctly.|||
This is strange.
If I use a loop and give it the full directory path as described before, the foreach loop picks up the filenames and runs the package successfully. But I do not want to use a loop.
The package gets called once per filename. So the filename gets passed to the package and I want it to run but obviously it does not.
Not sure why the filename does not get populated when it is being passed to the package.
Thanks
|||How are you populating the Filename variable otherwise?|||I have created a variable called @.FileName.
There is also a flat file connection which has a path to the directory as described before.
There is a command prompt which someone else is in charge of running, at the end of this command it passes the filename.
How do you think the filename should get populated?
note that I do not have this problem when I use a foreach loop but for this project loop is not required.
Thanks
|||
I have created a variable called @.FileName.
There is also a flat file connection which has a path to the directory as described before.
There is a command prompt which someone else is in charge of running, at the end of this command it passes the filename.
How do you think the filename should get populated?
note that I do not have this problem when I use a foreach loop but for this project loop is not required.
Thanks
I'm asking HOW you are passing a value to the Filename variable. HOW is the command line, that someone else is in charge of executing, populating the Filename variable? You need to know this before asking your questions here.|||
Ok,
If I want to run the package myself, how can I do this using the command line?
Subscribe to:
Posts (Atom)