New to asp

I am in a learning mode here as all my previous work has been with php.
I am trying to make an OLE DB connection.to SQL Server Express on my local
machine using ASP and VBscript. I followed the instructions for making a
NewConnection.udl and double clicked that, filled in the spots and tested
the connection. Everything was OK. When I put it into my asp code, using
that string in the conn.Open statement, I got the error message that it
couldn't open the database that I had specified in the connection string.
(I copied and pasted that string from the .udl file where the test
connection succeeded). This was with Windows Authorization.
I also created a user with the name ASPpage and password asptest. I gave
that user all the privileges using Sql Server Manager Studio Express. When
I changed the string so that it had a user id and a password (SQL Server
authentication), I got the message that the login failed for ASPpage. Note,
also, that I couln not create a connection with that user in Sql Server
Manager Studio Express, but I could make a connection with Windows
authentication.
The failure for the one with the user id is that the user is not associated
with a trusted SQL server connection (even though he has all privileges).
Any suggestions on how to proceed.
Here is the code:
<%
dim q, num, i, acct, coName
set Conn = Server.CreateObject("ADODB.Connection")
q = "SELECT * FROM Company"
Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
Info=False;Initial Catalog=the_database_name;Data Source=LAPTOP\SQLEXPRESS"
set rs = Conn.Execute(q)
set of = Server.CreateObject("ADODB.Field")
num = rs.Count
for i=0 To num - 1
acct = of("accountNumber")
coName = of("companyName")
Response.Write(acct & " " & coName & "<br>")
next
conn.Close
%>
When I put in to user info, I removed the Integrated Security stuff.
Thanks for any help
Shelly (Sheldon)You look forgot changing Authentication Method from Windows Authentication
to Mixed Authentication (SQL Server and Windows Authentication) from Server
Properties\Security.
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gfs30mhrjej53@.corp.supernews.com...
>I am in a learning mode here as all my previous work has been with php.
> I am trying to make an OLE DB connection.to SQL Server Express on my local
> machine using ASP and VBscript. I followed the instructions for making a
> NewConnection.udl and double clicked that, filled in the spots and tested
> the connection. Everything was OK. When I put it into my asp code, using
> that string in the conn.Open statement, I got the error message that it
> couldn't open the database that I had specified in the connection string.
> (I copied and pasted that string from the .udl file where the test
> connection succeeded). This was with Windows Authorization.
> I also created a user with the name ASPpage and password asptest. I gave
> that user all the privileges using Sql Server Manager Studio Express.
> When I changed the string so that it had a user id and a password (SQL
> Server authentication), I got the message that the login failed for
> ASPpage. Note, also, that I couln not create a connection with that user
> in Sql Server Manager Studio Express, but I could make a connection with
> Windows authentication.
> The failure for the one with the user id is that the user is not
> associated with a trusted SQL server connection (even though he has all
> privileges).
> Any suggestions on how to proceed.
> Here is the code:
> <%
> dim q, num, i, acct, coName
> set Conn = Server.CreateObject("ADODB.Connection")
> q = "SELECT * FROM Company"
> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> Info=False;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXPRESS"
> set rs = Conn.Execute(q)
> set of = Server.CreateObject("ADODB.Field")
> num = rs.Count
> for i=0 To num - 1
> acct = of("accountNumber")
> coName = of("companyName")
> Response.Write(acct & " " & coName & "<br>")
> next
> conn.Close
> %>
> When I put in to user info, I removed the Integrated Security stuff.
> Thanks for any help
> Shelly (Sheldon)
>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> You look forgot changing Authentication Method from Windows Authentication
> to Mixed Authentication (SQL Server and Windows Authentication) from
> Server Properties\Security.
I'm sorry but I don't understand what you are saying. Where do you find
"Server Properties"? When I tried a new connection and switched from
Windows Authentication to SQL Server, it didn't work.
> --
> Ekrem Önsoy
>
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13gfs30mhrjej53@.corp.supernews.com...
>>I am in a learning mode here as all my previous work has been with php.
>> I am trying to make an OLE DB connection.to SQL Server Express on my
>> local machine using ASP and VBscript. I followed the instructions for
>> making a NewConnection.udl and double clicked that, filled in the spots
>> and tested the connection. Everything was OK. When I put it into my asp
>> code, using that string in the conn.Open statement, I got the error
>> message that it couldn't open the database that I had specified in the
>> connection string. (I copied and pasted that string from the .udl file
>> where the test connection succeeded). This was with Windows
>> Authorization.
>> I also created a user with the name ASPpage and password asptest. I gave
>> that user all the privileges using Sql Server Manager Studio Express.
>> When I changed the string so that it had a user id and a password (SQL
>> Server authentication), I got the message that the login failed for
>> ASPpage. Note, also, that I couln not create a connection with that user
>> in Sql Server Manager Studio Express, but I could make a connection with
>> Windows authentication.
>> The failure for the one with the user id is that the user is not
>> associated with a trusted SQL server connection (even though he has all
>> privileges).
>> Any suggestions on how to proceed.
>> Here is the code:
>> <%
>> dim q, num, i, acct, coName
>> set Conn = Server.CreateObject("ADODB.Connection")
>> q = "SELECT * FROM Company"
>> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
>> Info=False;Initial Catalog=the_database_name;Data
>> Source=LAPTOP\SQLEXPRESS"
>> set rs = Conn.Execute(q)
>> set of = Server.CreateObject("ADODB.Field")
>> num = rs.Count
>> for i=0 To num - 1
>> acct = of("accountNumber")
>> coName = of("companyName")
>> Response.Write(acct & " " & coName & "<br>")
>> next
>> conn.Close
>> %>
>> When I put in to user info, I removed the Integrated Security stuff.
>> Thanks for any help
>> Shelly (Sheldon)
>>
>|||You get that setting you right click on the server name goto properties >
seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
But looking at your connection string you have an error ... Maybe a mistype.
But here are two possible connection strings for SQL 2005:
Trusted Connection:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI;
SQL User Name Connection:
Server=myServerAddress;Database=myDataBase;User
ID=myUsername;Password=myPassword;Trusted_Connection=False;
Also note if you are trying to use integrated security in ASP, the IIS
username IUSR account must have access to SQL server not your account.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Shelly" wrote:
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> > You look forgot changing Authentication Method from Windows Authentication
> > to Mixed Authentication (SQL Server and Windows Authentication) from
> > Server Properties\Security.
> I'm sorry but I don't understand what you are saying. Where do you find
> "Server Properties"? When I tried a new connection and switched from
> Windows Authentication to SQL Server, it didn't work.
>
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> > news:13gfs30mhrjej53@.corp.supernews.com...
> >>I am in a learning mode here as all my previous work has been with php.
> >>
> >> I am trying to make an OLE DB connection.to SQL Server Express on my
> >> local machine using ASP and VBscript. I followed the instructions for
> >> making a NewConnection.udl and double clicked that, filled in the spots
> >> and tested the connection. Everything was OK. When I put it into my asp
> >> code, using that string in the conn.Open statement, I got the error
> >> message that it couldn't open the database that I had specified in the
> >> connection string. (I copied and pasted that string from the .udl file
> >> where the test connection succeeded). This was with Windows
> >> Authorization.
> >>
> >> I also created a user with the name ASPpage and password asptest. I gave
> >> that user all the privileges using Sql Server Manager Studio Express.
> >> When I changed the string so that it had a user id and a password (SQL
> >> Server authentication), I got the message that the login failed for
> >> ASPpage. Note, also, that I couln not create a connection with that user
> >> in Sql Server Manager Studio Express, but I could make a connection with
> >> Windows authentication.
> >>
> >> The failure for the one with the user id is that the user is not
> >> associated with a trusted SQL server connection (even though he has all
> >> privileges).
> >>
> >> Any suggestions on how to proceed.
> >>
> >> Here is the code:
> >>
> >> <%
> >> dim q, num, i, acct, coName
> >> set Conn = Server.CreateObject("ADODB.Connection")
> >> q = "SELECT * FROM Company"
> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> >> Info=False;Initial Catalog=the_database_name;Data
> >> Source=LAPTOP\SQLEXPRESS"
> >> set rs = Conn.Execute(q)
> >> set of = Server.CreateObject("ADODB.Field")
> >> num = rs.Count
> >> for i=0 To num - 1
> >> acct = of("accountNumber")
> >> coName = of("companyName")
> >> Response.Write(acct & " " & coName & "<br>")
> >> next
> >> conn.Close
> >> %>
> >>
> >> When I put in to user info, I removed the Integrated Security stuff.
> >>
> >> Thanks for any help
> >>
> >> Shelly (Sheldon)
> >>
> >>
> >>
> >
>
>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
Thanks. When I do that in SQL Server Management Studio Express and hit the
"Test" button, I get that the testing failed because the user is not
associated with a trusted SQL Serverver connection. (Error 18452)
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
What about "Provider"? Also, should I leave out "Persist Security Info"?
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
What about "Provider"? Also, I can't really test this one further until I
somehow get those credentials working in SQL Server Management Studio
Express.
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
Could you expand upon this please? Where do I find/set the IIS username?
Thanks.
Shelly
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
It was a typo.
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
When I tried this string (without Provider, etc.) I got an error message of
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done". This was at the Conn.Open line.
Before I removed the Provider and Persist Security, it said that it failed
to open the_database_name as requested by the login.
Shelly
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||Shelly,
Please be sure that you have a LOGIN named ASPpage (or whatever) in main
Security node.
After that, be sure of that you have an appropriate USER which is associated
with that LOGIN and has necessary rights to perform its jobs in that
database.
Fire up SSMSE and go to Server Properties and go to Security and change the
Server authentication as "SQL Server and Windows Authentication mode" and
restart your SQL Server service and then try again.
Checklist:
In SQL Server Express, remote connections are disabled by default. So go to
Surface Area Configurations and be sure your instance is enabled for TCP\IP
or whatever protocol you use for connection.
Go to SQL Server Configuration Manager and be sure your Network
Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
must be entered etc.)
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13ghjl7nvigltfb@.corp.supernews.com...
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
> It was a typo.
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
> When I tried this string (without Provider, etc.) I got an error message
> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done". This was at the Conn.Open
> line. Before I removed the Provider and Persist Security, it said that it
> failed to open the_database_name as requested by the login.
> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
> Shelly,
> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
> Security node.
I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
the object explorer in SSMSE.
> After that, be sure of that you have an appropriate USER which is
> associated with that LOGIN and has necessary rights to perform its jobs in
> that database.
I have a user under Databases\the_database_name\Security\Users in SSMSE. It
has the same rights as the Administrator, including Connect SQL.
> Fire up SSMSE and go to Server Properties and go to Security and change
> the Server authentication as "SQL Server and Windows Authentication mode"
> and restart your SQL Server service and then try again.
Already there at Windows Authentication Mode -- but I restarted anyway.
> Checklist:
> In SQL Server Express, remote connections are disabled by default. So go
> to Surface Area Configurations and be sure your instance is enabled for
> TCP\IP or whatever protocol you use for connection.
I don't know what you mean with "Surface Area Configurations" or where to
find it. I went to the configuration manager and saw to it that shared
memory, named pipes and tcpip were all enabled. I restarted the sql server.
> Go to SQL Server Configuration Manager and be sure your Network
> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
> must be entered etc.)
Done. (Note that everything is taking place on my local laptop, called
LAPTOP, no external connections)
I still get the "Multiple step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done".
I don't know where or how to check these values.
My connection line is:
Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
Source=LAPTOP\SQLEXRESS"
Shelly
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>|||> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
Try "Server" instead of "Data Source" and "Database" instead of "Initial
Catalog".
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gmnt5drgdse27@.corp.supernews.com...
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
>> Shelly,
>> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
>> Security node.
> I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
> the object explorer in SSMSE.
>> After that, be sure of that you have an appropriate USER which is
>> associated with that LOGIN and has necessary rights to perform its jobs
>> in that database.
> I have a user under Databases\the_database_name\Security\Users in SSMSE.
> It has the same rights as the Administrator, including Connect SQL.
>> Fire up SSMSE and go to Server Properties and go to Security and change
>> the Server authentication as "SQL Server and Windows Authentication mode"
>> and restart your SQL Server service and then try again.
> Already there at Windows Authentication Mode -- but I restarted anyway.
>> Checklist:
>> In SQL Server Express, remote connections are disabled by default. So go
>> to Surface Area Configurations and be sure your instance is enabled for
>> TCP\IP or whatever protocol you use for connection.
> I don't know what you mean with "Surface Area Configurations" or where to
> find it. I went to the configuration manager and saw to it that shared
> memory, named pipes and tcpip were all enabled. I restarted the sql
> server.
>> Go to SQL Server Configuration Manager and be sure your Network
>> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
>> must be entered etc.)
> Done. (Note that everything is taking place on my local laptop, called
> LAPTOP, no external connections)
> I still get the "Multiple step OLE DB operation generated errors. Check
> each OLE DB status value, if available. No work was done".
> I don't know where or how to check these values.
> My connection line is:
> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
> Shelly
>> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on
>> >> my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest.
>> >> I gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with
>> >> that user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security
>> >> stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:58AF86FF-ED77-47B7-97F5-3D1D3B7B7CCE@.microsoft.com...
>> Conn.Open "Integrated Security=SSPI;Initial
>> Catalog=the_database_name;Data Source=LAPTOP\SQLEXRESS"
> Try "Server" instead of "Data Source" and "Database" instead of "Initial
> Catalog".
> --
> Ekrem Önsoy
Same result.

New SQL Express User - No Server Connection

Hello,

I am a new to SQL server express, although i have been using SQL server 2003 for development purpose. I just recently installed express and would like to connect to it but can't. I've downloaded and installed SQL Server 2005, SQL Server Management Studio Express, SQL Server Native Client, MSXML 6.0 Parser and .Net Framework 2.0.

When i setup my ODBC driver and test the connection i get the "test failed" message:

Attempting connection
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SSL Security error

I also get the following message from the SQL Server Management Studio Express.

Any help on slving this problem will be highly appreciated.

Thank you.

===================================

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476

Server Name: S2003\SQLEXPRESS
Error Number: -2146893019
Severity: 20
State: 0

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ConnectToServer(Object connectionInfo, IDbConnection liveConnection, Boolean validateConnection)

I hope someone can answer this one because I am having the same problem.|||It looks like client forces encryption while your sql server does not have a trusted certificate. You can either use c:\windows\system32\cliconfg.exe to disable encryption or install a valid certificate for your server.
check this to see what is a valid certificate: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

New SQL Express User - No Server Connection

Hello,

I am a new to SQL server express, although i have been using SQL server 2003 for development purpose. I just recently installed express and would like to connect to it but can't. I've downloaded and installed SQL Server 2005, SQL Server Management Studio Express, SQL Server Native Client, MSXML 6.0 Parser and .Net Framework 2.0.

When i setup my ODBC driver and test the connection i get the "test failed" message:

Attempting connection
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SSL Security error

I also get the following message from the SQL Server Management Studio Express.

Any help on slving this problem will be highly appreciated.

Thank you.

===================================

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476

Server Name: S2003\SQLEXPRESS
Error Number: -2146893019
Severity: 20
State: 0

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ConnectToServer(Object connectionInfo, IDbConnection liveConnection, Boolean validateConnection)

I hope someone can answer this one because I am having the same problem.|||It looks like client forces encryption while your sql server does not have a trusted certificate. You can either use c:\windows\system32\cliconfg.exe to disable encryption or install a valid certificate for your server.
check this to see what is a valid certificate: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

New SQL Express Installation

I just installed SQL Express in my workstation but when I try to create and ODBC connection, the new server does not show in the drop down. Do you have any tips...? Thanks.

If you are using down-level clients through MDAC, you'll need to enable TCP/IP in order to connect to SQL Express, even on the local computer. If you're additionally trying to connect from a remote computer, you'll need to consider turning on SQL Browser and open up your Firewall.

Check out http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx for information about this.

Mike

|||

Why ODBC out of curiousity?

Why not connect with ADO 2.8? or ADO.net using the new sql native client?

Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabaseName;Data Source=.\Sqlexpress

|||If the server is not the default instance (if you installed it as named instance) you may have to type in the instance name manually.

New session inside SP?

Hi:
I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
I ask this because I need a new sesssion with its own transaction.
Thanks,
Rui FerreiraThere are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

-PatP|||xp_cmdshell, osql will creat their own spid...

Mostly they've been a royal pain in the donkey....|||xp_cmdshell, osql will creat their own spid...

Mostly they've been a royal pain in the donkey....Yeah, that too.

Just from a security standpoint, the OLE objects are much better. The problem with all of the solutions to this kind of problem is that they have so many caveats and potential "tiger trap" situations embedded in them that there isn't a simple answer.

-PatP|||There are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

-PatP

I have a stored procedure that generates sequential IDs based on an auxiliar table. I want to include this SP in its own transaction to avoid concurrency problems I'm having now. When one transaction that uses the SP lasts a little longer the table is locked too much time.
I hope my explanation helps...

Thanks,
Rui Ferreira|||What sort of concurrency problems are you having? This sounds like a bad thing to me to try to sidestep any concurrency issues, because if they are causing problems now I think that side-stepping them will cause even bigger problems.

-PatP|||Sounds like the original design is the bigger problem...

Want to show us some code?

New Query with Current Connection

I am missing "New Query with Current Connection" button in SQL Editor in the
SQL management studio. Few posts on the Web refer explain that this button
should allow me to open a new query in the existing window.
I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
Any ideas?
Bojan Kuhar wrote:
> I am missing "New Query with Current Connection" button in SQL Editor in the
> SQL management studio. Few posts on the Web refer explain that this button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
> Any ideas?
I'm not sure it can be done, but "Ctrl-N" will give you the "New query
with current connection".
Regards
Steen
|||Fantastic.
Now, one more. Is there something like "Open Query with Current Connection".
I'd like to avoid the Connect to Database Enginde dialog.
More-less like Qury Analyzer that allows you to open a new query in the
existing window.
Regards
Bojan
"Steen Persson (DK)" wrote:

> Bojan Kuhar wrote:
> I'm not sure it can be done, but "Ctrl-N" will give you the "New query
> with current connection".
> Regards
> Steen
>
|||My fault. The button was called New Query with Current Connection in one of
the betas. The released version renamed it to New Query. I changed most of
the references, but I missed a few. I think I got rid of them all by web
release 1 of Books Online though.
The New Query button will use the connection that has focus when you click
it. Either Object Explorer, or an existing Query Editor window. If your
current context has no connection, then it will pop up the Connect to Server
dialog box.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Bojan Kuhar" <BojanKuhar@.discussions.microsoft.com> wrote in message
news:01230DB5-BEE5-4452-B89E-470DCCE488F2@.microsoft.com...
>I am missing "New Query with Current Connection" button in SQL Editor in
>the
> SQL management studio. Few posts on the Web refer explain that this
> button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find
> it.
> Any ideas?
|||Bojan Kuhar (BojanKuhar@.discussions.microsoft.com) writes:
> Fantastic.
> Now, one more. Is there something like "Open Query with Current
> Connection". I'd like to avoid the Connect to Database Enginde dialog.
> More-less like Qury Analyzer that allows you to open a new query in the
> existing window.
What Steen said, CTRL-N is the key. Provided one thing: under Tools->
Options->Keyboard select SQL 2000 instead of Standard.
The New Query button also works if you are in a query window or Object
Explorer, and so does File->New->Query with Current Connection.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

New Query Connect Dialog

The "Microsoft SQL Server Management Studio" opens a connection dialog with every new query. Please tell me there is setting to prevent this.

Thanx,

Greg

The popup of the dialog is related to your current context. If you have object explorer open and a server connected then the query will be connected to that server. If you have an existing query window connected it should connect to that server.

If working disconnected then you will get the pop up.

|||

Thank you for your reply, your answer does work nicely. It would seem this would be something you could define a default for. It is taking a little time to get used to the SQL 2005 tools after using SQL 2000 for so long.

If you are a member of Experts-Exchange.com please visit this link and post your answer to receive the allotted points. If after 48 hours or so I have not seen your post there I will post your answer for the benefit of others and close the question in that forum.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21773295.html

Regards,

Greg

|||Done, haven't answered a question on EE for a while|||

EE Saves my bacon from time to time:)

Your blog site is very kewl, you must be a busy person.

Sent an email to your yahoo address Returned Undeliverable.

|||I think this is semi-related to your thread, so I hope it's cool to post this here. If you have a query window in SSMS, and drag and drop one or more script files on it, it prompts you for the database to connect to, for each file you dropped. In 2000 query analyzer, it will automatically connect each file to the same server and database. Do you know of a way to accomplish this in 2005?|||I don't believe so.

New Query Connect Dialog

The "Microsoft SQL Server Management Studio" opens a connection dialog with every new query. Please tell me there is setting to prevent this.

Thanx,

Greg

The popup of the dialog is related to your current context. If you have object explorer open and a server connected then the query will be connected to that server. If you have an existing query window connected it should connect to that server.

If working disconnected then you will get the pop up.

|||

Thank you for your reply, your answer does work nicely. It would seem this would be something you could define a default for. It is taking a little time to get used to the SQL 2005 tools after using SQL 2000 for so long.

If you are a member of Experts-Exchange.com please visit this link and post your answer to receive the allotted points. If after 48 hours or so I have not seen your post there I will post your answer for the benefit of others and close the question in that forum.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21773295.html

Regards,

Greg

|||Done, haven't answered a question on EE for a while|||

EE Saves my bacon from time to time:)

Your blog site is very kewl, you must be a busy person.

Sent an email to your yahoo address Returned Undeliverable.

|||I think this is semi-related to your thread, so I hope it's cool to post this here. If you have a query window in SSMS, and drag and drop one or more script files on it, it prompts you for the database to connect to, for each file you dropped. In 2000 query analyzer, it will automatically connect each file to the same server and database. Do you know of a way to accomplish this in 2005?|||I don't believe so.

New Login with Trusted Connection

I have my development machine connected to my SQL 2000 server using trusted connection.

I want to setup another machine for testing purposes.

How do I setup SQL for another user? I can't get pass the Domain, when I setup a new login using trusted connection. It only shows the admin and my account on the sql server machine.

Once that is completed, how to I setup a w2k pc for trusted connection?

Thanks,
MikeD

Can you be a little more clear on what the error is that you're getting? Can you tell me exactly what steps you took to get you to where you're stuck?

Is your SQL Installation enabled for windows authentication?
Is your machine part of a domain?

|||

Greg,
Not getting an error, just could not complete the new login without a Domain.

What I ended up doing was adding a Local User Account to the server, using the same username and pwd as on the client pc.

I could then add the user login to SQL and use Windows Authentication, not SQL Server Authentication. I tried the new deployment and the win2k machine connected right up.

I don't have a domain controller. I have a small network with a server, 4 pcs, a router(broadband) and a lynksys hub that I uplink to the router.

My SQL2000 server is enabled for windows authentication. I enabled my XPpro a while back but forgot the steps. I thought it was somewhat obscure if I remember correctly.

Do you happen to have links to instructions on how to setup the different os
Thanks,
Mike

|||

Mike, I"m completely confused. What are you trying to do, create a new login using SQL Server Authentication? Not sure what an OS has to do with this.

|||Greg,

I have an application I'm developing that I'm using Windows Authentication for the SQL login instead of SQL Server Authentication.

Since I'm not using a Domain controller, I think things are a bit different.

Additionally I'm trying to setup the SQL Server for Windows Authentication.
1. Do you normally have to setup a Local User/Groups account on the server for each SQL login?. This is what I had to do to be able pick from a domain in the SQL Login setup window.

I'm trying to setup the best way to use Windows Authentication from different PC client OS's with SQL server.
2. On XPpro / home, is there anything special that has to be done?

Thanks,
Mike

new installation question

I've installed MSDE on a W2000 machine and want to set up an ODBC connection to it from an XP machine using an IP address and specifying a port. How do I determine the login and password I need to use to make the connection
Thanks...Hi,
First , you have to enable TCP/IP protocol from MDE server.
From command prompt of server execute (Server Network Utility) :-
svrnetcn.exe
Here, You can enable the TCPIP protocol
By default MSDE only support Windows authentication. So try creating an ODBC
using a Domain id. Ensure that domain user has got access to SQL Server.
By default users of local administrator group will have access. Have a look
into books online for the below procedures to give preevilages:-
1. sp_grantlogin
2. sp_grantdbaccess
Have a look into below link to identify for connectivity issues:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
Thanks
Hari
MCDBA
"Jeff" <jstemper@.wi.rr.com> wrote in message
news:9DFDAD9E-49E7-4A04-8A0F-56BD4821EC36@.microsoft.com...
> I've installed MSDE on a W2000 machine and want to set up an ODBC
connection to it from an XP machine using an IP address and specifying a
port. How do I determine the login and password I need to use to make the
connection?
> Thanks...|||Jeff,
if you are using trusted security, then there won't be a requirement for a
login and password. If you are using SQL Server security, then you just need
to create a SQL Server login on MSDE and incorporate its details in the
connectionstring. I'm guessing here, but it may be that your question is
wider and you want to know generally about administering the MSDE
installation. SQL Server client tools from any other box could be used to
administer it. Just make sure that the box which is using the client tools
uses trusted security, and the windows login on this box is in the local
administrators group on the MSDE machine. To cheaply set this up, you could
use the Developer Edition to get the client tools. Some people talk about
using the Evaluation client tools although the legality of this is dubious -
you'd have to check this out with MS.
HTH,
Paul Ibison|||If you require SQL authentication, you have to specify
following option for setup.exe while installing MSDE.
Setup.exe SECURITYMODE=SQL SAPWD=<password>
Shrikant Patil
MCDBA
>--Original Message--
>I've installed MSDE on a W2000 machine and want to set
up an ODBC connection to it from an XP machine using an
IP address and specifying a port. How do I determine the
login and password I need to use to make the connection?
>Thanks...
>.
>

new installation question

I've installed MSDE on a W2000 machine and want to set up an ODBC connection to it from an XP machine using an IP address and specifying a port. How do I determine the login and password I need to use to make the connection?
Thanks...
Hi,
First , you have to enable TCP/IP protocol from MDE server.
From command prompt of server execute (Server Network Utility) :-
svrnetcn.exe
Here, You can enable the TCPIP protocol
By default MSDE only support Windows authentication. So try creating an ODBC
using a Domain id. Ensure that domain user has got access to SQL Server.
By default users of local administrator group will have access. Have a look
into books online for the below procedures to give preevilages:-
1. sp_grantlogin
2. sp_grantdbaccess
Have a look into below link to identify for connectivity issues:-
http://support.microsoft.com/default...b;en-us;827422
Thanks
Hari
MCDBA
"Jeff" <jstemper@.wi.rr.com> wrote in message
news:9DFDAD9E-49E7-4A04-8A0F-56BD4821EC36@.microsoft.com...
> I've installed MSDE on a W2000 machine and want to set up an ODBC
connection to it from an XP machine using an IP address and specifying a
port. How do I determine the login and password I need to use to make the
connection?
> Thanks...
|||Jeff,
if you are using trusted security, then there won't be a requirement for a
login and password. If you are using SQL Server security, then you just need
to create a SQL Server login on MSDE and incorporate its details in the
connectionstring. I'm guessing here, but it may be that your question is
wider and you want to know generally about administering the MSDE
installation. SQL Server client tools from any other box could be used to
administer it. Just make sure that the box which is using the client tools
uses trusted security, and the windows login on this box is in the local
administrators group on the MSDE machine. To cheaply set this up, you could
use the Developer Edition to get the client tools. Some people talk about
using the Evaluation client tools although the legality of this is dubious -
you'd have to check this out with MS.
HTH,
Paul Ibison

new installation question

I've installed MSDE on a W2000 machine and want to set up an ODBC connection
to it from an XP machine using an IP address and specifying a port. How do
I determine the login and password I need to use to make the connection?
Thanks...Hi,
First , you have to enable TCP/IP protocol from MDE server.
From command prompt of server execute (Server Network Utility) :-
svrnetcn.exe
Here, You can enable the TCPIP protocol
By default MSDE only support Windows authentication. So try creating an ODBC
using a Domain id. Ensure that domain user has got access to SQL Server.
By default users of local administrator group will have access. Have a look
into books online for the below procedures to give preevilages:-
1. sp_grantlogin
2. sp_grantdbaccess
Have a look into below link to identify for connectivity issues:-
http://support.microsoft.com/defaul...kb;en-us;827422
Thanks
Hari
MCDBA
"Jeff" <jstemper@.wi.rr.com> wrote in message
news:9DFDAD9E-49E7-4A04-8A0F-56BD4821EC36@.microsoft.com...
> I've installed MSDE on a W2000 machine and want to set up an ODBC
connection to it from an XP machine using an IP address and specifying a
port. How do I determine the login and password I need to use to make the
connection?
> Thanks...|||Jeff,
if you are using trusted security, then there won't be a requirement for a
login and password. If you are using SQL Server security, then you just need
to create a SQL Server login on MSDE and incorporate its details in the
connectionstring. I'm guessing here, but it may be that your question is
wider and you want to know generally about administering the MSDE
installation. SQL Server client tools from any other box could be used to
administer it. Just make sure that the box which is using the client tools
uses trusted security, and the windows login on this box is in the local
administrators group on the MSDE machine. To cheaply set this up, you could
use the Developer Edition to get the client tools. Some people talk about
using the Evaluation client tools although the legality of this is dubious -
you'd have to check this out with MS.
HTH,
Paul Ibison

New IBM Laptop running SQL server cannot connect to another sql server on network

I am trying to connect a new LBM Laptop to an SQL server on the
network
and I get "test connection failed because of an error in initializing
provider, [dbnetlib][connectionopren(connect()),] SQL server does not
exist or access denied. The laptom has sql server running on it
locally and I am trying to connect to an SQL server on the network.
Thanks
TomHi

You should make sure that SQL Server is at Service Pack 3a on both machines
and check the MDAC installation on the laptop with the MDAC component
checker (see microsoft download centre)?

If you can connect to the network SQL Server from other machines, then check
the network settings on the laptop.

John

"TomCullen" <cullentw2000@.yahoo.com> wrote in message
news:6ba80eaf.0410220926.6c2f4355@.posting.google.c om...
> I am trying to connect a new LBM Laptop to an SQL server on the
> network
> and I get "test connection failed because of an error in initializing
> provider, [dbnetlib][connectionopren(connect()),] SQL server does not
> exist or access denied. The laptom has sql server running on it
> locally and I am trying to connect to an SQL server on the network.
> Thanks
> Tom

New data source connection failure

Hi,
Windows 2003 Server Std. Edition - SQL Server 200 Std. Edition
I'm able to create a new data source in the ODBC panel on the SQL
server which points to a db on the same SQL server but not creating
that same new data source in the SQL Server Enterprise Manager or
Analysis Manager. I tried creating a new cube, a new DTS and so on but
I won't get past the new data source failure.
Error: [DBNETLIB][ConnectionOpen(Connect()).] SQL Server does not
exist or access is denied.
I tried using local admin account, sa account which both belong to the
sysadmin serve role.
Thank you.
Steve"Steve" <steve@.500mg.be> wrote in message
news:6b781b0f.0410300430.775b6306@.posting.google.com...

> Windows 2003 Server Std. Edition - SQL Server 200 Std. Edition
> I'm able to create a new data source in the ODBC panel on the SQL
> server which points to a db on the same SQL server but not creating
> that same new data source in the SQL Server Enterprise Manager or
> Analysis Manager. I tried creating a new cube, a new DTS and so on but
> I won't get past the new data source failure.
> Error: [DBNETLIB][ConnectionOpen(Connect()).] SQL Server does not
> exist or access is denied.
> I tried using local admin account, sa account which both belong to the
> sysadmin serve role.
It sounds like your SQL Server 2000 is running in "windows only"
authentication mode, which means the 'sa' account would not be recognized.
Try either a windows account (preferred technique) local admin should work
if you have not changed the basic security install, or change the
authentication mode to "sql server and windows", stop and restart SQL
Server.
Steve

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

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

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

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

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

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

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

new custom flat file connection manager component

The standard flat file connection component does not handle flat files with different column counts on different rows. Since that's the type of file I have to read, I like to create a modified version of the flat file connection manager that can do this. I have found some info in how to create a connection manager. But I can't find anything that tels me how to build one that would be recognized by the flat file source dataflow component.

So how do you build an connection manager that can used by the standard flat file source component? Anyone ever did this or knows were to get the docs?

I know my problem is also solvable by reading the row into a single column and then using a script component to split them but that is not the solution I want.

The new connection manager is not going to solve this problem. The parsing logic is not part of the connection manager, it is part of the Flat File source adapter. You would need to build a new source, not connection manager.

Thanks,

Bob