database file locations: express

Showing posts with label express. Show all posts

Friday, March 30, 2012

New to SQL 2005 Express

I haven't used this software except locally on my laptop. If you have built a databased web app using this, how do you deploy the db? Does it have to be installed and/or supported on your webhosting service? If so, it seems to be of limited use, if not, very helpful.

Thanks for the help

To deploy the application, the destination will need to have SQL Server Express installed as well. You can read a detailed article about that here:

http://msdn2.microsoft.com/en-us/library/ms165639.aspx

Buck Woody

|||

That's what I was afraid of....doesn't look as this will be the answer. Back to mysql or MS SQl 2000.

Thanks for the reply!!

TLCoop

|||No problem - but MySQL or MS SQL 2000 will have the same issue, won't it?|||

There might be some confusion here. SQL Express is SQL Server 2005. Your hoster will need to support SQL Server 2005, but they don't have to have SQL Express installed. It's the exact same database file because they are the exact same products, just with different feature sets.

If you're hosting company requires that you upload your database using scripts, we are working on a tool to help do that. You can find a CTP of the tool on CodePlex.

Mike

|||

Thanks Mike,

My host doesn't offer mssql 2005 yet. They do have mssql 2000, and maybe(hopefully)some of sqlexpress2005 is backwards compatible. I can always generate scripts and run a query for db population on mssql 2000. I'll take a look at CodePlex.

I appreciate the help!

Terry

|||

Thanks Buck for the reply. My Host doesn't offer MSSQL 2005 nor SQLExpress 2005 support yet. They offer both MySQl and MS SQL 2000 right now, so I'll try and 'script' a db from express 2005. I appreciate the help.

Terry

Wednesday, March 28, 2012

New To Databasing

I have just a general question that I was hoping to have answered about the SQL Server Express edition software. I was looking at the data types that you can select from while creating tables and was wondering about other types of data that it can support. I wanted to find out if it supports audio files as one of the data types. I know from MS Access its a OLE object datatype that supports audio files. Is SQL Server Express even capable of handling such data types? Or any other data types that are not text or integers? Anybodys input would be greatly appreciated. Thanks! ~[MikE]~

You can store any file (or data) as raw binary with the varbinary datatype.

Use varbinary(max) to store data larger than 8000 bytes.

|||Hey thanks for clearing that up for me, I really appreciuate the help, you're like the only one that resoponded out of 20 views, and yea I know it was probably a dumb question, but I just need to make sure. Thanks a lot! ~[MikE]~
|||

No question is dumb, it would be naive to think like that. Some questions can be answered by searching but then it is not always obvious what to search for.

Browse around a bit and you might be able to help others here, with something that you have experience with. This is a community where we help each other. It might take some time but most questions gets answered.

|||Hey thanks Andreas! Im looking foward to learning a lot from everybod on here. Much Appreciated!sql

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 & " ")
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 & " ")
> 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 & " ")
>> 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 & " ")
> >> 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 & " ")
>> >> 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 & " ")
>> >> 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 & " ")
>> >> 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 & " ")
>> >> 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 & " ")
>> >> 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.

Monday, March 26, 2012

New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up

I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

Friday, March 23, 2012

New SQL Server User Questions

I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this part.
Thank you for your help.
Regards,
SailFL
You get a command prompt by going to Start->Run and typing CMD. From that,
you run SQLCMD. From what you have given us, try:
SQLCMD -S NGT\SQLEXPRESS
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"SailFL" <SailFL@.discussions.microsoft.com> wrote in message
news:28954F43-0CBC-42FC-B7BC-B14C3E67BC83@.microsoft.com...
I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this
part.
Thank you for your help.
Regards,
SailFL
|||SQLCMD can be issued from the commadn prompt of Windows, so
Start->Run->cmd
int he doscommand you can type in e.g.
sqlcmd.exe -S NGT\SQLEXPRESS -E
-Whereas NGT is the servername and SQLEXPRESS the instancename
-E if you are using Trusted authentication to connect to the database
server.
You can test the successfull connection therefore that you get no error
are issue a Select command to any table:
USE AdventureWorks
GO
SELECT * from Person.Contact
GO
--or simpler
SELECT 1
GO
HTH, jens Suessmeyer.
|||Thank both you for your assistance.
I was able to use the commands and to attach the AdventureWorks Database to
my SQL server.
SailFL
"Jens" wrote:

> SQLCMD can be issued from the commadn prompt of Windows, so
> Start->Run->cmd
> int he doscommand you can type in e.g.
> sqlcmd.exe -S NGT\SQLEXPRESS -E
> -Whereas NGT is the servername and SQLEXPRESS the instancename
> -E if you are using Trusted authentication to connect to the database
> server.
> You can test the successfull connection therefore that you get no error
> are issue a Select command to any table:
> USE AdventureWorks
> GO
> SELECT * from Person.Contact
> GO
> --or simpler
> SELECT 1
> GO
>
> HTH, jens Suessmeyer.
>

New SQL Server User Questions

I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this part.
Thank you for your help.
Regards,
--
SailFLYou get a command prompt by going to Start->Run and typing CMD. From that,
you run SQLCMD. From what you have given us, try:
SQLCMD -S NGT\SQLEXPRESS
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SailFL" <SailFL@.discussions.microsoft.com> wrote in message
news:28954F43-0CBC-42FC-B7BC-B14C3E67BC83@.microsoft.com...
I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this
part.
Thank you for your help.
Regards,
SailFL|||SQLCMD can be issued from the commadn prompt of Windows, so
Start->Run->cmd
int he doscommand you can type in e.g.
sqlcmd.exe -S NGT\SQLEXPRESS -E
-Whereas NGT is the servername and SQLEXPRESS the instancename
-E if you are using Trusted authentication to connect to the database
server.
You can test the successfull connection therefore that you get no error
are issue a Select command to any table:
USE AdventureWorks
GO
SELECT * from Person.Contact
GO
--or simpler
SELECT 1
GO
HTH, jens Suessmeyer.|||Thank both you for your assistance.
I was able to use the commands and to attach the AdventureWorks Database to
my SQL server.
--
SailFL
"Jens" wrote:
> SQLCMD can be issued from the commadn prompt of Windows, so
> Start->Run->cmd
> int he doscommand you can type in e.g.
> sqlcmd.exe -S NGT\SQLEXPRESS -E
> -Whereas NGT is the servername and SQLEXPRESS the instancename
> -E if you are using Trusted authentication to connect to the database
> server.
> You can test the successfull connection therefore that you get no error
> are issue a Select command to any table:
> USE AdventureWorks
> GO
> SELECT * from Person.Contact
> GO
> --or simpler
> SELECT 1
> GO
>
> HTH, jens Suessmeyer.
>

New SQL Server User Questions

I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this par
t.
Thank you for your help.
Regards,
SailFLYou get a command prompt by going to Start->Run and typing CMD. From that,
you run SQLCMD. From what you have given us, try:
SQLCMD -S NGT\SQLEXPRESS
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SailFL" <SailFL@.discussions.microsoft.com> wrote in message
news:28954F43-0CBC-42FC-B7BC-B14C3E67BC83@.microsoft.com...
I have installed SQL Server 2005 Express. I am having trouble understanding
how to connect up with the AdventureWorksDB. Looking at the online book, I
found these instructions:
1) Open the command prompt on the server.
2) From the command prompt, connect to an instance of SQL Server by using
the following sqlcmd command:
sqlcmd -S Server\Instance
Where Server is the name of the computer and Instance is the name of the
instance.
These instructions assume you have some previous knowledge.
1) I am assuming that you are in the SQL Server Management Express Console.
2) How do you open the command prompt? Or where is it?
3) The "sqlcmd -S Server\Instance" instruction. I looked at the SQL Server
Service Manager and I believe I have the right information for my
installation: Server = NGT\SQLEXPRESS and Instance = SQL Server which came
from the Services:.
If this is not correct, what name do I use for the Server and Instance?
After I run this command, what will the results be and how will I know they
have run correctly?
I believe I can follow the remaining instructions if I can get past this
part.
Thank you for your help.
Regards,
SailFL|||SQLCMD can be issued from the commadn prompt of Windows, so
Start->Run->cmd
int he doscommand you can type in e.g.
sqlcmd.exe -S NGT\SQLEXPRESS -E
-Whereas NGT is the servername and SQLEXPRESS the instancename
-E if you are using Trusted authentication to connect to the database
server.
You can test the successfull connection therefore that you get no error
are issue a Select command to any table:
USE AdventureWorks
GO
SELECT * from Person.Contact
GO
--or simpler
SELECT 1
GO
HTH, jens Suessmeyer.|||Thank both you for your assistance.
I was able to use the commands and to attach the AdventureWorks Database to
my SQL server.
SailFL
"Jens" wrote:

> SQLCMD can be issued from the commadn prompt of Windows, so
> Start->Run->cmd
> int he doscommand you can type in e.g.
> sqlcmd.exe -S NGT\SQLEXPRESS -E
> -Whereas NGT is the servername and SQLEXPRESS the instancename
> -E if you are using Trusted authentication to connect to the database
> server.
> You can test the successfull connection therefore that you get no error
> are issue a Select command to any table:
> USE AdventureWorks
> GO
> SELECT * from Person.Contact
> GO
> --or simpler
> SELECT 1
> GO
>
> HTH, jens Suessmeyer.
>

NEW SQL Server Release`

Check this out
this is the replacement for MSDE 2000

SQL Server 2005 Express Edition (http://lab.msdn.microsoft.com/express/sql/default.aspx)

Top 10 cool things about SQL Server Express Edition (http://lab.msdn.microsoft.com/express/sql/top10/default.aspx)

SQL Server Express Books Online (http://www.microsoft.com/downloads/details.aspx?FamilyId=2ADBC1A8-AE5C-497D-B584-EAB6719300CD&displaylang=en)

by the way if you follow the first link, you will find a pretty accurate photo of rdjabarov...Check this out
you will find a pretty accurate photo of rdjabarov...
Looks pretty nice and decent just like the Express Edition. Where he's from by the way, USA!!|||Hey, you see what you started, Scott? And YOU KNOW I don't look like this!.. And where the h*** did you get 2005 Express? In Ala-freaking-bama?|||Hey now, I'm from Ala-freaking-bama|||Hey, you see what you started, Scott? And YOU KNOW I don't look like this!.. And where the h*** did you get 2005 Express? In Ala-freaking-bama?

i'm just kidding
but on further reflection he kind of looks like fabian pascal.
Pat can verify that for us because as we all know, he's fabian's best friend.|||Do you really believe that I think that way?..Well, I DO love Texas, but I am ready for a change (ideally would be to move without my kids knowing where to :D )|||Anyone else think that CLR is a bunch of nonsense?|||Anyone else think that CLR is a bunch of nonsense?
I think it's VERY neat. I also think that this will force some hard core production DBA's to have a hard look at themselves and see if the career they've chosen is what they want to continue pursuing. Personally, I am happy with a definition that is given to me, - hybrid DBA! And the direction the job description is taking is where I am at now.|||Pat can verify that for us because as we all know, he's fabian's best friend.It's good to be loved, isn't it? Actually, I've never met Fabian in person, or even seen a picture from a reliable source, so I'm no authority there. It's hard to imagine that I've never seen anyone that loves me so, isn't it?

-PatP|||Anyone else think that CLR is a bunch of nonsense?Not really, I think that the CLR brings a lot of interesting benefits, but it brings some new risks in the baggage with it.

The benefits are basically the ones that UCSD promised with the p-machine and Java didn't quite deliver with their virtual machine. A standard, more or less hardware independant virtual machine. One code base for many platforms. One target for many compilers, which allows much better tools to evolve because the economies of scale kick in much sooner.

The big risks seem to be that once something makes it into the runtime, who will ensure that it makes sense for it to stay there as it evolves. Many things that make perfect sense on the desktop (or even handheld) are a positive liability on a server!

It is a two edged sword. It brings many benefits, but at a significant cost.

-PatP|||I think it's VERY neat.

Why? And yes, PROD DBA's who sit there waiting for an alert...sure...but someones got to do that...and I'm glad it's not me...not sure if they even know what a sproc is...let alone a trigger...

And what can you do with "External Procedures" (CLR) that you can't do with T-SQL or it's extensions?

CLR is just more overhead...

And I'd love to see what they mean by Extensive XML support...

What? Did they add Relational extenders to XML?|||Well, I've run to the rescue of "External Procedures" via sp_oaxxx. You're write, the same thing can be accomplished by T-SQL, but the final choice is based on many factors. I prefer to use a tool for a task that is designed to do that task. Of course I can create a file using T-SQL, but if there is a better way to do the same thing (Scripting.FileSystemObject) I'd pick that one.|||i like the concept and intent of the Common Language Runtime.
now you can develop in your native language without having to learn an entirely new discipline.
standardization is a good thing. it allows developers to get back to the act of developing logic instead of all of these funky diversions that we have to endure.
case in point --dbforums.com
what percent of all of the questions raised here are by dba's to dba's about dba stuff.
most of them are actually developers trying to reinvent the wheel in a entirely new discipline (T-sql SQL Server etc..). by giving them a internal wrapper for all of their "stuff" they can truly screw everything up and then call us "The Consultant" to fix it (for a fee of course)|||I think Pat summed it up pretty well. There are some things that the CLR will just do better. There's also a LOT of risk if it's used wrong. The XML thing actually worries me more then the CLR though. It's harking back to the days of hierarchial design and promising "extensibility". I have a feeling we'll end up regretting the degradation of a relational environment to cater to the desires of a shortcut "extend into anything without considering the consequences" world. Oh well....rant off.|||i like the concept and intent of the Common Language Runtime.
now you can develop in your native language without having to learn an entirely new discipline.
standardization is a good thing. it allows developers to get back to the act of developing logic instead of all of these funky diversions that we have to endure.
case in point --dbforums.com
what percent of all of the questions raised here are by dba's to dba's about dba stuff.
most of them are actually developers trying to reinvent the wheel in a entirely new discipline (T-sql SQL Server etc..). by giving them a internal wrapper for all of their "stuff" they can truly screw everything up and then call us "The Consultant" to fix it (for a fee of course)
Right on, man, right on!

Wednesday, March 21, 2012

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,

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.

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

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:

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 SQL Express

I am working with Microsoft Office Access 2003 on MSWXP Professional SP2. In attempting to open one of the sample database projects that come with Access 2003, NorthwindCS.adp, I get the following message: "Microsoft Access could not find either Microsoft SQL Server 2000 Desktop Engine or Microsoft SQL Server installed on your computer. Click OK to display the Data Link Properties dialog box and log on to a remote server. If you want to install SQL Server 2000 Desktop Engine on this computer first, click Cancel, install it from the MSDE2000 folder on the Office CD-ROM, and then reopen this Access project". Instead of downloading & installing the Microsoft SQL Server 2000 Desktop Engine, I downloaded & installed the SQL Server 2005 Express Edition SP1 instead. After having restarted the computer and relaunching Access 2003 & opening the NorthwindCS.adp I still get the same message as mentioned previously above. My question is can the SQL Server 2005 Express Edition SP1 be used or must the SQL Server 2000 Desktop Engine be used instead. I understand that running Microsoft Windows Vista must use the SQL Server 2005 Express Edition SP1 but I am running MSWXP Professional SP2 instead. Advise?

Hi,

AFAIC remember 2003 ADP projects are not fully supported / working in conjunction with SQL Server Express even with t the compt. settings changed.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

New spin on SQL Server Express management.

Hi there - I asked the question about automated adminsitration on Express and was given some good solutions since it didn't have SSIS which is used to schedule admin tasks.

HOWEVER, unless I am wrong, I think I heard at PASS that in SP2 SQL Server Express will have maintenance plans -- and thus SSIS? Is this true -- can someone fill me in. I was happy when tooling came down to Express and automated maintenance would help as well.

Thanks

Hi LearningDBA,

You're only option for automated admin of an Express instance is to use the Windows Task scheduler to run SQLCMD scripts. I provided a brief demo for a TechNet webcast - http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032294987&EventCategory=5&culture=en-US&CountryCode=US. You can't create Maint Plans against a SQL Server Express instance because it requires SQL Agent.

Thank you,
Bill Ramos

sql

Monday, March 19, 2012

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!

Many Thanks

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Many Thanks

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Server Registration offers no Server Name options

Real newbie question this, I suppose. (Apologies if in the wrong forum!)

Have just downloaded and installed SQL Server Express. When I attempt to start it up, the New Server Registration dialogue box appears. When I attempt to enter a Server Name, there are no options in the drop-down box except <Browse for more>, which then offers me neither Local Servers or Network Servers.

The machine is running XP Pro and I wish to use the SQL package to develop on the PC as a stand-alone environment while I evaluate it.

So what am I doing wrong?

Many thanks in advance for any help.

I have been looking through previous threads and camce across an entry from Mike Wachal, who suggested typing the following into a command window.

SQLCmd -S .\sqlexpress -E

The response was 'SQLCmd is not recognized as an internal or external command, operable program or batch file'. Is this anything to do with my problem?

|||

Duh! I realized that I had only downloaded the Management Studio package and not SQL Server itself. All working now ...

Wednesday, March 7, 2012

New Installation - Can Not Connet to Server On My PC

I have just installed SQL Express and Server Management Studio on my PC. I also have IIS 5.0 on WinXP. When I open Management Studio and try to connect the sI get the following error message:- Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider) .

This happens if I change the server, change the authentication method or try my user name or the 'sa ' user. To make things even more frustrating I did the same installation on my laptop earlier this year with no problems.

Has anyone any answers to my problem?

Thanks

hi,

having modified the authentication property (and restarted the service), did you enable the sa login?.. verify in the Instance->Security->Logins->sa Properties (Status tab) the login is enabled..

regards

Friday, March 30, 2012

New to SQL 2005 Express

Wednesday, March 28, 2012

New To Databasing

New to asp

Monday, March 26, 2012

New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up

Friday, March 23, 2012

New SQL Server User Questions

New SQL Server User Questions

New SQL Server User Questions

NEW SQL Server Release`

Wednesday, March 21, 2012

New SQL Express User - No Server Connection

New SQL Express User - No Server Connection

New SQL Express Installation

New SQL Express

New spin on SQL Server Express management.

Monday, March 19, 2012

New Server Registration to External Database

New Server Registration to External Database

New Server Registration offers no Server Name options

Wednesday, March 7, 2012

New Installation - Can Not Connet to Server On My PC

database file locations

Blog Archive

About Me