HOW DO I ACCESS SQL SERVER TO USE NORTHWIND DB

I am attempting to access the SQL server in order to use the Northwind database.

Typing in via the command prompt the following:

osql –E -1 “EXEC sp_attach_db N’Northwind’,N’c:\SQL Server 2000 Sample Databases\northwind.mdf’”

The command prompt response:

[SQL Native Client] "An error has occurred while establishing a connection to the server when connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connects."

Also, I loaded SQL Server Management Studio Express and not sure if I even need this. I click on the Icon but I’m not sure what I need to do to establish a logon and connect.

Any help will be appreciated.

Thanks!!

configure sql server express to

accept remote connection by

runing the sqlserver surface are configuration

Start>programs>sqlserver2005>configuration tools>surface area configuration

>configuration for service and connection>remote connection>local and REMOTE connection

|||

Make sure that you follow all of the instructions in the following KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277.

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the ans

We are going to need a little more context to evaluate your exact situation, and how to do what you ask, but here's 1 method:

Dim sqlConn as new SqlConnection("connection string here")

Dim sqlCmd as new SqlCommand("SELECT password from users where username = @.username", SqlConn)

sqlCmd.Paramaters.AddwithValue("@.username", username.text)

Dim DA as new SqlDataAdapter(SqlCmd)

Dim DT as new DataTable

DA.Fill(DT)

Dim myString as string = DT.rows(0).item("password").value ' This line may not be totally correct, doing it from memory and will edit it later

If myString.tolower = stringToCompare.tolower then

'Password is valid

else

'Password could be valid - but something threw you into the else statment, maybe an empty string.

end if

|||

Connection string and select command are already defined in the SqlDataSource.

how can you use the SqlDataSource to extract the value of the password?

i tried to use a hidden GridView but i didn't know how to access a specific cell

by the way i am using C#

|||

The above example is a much cleaner method of getting information like this. Putting a hidden gridview on your page is a VERY heavy method of trying to extract a single value, but it can be done.

Please see this article on how to extract a Datatable from the SqlDataSource control. Once you've done that, you can access a specific row of your datatable as mentioned above.

http://msmvps.com/blogs/egoldin/archive/2006/12/27/how-to-get-datatable-out-of-sqldatasource.aspx

I really have to recomend against this method though. If you insist on using the SqlDataSouce control to get this done, consider putting two hidden textbox on the page, and binding the value that comes back from the database to the hidden textbox, then use a compare validator and only let the user continue if page.isvalid.

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?!

to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the answer

Hello Ahmad,

Here's some code that should help you out... I'd be happy to clarify anything if needed.

using System.Data;

using System.Data.SqlClient;

string userPassword;

string userName;

SqlConnection cnn = new SqlConnection("Data Source = (local); Initial Catalog = nameofdatabase; Integrated Security = True");

SqlCommand cmd = new SqlCommand("select Password From Users Where username = @.username", cnn);

SqlParameter UserName = new SqlParameter("@.username", userName);

cmd.Parameters.Add(UserName);

cnn.Open();

string password = (string)cmd.ExecuteScalar();

cnn.Close();

cnn.Dispose();

if (userPassword == password)

{

// success! Do something here

}

else

{

// Failure...

}

Regards.

sql

How come this dont work?

Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
error out for me.
Heres what I have;
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
FIELDTERMINATOR = '~'
)
I am running this thru QA logged in as the Admin.
The file is in the location and the date looks like so, in Notepad.
302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
~ is the field and row terminator.
When I run above cmd I get it telling me it ran successfully.
But theres no data in table and it runs instantly.
Any help would be appreciated.
Thanks
Deasun
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.What is the definition of the table. Also, you didn't specify a row separator. Perhaps the table has
only one columns and you really want ~ as the row terminator?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deasun" <Deasun@.discussions.microsoft.com> wrote in message
news:BCA35F53-B895-4F6A-9435-8AA493BA3338@.microsoft.com...
> Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
> error out for me.
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
> I am running this thru QA logged in as the Admin.
> The file is in the location and the date looks like so, in Notepad.
> 302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
> ~ is the field and row terminator.
> When I run above cmd I get it telling me it ran successfully.
> But theres no data in table and it runs instantly.
> Any help would be appreciated.
> Thanks
> Deasun
> --
> Deasun
> Home Site: www.tirnaog.com
> Check out: The Code Vault in my forums section.|||"Deasun" wrote:
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
That should be:
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
ROWTERMINATOR = '~'
)
ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
get the idea.
Maury|||Thanks to you both for the reply.
I had specified both row and field terminators at one point neither worked.
We now think it has something to do with the SQL server itself.
We have tried the cmd on another server and it worked fine. :)
Thanks again.
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.
"Maury Markowitz" wrote:
> "Deasun" wrote:
> > Heres what I have;
> > BULK INSERT TBL_Master_DoNotCallList_Loader
> > FROM 'E:\DE_210652006.txt'
> > WITH
> > (
> > FIELDTERMINATOR = '~'
> > )
> That should be:
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> ROWTERMINATOR = '~'
> )
> ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
> get the idea.
> Maury
>sql

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoy
Typically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:

> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> the
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoyTypically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:

> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> the
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoyTypically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
--
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:
> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> > Good afternoon,
> > I'd like to know what the command or where you look to determine if
> the
> > disks on an array are raided or not. Is there a command we can run to see
> > what it is. I'm talking specifically about hardware raid arrays only.
> >
> > Thanks,
> >
> > LeeRoy
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can I use the Variables in SQL Execute SQL task?

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @.[User::varOutTable] from @.[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?

thanks!!

Use an expression to build your SQL statement. This is:

Open SQL Task editor|||

thank for your reply.

you are right,

it runs great now.

|||Will this work for a select count(*)?|||

agentf1 wrote:

Will this work for a select count(*)?

Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||

agentf1 wrote:

Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.

That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.|||

That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.

If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error

|||That was it, I needed to change result set to single row. Thanks.

How can I use the Variables in SQL Execute SQL task?

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @.[User::varOutTable] from @.[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?

thanks!!

Use an expression to build your SQL statement. This is:

Open SQL Task editor|||

thank for your reply.

you are right,

it runs great now.

|||Will this work for a select count(*)?|||

agentf1 wrote:

Will this work for a select count(*)?

Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||

agentf1 wrote:

Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.

That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.|||

That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.

If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error

|||That was it, I needed to change result set to single row. Thanks.