Getting Sql Variable value

Hi, Looking for a sample in C# (ASP2.0 vwd) of getting a SqlDataSource Variable (@.uNameID) into a string Variable (string uNameID)

Any help would be appreciated!

<asp:SqlDataSource ID="SqlDataSource3" runat="server" OnSelected="SqlDataSource3_Selected" ConnectionString="<%$ ConnectionStrings:testExpressConnectionString %>"
SelectCommand="SELECT userName, userPWD FROM user_test whereuserName=@.userName ">

<SelectParameters>
<asp:controlParameter Name="userName" Direction="InputOutput" Type="String" ControlID="TextBox1" />

</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="Label2" runat="server" Text="Label" Width="258px"></asp:Label><asp:Button
ID="Button1" runat="server" Text="Button" /><asp:Label ID="Label3" runat="server"
Text="Label"></asp:Label>

code--

Protected Sub SqlDataSource3_Selected(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)

Dim myvalue As Object = e.Command.Parameters("@.userName").Value
Label3.Text = CType(myvalue, String)

End Sub

--code

Within the selectparameter part, you can change the direction property to meet your need.

|||

Thank for the reply! Is it possible to see the code in C#? I don't know VB.

|||

protected void SqlDataSource3_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
object myvalue = e.Command.Parameters("@.userName").Value;
Label3.Text = ((string)(myvalue));
}

By the way, for learning purpose you can use this VB.NET to C# converter to convert some code snippets.

http://www.developerfusion.co.uk/utilities/convertvbtocsharp.aspx

|||

Thanks again for the help but I'm getting the error:

"Parameters is a Property' but is used like a method"

|||

bmanmike39:

Thanks again for the help but I'm getting the error:

"Parameters is a Property' but is used like a method"

C# uses [] brackets for indexers, so it should be:

object myvalue = e.Command.Parameters["@.userName"].Value;

HTH,
Ryan

|||

Now i'm getting the exception : "an sql Parameter with parameter name @.userName is not contained in this sql parameter collection"

Thanks for any help!

sql

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?
|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx

*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

execsp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

execsp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,sql

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?
|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx

*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

exec sp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

exec sp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx

|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx

*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

exec sp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

exec sp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?
|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx

*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

execsp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

execsp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?
|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx

*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

exec sp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

exec sp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,

Getting only multiple records for all customers

Hi,

How can I get desired data results from Sample Data given using SQL statement? Preferably without using Cursors and simple as possible.

Sample Data:

OrderID CustomerID ProductID
2 2 2
3 3 null
4 3 2
5 2 2
6 4 3
7 5 4
8 2 10
11 3 5
13 5 8
14 6 9

Desired Results:

OrderID CustomerID ProductID
2 2 2
4 3 2
5 2 2
7 5 4
8 2 10
11 3 5
13 5 8

It is not easy to 'guess' what criteria you have used.

Obviously, eliminating OrderID 3 is because nothing was ordered.

But why have OrderIDs 6 and 14 been left out?

|||

The following query will work for You..(multiple records for all customers - customers who have multiple records)

Select * from Orders Where
CustomerId in (Select CustomerId from Orders Group By CustomerId Having Count(ProductID) > 1)
and ProductId is NOT NULL

|||

Thanks for reply, Arnie.

Thanks for the answer, ManiD.

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXECsp_configure'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXEC sp_configure 'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus