|
Can anyone help!
I am capturing data through scanning (OMR) and it might happen that some of the data is not recognised by the scanner hence it will be left blank, and I do not want variables with blank entries.
Now, I am storing that dataset in an SQL database table. All I want is to have a stored procedure that when I run on that data it will pick up the variable that are left blank with no entry.
I also want something that will help me say to check consistency in my data set. Here is the pseudocode:
- Open table i.e. MyTable
- Input a parameter say ID, i.e for searching
- If found, locate all the variables for that parameter where the appears to be some blanks.
- If blanks are found, then print thru a printer, say Gender empty for person i
- Check if married couples are not of different sex, if they are print a message giving out the id for that particular record.
Please help me!
phokojoe
|
|
|
|
|
Okay, I'm having a bit of trouble understanding what you want because the terminology you are using is inconsistent with the domain.
What do you mean by "variable"? Do you mean a column in the table?
By "input a parameter", do you mean you want to take as a parameter to the stored procedure the name of a column that you want to search?
You use the word "variables" a second time, but this time I'm thinking that "row" may be the correct interpretation for this instance. Do you mean you want to return the rows in the table that have a null value in some column (as specified by the user)?
Printing is not really a recommended action for a database server to take. It can be done, but it makes for messy applications.
Your last part on married couples is more of a business rule rather than a check of the OMR.
I am wondering if these checks cannot be made before the data from the OMR gets to the data. Or is this a senario where a lot of bad data has already gone into the database and you need to find it?
Answering your question as best I can
CREATE PROCEDURE dbo.SearchForNull
@columnName sysname
AS
DECLARE @dynamicSql nvarchar(4000);
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
AND COLUMN_NAME = @columnName)
BEGIN
-- The column does not exist
RETURN;
END
SET @dynamicSql = '"SELECT id FROM MyTable WHERE ['+@columnName+'] IS NULL';
EXEC(@dynamicSql);
GO
This stored procedure will return a set containing the id of any row that has a null value for the supplied @columnName. The check of the INFORMATION_SCHEMA.COLUMNS table is important as it helps prevent an error or attack if someone has managed to put in a dodgy column name (either accidentally or maliciously).
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thank you for the code, it looks like it is going to work just few things.
When I try to execute the procedure giving the parameter, it says"
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'.
--------
By the way let me clarify it. Data is already in the database. This is the example:
Id Sex Subject1 Subject2 Subject3
001 1 45 .. 50
002 . 65 80 ..
003 2 .. 90 ..
Now all i want is if i supply '001' for the column id, it has to retrive the columnnames that have empty or .. for that particular id.
this time it has to retrive subject2 for id 001. etc.
Thank you.
I am working on that message I get when executing the procedure.
Let's take a ride.
phokojoe
|
|
|
|
|
phokojoe wrote:
When I try to execute the procedure giving the parameter, it says"
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'.
That's what I get by typing in SQL directly onto the forum. The " symbol just before the SELECT statement should be removed.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
This stored procedure should be a bit easier - no dynamic SQL - but probably a bit more tedious as it is a bit repetative.
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id int
AS
SELECT 'Sex' AS ColumnName
FROM MyTable
WHERE (Sex IS NULL OR Sex='..') AND id = @id
UNION
SELECT 'Subject1' AS ColumnName
FROM MyTable
WHERE (Subject1 IS NULL OR Subject1='..') AND id = @id
UNION
SELECT 'Subject2' AS ColumnName
FROM MyTable
WHERE (Subject2 IS NULL OR Subject2='..') AND id = @id
UNION -- And so on for all the columns in your table that you want to check.
GO
This just performs a number of SELECT statements and UNIONs them all together into one result set.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
well this looks like it will work. Hoever, I foxed the first procedure 'searchfornull' and when I give it the parameter it returns all the columns names without records.
But as for procedur GetEmptyColumnNames, it returns only the first column name in the first select but not records and it looks like it does not return all other coulmn names.
e.g. This is how it looks:
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as A1
FROM hhdata
WHERE (a1 IS NuLL Or a1='..') AND refno = @id
UNION
Select 'a2' as A2
FROM hhdata
WHERE (a2 IS NuLL Or a2='..') AND refno = @id
UNION
Select 'a3' as A3
FROM hhdata
WHERE (a3 IS NuLL Or a3='.') AND refno = @id
exec getemptycolumnnames 043701137010041
where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see.
------------returns
A1 not even a record in a1.
phokojoe
|
|
|
|
|
I notice that you are putting things like
SELECT 'a1' AS A1...
SELECT 'a2' AS A2...
Remember this is UNIONing all these into one set so it might confuse it* if you give all these different column names, they are supposed to be returning the result into a single column
So, my first suggestion (and its a long shot) is to ensure that the A1, A2, A3 are all the same column name (If I remember I said ColumnName)
DROP PROCEDURE dbo.GetEmptyColumnNames
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as ColumnName
FROM hhdata
WHERE (a1 IS NULL Or a1='..') AND refno = @id
UNION
Select 'a2' as ColumnName
FROM hhdata
WHERE (a2 IS NULL Or a2='..') AND refno = @id
UNION
Select 'a3' as ColumnName
FROM hhdata
WHERE (a3 IS NULL Or a3='.') AND refno = @id
phokojoe wrote:
where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see.
I'm getting confused by your terminology.
"043701137010041 is a record in the refno" --> a column value in the refno column??
"there are many records in the refno column" --> there are many rows with the same refno??? (A column does not contain records, it contains values)
* DISCLAIMER: I've never thought of putting different column names here, so I don't know what the result would be, but it could explain why you only get the first result back.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Is there a good comparison of the various types of products out there like codebase vs msde 2000 vs sql server express 2005.
.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.
Fiat justitia, et ruat cælum
|
|
|
|
|
I have a MS-SQL production database on a server running under the default instance. I have a copy of that database running on my desktop under the NetSDK instance of MSDE. I created a new copy of MSDE on a laptop to demo the product. It is also a named instance (NetSDK). The mystery is that I can create a database backup on the production server (db file path is c:\program files\Microsoft SQL server\MSSQL\Data\database.mdf), copy it to my machine and restore it (I use Access to restore it.) Works in spite of the fact that the data files are in slightly different paths (MSSQL\Data versus MSSQL$NETSDK\Data). But if I try to restore the backup on the new laptop installation it blows up telling me I need to use the RESTORE WITH MOVE.
As an interim solution, I am backing up the server, restoring on the desktop, then backing up the desktop and restoring the second backup to the laptop.
Can anyone tell me why the restore works on the desktop but not the laptop? I would like to avoid this giant shuffling of files if possible.
Thx
Mark Jackson
|
|
|
|
|
When you are restoring on the laptop, check the physical destination path of the data files being restored.
It might be pointing to a different directory than the default data directory present on the laptop.
Or maybe there are the "same named" .mdf and .ldf files already present in the data folder of the laptop.
cheers,
[r a w]
Little Johnny was a scientist. Little Johnny is no more.
For what he thought was H2O, turned out to be H2SO4.
|
|
|
|
|
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on
cmdSQL.ExecuteNonQuery()
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it.
Your immediate response will be higly appreciated.
Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean
Dim cmdSQL As SqlClient.SqlCommand
Dim blnReturn As Boolean = False
Dim objParam As SqlClient.SqlParameter
Dim arrData() As Byte
Try
If Not IO.File.Exists(strFileName) Then
Throw New Exception("PDF file not found:" & strFileName)
Else
arrData = ReadFile(strFileName)
End If
cmdSQL = New SqlClient.SqlCommand("UPDATE [<job>] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("<job>", Me.Job.JobName), Me.m_cnnSQL)
cmdSQL.CommandType = CommandType.Text
objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar)
objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1)
objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image)
objParam.Value = arrData
objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar)
objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat")
cmdSQL.ExecuteNonQuery()
cmdSQL.Dispose()
blnReturn = True
Catch ex As Exception
Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly)
Finally
If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName)
cmdSQL = Nothing
End Try
Return blnReturn
End Function
|
|
|
|
|
in your connection string try adding pooling=false
will
|
|
|
|
|
cmdSQL.CommandTimeout = 30
is by default.
Try setting it to 60.
|
|
|
|
|
First load up your query in query analyzer and check the execution plan to make sure it is not doing a table scan. If it is check to see if there is an index on [File_Name], if not then add one. If there is then try rebuilding the statistics on that table.
Increasing the timeout value should be the approach of last resort.
|
|
|
|
|
Agreed.
But this is just not the execution plan and table statistics but sometimes the network traffic is high and the default CommandTimeout is just not sufficient. I have encountered this before.
|
|
|
|
|
It depends how high the timeout is and how much traffic you need to support. If you set your timeout too high, the entire site can be brought down by a few poorly performing pages. The timeout exists for a very important reason, to allow the server to recover when it is overloaded. I have encountered this a few times myself.
IMHO. Exceution plan and table statistics are the most common reasons for an update to run slow especially when there are a number of records in the table and should be the first solutions considered since they have very little cost. Next, I'd evaluate contention with other queries by loading up the profiler and looking for long running operations.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
by default the SqlConnection object sets pooling = true which means there is a limited ammount of time that connection is allowed in the pool.
Clicky[^]
I have ran into the same situation where no matter how good my execution plan was or how normalized my data was I would still get time outs. After adding pooling = false to my connection strings the time outs stopped..
hope this helps
Will
|
|
|
|
|
This is true,
but there are two aspects of it, command timeout and connection timeout.
Do you think they are related?
|
|
|
|
|
I think it is a mix of several settings.
You could set your command timeout to 4 hours if you wanted to but if the connection is pooling then
you are stuck.
Clicky[^]
Question 4:
The ConnectionTimeout property cannot be reset on a pooled connection
I may be wrong but this worked for me...
Will
|
|
|
|
|
|
Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening.
Once again thnx for ur immediate response.
Jaffer
|
|
|
|
|
Then this is totally a data problem. You could try to partition your table...
Here is some light reading on the how and why.
Clcky[^]
hope this helps
Will
-- modified at 12:33 Monday 12th September, 2005
|
|
|
|
|
First of all the image data type can only hold 2GB of data so even if your operation didn't time out, it would fail. If you have to store documents which are that large, you are going to have to increase your command timeout. The connection timeout should only increase if the server is getting overloaded.
120,000 records is a relatively small amount for sql server to handle if indexes are setup properly. Make sure the indexes are on the table to support all your queries. If that doesn't fix the problem, then start looking to see if SQL server is bound up with disk or memory access from the large files, or if it is just load from the tables in the database.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi everybody !
I have a marvelous problem in deletion of a DataRow from a dataset table.I want my application to delete rows virtually.To aim that,I handeled the 'RowDeleted' event of the DataTable and in that event handler set the "Deleted" column of row (A bit column in SQLServer) to 1 and reject the changes of row to avoidance of row deletion after Updating the dataset to database.(I don't want the rows be deleted REALLY for undo capability of deletions).
See the code :
<code>
this.dataSet.Tables["tablename"].RowDeleted += new DataRowChangeEventHandle(RowDeleted);
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{
e.Row.RejectChanges();
e.Row["Deleted"] = 1;
}
But when the 'e.Row.RejectChanges();' Executes I receive the following Exception :
System.Data.DeletedRowInaccessibleException : Deleted row information cannot be accessed through the row.
Wonderful thing is that when I replace the above code with the following code, everything is ok and the RowState is changed to UnChanged after Catch block !!!!
<code>
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{
try
{
e.Row.RejectChanges();
}
catch{}
e.Row["Deleted"] = 1;
}
How can I do with this problem ?
Sorry for bad english.Thanx alot !
[ _ Always there is another way _ ]
|
|
|
|
|
Assuming your DataTable is bounded to a DataGrid, I think this is not the best way to do it.
To do virtual deletes, you can use the default DataView and add a RowFilter with the condition "Deleted != 1".
It will display only the Rows with "Deleted != 1".
I hope this helps.
Thanks
-- modified at 13:56 Friday 9th September, 2005
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
