|
thedom2 wrote: one of the propellor heads to figure it out
So we're all just "propellor heads" to you. I see....
Anyway, this "expert" failed to mention the need to defend against incorrect or malicious input into the stored procedure.
You may like to consider putting the following code at the start of your stored procedure:
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='SorDetail'
AND SCHEMA_NAME = 'dbo'
AND COLUMN_NAME = @Column)
BEGIN
-- Column does not exist.
RETURN 0;
END
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Anybody know how to dump a database to SQL? (Yup, all tables, procedures, etc...)
I only read CP for the articles.
Code-frog System Architects, Inc.
|
|
|
|
|
Hi,
Take the enterprice manager and just select what all the objects you want to script and then COPY and take the query analiser or any editor and PASTE it..
|
|
|
|
|
If you are using SQL Server, select the database from the tree in left pane of Entrprise manager. Right click, select "All Tasks"->"Generate SQL Script". The resulting dialog will allow you to select whatever you want to script, and will generate scripts for all the schema. To get data in the tables, you will need to use somthing else, like BCP, or roll your own.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 17:41 Friday 3rd March, 2006
|
|
|
|
|
Okay, this may be a really stupid question, but sitting here and thinking about it, followed by a large number of Google attempts has left me unenlightened. Here goes:
Let's say I have an ASP.Net web application that displays a report to users using a datagrid. The query for the report is fairly complex and involves several joins using either poorly- or non-indexed tables containing hundreds of thousands of rows each. Suffice it to say, the query is long-running, which is not the greatest thing to have in a web application, but that's not really the point of my question.
Now, let's say an impatient user signs in to the application and requests the slow report described above. The server-side application logic opens a connection, generates a command for the report, and executes it against the database server. The user drums his fingers on his desk for a while, whistles for a few minutes after that, and finally says "Something must be wrong!" and stabs the 'Submit' button to generate the report again (causing a new request against the web server, causing another command to be created and executed etc.).
A minute passes and the query has still not returned...
The user, now completely disgusted, closes his/her browser and goes to lunch.
Now the question:
What happens to the two commands - in fact two *connections* - that are presumably executing at the database server? They were both created under the main process of the ASP.Net worker account, and the process has not disappeared, however there is no longer anyone listening for the command to complete and return (either the browser is closed or the user has browsed away).
Do the queries complete and return (to nowhere)? (no idea)
Is the database server somehow signalled that the commands have been 'orphaned'? (doubt it)
Do the commands, now presumably with a broken communication pipe the the client that requested their execution, sit until timeout occurs?
Is the end nigh, and should I try to find a cool, dry place to watch Armageddon?
The reason I ask is that I've observed similar issues in cases where a user posts a request for data from a long-running command, then closes his/her browser thinking that opening a new browser will make things go faster, only to find out that the DB server is almost completely unresponsive. I have also observed it from the DB server side, where there are large numbers of processes, alive and taking up CPU time, but never seeming to complete.
Thanks for any input.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-- modified at 17:10 Thursday 2nd March, 2006
|
|
|
|
|
This is my grand theory (im quite sure on this as i have SQLprofiled it in test environment)
Once the browser is closed the connection to Sql is lost and then sql svr aborts the process...
Prove me wrong People =)
|
|
|
|
|
That's intuitively what I would expect, but I'm not certain it's what always happens. I should clarify somewhat by pointing out that my observations have been made with Oracle 9i, not SQL Server.
The reason I'm not sure orphaned connections are always cleaned up neatly and efficiently is based on two main points:
1. I've watched from the DB side as connections were created from ASP.Net applications, during the execution of which the client browser was either closed or directed to a different (and unrelated) URL. The connections have not always dropped gracefully.
2. This is more of a gap in my knowledge than anything, but it seems as if there's a point in the execution of a SQL command from any application client where control is passed to the DB server to complete the work and return results across the pipe. In ASP.Net, where the (OS) process creating the connection is tied to the application, not the client browser, and is not terminated merely because the browser closes. In such cases, it appears as if the message pipe might be broken with regard to returning results to the end client, but the process under which the work is being performed is alive and well.
Thanks for your input. If possible, could you provide some information on how you structured your tests? I'll be doing some of my own, so I'd be curious to see what you did.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
I have a form that has a ComboBox and 5 Text box controls. All controls contain text field databinding to a dataset, except for the combobox, which has its datamember and datasource properties set to the same dataset. With this being said, I have a Binding Manager Base object that I'm using its methods to update the dataset, and then the tables. When the user selects a value in the combo box it fills the textbox controls. The problem I'm having is the combobox is populated with the existing Primary_Key fields for the table. I'm attempting to add records to the table. So when using my binding manager base function addnew() and populating the text property of the cbo box, it doesn't send the input text with the insert command. I attempted to bind the text field of the cbo to the dataset and that failed as well. However, I can hide a label (which is bound to the primary_key) and assign the text value of the cbo to the label when the cbo looses focus, and that works. Any ideas on how to get the cbo text value to work without having to pass it to a label? Thanks for your help!
|
|
|
|
|
I'm trying to build a dynamic stored procedure. The parameter "@Column" is used to determine which column it's going to update. The problem I'm having is when i try to use an Int data type. Here's the code.
set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
go<br />
ALTER PROCEDURE [dbo].[MON_UpdateAppMsgPubStats]<br />
@Status char(1),<br />
--@StatusDTTM datetime,<br />
@PubId int,<br />
@Instance char(3),<br />
@Env char(5),<br />
@Node varchar(15),<br />
@Chnl varchar(30),<br />
@Column varchar(30)<br />
AS<br />
BEGIN<br />
DECLARE @sqlString varchar(8000)<br />
--SET @StatusDTTM = convert(char(30),getdate(),1)<br />
SET NOCOUNT ON<br />
SET @sqlString = 'UPDATE MON_AppMsgPubStatistics SET '+@Column+'='+@Status+', '<br />
+@Column+'DTTM='+convert(char(20),getdate())+'WHERE PUBID='+@PubId+<br />
' AND Instance='+@Instance+' AND Environment='+@Env+<br />
' AND PUBNODE='+@Node+' AND CHNLNAME='+@Chnl<br />
<br />
EXECUTE(@sqlString)<br />
END
And the Error Message...
Msg 245, Level 16, State 1, Procedure MON_UpdateAppMsgPubStats, Line 20<br />
Syntax error converting the varchar value 'UPDATE MON_AppMsgPubStatistics SET MsgParsed=T, MsgParsedDTTM=Mar 2 2006 12:44PM WHERE PUBID=' to a column of data type int.
Any help would be appreciated. Thanks!
|
|
|
|
|
@PubId is an int, you need to CAST it first.
You also need to put apostrophes in the @sqlString to delimit various values (like varchars and datetimes)
You should put square brackets in the @sqlString around @Column in case you have column names with spaces or with the same name as SQL reserved words (e.g. [date])
Finally, if you really must use Dynamic SQL then please validate the data coming into your stored procedure to reduce the risk of a SQL Injection Attack[^]. For example:
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='MON_AppMsgPubStatistics'
AND SCHEMA_NAME = 'dbo'
AND COLUMN_NAME = @Column)
BEGIN
-- Column does not exist.
RETURN 0;
END
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I'm a new user of VB.NET with .NET Framework 1.1. My tutorial textbook included an ADO.NET chapter, and it made the following statement: "Although the sample in this chapter uses a Microsoft Access database, you don't have to have Microsoft Access installed. Visual Studio and ADO.NET include the necessary support to understand the Access file format as well as other formats."
Based on this statement, I developed my application on a WinXP box and built it's database usage around a Microsoft Access file. But when I deploy to a Win2k box that does not have Microsoft Access, my application crashes at the first database hit. If I deploy to a system with Access, then all is fine, so I know that the app is finding the file in the place where I told the installer to put it, but it looks like a non-Access system doesn't know what to do with the file. My deployment does include MDAC 2.7.
So what am I missing here? I've been scouring this website and the MSDN site looking for help and example code, but nothing addresses my question.
Thanks,
Phil
|
|
|
|
|
I believe there is a separate install for the "Jet Engine" pieces needed for Access.
WinXp has them as part of the OS install, but Win2K might not. In the earlier versions of ADO, these were included in the MDAC install, but not in later versions.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I'm trying to execute a stored procedure (like i've done many times) in C# using ADO.Net. Everything works fine up until 2 of the parameter additions, then it all goes to hell. Here's the code.
<br />
SqlConnection conn = new SqlConnection(strSqlConn);<br />
SqlCommand cmd = new SqlCommand();<br />
int pub = Int32.Parse(_pubid);<br />
<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.CommandText = "MON_UpdateAMPTransformDataAndQueue";<br />
<br />
cmd.Parameters.Add("@environment", SqlDbType.Char,8);<br />
cmd.Parameters["@environment"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@environment"].Value = _environment;<br />
<br />
cmd.Parameters.Add("@node", SqlDbType.VarChar,30);<br />
cmd.Parameters["@node"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@node"].Value = _node;<br />
<br />
cmd.Parameters.Add("@channel", SqlDbType.VarChar,30);<br />
cmd.Parameters["@channel"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@channel"].Value = _chnlname;<br />
<br />
cmd.Parameters.Add("@pubid", SqlDbType.Int);<br />
cmd.Parameters["@pubid"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@pubid"].Value = pub;<br />
<br />
cmd.Parameters.Add("@subchannel", SqlDbType.VarChar,50);<br />
cmd.Parameters["@subchannel"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@subchannel"].Value = _subchnl;<br />
<br />
cmd.Parameters.Add("@queue", SqlDbType.VarChar, 30);<br />
cmd.Parameters["@queue"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@queue"].Value = targetQueue;<br />
<br />
cmd.Parameters.Add("@transdata", SqlDbType.Text);<br />
cmd.Parameters["@transdata"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@transdata"].Value = transformed;<br />
<br />
cmd.Parameters.Add("@instance", SqlDbType.Char, 3);<br />
cmd.Parameters["@instance"].Direction = ParameterDirection.Input;<br />
cmd.Parameters["@instance"].Value = _instance;<br />
<br />
cmd.Connection = conn;<br />
conn.Open();<br />
cmd.ExecuteNonQuery(); <<<< Throws Exception::: Procedure 'MON_UpdateAMPTransformDataAndQueue' expects parameter '@queue', which was not supplied. >>><br />
conn.Close();<br />
<br />
I know what the problem is, I'm just not sure how to fix it. When the debugger gets to the point of setting the Value of the @queue parameter. The targetQueue variable holds a value, but it never sets the Parameters value to it. It just stays null.... Same thing happens with @transdata, the variable that i'm assigning it hold a value, but the ".Value =" statement isn't setting the value.
Anyone have an idea?
|
|
|
|
|
sonicsqwirl wrote: The targetQueue variable holds a value, but it never sets the Parameters value to it. It just stays null....
Is targetQueue == null? If it is, then you'll need to set the parameter value to System.Data.SqlTypes.SqlString.Null[^].
cmd.Parameters["@queue"].Value = (targetQueue == null ? SqlString.Null : targetQueue);
Jon Sagara
Look at him. He runs like a Welshman. Doesn't he run like a Welshman? Doesn't he? I think he runs like a Welshman.
Sagara.org | Blog | My Articles
|
|
|
|
|
I have a table where I store an enum value from C#. I would prefer to store the value as text so that my sprocs that use the value would be more readable and flexible, but am worried about data bloat and perf since there will be a few hundred thousand entries. Does SQL do some smart things storing the same few strings many times? Or should I suck it up and store the enum as a tinyint?
|
|
|
|
|
Brian Perrin wrote: Does SQL do some smart things storing the same few strings many times?
Nope.
Brian Perrin wrote: Or should I suck it up and store the enum as a tinyint?
Yes. And welcome to the 5th normal form (If I remember my Database lectures)
Actually, saying "Yes" is not necessarily the correct answer. While your enum table is likely to be small and will probably only take one SQL Server page* which means its access will be very efficient, you may find that in certain situations this can slow down your application. For example in reporting situations it is normally better to have a slightly denormalised data model. The real answer lies in how you intend to use the data. Having said all that, in general the more normalised your data the better.
* SQL Server stores data in pages of 8K each. Well, you get a little less than that and SQL Server takes the rest for its own uses. Each page is one of 8 in an extent (total 64K in an extent) and it in those chunks that SQL Server accesses data from the disk
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
hi,
i have a table softwareMeter(id [primarykey], pcID, softID, myDateTime). what is the query to get the last usage of the software per pc?
|
|
|
|
|
Hi
Just have a try with the below code
<br />
Create Table #softwareMeter(id Int primary key, pcID Int, softID Int, myDateTime DateTime)<br />
<br />
Insert Into #softwareMeter Values (1,100,501,'20060301 10:12')<br />
Insert Into #softwareMeter Values (2,102,509,'20060301 12:40')<br />
Insert Into #softwareMeter Values (3,100,503,'20060302 08:12')<br />
Insert Into #softwareMeter Values (4,102,508,'20060302 13:50')<br />
Insert Into #softwareMeter Values (5,103,508,'20060303 13:50')<br />
Insert Into #softwareMeter Values (6,104,509,'20060303 15:20')<br />
Insert Into #softwareMeter Values (7,105,503,'20060303 18:13')<br />
<br />
select top 10 with ties PCID,max(MyDateTime) from #softwareMeter <br />
Group by PCID<br />
Order By PCID
The top close is a must and the number after top should be set wisely.
|
|
|
|
|
hey thanks for the reply... and for giving me the sql command.. thanks alot.. i appreciate it!
|
|
|
|
|
Hi All,
Is the value NULL different from SQL NULL??If yes, in what way??
create proc test
(
@dtm datetime
) As
begin
if(@dtm=NULL)
print 'It is NULL'
else
print 'It is not NULL'
when I execute this SP with like the one shown below
test NULL
it displays me the output as 'It is not NULL'. y is it so?? what is actually happening?
Thanks
|
|
|
|
|
Not even NULL is NULL.
In SQL a value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Null values usually indicate data that is unknown, not applicable, or to be added at a later time.
I think the comparison you are trying to make is testing a NULL value against another NULL which is like saying is Unknown = Unknown which cannot be compared, its Unknown
When doing comparisons you have to state :-
IS NULL or IS NOT NULL in a WHERE clause. So I am not too sure if you could say if(@dtm IS NULL)
....
Don't know if this helps any. 
|
|
|
|
|
Hi,
I think what you are doing is something like this.
CREATE PROCEDURE dbo.Test
(@dtm datetime)
As
if(@dtm IS NULL) <--- Compare NULL not =
print 'It is NULL'
else
print 'It is not NULL'
GO
To check against NULL if thats what you really wish to do try this.
EXECUTE test Null;
Which produces this.
It is NULL
If you are trying to stop invalid dates you will need to look at other options as doing something like this, will not detect NULL and not passing a value would simply generate an error...
EXECUTE test ''; <-- This is not NULL its a value.
Hope this rambling helps?
Cheers
Dave
|
|
|
|
|
I am trying to use Data Access block V2. I am trying to use stored procedures to manipulate with data. Please tell me the way to pass Input and Output parameters with Data Access Application block.
Thanks,
Sandy Sekhon
|
|
|
|
|
Hi,
I am new to .NET and trying to understand the concept of the DataSet, so if I am talking rubbish please excuse my ignorance.
I believe one usage for the DataSet cab be to transport changes using the GetChanges() method to data services, hence limiting the amount of data in this transfer via a new DataSet.
In my simple example I use the Northwind database from SQL server to populate a DataSet and stored procedures to perform the required update commands. If I use these they work fine.
DataSet changedDataSet = new DataSet(); // Transport DataSet
DataSet commonDataSet = new DataSet(); // Working DataSet
SqlDataAdapter commonDataAdapter = new SqlDataAdapter ( ) ;
commonDataAdapter.SelectCommand = GenerateSelectCommand ( Sqlconn ) ;
commonDataAdapter.InsertCommand = GenerateInsertCommand ( Sqlconn ) ;
commonDataAdapter.UpdateCommand = GenerateUpdateCommand ( Sqlconn ) ;
commonDataAdapter.DeleteCommand = GenerateDeleteCommand ( Sqlconn ) ;
commonDataAdapter.Fill(commonDataSet,"Region");
At this point the commonDataSet is filled will all the records I require from the Region database table using a stored procedure for the select. When reviewing the data I get the following, this also indicates the RowState. I realise that the DataSet is a cached data/schema and AcceptChanges would commit and the Data Adapter Update() would fire the stored procedure updates to the DB based on RowState.
1 My Record To Edit. Unchanged
2 Western Unchanged
3 Northern Unchanged
4 Southern Unchanged
5 NorthWest Unchanged
6 NorthWest Unchanged
Now I perform some simple operations on the records as follows.
// Add new record
DataRow aRecord = commonDataSet.Tables["Region"].Rows.Add(new object [] {999, "NorthWest"});
// Edit record 1
aRecord = commonDataSet.Tables["Region"].Rows[0];
aRecord.BeginEdit();
aRecord["RegionDescription"] = "Edit this.";
aRecord.EndEdit();
// Delete record 4
aRecord = commonDataSet.Tables["Region"].Rows[3];
aRecord.Delete();
This results in the following. I have simply dumped the text out if the record is deleted, as I cannot access fields.
1 Edit this. Modified
2 Western Unchanged
3 Northern Unchanged
Row Deleted
5 NorthWest Unchanged
6 NorthWest Unchanged
999 NorthWest Added
I now trap the changes into a new DataSet as follows. I could use the override methods to select what I want but here I take all.
changedDataSet = commonDataSet.GetChanges();
//changedDataSet = commonDataSet.GetChanges(DataRowState.Modified | //DataRowState.Added); - Could do this to overload.
Now I look at the changes (changedDataSet) as follows.
1 Edit this. Modified
999 NorthWest Added
Row Deleted
Now my question is if I transport this DataSet to a data service component, which is responsible for updating the records on the central DB, how do I handle the deleted record as I have no field information such as Primary key ID etc?
Any help would be appreciated.
|
|
|
|
|
Hi Dave,
Cant you retrieve all the Required information for the deleted row before deleting it buy adding a new command before deleting.
For updating the DB you can use the commonDataAdapter Update Command
//Like this
commonDataAdapter.Update(commonDataSet);
and the adapter will update the DB and apply the changes to the DataSet.
That can be of help for you.
Best Regards
Live forever or just live a happy life (You Decide !)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
