|
You could schedule a SQL job to run the SP at a given time.
Paul
|
|
|
|
|
Set up a scheduled SQL job to do it.
Paul
|
|
|
|
|
Rewriting the SP no to use dynamic SQL should improve performance. SQL Server can't optimize the query by caching an execution plan because it doesn't know what the SQL statement is going to be until run-time. You could do this by putting an IF statement in your code that runs 1 query without filtering when no filtering was passed in, or a query with filtering if it was.
Regarding security, you should consider configuring the SP to only be run by particular user accounts that you know are authorized to run it.
Paul
|
|
|
|
|
AlirezaMaddah wrote: If @FILTER IS NOT NULL
WITH RECOMPILE
That isn't syntactically correct. I meant something like:
IF @FILTER IS NULL BEGIN
-- Query without filtering
END
ELSE BEGIN
-- Query with filtering
END
More code, but at least it's not dynamic SQL.
Paul
|
|
|
|
|
That would work.
You would still have a dynamic query for filtering, but a static one where filtering is not required.
Paul
|
|
|
|
|
The normal thing when doing this is to actually pass the filter criteria as individual items into the procedure and then apply them using something like this:
SELECT item1, item2,...
FROM table1
WHERE (ID = @ID OR @ID IS NULL)
AND (Name = @Name OR @Name IS NULL) This allows the database engine to actually produced a plan.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
You can store connection strings as encrypted sections in your config files. In this example, we are encrypting the connectionStrings for the application MyWebApp:
aspnet_regiis -pe "connectionStrings" -app "/MyWebApp"
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Try a where clause like
where id = 7 or id = 42 or id = 12
in other words, write the SQL so it targets all the items you want to update.
Doing this in a proc, you may want to use OpenXML to pass your collection of Ids as an XML document. Then you're likely to create a temporary table with the ids and write a different type of where clause altogether, which checks if the Id is contained in your temporary table.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
1 is obviously more optimised, and if security is a concern at all, 1 also stops the code from having the full table in memory, which is the only point of security I can see.
If you have issues with the server becoming very busy, all the more reason not to clog the network by passing the full contents of the table all the time.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
And querying a DataSet with a View is surely slower than querying data using SQL server when the queries are getting complex (e.g. range queries) or there is a lot of data.
-^-^-^-^-^-
no risk no funk ................... please vote ------>
|
|
|
|
|
I work in visual studio.net and because of a virus I had to format drive c and change my win XP and after reinstalling it I change my login name.
After installing visual studio 2005 and sql server 2005 with it , when in the server explorer -> right click on connection -> add connection , I add my last database file (.mdf) and select ‘use windows authentication’ (like when I created this database) .
After runnig my code it shows this exception :
Cannot open database "saraSqlDB" requested by login.
The login failed.
Login failed for user 'HOME\Sara'.
How can I correct this exception?
And if I hadn’t this problem and want to make a setup file from this program if I install this setup file in another computer with another login name and computer name then will it give me this exception or another error like this ? if its so then what can I do ? what about using sql server authentication ?
|
|
|
|
|
I sound like you need to restore the database in SQL and add your login to security in SQL Management Studio.
|
|
|
|
|
the problem is that this is not a full version and is a plugin with visual studio.net 2005 and doesn't have SQL Management Studio . and i dont know how to work with commands on it.
anyway thank you.
|
|
|
|
|
You can restore if you have a backup
RESTORE DATABASE [pubs] FROM DISK = N'C:\SQLBackups\pubs_backup_200709180200.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Or if you dont have a backup but havbe the .mdf database file use
CREATE DATABASE [Pubs] ON
( FILENAME = N'D:\SQLDATA\Pubs_Data.mdf' ),
( FILENAME = N'F:\SQLLOGS\Pubs_Log.ldf' )
FOR ATTACH
|
|
|
|
|
thank you
i can restore my .mdf file in server explorer and browse it. and dont have any problem in connecting to sql server but when execute my app. it gives me an exception that the user cant login into this database and with changing user accounts in computer management and adding access rights , the problem still exist.
|
|
|
|
|
It may be that your login is orphaned? you may need to run
sp_change_users_login 'Auto_Fix', 'susan'
|
|
|
|
|
thank you
but i dont understand where i must do it and how ?
sorry im a beginer
|
|
|
|
|
good evening
i have made a online reservation airline tickets website ,my question is if many users reserves online at the same how can the database will respond to those users ,i knew that there is (isolation level )enum that solves this problem but i don't know how to use it in the code
thanks in advance
|
|
|
|
|
Sounds like you need to use a transaction
BEGIN TRANSACTION
UPDATE tblOpenSeats (mark the seat as reserved)
INSERT INTO reservations...
COMMIT
|
|
|
|
|
I am a newbie to working with SQL. I am attempting to Insert a record from one table to another table in the same database and add an additional field to the record. The tables are identical with the following exceptions: t2 has a datedeleted(for when it is added to t2 table) and deletedID columns(identity field incrementing by 1).
I am writing the code in an ASP page and cannot seem to get anything to work or find the appropriate answer on the web. Here is what I have so far:
' Insert record into Deleted table
SQL = "INSERT INTO t2 Select * " & ", DateDeleted "
SQL = SQL & "FROM t1 "
SQLWHERE = "WHERE PotentialID = " & Session("PotentialID")
SQL = SQL & SQLWHERE
MyConn.Execute(SQL)
I get an error indicating that DateDeleted is an invalid column name. Any help is greatly appreciated.
Jeff Hundt
|
|
|
|
|
Is 'DateDeleted' actually a field in table t1?
Paul
|
|
|
|
|
No it is not. But I was just talking with one of my colleagues and I think that is the simplest choice. Add that field to t1 and then insert into t2.
Jeff Hundt
|
|
|
|
|
Obviously, any field names you include in a query have to be present in one of the tables referenced in your query, otherwise you will get this error.
Paul
|
|
|
|
|
Has anyone else noticed that if you update through a data adapter and the row it is updating hasn't changed, that the data adapter will not update the database, but will report that it DID update the database?
I have a program where we tried to change the value of a column from 1.0 to 1. The row says it was modified, the data adapter says it updated the row, but the update trigger on the table was never fired. And in fact the table says it was never changed. (And by the way there are no bugs if I change the value from 1.0 to 2.)
If I run the same update command in generic SQL on the database, it does update the table and the trigger is fired. If I just use a regular ODBCCommand object to run the SQL command everything works just fine.
But apparently the dataadapter wants to scrutinize the data and say "no you didn't really change anything so I'm not going to run the command, BUT I'll tell you that I updated the database, just so you won't worry."
Is there a way to force the data adapter to run the command anyway? Or conversely to have it report that it didn't do anything?
Thanks
Brent
-- modified at 9:45 Wednesday 19th September, 2007
Brent
|
|
|
|
|
I believe each row looks at the status of each column. The column most likely compares the current and original values to see if it needs to be modified. Since you really didn't change anything, these values remain equivalent.
Take a look at DataRow.SetModified(), it may be able to force an update to fire your triggers. See DataRow[DataColumn, DataRowVersion] to visually check the status of each column in a row.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
