Prevent SP Compilation
Hi,
I'm using SQL Server 2000 MSDE on a laptop running Windows XP.
I have a couple of SP's that that quite some time to compile. So I was
wondering: is there any way to have the database *not* recompile them every
time after a reboot?
BOL says: "As a database is changed by such actions as adding indexes or
changing data in indexed columns, the original query plans used to access
its tables should be optimized again by recompiling them. This optimization
happens automatically the first time a stored procedure is run after
Microsoft® SQL ServerT 2000 is restarted."
Now the SQL Server is restarted a lot, because laptops don't have endless
batteries <g>
Cheers,
Bas
View Complete Forum Thread with Replies
Related Forum Messages:
Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes: DBConn= New OledbConnection("Provider=sqloledb;" _ DBInsert.Commandtext = "Insert Into GuestInfo" _ DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery() What I'm trying to do is connect to the SQL database and input new information to the database. This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out. Dim DBConn as oledbConnection Dim DBInsert As New oledbCommand DBConn= New OledbConnection("Provider=sqloledb;" _ & "server=localhost;" _ & "Initial Catalog=HMS;" _ & "User id=sa;" _ & "Password=yourpassword;") DBInsert.Commandtext = "Insert Into GuestInfo" _ & "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _ &"'" & txtFirstName.Text & "', " _ &"'" & txtLastName.Text & "', " _ &"'" & txtAddress.Text & "', " _ &"'" & txtCity.Text &"', " _ &"'" & txtState.Text &"', " _ &"'" & txtZipCode.Text &"', ")" DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery()
View Replies !
Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny
View Replies !
Compilation / Re-build Issue
Hi, We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we do modification in the code it take longer time to build approax it takes around 2 min to display the default page (login page). Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster. Regards K.Karthik Doss
View Replies !
SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does. In oracle, system will let you know whether the the procedure is successfully complied or not? Thanks/
View Replies !
SQL Compilation And Execution Plan
Hi all, I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something?
View Replies !
Compilation Of Stored Procs
Hi, I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs? The thing is when I am running the Stored procs in 2005, its performing really slow in first run. Any help in his regard is highly appreciated. Thanks, Ritesh
View Replies !
Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END RETURN @@ROWCOUNTENDGO --execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.
View Replies !
C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike
View Replies !
Function Returning Error During Compilation.....
Hi , I am creating a function which is going to return a table. The Code ofr the function is as follows... =============================== Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70)) AS begin declare @t1_sys char(10),@t1_all varchar(11) declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS) if (substring(@cg1,1,2)='Q$') set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$') DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY insert into @temp_qcard values(@cg1) OPEN C1 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t1_all) declare @t2_sys char(10),@t2_all varchar(10) DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C2 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t2_all) declare @t3_sys char(10),@t3_all varchar(10) DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C3 FETCH NEXT FROM c3 INTO @t3_sys,@t3_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t3_all) FETCH NEXT FROM c3 INTO @t3_sys,@t3_all end end close c3 deallocate c3 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all end end close c2 DEALLOCATE c2 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all END CLOSE c1 DEALLOCATE c1 Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata RETURN END ========================== While compiling this I am getting the Below error .... ================== Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35 Mixing old and new syntax to specify cursor options is not allowed. ================= Can Anyone please help me how to resolve this issue... Thanks with Regards. -Mohit.
View Replies !
SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run? What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this? Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job. Thanks in advance for any help you can give me. Wayne E. Pfeffer Sr. Systems Analyst Hutchinson Technolgy Inc.
View Replies !
Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me? ------------------ Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader' Source Error: Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); Line 17: myConn.Open(); Line 18: SqlDataReader myReader = myConn.ExecuteReader(); Line 19: do Line 20: { Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18 void Login(string username, string password) { SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels"); SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); myConn.Open(); SqlDataReader myReader = myConn.ExecuteReader(); do { while (reader.Read()) { if (username == myReader.GetString(1) && password == myReader.GetString(2)) { messages.Text = "Your login was successful!"; } else { messages.Text = " Your login was unsuccessful!"; } } } while (reader.NextResult()); myReader.Close(); myConn.Close(); } void Submit_Click(Object sender, EventArgs e) { Login(username.Text, password.Text); } Edit by moderator - NetProfit: Added < code>< /code> tags.
View Replies !
Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as: check documents table to determine whether a full text index for SQL's full text engine has been created. If it has not, query the documentText table (which is the table for my in-house full text search) If it has, use the full text indexing engine My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error: Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed. In my test lab, I tried: 1. creating the full text index 2. creating the stored procedure 3. deleting the ful text index which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option. My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure? Here's a code snippet stripped down to the bare minimum to generate the error: CREATE PROCEDURE [My_FullTextSearch] @Term VarChar(1000) AS BEGIN SET NOCOUNT ON; IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1 BEGIN Select [DocumentID] from [DocumentText] where [Term] like '%' + LTRIM(@Term) + '%' END ELSE BEGIN Select [key] from FREETEXTTABLE(Documents, Contents, @Term) END END
View Replies !
Stored Procedure Compilation Question: Doing Disparate Things In Aproc
To minimize the very large number of stored procedures typicallyassociated with an application, I have gotten in the habit ofcombining a select, insert, update, and delete all in one procedure,and passing an argument to indicate which to use. (I use defaultvalues for all input params to avoid having to declare them forselects and deletes.) So I'll have just one PersonAdmin proc insteadof PersonGet, PersonInsert, PersonUpdate, and PersonDelete procsWhile this is nice for housekeeping, I wonder what the compiler doeswith such an architecture,and I fear the worst. The select returns arecordset; the others don't.Is this a bad idea?If it is, I really wish SQL would permit some sort of user folderstructure in the proc list.
View Replies !
SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared: Server Error in '/AverageTCE' Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error: Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server"> <div> Average TCE<br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="SampleID" DataValueField="SampleID"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString2 %>" SelectCommand="SELECT [SampleID] FROM [LabData]"></asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SampleID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="SampleID" HeaderText="SampleID" ReadOnly="True" SortExpression="SampleID" /> <asp:BoundField DataField="SampleName" HeaderText="SampleName" SortExpression="SampleName" /> <asp:BoundField DataField="AnalyteName" HeaderText="AnalyteName" SortExpression="AnalyteName" /> <asp:BoundField DataField="Concentration" HeaderText="Concentration" SortExpression="Concentration" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="ddlLabData" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString %>" SelectCommand="SELECT * FROM [LabData] WHERE ([SampleID] = @SampleID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="3" Name="SampleID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString3 %>" SelectCommand="SELECT * FROM [LabData]"></asp:SqlDataSource> <br /> <br /> LabData-Analyte: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> LabData-Conc: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> Average values: <asp:Label ID="Label1" runat="server" Text="lblAverageValue"></asp:Label><br /> <br /> <br /> <br /> </div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
View Replies !
SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageID int, @DateAdded datetime, @ImageName nvarchar(64), @ImagePath nvarchar(64), @IsApproved bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE [syl_AvatarImages] SET [DateAdded] = @DateAdded, [ImageName] = @ImageName, [ImagePath] = @ImagePath, [IsApproved] = @IsApproved WHERE [AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
Prevent Sql Job Failure.
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.
View Replies !
How To Prevent Db Deletion
Hi I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database. Any help/direction here would be appreciated.
View Replies !
Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks. Exception type: SqlException Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. In general, what's the best way to resolve this ? Should I see if I can apply "WITH (NOLOCK)" to my data ? Any suggestions are greatly appreciated ! thanks again! mike123
View Replies !
Prevent Duplication On UPDATE
Hello I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling: UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside' The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows. What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them. Thanks Lynn
View Replies !
How Do I: Prevent Race Condition?
I want to be able to read and update a value in the database without entering a race condition. For example: User #1 reads a row from the database, changes a value then writes the value back. User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote. I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out. Does that make sense?
View Replies !
How To Prevent SQL Injection Attacks
Hi, On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg% There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable. Can anyone help, perhaps point me in the direction of furthur resources on the subject? Thanks Ben
View Replies !
What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full ------------ I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp ex: create proc ex @orden varchar(100) @criterio varchar(100) as declare consulta varchar(4000) set consulta=N'select pais from paises where '+@criterio' order by '+@orden ------------ I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)? ----- They told me this coding is not good (dynamic sql) because it can give control to the user? I ask, how does it give control to use? what ar sql injection attack and how to prevent them? I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right? can u help me?
View Replies !
Prevent DELETE And/or UPDATE
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO
View Replies !
Prevent One Single Row From Being Updated
I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne
View Replies !
Trigger To Prevent Duplicates
Hi all, I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key. If I create a table with 2 columns CREATE TABLE Table1 (CentreCHAR(10), Month CHAR(3) ) Then create a trigger to prevent duplicates CREATE TRIGGER trigger_Check_Duplicates ON Table1 FOR INSERT, UPDATE AS -- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table. DECLARE @IsDuplicate INTEGER -- Check if row exists SELECT @IsDuplicate = 1 FROM Inserted i, Table1 t WHERE t.Centre = i.Centre AND t.Month = i.Month IF (@IsDuplicate = 1) -- Display Error and then Rollback transaction BEGIN RAISERROR ('This row already exists in the table', 16, 1) ROLLBACK TRANSACTION END Then insert a row into the new table (no other data is in there) INSERT Table1 VALUES('0691040176','AUG') I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image. Please help!!! Thanks Neill
View Replies !
Prevent ODBC By USER/APP
Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps. thanx
View Replies !
Prevent SQL/CLR Auto Deploy
Hi, I have a solution (VS 2005, C#) that includes a SQL 2005 CLR project. When I run the solution (in debug mode, and with a test harness in a different project in the solution set as the startup project) the IDE often wants to deploy my CRL assembly. How do I tell it NOT to try to do the deployment? (i.e. to only do it when I right-click the project and tell it to). Cheers,
View Replies !
Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?
Hi, I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor. Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn? Thanks for your help, David
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
Prevent MS Access To Connect
Hello I'm facing a dilemma. I have quite a lot of users who need read access to data for analysis purposes. By granting them read access you also give them the option to connect to the database using MS Access which puts locks on the data. First I thought of generating stored procedures for all tables within a database those users need to query but when users execute a stored procedure with the name of the table, they get a resultset of all the data. They cannot filter the data by for example using select top 100* from usp_table where name like '%worf%' I'm sure I'm not the only one here but I don't see any alternative then grant them those rights. Thanks! Best Regards, Worf
View Replies !
Is It Possible To Prevent Databases From Being Copied?
Hi, We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end. Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data. Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies. Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication. Is this possible? Regards, Sigol.
View Replies !
How To Prevent A Distributed Transaction?
I have a stored proc that performs some validations and returns a result set. I catch the result set in a calling procedure as follows: declare @tblErr (ErrType varchar(20), ErrMsg varchar(255)) insert @tblErr (ErrType, ErrMsg) exec dbo.spMyProc ...(args) Within the def of spMyProc there is a cross-server call: create procedure dbo.spMyProc (...params...) as begin declare @tblErr (ErrType varchar(20), ErrMsg varchar(255)) ...(do checking, inserts to @tblErr)... -- check other server exec @n = otherserver.mydb.dbo.spCheck ...(args) if @n != 0 insert @tblErr values ('someerr', 'somemsg') ...etc. select ErrType, ErrMsg from @tblErr end Apparently, the implicit transaction of the outer Insert statement is causing a distributed transaction to be started. There is no 'begin tran', distributed or non, and the foreign sp has no data-modifying statements. The call to spMyProc, if done from a query window, works fine, returning the desired resultset. When called from the outer proc, the call fails because the servers are not configured for distributed transactions. When performed without the cross-server call, using a copy of the remote db attached to the local server, the outer proc works fine. I do not want a distributed transaction. How can I prevent one? The local server is 2005 Standard on Server 2003, and the linked server is SQL2000 on Windows 2000 Server.
View Replies !
Prevent Email Authentication
Due to the 'interesting' set up of AD, email etc we are unable to send reports as attachments. It would appear that it is trying to authenticate the user to see if they have permission to view the report, as though they were viewing it in real time. Is there any way to override this behaviour? If not then all we can do is email them a link to the report, which sort of defeats the object.
View Replies !
How To Prevent Ordering Of Records....
hello, i use VC# and sql server 2005. i create a table using the following command : create table history(name varchar(20) primary key); I insert records in a particular order.After all the records are inserted and when I try to retrieve the records, all of them are sorted in a ascending order.Is there any way to prevent them from being sorted. pls reply asap..... - Sweety
View Replies !
How To Prevent My Co-Workers From Accessing My SQL
Hi all In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000. So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects. for better illustration i have created a Flash Movie from this process , please check the following Link : http://www.Parsisoftco.com/movie/Sql/Sql.htm How can i prevent others from doing so? Any help greatly Appreciated. Kind Regards.
View Replies !
How Can I Prevent My Co-Workers From Accessing My SQL Instance..
Hi all In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000. So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects. for better illustration i have created a Flash Movie from this process , please check the following Link : http://www.Parsisoftco.com/movie/Sql/Sql.htm How can i prevent others from doing so? Any help greatly Appreciated. Kind Regards.
View Replies !
How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
View Replies !
How Can I Prevent From Inserting Duplicate Data?
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue? Thanks.
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
What Type Of Constraint To Prevent Duplicates
I am trying to either write a trigger or a check constraint to preventduplicates in my table. There are two columns I need to look at for theduplicates and only one combo value for both columns is allowed in thetable. For e.g.Column Serial can have only one '123456' value with testresult value as'PASS'. This serial can be in the table many times with any other comboso for e.g.The table could contain 100 entries for serial column value '123456'with testresult value 'FAIL', 'PENDING' etc.** TESTED SCHEMA BELOW **-- create tableCREATE TABLE bstresult(ID int IDENTITY (1, 1) NOT NULL ,serial char (10) NULL ,testresult char (10) NULL)-- Insert valid valuesinsert into bstresult values ('123456','PASS')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','PENDING')-- insert invalid value this should failinsert into bstresult values ('123456','PASS')If I simply create a unique constraint on both columns it will notallow the FAIL combo or PENDING combo with the same serial which I needto allow.Appreciate your help.
View Replies !
DTS Package - How To Prevent A 0 Byte File?
I've created a DTS package -- that uses a query to export to a .txt file. My question is -- if the results of this query are zero (no results returned within the package ) -- how can I tell the package not to export a zero byte file. Any thoughts on that? Any help you could give would be greatly appreciated. Thanks!
View Replies !
How To Prevent Doublicate Entry To A Table
hi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that, I am using sql server 2000. thanks Ali
View Replies !
|
TRACKER
