Prevent SP Compilation
Jul 20, 2005
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 3 Replies
ADVERTISEMENT
Sep 30, 2004
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 3 Replies
View Related
Jan 28, 2014
SP is taking more time for compilation.SP has 30 update statement all are using indexed column in where clause.how can i force SP to take less compilation time.
View 5 Replies
View Related
Jul 20, 2005
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 3 Replies
View Related
May 5, 2008
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 1 Replies
View Related
Aug 10, 2006
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 1 Replies
View Related
Nov 13, 2006
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 6 Replies
View Related
Jul 5, 2007
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 1 Replies
View Related
Oct 24, 2005
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 9 Replies
View Related
Feb 12, 2004
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 1 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Mar 6, 2007
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 1 Replies
View Related
Nov 29, 2006
Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?
Thanks
View 1 Replies
View Related
Dec 30, 2003
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 7 Replies
View Related
Oct 12, 2015
I am re-writing old DTS packages(from sql 2005) to convert them to SSIS packages(sql 2014) and in one of the script task, the old activex script does not run.
The script is :
'************************
' Visual Basic ActiveX Script
'************************
Function Main()
mydate = now()
yrs = ""
mth = ""
mth = Month(mydate) - 1
yrs = Right(Year(mydate), 4)
If Month(mydate) = 1 Then mth = "12"
[code].....
Not sure how to proceed forward? I am quite new to package migration
View 0 Replies
View Related
Apr 2, 2008
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 5 Replies
View Related
Dec 8, 2007
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 2 Replies
View Related
Nov 7, 2006
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 2 Replies
View Related
Jan 21, 2007
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 2 Replies
View Related
May 21, 2008
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 12 Replies
View Related
May 29, 2008
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 4 Replies
View Related
Nov 22, 2006
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 8 Replies
View Related
Apr 15, 2007
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 3 Replies
View Related
Oct 18, 2007
How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.
View 7 Replies
View Related
Jan 24, 2004
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 4 Replies
View Related
Apr 8, 2004
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 3 Replies
View Related
Jun 10, 2004
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.
View 6 Replies
View Related
Mar 22, 2006
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 2 Replies
View Related
Feb 23, 2001
hi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .
Ahmed
View 2 Replies
View Related
Jun 20, 2003
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 4 Replies
View Related
Aug 21, 2001
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 1 Replies
View Related
Jan 13, 2004
Hi,
I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.
Any Ideas..
View 5 Replies
View Related
May 18, 2004
I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?
View 1 Replies
View Related
