Showing posts with label edition. Show all posts
Showing posts with label edition. Show all posts

Friday, March 30, 2012

ID Edition? General MS SQL training?

I have two questions.

First is there an easy way to tell what
edition of MS SQL Server is on a machine?
I found some code that is supposed to do
this, but if I put that code into the query
analyzer, it doesn't work. (Doesn't look
like SQL query in any case.)

A more general question: Education about SQL.

I have an MS in biostats so I'm not totally
computer illiterate, but I've never programmed
anything GUI.

Thanks for any answers

Michael Young<mcl2@.vms.cis.pitt.edu> wrote in message
news:c5eton$kp4$1@.usenet01.srv.cis.pitt.edu...
> I have two questions.
> First is there an easy way to tell what
> edition of MS SQL Server is on a machine?
> I found some code that is supposed to do
> this, but if I put that code into the query
> analyzer, it doesn't work. (Doesn't look
> like SQL query in any case.)
> A more general question: Education about SQL.
> I have an MS in biostats so I'm not totally
> computer illiterate, but I've never programmed
> anything GUI.
> Thanks for any answers
> Michael Young

SELECT @.@.VERSION
SELECT SERVERPROPERTY('Edition')

The second one is only in SQL2000 only. As for education, you might want to
start with some general books on databases and the SQL language, although
Microsoft's own training material is usually pretty good. You may find some
useful information here:

http://vyaskn.tripod.com/sqlbooks.htm

Simon|||<mcl2@.vms.cis.pitt.edu> wrote in message
news:c5eton$kp4$1@.usenet01.srv.cis.pitt.edu...
> I have two questions.
> First is there an easy way to tell what
> edition of MS SQL Server is on a machine?
> I found some code that is supposed to do
> this, but if I put that code into the query
> analyzer, it doesn't work. (Doesn't look
> like SQL query in any case.)
> A more general question: Education about SQL.
> I have an MS in biostats so I'm not totally
> computer illiterate, but I've never programmed
> anything GUI.
> Thanks for any answers
> Michael Young

SELECT @.@.VERSION
SELECT SERVERPROPERTY('Edition')

The second one is only in SQL2000 only. As for education, you might want to
start with some general books on databases and the SQL language, although
Microsoft's own training material is usually pretty good. You may find some
useful information here:

http://vyaskn.tripod.com/sqlbooks.htm

Simon

IBMDASQL OLEDB linked server "access denied" using Windows Authentication

I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".

The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.

In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.

So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.

Does anyone know why?

I had the same problem and here is how I fixed it:

Following the advice from another post, ensure "Allow inprocess" is selected for the linked server provider:

Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--

Ensure the ID has permission to use the linked server (try with sysadmin for starters)

Ensure the security tab of the linked server is using an AS400 ID and password

I also noticed that my default database as my login ID was tempdb. If I changed my context to master, it ran without giving me the permission error.

Hope this helps!

|||

Lexner55 -

That fixed it for me. Thanks very much. FYI, I am using the IBMDASQL provider.

|||

I am actually not using the IBMDASQL driver anymore for my servers as I noticed there was quite a difference in response time versus using ODBC (which as a side note is not available in 64 bit servers).

I tested the difference between the two drivers by doing a select * on a large table and compared the time it took to complete using the IBMDASQL driver versus the ODBC driver. It took the ODBC driver a little over 3 minutes to call the 91000 rows whereas it took the IBMDASQL driver over 7 minutes running the same query. With a 64 bit server, I am using the OLE for DB2 driver provided by Microsoft and it's comparable to the ODBC driver.

For the record, I am not saying IBMDASQL should not be used. If you do not need to call large data sets from the AS400, this driver will be fine (in production scenarios as we all know, it's not best practice to select * anyways). It could also be an issue with how our AS400 server is configured or some other setting I may be missing. Regardless, you might want to run some tests and compare the different drivers.

I'm glad to hear you got it working though. Thanks for letting me know.

|||

Lexner55 -

We had similar performance issues when we converted from ODBC to OLE using the IBMDASQL driver. We got around the major performance issues by using OPENQUERY instead of direct SQL, although we are still not very happy with the performance. I believe the reason for the improvement using OPENQUERY is that OPENQUERY ships the query to DB2 and lets DB2 optimize and execute it whereas a direct query ( using 4 part names ) pulls all tables involved over to SQL Server and then optimizes and exeutes the query on SQL Server. I could be wrong about that but that's the way it looks to me.

In our 64 bit environment, since the ODBC driver is no longer available, we are using the IBMDASQL OLE driver. Where did you get the OLE for DB2 driver that you referred to? I don't see it in my list of providers when I look in Management Studio.

|||

Lexner55 -

I found the DB2OLEDB driver in the feature pack. So, thank again.

IBMDASQL OLEDB linked server "access denied" using Windows Authentication

I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".

The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.

In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.

So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.

Does anyone know why?

I had the same problem and here is how I fixed it:

Following the advice from another post, ensure "Allow inprocess" is selected for the linked server provider:

Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--

Ensure the ID has permission to use the linked server (try with sysadmin for starters)

Ensure the security tab of the linked server is using an AS400 ID and password

I also noticed that my default database as my login ID was tempdb. If I changed my context to master, it ran without giving me the permission error.

Hope this helps!

|||

Lexner55 -

That fixed it for me. Thanks very much. FYI, I am using the IBMDASQL provider.

|||

I am actually not using the IBMDASQL driver anymore for my servers as I noticed there was quite a difference in response time versus using ODBC (which as a side note is not available in 64 bit servers).

I tested the difference between the two drivers by doing a select * on a large table and compared the time it took to complete using the IBMDASQL driver versus the ODBC driver. It took the ODBC driver a little over 3 minutes to call the 91000 rows whereas it took the IBMDASQL driver over 7 minutes running the same query. With a 64 bit server, I am using the OLE for DB2 driver provided by Microsoft and it's comparable to the ODBC driver.

For the record, I am not saying IBMDASQL should not be used. If you do not need to call large data sets from the AS400, this driver will be fine (in production scenarios as we all know, it's not best practice to select * anyways). It could also be an issue with how our AS400 server is configured or some other setting I may be missing. Regardless, you might want to run some tests and compare the different drivers.

I'm glad to hear you got it working though. Thanks for letting me know.

|||

Lexner55 -

We had similar performance issues when we converted from ODBC to OLE using the IBMDASQL driver. We got around the major performance issues by using OPENQUERY instead of direct SQL, although we are still not very happy with the performance. I believe the reason for the improvement using OPENQUERY is that OPENQUERY ships the query to DB2 and lets DB2 optimize and execute it whereas a direct query ( using 4 part names ) pulls all tables involved over to SQL Server and then optimizes and exeutes the query on SQL Server. I could be wrong about that but that's the way it looks to me.

In our 64 bit environment, since the ODBC driver is no longer available, we are using the IBMDASQL OLE driver. Where did you get the OLE for DB2 driver that you referred to? I don't see it in my list of providers when I look in Management Studio.

|||

Lexner55 -

I found the DB2OLEDB driver in the feature pack. So, thank again.

Monday, March 26, 2012

I/O error (torn page) detected during read

we are running a maintainance plan on sql 2000 standard edition, got the error,
[2] Database db_source: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page (1:197116)
could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1221579390, index ID 0, page (1:197116).
Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057
and -1.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'xxx'(object ID 1221579390).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'db_source'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (db_source noindex).
when i run query on anlyzer select * from xxx, i got the error
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000603f8000 in
file 'F:\Program Files\Microsoft SQL Server\MSSQL\data\db_Data.MDF'.
Connection Broken
please help. thanks
Unless this is a nonclustered index your best bet is to restore from the
last known good backup. Do you have Torn Page Detection turned on for that
db? If not you should so you can spot issues like this sooner. In any case
have a look at this series from Paul Randal on checkdb and what your options
are.
[url]http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAng le.aspx[/url]
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:DF042B2B-3DE5-455A-90C2-038534D6CEC7@.microsoft.com...
> we are running a maintainance plan on sql 2000 standard edition, got the
> error,
> [2] Database db_source: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page
> (1:197116)
> could not be processed. See other errors for details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1221579390, index ID 0, page (1:197116).
> Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
> 2057
> and -1.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'xxx'(object ID 1221579390).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'db_source'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is
> the
> minimum repair level for the errors found by DBCC CHECKDB (db_source
> noindex).
> when i run query on anlyzer select * from xxx, i got the error
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000603f8000 in
> file 'F:\Program Files\Microsoft SQL Server\MSSQL\data\db_Data.MDF'.
> Connection Broken
> please help. thanks
>
|||i found that the error is dated back to 2006, so the backup since then won't
be valid, right?
what does this mean: repair_allow_data_loss is the minimum repair level for
the errors found by DBCC CHECKDB (db_source noindex).
thank you
"Andrew J. Kelly" wrote:

> Unless this is a nonclustered index your best bet is to restore from the
> last known good backup. Do you have Torn Page Detection turned on for that
> db? If not you should so you can spot issues like this sooner. In any case
> have a look at this series from Paul Randal on checkdb and what your options
> are.
> [url]http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAng le.aspx[/url]
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "tulip" <tulip@.discussions.microsoft.com> wrote in message
> news:DF042B2B-3DE5-455A-90C2-038534D6CEC7@.microsoft.com...
>
|||> what does this mean: repair_allow_data_loss is the minimum repair level
> for
> the errors found by DBCC CHECKDB (db_source noindex).
It means that an attempt to repair the table may result in data loss, but
the only way you can try at all, is to allow for that to happen.

I/O error (torn page) detected during read

we are running a maintainance plan on sql 2000 standard edition, got the error,
[2] Database db_source: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page (1:197116)
could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1221579390, index ID 0, page (1:197116).
Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'xxx'(object ID 1221579390).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'db_source'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (db_source noindex).
when i run query on anlyzer select * from xxx, i got the error
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000603f8000 in
file 'F:\Program Files\Microsoft SQL Server\MSSQL\data\db_Data.MDF'.
Connection Broken
please help. thanksUnless this is a nonclustered index your best bet is to restore from the
last known good backup. Do you have Torn Page Detection turned on for that
db? If not you should so you can spot issues like this sooner. In any case
have a look at this series from Paul Randal on checkdb and what your options
are.
http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAngle.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:DF042B2B-3DE5-455A-90C2-038534D6CEC7@.microsoft.com...
> we are running a maintainance plan on sql 2000 standard edition, got the
> error,
> [2] Database db_source: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page
> (1:197116)
> could not be processed. See other errors for details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1221579390, index ID 0, page (1:197116).
> Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
> 2057
> and -1.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'xxx'(object ID 1221579390).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'db_source'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is
> the
> minimum repair level for the errors found by DBCC CHECKDB (db_source
> noindex).
> when i run query on anlyzer select * from xxx, i got the error
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000603f8000 in
> file 'F:\Program Files\Microsoft SQL Server\MSSQL\data\db_Data.MDF'.
> Connection Broken
> please help. thanks
>|||i found that the error is dated back to 2006, so the backup since then won't
be valid, right?
what does this mean: repair_allow_data_loss is the minimum repair level for
the errors found by DBCC CHECKDB (db_source noindex).
thank you
"Andrew J. Kelly" wrote:
> Unless this is a nonclustered index your best bet is to restore from the
> last known good backup. Do you have Torn Page Detection turned on for that
> db? If not you should so you can spot issues like this sooner. In any case
> have a look at this series from Paul Randal on checkdb and what your options
> are.
> http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAngle.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "tulip" <tulip@.discussions.microsoft.com> wrote in message
> news:DF042B2B-3DE5-455A-90C2-038534D6CEC7@.microsoft.com...
> > we are running a maintainance plan on sql 2000 standard edition, got the
> > error,
> >
> > [2] Database db_source: Check Data Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
> > SQL
> > Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page
> > (1:197116)
> > could not be processed. See other errors for details.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1221579390, index ID 0, page (1:197116).
> > Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
> > 2057
> > and -1.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> > errors and 2 consistency errors in table 'xxx'(object ID 1221579390).
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> > errors and 2 consistency errors in database 'db_source'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is
> > the
> > minimum repair level for the errors found by DBCC CHECKDB (db_source
> > noindex).
> >
> > when i run query on anlyzer select * from xxx, i got the error
> > Server: Msg 823, Level 24, State 2, Line 1
> > I/O error (torn page) detected during read at offset 0x000000603f8000 in
> > file 'F:\Program Files\Microsoft SQL Server\MSSQL\data\db_Data.MDF'.
> >
> > Connection Broken
> >
> > please help. thanks
> >
> >
>|||> what does this mean: repair_allow_data_loss is the minimum repair level
> for
> the errors found by DBCC CHECKDB (db_source noindex).
It means that an attempt to repair the table may result in data loss, but
the only way you can try at all, is to allow for that to happen.

Wednesday, March 21, 2012

I want to change this license option to per processor

Hi,
I have done installation of SQL Server 2000 Standard edition with
licensing option as per seat (no. of seats 100). By mistake I had
choosen this option. Now I want to change this license option to per
processor (no. of processors 4).
Pls advice me is there any way to change this license option without
re-installation of SQL Server.
Karan
http://www.tkdinesh.com/faq/ans/license.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
sql

I want to change this license option to per processor

Hi,
I have done installation of SQL Server 2000 Standard edition with
licensing option as per seat (no. of seats 100). By mistake I had
choosen this option. Now I want to change this license option to per
processor (no. of processors 4).
Pls advice me is there any way to change this license option without
re-installation of SQL Server.
Karanhttp://www.tkdinesh.com/faq/ans/license.html
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

I want to change this license option to per processor

Hi,
I have done installation of SQL Server 2000 Standard edition with
licensing option as per seat (no. of seats 100). By mistake I had
choosen this option. Now I want to change this license option to per
processor (no. of processors 4).
Pls advice me is there any way to change this license option without
re-installation of SQL Server.
Karanhttp://www.tkdinesh.com/faq/ans/license.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Monday, March 12, 2012

i really need some help!!!!!!!

well......im from peru....i dont speak english too well.......but...i have the SQL express edition....but when i open it....i gotta to connect to a server.....but i dont have any server.....what do i should put to in "server name" and "authentication"...to star to use the SQL express edition....

well..if there are some grammathical mistakes....sorry...im learnin` english

please answer me
is an emergency!!!!
Jaime

well generally you connect to the local computer\SQLExpress:

(local)\SQLExpress

in regards to authentication, again it depends what type of authentication you set up. Was it Windows/trusted authentication or SQL authentication? if its Windows then you just type in your Windows username followed by the Windows password I believe otherwise its the SQL user account you created

|||well if you can tell me what do i should put in "server name" and "authentication"......i`ll fell better|||

servername AFAIK is usually:

(local)\SQLExpress

as for authentication as stated, depends on the type of authentication you have set up. If its Windows, then enter your username and password you use for Windows login. If its SQL user account, enter the username and password for the SQL user account you created.

|||ahmedilyas...im 17...........im learnin SQL in my university....i dont understand what u said.......but-......how do i know the type of authentication i set up?....can u help me please!!!!!!!|||

try www.connectionstrings.com

Attach a database file on connect to a local SQL Server Express instance:

"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
(use |DataDirectory| when your database file resides in the data directory)

Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).

Using "User Instance" on a local SQL Server Express instance:

"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"

The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)

Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

|||

Hi Jamie,

When you installed SQL Express, you installed the server onto your computer. SQL Express installs by default to something called a named instances, which is just the way we refer to the name of the server. As long as you didn't change anything when you were installing, the name of your SQL Express instance is "SQLEXPRESS". When connecting to SQL Server, you have to specify both the machine name and the instance name, in the format <machine name>\<instance name>. In cases where you are connecting to a SQL Server that is on the same machine that your are logged into, you can replace the <machine name> part with either the word "(local)" or with a period "." as form of short hand. So, in order to connect to the default copy of SQL Express on the computer you are logged into, you would put the following in the Server name text box:

(local)\SQLEXPRESS

Normally, SQL Express uses Windows Authentication, which means that SQL will recognize the same user account that you use when logging onto Windows. If you did not change anything when you installed SQL Express, you should just be able to select Windows Authentication and SQL Express will recognize you without providing any additional user name or password.

Hope this helps,

Mike

|||Hi Michael,

I love your messages! You are excellent

at explaining answers to problems and queries for beginners.

You're the TOP answer provider on the MSDN site in terms of

quality! Thank you!

For your information, this query is probably a popular one due to that

lack of explanation on the SQL video lesson05. He simply assumes

that you can click on the "Connect" button without any explanation

about server name etc - and any beginner will, of course, probably be

opening SQL Server Management for the first time, so therefore would

require this information to continue further.

Like Me!!!

However, following your instructions, I now have another error message

that, according to your information re local connections sounds bizzaar.

I typed in:

(local)SQLEXPRESS

I left "Windows Authentication" as is.

And clicked connect.

The User Name came up with:

YOUR-6202AC0500\user

(which I vaguely recognise as seen somewhere else and appears to

describe my PC.) My directory for "Documents and Settings" is

"user's Documents" (I obviously haven't named my PC) and the owner is

Fran Smith (in case this is relevant) - sorry - as mentioned, beginner.

The error says the following:

Connect to Server

Cannot connect to (localSQLEXPRESS)

Additional Information:

An error has occurred while establishing a connection to the

server. When connecting to SQL server 2005, this failure may be

caused by the fact that under the default settings SQL Server does not

allow remote connections. (provider: Named Pipes Provider,

error: 40 - Could not open a connection to SQL Server)(Microsoft

SQL Server, Error: 53)

There's a little "Show Technical Details" icon at the bottom of the

error message, which I clicked on which provided a "For Help:" http

link. This took me to a page that began "We're sorry.

There's no additional information...". The "Help" icon took me to

the same page.

Why is it seemingly attempting to connect to a remote server when a)

I've specified local and b) it has also even found my PC user

name? What do I do to correct this. I did install it

accepting the defaults provided.

Thank you in advance.

I'm now a little frustrated. I have began the video sequences

(which is a fabulous learning tool because it is explained WHY etc, not

just click here/do this as the written tutorials tend to do); however I

am stuck awaiting answers to all three sequences:

VB - on the

databinding issue: I read the other forums etc, and found the

basic problem regarding the two databases/saving to only the

one/debugging overwrites etc, but I wanted to know how then, do we save

to the actual database, how and when does this occur, if not at that

time? refer VBLesson09.

VWD - userCount

doesn't work for me - either in FireFox (where it doesn't increment) or

in IE7 (where it continues to increment each time a window is

refreshed). refer VWDLesson03

And now, SQL Lesson05.

My other username is BasilTabethaCat. These have been outstanding

for 3 and 2 days respectively, with no reply attempts or answers.

How do I invoke an answer?

I am now stuck as I would rather not continue the learning process

while I have a hole in my understanding (I am a student teacher - so

I'm well informed on the pitfalls of this!)

HELP!!

Cheers,

Fran...

PS I meant the compliment above - your answers in several areas have helped me a lot!

i really need some help!!!!!!!

well......im from peru....i dont speak english too well.......but...i have the SQL express edition....but when i open it....i gotta to connect to a server.....but i dont have any server.....what do i should put to in "server name" and "authentication"...to star to use the SQL express edition....

well..if there are some grammathical mistakes....sorry...im learnin` english

please answer me
is an emergency!!!!
Jaime

well generally you connect to the local computer\SQLExpress:

(local)\SQLExpress

in regards to authentication, again it depends what type of authentication you set up. Was it Windows/trusted authentication or SQL authentication? if its Windows then you just type in your Windows username followed by the Windows password I believe otherwise its the SQL user account you created

|||well if you can tell me what do i should put in "server name" and "authentication"......i`ll fell better|||

servername AFAIK is usually:

(local)\SQLExpress

as for authentication as stated, depends on the type of authentication you have set up. If its Windows, then enter your username and password you use for Windows login. If its SQL user account, enter the username and password for the SQL user account you created.

|||ahmedilyas...im 17...........im learnin SQL in my university....i dont understand what u said.......but-......how do i know the type of authentication i set up?....can u help me please!!!!!!!|||

try www.connectionstrings.com

Attach a database file on connect to a local SQL Server Express instance:

"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
(use |DataDirectory| when your database file resides in the data directory)

Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).

Using "User Instance" on a local SQL Server Express instance:

"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"

The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)

Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

|||

Hi Jamie,

When you installed SQL Express, you installed the server onto your computer. SQL Express installs by default to something called a named instances, which is just the way we refer to the name of the server. As long as you didn't change anything when you were installing, the name of your SQL Express instance is "SQLEXPRESS". When connecting to SQL Server, you have to specify both the machine name and the instance name, in the format <machine name>\<instance name>. In cases where you are connecting to a SQL Server that is on the same machine that your are logged into, you can replace the <machine name> part with either the word "(local)" or with a period "." as form of short hand. So, in order to connect to the default copy of SQL Express on the computer you are logged into, you would put the following in the Server name text box:

(local)\SQLEXPRESS

Normally, SQL Express uses Windows Authentication, which means that SQL will recognize the same user account that you use when logging onto Windows. If you did not change anything when you installed SQL Express, you should just be able to select Windows Authentication and SQL Express will recognize you without providing any additional user name or password.

Hope this helps,

Mike

|||Hi Michael,

I love your messages! You are excellent

at explaining answers to problems and queries for beginners.

You're the TOP answer provider on the MSDN site in terms of

quality! Thank you!

For your information, this query is probably a popular one due to that

lack of explanation on the SQL video lesson05. He simply assumes

that you can click on the "Connect" button without any explanation

about server name etc - and any beginner will, of course, probably be

opening SQL Server Management for the first time, so therefore would

require this information to continue further.

Like Me!!!

However, following your instructions, I now have another error message

that, according to your information re local connections sounds bizzaar.

I typed in:

(local)SQLEXPRESS

I left "Windows Authentication" as is.

And clicked connect.

The User Name came up with:

YOUR-6202AC0500\user

(which I vaguely recognise as seen somewhere else and appears to

describe my PC.) My directory for "Documents and Settings" is

"user's Documents" (I obviously haven't named my PC) and the owner is

Fran Smith (in case this is relevant) - sorry - as mentioned, beginner.

The error says the following:

Connect to Server

Cannot connect to (localSQLEXPRESS)

Additional Information:

An error has occurred while establishing a connection to the

server. When connecting to SQL server 2005, this failure may be

caused by the fact that under the default settings SQL Server does not

allow remote connections. (provider: Named Pipes Provider,

error: 40 - Could not open a connection to SQL Server)(Microsoft

SQL Server, Error: 53)

There's a little "Show Technical Details" icon at the bottom of the

error message, which I clicked on which provided a "For Help:" http

link. This took me to a page that began "We're sorry.

There's no additional information...". The "Help" icon took me to

the same page.

Why is it seemingly attempting to connect to a remote server when a)

I've specified local and b) it has also even found my PC user

name? What do I do to correct this. I did install it

accepting the defaults provided.

Thank you in advance.

I'm now a little frustrated. I have began the video sequences

(which is a fabulous learning tool because it is explained WHY etc, not

just click here/do this as the written tutorials tend to do); however I

am stuck awaiting answers to all three sequences:

VB - on the

databinding issue: I read the other forums etc, and found the

basic problem regarding the two databases/saving to only the

one/debugging overwrites etc, but I wanted to know how then, do we save

to the actual database, how and when does this occur, if not at that

time? refer VBLesson09.

VWD - userCount

doesn't work for me - either in FireFox (where it doesn't increment) or

in IE7 (where it continues to increment each time a window is

refreshed). refer VWDLesson03

And now, SQL Lesson05.

My other username is BasilTabethaCat. These have been outstanding

for 3 and 2 days respectively, with no reply attempts or answers.

How do I invoke an answer?

I am now stuck as I would rather not continue the learning process

while I have a hole in my understanding (I am a student teacher - so

I'm well informed on the pitfalls of this!)

HELP!!

Cheers,

Fran...

PS I meant the compliment above - your answers in several areas have helped me a lot!

Friday, March 9, 2012

i need to migrate from sql 2005 express to sql workgroup edition

hello,

i have no idea what i am doing here and could use some help from someone who knows what they are doing.

i have a proliant ml350 g3 server running windows 2003 server with sql express 2005 software.

all the databases are running in compatibility sql 7 mode.

I would like them to be in sql 2005 mode if possible. Is there a way to upgrade the databases in the process?

here's a plan I came up with:

take sql workgroup 2005 edition evaluation, install it on a machine.

Mount all databases on the machine.

Wipe out the sql express 2005 machine, reinstall workgroup edition on the new server.

but here is my question

is there a way to migrate the data from the workgroup edition over to the new server through a DTS copy process, and if so, will it create a new version of the databases with the proper collation or the proper english?

please let me know. if this works, i will be thrilled!

First, you can upgrade in place by installing workgroup over Express on the same machine. Make sure you indicate you want to upgrade by selecting the Express instance name at the appropriate point during the install.

If you do want to switch machines as part of the upgrade, you can either copy the database files (both .mdf and .ldf) manually or use DTS. Either way, I suspect you're likely to lose logins and some other info - but I'm not a DTS expert so I may be misspeaking. You should post a question in that forum if you are going to consider using DTS.

Books On Line (BOL) has a good description of how to go from one compatibility mode to another. Use the sp_dbcmptlevel stored procedure, either before or after the upgrade.

Paul

|||

one more question Paul if you don't mind.

Does the BOL Books on Line article show how to change from the existing collation? I apparently have a "conflict"

when I start the SQL Server Management Studio and right click on any of my databases, they show this error:

"Cannont Show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

-> an exception occured while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Express.ConnectionInfo)

--> Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Compatibility_52_409_30003" in the equal to operation. (Microsoft SQL Server, Error: 468)

I have no idea what this means, which is why I had the idea to transform the packages using DTS?

please let me know if you have an answer. If this is for another forum, please let me know that. Thanks for your help.

|||

I would post the question about collation in the tools forum. Are Management Studio and Express on the same box? I have no idea what would cause such an error.

Paul

|||

thanks this has helped tremendeously.

I still have problems with collation but it looks like the SQL Server default collation and the master/temp/model db are different and this is causing problems.

I need to find a tool to rebuild the master and temp and model db.

One came with SQL 2000 but I do not have one for SQL 2005.

I am still searching for rbuild.exe to use.

hopefully I'll find it!

thanks,

j

Wednesday, March 7, 2012

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL Server Management Studio!

Hi,

I have just completely installed MS SQL Server 2005 Express Edition in MS Server 2003.
After installation, I only have the Configuration Manager, Error and Usage Reporting and Surface Area Configuration, listed under Configuration Details.

SQL Server Management Studio is not accessible anywhere.
I also do not have the folder C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell in which the SQL Server Management Studio is installed.
What must I do to get this component installed?

In this article http://msdn2.microsoft.com/en-us/library/ms143219.aspx, it is also written in step 8 that there should be a "Components to Install" page. I have done the installations and uninstallations a few times, but I still do not have "Components to Install" page. In fact, after step 7, I am brought to the "Features Selection" page.

What can I do to solve this problem? Please help.

Thanks.

You'll need to download the SQL Server Express Edition Toolkit. Get it at the following page:

http://msdn2.microsoft.com/en-gb/express/bb410792.aspx

HTH!

|||Thanks, it has helped a lot!