Calculate The Time To Run SP

Hi All
There is Any Way To Calculate The Time To Run SP Or Select Statement Before
Run It
For Example
SELECT *
FROM stores
WHERE (state = 'CA')
How long Time Take This Query to Run
Thankstry using
SET STATISTICS TIME ON|||No, there are no such facilities in SQL Server. One of the reasons is that t
he optimize can pick
different executing plans, and any estimates based on one execution plan wil
l be totally off if some
other execution plan is selected. You also have the probability of blocking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...

> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement Befor
e Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||Thank You Fro Reply
But What I Looking For UDF Or SP That Return Elapsed time For Select
Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Return
Just The Time To Calculate This Statement
Because I Have Large data and I want say to the user how many this query
take time
Thanks
"Taha" <taha105@.hotmail.com> wrote in message
news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement
> Before Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||As a maximum you could say them an approximation
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Taha" wrote:

> Thank You Fro Reply
> But What I Looking For UDF Or SP That Return Elapsed time For Select
> Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Retur
n
> Just The Time To Calculate This Statement
> Because I Have Large data and I want say to the user how many this query
> take time
> Thanks
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
>
>|||There is no way to find an accurate time for an SP to run. As Tibor Karaszi
had said, the same SP can run for different periods depending on the server
load, configuration, database size and the mood of the SQL Engine :)
You can only find the time it took for the current execution.|||Ok How find the time it took for the current execution Please
Only Time return Parameter I need
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
> There is no way to find an accurate time for an SP to run. As Tibor
> Karaszi
> had said, the same SP can run for different periods depending on the
> server
> load, configuration, database size and the mood of the SQL Engine :)
> You can only find the time it took for the current execution.
>|||You have to do this yourself. Either in the client application (declare a va
riable, set it to
current time before execution and after execution check number of ms or s el
apsed), or in the stored
procedure with same basic logic and have an output parm of the procedure whe
re you send out the
number of ms.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
> Ok How find the time it took for the current execution Please
> Only Time return Parameter I need
> Thanks
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
>[/color]|||Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguNqexVGHA.1536@.TK2MSFTNGP15.phx.gbl...
> You have to do this yourself. Either in the client application (declare a
> variable, set it to current time before execution and after execution
> check number of ms or s elapsed), or in the stored procedure with same
> basic logic and have an output parm of the procedure where you send out
> the number of ms.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...
>|||You can use profiler also.
Regards
Amish shahsql

Calculate sum of SQL Top 10

I have an SQL statement which returns the Top 10 states with the number of visitors

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

So far this is what I have

state| visitors

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9

But what i need is to calculate the total for the Visitors column in my SQL so that is like so

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100

I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?

Thank you

You can play with rollup and cube to get your result. Here is a sample for you to get start:

SELECT

ISNULL(state,'top10'),SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1

GROUP

BY state

WITH

rollup

UNION

ALL

SELECT

'all states',SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1|||

where would i place this query

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

|||

Somehting like this:

SELECT

t3.state, t3.mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM Customer

GROUPBY state

ORDERBYCOUNT(*)DESC) t3

UNION

SELECT

'top10'as state,SUM(t1.mycount1)as mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM CustomerGROUPBY stateORDERBYCOUNT(*)DESC)as t1

UNION

ALL

SELECT

'ALL',COUNT(customer)as mycount1

FROM

Customer|||

this example you gave me is not working...

basically I need a way to combine the following two SQL statements to have one final result

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

SELECT 'Total Top 10', SUM(t1.Visitors)
FROM
(SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC )t1|||

declare @.result table

(

States varchar(100),

Visitors int

)

insert into @.result (States, Visitors)

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

select *

from @.result

union all

select 'Total Top 10', sum(Visitors) from @.result

|||thanx you're a life saver|||

Hello,

I don't know why it is not working for you since I don't have any data from you to test.

Here is something I used to test the script.

CREATE TABLE [dbo].[tab1$]([state] [nvarchar](50), [customer] [nvarchar](50) )Sample Data:INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c6')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c7')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c8')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c9')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b1')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b2')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b3')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b4')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b5')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b6')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b7')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b8')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b9')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b11')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b12')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b13')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b14')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d1')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d2')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d3')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d4')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d6')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d7')

Here is the SQL script (I chose top 2 instead):

SELECT t3.state, t3.mycount1FROM(SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC) t3UNION SELECT'top 10'as state,SUM(t1.mycount1)as mycount1FROM (SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC)as t1UNIONALLSELECT'ALL',COUNT(customer)as mycount1FROM tab1$

|||

When i try to use this @.result table on this query I get the following error

SELECT TOP (10) t1.City City,t1.State State,SUM(t1.Population) Population , SUM(t1.Visitors) Visitors
FROM (
SELECT Customer.zip Zipcode, COUNT(Zipcode) Visitors,Census.city,Census.State,Census.Population
FROM [Customer] JOIN [Census Test Data] Census ON Customer.zip = Census.zipcode
WHERE Customer.month = '8' AND Customer.year = '2006'
GROUP BY Customer.zip, Census.city,Census.State,Census.Population
) t1
GROUP BY t1.city,t1.State
ORDER BY Visitors DESC

ERROR: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

How do i make the values match

Caching reports

I have many reports that run off the same query statement. The Statement
uses parameters that are supplied when the report is executed. There are
allso additional parameters that are used in filters and displayed on the
report (such as title information). I have set up the reports to be cached
and modified the parameters in the report that are not used in the Query with
<UsedInQuery>False. If I run the report with all the same parameters the
Cache seems to work well. If I change one of the parameters used as a filter
the cache is not used. Is there any way around this?
What would be even better is if I could set up cacheing on the shared data
source instead of the report and cache the data for all reports that uses the
same query.
ThanksIt sounds like the report cache includes filters in its caching of data.
It's important to remember that the cache is not just a cache of query data,
but a cache of data the way it will be used in the report, ready for
rendering to any of several different formats.
Now, you could work on the SQL side of things, to see if you can streamline
your datasource (the database itself) to work more effeciently with multiple
reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ken McCullough" <Ken McCullough@.discussions.microsoft.com> wrote in message
news:44B0FAB8-5260-4F06-830E-47429006D5FB@.microsoft.com...
>I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be
> cached
> and modified the parameters in the report that are not used in the Query
> with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a
> filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses
> the
> same query.
> Thanks
>|||Ken,
Double check that the <UsedInQuery>False</UsedInQuery> that you added are
still in the RDL.
Though I have never determined the exact sequence to duplicate, I have had
times where I believe the Report Designer removed <UsedInQuery> settings and
I had to add them again.
We have done a fair amount of testing with
<UsedInQuery>False</UsedInQuery> and its cache effects, and at least for us
it is definately working as advertised.
Bob
"Ken McCullough" wrote:
> I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be cached
> and modified the parameters in the report that are not used in the Query with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses the
> same query.
> Thanks
>|||I stand corrected then. The documentation indicates that UsedInQuery
affects report snapshots, which is similar to caching.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:289783D1-D1DC-4E63-B967-B49DC416FCC8@.microsoft.com...
> Ken,
> Double check that the <UsedInQuery>False</UsedInQuery> that you added are
> still in the RDL.
> Though I have never determined the exact sequence to duplicate, I have
> had
> times where I believe the Report Designer removed <UsedInQuery> settings
> and
> I had to add them again.
> We have done a fair amount of testing with
> <UsedInQuery>False</UsedInQuery> and its cache effects, and at least for
> us
> it is definately working as advertised.
> Bob
> "Ken McCullough" wrote:
>> I have many reports that run off the same query statement. The Statement
>> uses parameters that are supplied when the report is executed. There are
>> allso additional parameters that are used in filters and displayed on the
>> report (such as title information). I have set up the reports to be
>> cached
>> and modified the parameters in the report that are not used in the Query
>> with
>> <UsedInQuery>False. If I run the report with all the same parameters the
>> Cache seems to work well. If I change one of the parameters used as a
>> filter
>> the cache is not used. Is there any way around this?
>> What would be even better is if I could set up cacheing on the shared
>> data
>> source instead of the report and cache the data for all reports that uses
>> the
>> same query.
>> Thanks
>>

Cache plan different using sp_prepare and sp_executesql.

I have a 3rd party application that uses sp_prepare and sp_execute for data retreival. The following statement takes around 40 seconds to run:

declare @.P1 int

exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4 bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', N'SELECT SHAPE ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINCT sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AND SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_ ,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fid AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320, 16825) ))', 1 select @.P1

exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840, 117193071

The query plan created by sp_prepare is used during the sp_execute but it's very slow compared to running this query ad hoc or with sp_executesql. If I clear the proc cache before running the sp_execute this runs in less than 1 second. The tables used are pretty large (8 - 15 million rows) but are indexed correctly and I've updated the statistics and rebuilt the indexes but neither improves the performance.

Does anyone know why using the sp_prepare statement causes a poor query plan?

Thanks.

Doug Matney

Hi

I have come across an interesting article regarding sp_prepare. Hope , it'll be useful for you too

http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=51

NB.

c# reusing parameters

I have a select statement which requires numerous parameters.
here is a snippet.

SqlCommand cmd = new SqlCommand("SELECT this from MyTable WHERE Answer1 = @.Att 1AND Answer2 = @.Att2 AND Answer3 = @.Att3", connection)

my parameters are added as follows.

SqlParameter Att1 = new SqlParameter("@.Att1", SqlDbType.VarChar, 50);
Att1.Value = Attributes1;
cmd.Parameters.Add(Att1);

and so on...

What I would like to do is be able to remove a parameter and re-run the SELECT statement if the number of entries retrieved is less than 5 (or any number)

I tried just having a new Sql command like this.

SqlCommand cmd2 = new SqlCommand("SELECT this from MyTable WHERE Answer1

= @.Att 1AND Answer2 = @.Att2, connection)

and then did this..

cmd2.Parameters.Add(Att1);

but it didn't work.

Is there a way to do this so I don't have to keep copying the whole parameter command?

Thank you in advance for your help. And please be gentle, I'm very new to this.YOu will have to remove the parameter from the first collections before using it in the second command.

cmd.Parameters.Remove(param);

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

sometimes it's too simple.

thanks.

C# and SQL Express Problems

Hi,

Im having an issue with the INSERT statement in C# using SQL Express.

First off, I believe my INSERT statement is correct. Using it in console mode works fine (adding it manually to the db), I've also had a resident SQL expert check it out and he said it looked good.

In my software, it seems to work, but nothing saves. Im writing my own DVD catalogue, mostly to get practice with both C# and SQL working together.

For example:

On my form, there a 3 options.

1. Catalogue Number

2. Dvd Type

3. Dvd Name

The catalogue option gets the number from a table in the DB via SELECT statement. This works.

The DVD Type reads options from another table in the DB via SELECT statement. This works.

The DVD Name is manually typed in a text box.

Once all 3 are filled out, I click the button to save it to the DB. No exceptions are thrown, and the form moves to the "next" entry. (Ie. in catalogue number, if it was 1, it becomes 2)

Upon exiting the program, I look at the DB to find nothing there.

Heres my code for adding (teh click handler for the button).

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (txtID.Text, cmbType.Text, txtName.Text)"; //This is wrong for the purpose of this post, simply to eliminate a few lines of String.Concat code!

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

I apologize if this has come up before, I didnt find an exact solution to this.

Thanks in advance for any help!

What is the error you are getting?

can you also post the exact String.Concat value?

|||

There is no error.

It seems to work, only after the program finishes execution, the changes dont commit.

Heres the code with the String.Concat

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (";

sQuery = String.Concat(sQuery, " ' ", txtID.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", cmbType.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", txtName.Text, " ' )";

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

|||

I wouldn't understand why.

Just for your knowledge, you should be using parameterized queries as they are securer and prevent SQL injection attacks - it is best practice to use them. It can also resolve some common problems and reduces the whole string parsing routine as well as making the code cleaner. :-)

Example:

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (@.p1, @.p2, @.p3)";

SqlParameter p1 = new SqlParameter("@.p1", this.txtID.Text);

SqlParameter p2 = new SqlParameter("@.p2", this.cmbType.Text);

SqlParameter p3 = new SqlParameter("@.p3", this.txtName.Text);

using (sqlConn = new SqlConnection(sConnection))

{

using (sqlCommand = new SqlCommand(sQuery, sqlConn))

{

sqlCommand.Parameters.Add(p1);

sqlCommand.Parameters.Add(p2);

sqlCommand.Parameters.Add(p3);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

}

}

Also if you are finding that once the application closes and you find the values in the database being inserted, be sure that you are not entering into the debugger (stepping through) line by line as this can also cause some confusion on what is actually happen, just let it run and see what happens.

|||

Ok, I switched it over to the Parameterized Query, but it still does the same thing.

Im getting really frustrated by this now!!! It does the same thing on 2 machines.

It really doesnt make any sense at all. I query the table for the highest "ID" of the movie (SELECT MAX(ID) FROM Dvd), add 1, and display that value for the next DVD to be entered. When I enter one and click ADD, the number increments like it should, so if nothing else, it seems to be putting at least the ID into the table by creating a new row for it. I try this several times (ie. Add 10 DVDs), and the number counts correctly, once I exit the program, there is nothing in the DB. When I restart the program, the counter starts at 1 again...

Any ideas why nothing is actually saving in the database? Ive never seen anything like this before.

|||

maybe you should post the entire class?

gives us an overall view on what might be going wrong...

have a good one

adam

|||

About an hour ago, I came across the reason behind it.

It has to do with using the Express Editions of C# and SQL Server (or at least the person posted the reasoning behind it).

Since the db is an object in the solution, it gets copied (in its original, empty state) everytime the program gets compiled. So, while I was looking at what I believed to be the db, it was actually somewhere else, and everytime the program was run, a "fresh" copy of the db was copied over the one that actually had the data.

Does anyone know of any programs that will load SQL Server (Express) .mdf files? I have yet to come across one. I tried installing the Management Tools Suite, but I cant get it to open the file correctly (the program errors when I try), I have a feeling it has to do with this machine, I'll try another machine tomorrow and see what I can come up with. I'll post the results here, but in the meantime, if anyone knows of any software that will open and show the .mdf files, please let me know!

Thanks.

|||

As it turns out, that was the problem the whole time. Now, I just copy the .mdf file over and attach it to SQL Management Studio Express.

The SQL Management Studio Express allows you to "attach" the .mdf file to the current open db. THis will allow you to run queries to get whatever data from the database.

Thanks everyone for your help.

|||

hi,

i read your answer, but still i could not get the solution. because when i open the SQL Server Mangement, there is only one record affected by insert statment so i can not insert more than one record becase the insert treated as an update statment.

and when i used the update statment, no changes are done to the table data

pls pls help

|||Hmm, if you want to post your code, (or at least snippets of it) to show where things should be working and are not, that would be helpful. Off the top of my head, you may have an improper insert statement, or else the connectionstring you use may be off a bit. Post what you have and I'll try to help out.|||

Hi again,

I used the below code and it affect only one row

private void form_Load(object sender, EventArgs e)

{

strSql=”update tbl set fldName=’AAA’”;
cmd=new sqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close()

}

So I changed my code and used button_Click instead of form_load and I was able to insert many rows. But when I close the application and open it again the previous data disappeared like that if I am using blank database.

So any idea. What I understand that the application creates a copy of the .mdf file while it is running and after closing the application it overwrite the original .mdf file

Correct me if am wrong and how to overcome this because I need to use it as database with historical data

I really really need your help

Thanks for your time

|||i get the answer. thats by setting the copy to output directory to copy if newer. before it was set to copy always.|||

I just set mine to copy never!

But it doesnt overwrite the file AFTER closing the application. It copies the .MDF to the output directory at compile time. All I did at first, was run the program, once it was finished, I would copy the db.mdf and db_log.ldf files to a TEMP folder and open them from there with SQL Management Studio Express. The reason I copied, when I tried to attach the file, it wouldnt let me browse to C:\documents and settings\......\visual studio 2005\Projects\...\bin

Did that copy if newer setting solve the problem(s)?

Button1_click code behind

Hi, I have the following code which creates a dropdown box with values from the database.

1) I would like an sql statement to take place to update the database. When the button is clicked I would like the database to update so 'doc_area_default' is changed to '1' for the value that is selected via the dropdown. All others should be changed to '0'. Then direct the user to ManageAreas.aspx with a success message.

2) I would like to be able to do this in the code behind page (cs). Using the "protectedvoid Button1_Click(object sender,EventArgs e)" method. I'm not sure how to do the database connection like I would in the aspx page either.

Thanks for any help you can give!

<

asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="doc_area_name"DataValueField="doc_area_id"></asp:DropDownList><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"SelectCommand="SELECT * FROM [document_area] WHERE [doc_area_type] = 1 ORDER BY [doc_area_default] DESC"></asp:SqlDataSource>

<

asp:ButtonID="Button1"runat="server"Text="Update"OnClick="Button1_Click"/>

Dearmlawton40 .

I guess u can use store procedure to do this.

Try to use these code

//Create procedure

create proc sp_update

@.doc_area_id int

as

update document_area set doc_area_type=1 where [doc_area_id] = @.doc_area_id

update document_area set doc_area_type=0 where [doc_area_id] <> @.doc_area_id

go

And Code behind your page

public void Button1_Click()
{

SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("sp_update", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.doc_area_id",DropDownList1.selectedvalue));
cmd.Connection.Open();
cmd.ExecuteNonQuery();

Response.Redirect("ManageAreas.aspx");
}

Hope that it will help you..