Re: Automatically generating scripts? - Database Discussion Boards

Tauseef A16-Mar-07 16:07

16-Mar-07 16:07

hi all
can any body guide me please the exzact difference between a stored procedure,view and a simple aql query what is the major difference based on functionality between these three ,,,
thanks in advance

hello

Re: stored procedure vs sql query

kubben16-Mar-07 16:57

kubben

16-Mar-07 16:57

A stored procedure is a cached pre compiled sql statement that exists on the database. It can have parameters or not, it can return result sets or not.
A Sql Query is a sql statement that you pass to the database.
A view is a subset or whole table or joined tables that you want to expose. Often if you don't want to do the same join over and over again you can create a view that does the join and only has the columns you are interested in. Some views are updateable, normally when they are only of a single table. Some people use views to control what other users have access to. So the user does not have access to the table, but the do have access to the view.

You can always do some searching on the net to get more info:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx[^]

Hope that helps.
Ben

master data - 'These columns don't currently have unique values'

freeisgood16-Mar-07 13:11

freeisgood

16-Mar-07 13:11

Hi, I am working on this master data repeater and got almost everything working, i have 3 levels of categories that show correct

i believe the problem is on the data that i provide through stored ptocedures

then i have the items under the first level almost fine, they are showing fine under their first level category, but the second and third go all under the first instead of group nicelly under their second like

<table><br />
<tr><br />
<td>correct:</td><td>how it is:</td><br />
<td>1.</td><td>1.</td><br />
<td>1.1</td><td>1.1</td><br />
<td>2.</td><td> 2.1 </td><br />
<td>2.1</td><td>3.2</td><br />
<td>3.</td><td>2.</td><br />
<td>3.1 </td><td>3.</td><br />
</tr><br />
</table>

here it is how the relation goes:

DataRelation relation1 = new DataRelation("STOCK_CAT_1", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Items"].Columns["CAT_LEVEL_1"]);<br />
            ds.Relations.Add(relation1);<br />
<br />
            DataRelation relation2 = new DataRelation("STOCK_CAT_2", ds.Tables["Cat1"].Columns["LEVEL1"], ds.Tables["Items"].Columns["CAT_LEVEL_2"], false);<br />
            ds.Relations.Add(relation2);<br />
<br />
            DataRelation relation3 = new DataRelation("STOCK_CAT_3", ds.Tables["Cat1"].Columns["LEVEL2"], ds.Tables["Items"].Columns["CAT_LEVEL_3"], false);<br />
            ds.Relations.Add(relation3);<br />
<br />
<br />
            DataRelation relation4 = new DataRelation("CAT_1toCAT2", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL1"]);<br />
            ds.Relations.Add(relation4);<br />
<br />
            DataRelation relation5 = new DataRelation("CAT_2toCAT3", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL2"]);<br />
            ds.Relations.Add(relation5);

then when i remove the false from ["CAT_LEVEL_3"], false); to create a relation, ig throws a "These columns don't currently have unique values"

here is the data, can anyone spot what is wrong?:

Items	Items	Items	Cat1	Cat1	Cat1
CAT_LEVEL_1	CAT_LEVEL_2	CAT_LEVEL_3	STOCK_CAT_ID	Leve1	Level2
62	0	0	174	0	0
62	0	0	65	0	0
62	0	0	176	0	0
62	0	0	177	0	0
62	0	0	178	0	0
62	0	0	175	0	0
62	0	0	179	0	0
62	0	0	180	0	0
62	0	0	181	0	0
62	0	0	182	0	0
62	0	0	213	182	0
62	0	0	214	182	0
62	0	0	215	182	0
62	0	0	216	182	213
62	0	0	217	182	213
62	0	0	218	182	213
65	0	0	219	182	213
65	0	0	220	182	213
176	0	0	221	182	213
176	0	0	222	182	213
176	0	0	223	182	213
176	0	0	224	182	213
176	0	0	225	182	213
176	0	0	226	182	214
176	0	0	227	182	214
176	0	0	228	182	214
176	0	0	229	182	214
176	0	0	230	182	214
176	0	0	231	182	214
176	0	0	232	182	214
176	0	0	233	182	214
176	0	0	234	182	215
176	0	0	235	182	215
176	0	0	236	182	215
176	0	0	237	182	215
176	0	0	238	182	215
176	0	0	239	182	215
176	0	0	240	182	215
176	0	0	241	182	215
176	0	0	242	182	215
176	0	0	243	182	0
176	0	0	244	182	243
176	0	0	62	0	0
176	0	0	63	0	0
176	0	0	246	182	243
176	0	0	247	182	243
177	0	0	248	182	243
177	0	0	245	182	243
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
177	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
178	0	0
179	0	0
179	0	0
179	0	0
179	0	0
179	0	0
179	0	0
179	0	0
179	0	0
181	0	0
181	0	0
182	213	216
182	213	216
182	213	216
182	213	217
182	213	217
182	213	217
182	213	218
182	213	218
182	213	218
182	213	218
182	213	218
182	213	218
182	213	218
182	213	218
182	213	218
182	213	220
182	213	220
182	213	220
182	213	220
182	213	220
182	213	220
182	213	220
182	213	220
182	213	220
182	213	221
182	213	221
182	213	221
182	213	221
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	222
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	223
182	213	224
182	213	224
182	213	224
182	213	224
182	213	224
182	213	224
182	213	224
182	213	224
182	213	224
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	213	225
182	214	222
182	214	222
182	214	222
182	214	223
182	214	232
182	214	232
182	214	232
182	214	233
182	214	233
182	214	233
182	214	233
182	214	233
182	214	233
182	243	244
182	243	245
182	243	245
182	243	246
182	243	247
182	243	248

Re: master data - 'These columns don't currently have unique values'

PlayByTheRules16-Mar-07 23:58

PlayByTheRules

16-Mar-07 23:58

Did you read the posting guidelines?

Did you take note of rule 4: Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.

Did you even notice the warning before you posted that your message was very long? You must have, because you would have had to acknowledge it. I don't supposed you really cared. That shows a complete disregard and disrespect for other people here.

Return output parameter and dataset

Ridge Howison16-Mar-07 11:09

Ridge Howison

16-Mar-07 11:09

I have a stored proc which takes as input a user name and a password, and checks against a table if the user name and password exist. If it is a valid username-password,there is a return code of 0 followed by the customer name,address1,city,state etc(these are the fields in the table), if it is an invalid combination of username and password i get a return code of 1....how do i get the values from the db. here is the code i have written...i manage to get only the return code.

imports Microsoft.applicationblocks.data
'-----------below is the code in my function
Dim oPar() As SqlParameter = New SqlParameter(2) {}

'Set Parameters Up
oPar(0) = New SqlParameter("@UserID", Data.SqlDbType.VarChar, 40)
oPar(0).Value = userid
oPar(1) = New SqlParameter("@PW", Data.SqlDbType.VarChar, 20)
oPar(1).Value = password
oPar(2) = New SqlParameter("@Return", Data.SqlDbType.Int)
oPar(2).Direction = Data.ParameterDirection.Output

SqlHelper.ExecuteNonQuery(SetupProperties.ApplicationsDBConnection, Data.CommandType.StoredProcedure, _
"WebUserProc", oPar)
If oPar(2).Value Is DBNull.Value Then
Return ""
Else
Return (oPar(2).Value)
End If

Please help !!!!

RH

Re: Return output parameter and dataset

kubben16-Mar-07 12:47

kubben

16-Mar-07 12:47

Because you are using ExecuteNonQuery the stored procedure select statements will not be returned. You will need to executereader or something like that to return data.

Hope that helps.
Ben

Automatically generating scripts? [modified]

LongRange.Shooter16-Mar-07 7:00

LongRange.Shooter

16-Mar-07 7:00

I am running SQL Server 2005 and I'm using Sql Server Management Studio Express. Is there an easy way that I can quickly generate creation scripts for my database (including table!)? If not with what I have is there a tool I can grab to do this? All I'd like to do is just right click the database and select the 'Generate Creation Scripts' and get all I need to build a new database with my tables from scratch.

Thanks,

Michael

-- modified at 13:14 Friday 16th March, 2007

Re: Automatically generating scripts?

Mark J. Miller19-Mar-07 5:57

Mark J. Miller

19-Mar-07 5:57

Have you tried the following from SSMS:

1) Right-click database
2) Tasks - Generate Scripts...
3) Select Database and check the box at the bottom "Script all objects in the selected database"

The rest of the steps in the wizard allow you to set options for the output (location and script settings). Does this meet your needs? Or do you need additional functionality not offered by the Generate Scripts wizard?

Re: Automatically generating scripts?

LongRange.Shooter20-Mar-07 6:27

LongRange.Shooter

20-Mar-07 6:27

Thanks much.

You know, I've been looking at that context menu but never 'saw' the Generate Scripts option at the bottom. Just stopped looking once I saw the Create Script... option. Thanks, that worked just great!

Michael

Multiple Instances - SQLSERVER

clint198216-Mar-07 5:00

clint1982

16-Mar-07 5:00

Hello all
How can I rename an instance of sql server not in sql.

Clint

Re: Multiple Instances - SQLSERVER

Mike Dimmick16-Mar-07 6:22

Mike Dimmick

16-Mar-07 6:22

I don't believe it's possible to rename a SQL Server instance. I think the only way to achieve that is to uninstall it and install a new instance with a different name. Be sure to back up all your databases before doing it so that you can restore them. Alternatively you might be able to detach and reattach the databases to the new instance (but I'd still take a backup first).

Stability. What an interesting concept. -- Chris Maunder

Re: Multiple Instances - SQLSERVER

clint198216-Mar-07 8:26

clint1982

16-Mar-07 8:26

thanks

stored procedures Vs dynamic SQL

digsy_16-Mar-07 3:40

digsy_

16-Mar-07 3:40

I have a question about stored procedures Vs dynamic SQL.

I'm a noob but from the googling I've done this seems to be a hot topic so I dont want to start an arguement but I'm stuck.

The website I am working on has a data access layer that takes in a SP name & parameters. It runs the SP and returns the resulting data to the page that requested it.

This is easy & quick to maintain for me.

However, my users want to be able to search the customer using search criteria parameters that can be different each time. By that I mean they want to be able to search for customers using whatever data they have. Sometimes they will have a last name, other times a last name & birth date, sometimes a phone number and a first name.

This doesnt seem to lend itself to a SP - the parameters change each time so the solutions seem to be:

1. A SP that has every criteria in it and use LIKE '%%' when the customer doesnt use one of them
2. Have the data access layer dynamically build the required SQL and then insert it into the SP somehow. (Which sorta kinda sounds like SQL injection)
3. Build an SP for each group of search criteria (Name, DOB, Phone numbers, TIN) and then only allow the users to use one search criteria group at a time.

Neither solution sounds that great to me. (There may be others but like I say I'm a noob)

Which leads me to think that maybe in this case I should use Dynamic SQL - e.g. have the data acccess layer create the SQL statement and then execute it against the database without using an SP.

But then that breaks my rule of only using SP (which I originally created cos I thought SP ran quicker but then I read that according to some guys at MS that dynamic SQL runs as quickly as SP in the .Net environment) so now I'm confused.

Re: stored procedures Vs dynamic SQL

Pete O'Hanlon16-Mar-07 3:44

Pete O'Hanlon

16-Mar-07 3:44

If you want to continue using SPs, you could always check the value of the parameter to see if it is null or not in the where clause.

For instance:

SELECT ....
FROM table1
WHERE
  (Col1 = @Col1 OR @Col1 IS NULL) AND
  (Col2 = @Col2 OR @Col2 IS NULL)

Deja View - the feeling that you've seen this post before.

Re: stored procedures Vs dynamic SQL

digsy_16-Mar-07 3:56

digsy_

16-Mar-07 3:56

OK so it ignores the where col1 = @col2 part of the statement if @col1 is null ?

Re: stored procedures Vs dynamic SQL

Pete O'Hanlon16-Mar-07 4:09

Pete O'Hanlon

16-Mar-07 4:09

Effectively yes because it evaluates the boolean to true. If you want to shortcircuit it, you can swap the order of the tests around.

Deja View - the feeling that you've seen this post before.

Re: stored procedures Vs dynamic SQL

digsy_16-Mar-07 4:51

digsy_

16-Mar-07 4:51

Sorry - I'm not sure what you mean by shortcircuit it. Could you explain that please ?

Re: stored procedures Vs dynamic SQL

Pete O'Hanlon16-Mar-07 13:02

Pete O'Hanlon

16-Mar-07 13:02

Short circuiting simply refers to the case where a boolean condition halts before it evaluates the next part. A simple C# demonstration should show this:

DateTime? dtToday = null;
if (dtToday == null || dtToday == DateTime.Now)

The second part never executes because dtToday is null and so the conditions for an or statement is satisfied. In the case of an AND condition, the second part of the statement would not be executed if the first part is false.

Deja View - the feeling that you've seen this post before.

Re: stored procedures Vs dynamic SQL

Mike Dimmick16-Mar-07 6:44

Mike Dimmick

16-Mar-07 6:44

SQL Server compiles an execution plan for a query the first time it's run. That plan is then cached and reused. It will be discarded either when it's aged out of memory (i.e. there was enough memory pressure for query plans to be removed from memory, and this was less recently used/important than other cached plans) or when the statistics on the index columns have changed enough to force a recompile, or you add or remove some indexes on tables used in the query.

The same is true for stored procedures - their query plans are just weighted higher than dynamic SQL. It's got nothing to do with the .NET environment, it applies to all interfaces.

I'm concerned that queries using LIKE '%%' or OR @param='' etc will have a query plan constructed based on the first set of arguments supplied, but which will then be a poor plan for a different set of arguments, and SQL Server will reuse that initial plan rather than construct a better one. This is something I don't have data on, however - you should profile this. In this case I think dynamic SQL could be better.

You can mark a stored procedure to be recompiled every time by specifying WITH RECOMPILE. However, you incur the overhead of computing the plan every time; caching the dynamic SQL's plan might work better.

If your query does not use parameters, SQL Server will attempt to auto-parameterise the query so that it can reuse the plan for more queries. That is, it will replace explicit literal values with parameters. However, it may be better to mark the parameters yourself if there are literals in the query which don't change.

Stored procedures are useful as a security boundary - you can GRANT a user permission to EXECUTE a stored procedure even if they're denied permission to perform the operations of the SP directly.

Stability. What an interesting concept. -- Chris Maunder

Multiple Instances - SQLSERVER

clint198216-Mar-07 3:39

clint1982

16-Mar-07 3:39

Hello
I have a computer with sql server 2000 and sql server 2005.
for some reason the instance of sql server 2005 is not published in the network appropriately.
When I try to connect to the database engine I am going to the server name and I browse.
I go to network servers and I can find the instance of SQL Server 2000 but I cant find the instance of sql server 2005.
Does any one know what are all the options for registering the sqlserver instance ?

Clint

Re: Multiple Instances - SQLSERVER

Hesham Amin16-Mar-07 9:01

Hesham Amin

16-Mar-07 9:01

Can't you connect to it? Or you just can't find it in the list of servers in connection dialogs?
for MS SQL Server 2005 to be browsable, you must run the SQL Server browser service.
Start->SQL Server Configuration Tools->SQL Server Configuration manager
make sure that the SQL Server browser service is running.

I Hope this helps.

Hesham A. Amin
blog

how we can use stored proceder with asp.net

jayvaishnav8216-Mar-07 2:42

jayvaishnav82

16-Mar-07 2:42

pls answer my question

Re: how we can use stored proceder with asp.net

kubben16-Mar-07 2:47

kubben

16-Mar-07 2:47

Need a little more info here.

What is your database?
If it is Sql server then:
First you will need a SqlConnection object:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx[^]

Then you will need a Sqlcommand object:
http://msdn2.microsoft.com/en-us/library/system.data.dataset.aspx[^]

In your Sqlcommand object you set the stored procedure name, add the parameters and set them.

Next the question is: Does this stored procedure return data or insert records or what?
Based on that answer you will do a ExecuteReader or ExecuteNonQuery.

Hope that helps.
Ben

Re: how we can use stored proceder with asp.net

Marcus J. Smith16-Mar-07 2:47

Marcus J. Smith

16-Mar-07 2:47

Googled it[^] Big Grin | :-D

_Cle^aKO

"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)

Re: how we can use stored proceder with asp.net

Colin Angus Mackay16-Mar-07 3:37

Colin Angus Mackay

16-Mar-07 3:37

jayvaishnav82 wrote:
how we can use stored proceder with asp.net

You don't. ASP.NET is a framework for building web applications.

ADO.NET is the component of the .NET Framework that deals with databases.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.ExecuteNonQuery(); // If you don't expect the stored procedure to return anything
// or
SqlDataReader reader = cmd.ExecuteReader(); // If you expect results

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.