Hi Guys,
We have developed some applications and we wanted to have a performance bench mark for the database. Can anyone pl let me know how to have a baseline for performance. What would be and acceptable response time. Thanks for your reply.
--Joy
I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.
I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?
// stored procedure to write externally generated random number value into database
PROCEDURE RandomizeLinks@L_ID int,@L_Rank intASUPDATE Links SET L_Rank = @L_RankWHERE (L_ID = @L_ID)
// Part One select links that need random number inserted.
public DataTable GetRandLinks() { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd = new SqlCommand("RandomizerSelect001", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "Random001"); return ds.Tables["Random001"]; } catch { throw new ApplicationException("Data error"); } }
// Part Two I need two write a random number back into each row
protected void Button1_Click(object sender, EventArgs e) { GetRandLinks(); int LinkID; // this generates unassigned local variable "LinkID' error int LRank; // this generates unassigned local variable "LRank' error SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("@L_ID", LinkID); cmd2.Parameters.AddWithValue("@L_Rank", LRank); SqlDataAdapter da2 = new SqlDataAdapter();
int RowIncrement; RowIncrement = 0; DataTable dt = GetRandLinks(); foreach (DataRow row in dt.Rows) { System.Random myRandom = new System.Random(); int LinkRank = myRandom.Next(25, 250); LRank = LinkRank; da2.UpdateCommand = cmd2; RowIncrement++; } }
Hello Experts,
What is the use of question mark "?" in T-SQL?
Thanks in advance!
------------------------------------------------------
We took the European Cup, will take the World Cup.
I have problem with sql query with aspnet.
In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark => ' .
Any suggestions? Thanks.
Hi,
I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique.
I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc....
Can anyone suggest 'any' possible way of implementing it ?
Thanx
Nisha
One of the tables in my database was originally designed with a question mark in the column name. When creating my query for jdbc i did this:
String q = "o."Cancelled?" from ORDERS o";
However, this query gives me the following error message:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'Cancelled@P1'.
Is there a way of stopping the driver from turning the '?' into '@P1'?
I have tried single quotation marks instead of the double ones around the column name and also back ticks, but with no success.
I have also seen an option in a local ODBC connection I have created an option for ansi quoted strings which solves the problem for oDBC queries, is there an equivilant for JDBC?
Thanks to anyone able to help!
Sincerely,
Andrew
I can find nothing in MSDN or the Web to explain
the question mark in the WHERE clause here:
SELECT DEPARTMENT.ID,
DEPARTMENT.NAME,
DEPARTMENT.FUNDCODE,
DEPARTMENT.DEPTCODE,
DEPARTMENT.DEFAULTCONTACT,
CONTACT.LNAME AS DEFAULTCONTACTLASTNAME,
CONTACT.FNAME AS DEFAULTCONTACTFIRSTNAME,
CONTACT.POSITION AS DEFAULTCONTACTPOSITION,
CONTACT.PHONE AS DEFAULTCONTACTPHONE,
CONTACT.FAX AS DEFAULTCONTACTFAX,
CONTACT.EMAIL AS DEFAULTCONTACTEMAIL,
DEPARTMENT.ISACTIVE,
DEPARTMENT.ISACTIVECOMMENTS,
DEPARTMENT.POSTUSERID,
DEPARTMENT.POSTDATETIME
FROM TDEPARTMENT AS DEPARTMENT
LEFT JOIN TCONTACT AS CONTACT
ON DEPARTMENT.DEFAULTCONTACT = CONTACT.ID
WHERE DEPARTMENT.ID = ?
ORDER BY DEPARTMENT.NAME ASC
I have never seen a question mark in the WHERE clause like this before.
If I run such a query in Query Analyzer I get:
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
Can anyone explain: "DEPARTMENT.ID = ?" ?
Hallo All,
Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.
On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.
If you delete rows in a table and do a full table scan...Is that supposed to read up to the highest block/extent that thetable ever attended.(like in some databases I use)If so what is the best way to take care of such tables in sql server.I appreciate your responsesVince
View 4 Replies View RelatedHi,I've created a table in SQL Server 2000 and I'm now trying to searchthrough the data and return specific rows. I'm using this command:select * from Export where libelle_court='Recherche d'investisseurs'The problem is this: The search fails whenever there is a curlysingle quotation mark within the table field ( ' as opposed to ' ).For example, if the field entry in my table is this:Recherche d'investisseursthen both of the following commands retun no fields:select * from Export where libelle_court='Recherche d'investisseurs'select * from Export where libelle_court='Recherche d''investisseurs'However, if the field entry in my table is this:Recherche d'investisseursthen both of the commands quoted above succeed.How can I get SQL Server to treat the curly quotation mark correctlyand return the right results? I've tried changing the collation butwith no success.Thanks,Rob
View 3 Replies View RelatedI have a requirement to mark duplicate records when I pull them from the database.
However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.
The code I have below creates a column called Dupes but marks all the duplicates - including the first one.
Is there a way to only mark the 2nd, 3rd, 4th etc record ?
SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,
Dupes = (select count(id) from CallLogswhere (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21),
FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'
Hi i have to pass Query to the SqlCommand with " ' " single quote when i try this i got the error msg
Unclosed quotation mark before the character string ')'.
insert into service(problem) values('Receipt Printer Can't Work') where service_id = 112;
help me Very Urgent
.ctl00_Menu1_0 { background-color:white;visibility:hidden;display:none;position:absolute;left:0px;top:0px; }
.ctl00_Menu1_1 { color:#284E98;font-family:Verdana;font-size:0.8em;text-decoration:none; }
.ctl00_Menu1_2 { color:#284E98;background-color:#B5C7DE;font-family:Verdana;font-size:0.8em;height:29px;width:100%; }
.ctl00_Menu1_3 { }
.ctl00_Menu1_4 { padding:2px 5px 2px 5px; }
.ctl00_Menu1_5 { }
.ctl00_Menu1_6 { padding:2px 0px 2px 0px; }
.ctl00_Menu1_7 { }
.ctl00_Menu1_8 { background-color:#507CD1; }
.ctl00_Menu1_9 { }
.ctl00_Menu1_10 { background-color:#507CD1; }
.ctl00_Menu1_11 { color:White; }
.ctl00_Menu1_12 { color:White;background-color:#284E98; }
.ctl00_Menu1_13 { color:White; }
.ctl00_Menu1_14 { color:White;background-color:#284E98; }
I try to get values from database table name GoldPriceRecord , and display them in a simple labels text. However, i receive the following error:
Error:
Unclosed quotation mark before the character string ''.
Public LondonDateTime As Date
Public CommodityPrice As String
Public SysDate As DatePublic CommodityPrice_Check As String
Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs)
Dim strsql1 As String
Dim strsql2 As String
strsql1 = "SELECT TOP 1 Bid, SystemDate FROM GOLDBEST.DBO.GoldPriceRecord ORDER BY SystemDate DESC'"
SetGoldPriceRecord(strsql1)
fdsa1.Text = LondonDateTime
fdsa2.Text = CommodityPrice
fdsa3.Text = SysDate
fdsa4.Text = CommodityPrice_Check
End Sub
Public Function SetGoldPriceRecord(ByVal strsql As String)
Dim connString As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
Using myConnection As New SqlConnection(connString)Dim myCommand As New SqlDataAdapter(strsql, myConnection)
Dim DS As New DataSetmyCommand.Fill(DS, "GoldPriceRecord") <---- Error
CommodityPrice = CType(DS.Tables(0).Rows(0)("Bid"), String)CommodityPrice_Check = CType(DS.Tables(0).Rows(0)("Bid"), String)
SysDate = CType(DS.Tables(0).Rows(0)("SystemDate"), Date)LondonDateTime = CType(DS.Tables(0).Rows(0)("SystemDate"), Date)
myConnection.Close()
End UsingReturn LondonDateTime
Return CommodityPriceReturn SysDate
Return CommodityPrice_Check
End Function
<form id="form1" runat="server">
<p><asp:Label ID="fdsa1" runat="server" /></p>
<p><asp:Label ID="fdsa2" runat="server" /></p>
<p><asp:Label ID="fdsa3" runat="server" /></p>
<p><asp:Label ID="fdsa4" runat="server" /></p>
</form>
Hi,Is there anyone encountered this error before & how it is being resolved?[Microsoft][ODBC SQL Server Driver][SQL Server]Missing end comment mark '*/'The error pops-up when I was running a DTS Import/Export from a SQL server(source) to another SQL server (destination) residing on a differentmachine. I'm copying all tables, views, & stored procedures of a database.Thanks for any input.Regards,Maricel
View 1 Replies View RelatedHi,
I am getting the error "Unclosed quotation mark after the character string ')" with this code:
Code Snippet
cmd = New SqlCommand("INSERT INTO PRODUCT VALUES ('" & Me.TextBox1.Text & _
"','" & Me.TextBox2.Text & "','" & Me.TextBox3.Text & "','" & Me.ComboBox.Text & _
"','" & Me.TextBox4.Text & "'"")", conn)
Do anyone know what's going wrong around Me.TextBox4.Text. Thanks.
How would I go about finding out what my high-water mark would be for concurrent
connections over a period of time (SQL Server 7, SP 3 applied)?
Thanks in advance!!!
Gary Andrews
andrews_gary_w@solarturbines.com
When I try to search a string including Quatation Mark it fails:
Like: The new Book "Harry Potter" is
it stated that the syntax is incorrect.
by the way I am also using * for indicating any suffix.
What is the correct syntax for searching: "
Itzik
Ok, I've spent a good amount on time on debugging an unupdating scenario in my application. Finally I knew the reason which is very annoying. Either I'm missing something really obvious (I hope so,) or this is a bug.
to reproduce what I'm talking about:
1- create a new win forms application using VS2005 sp1
2- add some SQL Compact Edition data file that have some records from the data menu, you'll get the designer to generate the dataset and everything..
3- drag a table from the data sources window, you'll get the data grid and the navigator on the form
4- add a button and have this in the click event handler:
datasetname.Clear();
TableAdapterName.Update(datasetname);
launch the program, click the button, you'll see the grid get wiped out as it supposed to do. close the program and relaunch. the data is there again (this has nothing to do with the copy always, copy if newer infamous stupidity)
now:
5- edit the click event handler and change it so something like this:
foreach (datasetname.TableRow row in datasetname.Table)
{
row.Delete();
}
TableAdapterName.Update(datasetname);
Launch the program, hit the button, grid wiped out. exit and relaunch. You'll see no data (i.e. the update on table adapter worked alright)
You can also try the GetChanges method on the dataset rightafter you use the clear method and you'd get no deleted records at all.
So, in 100,000+ records dataset, if i need to wipe the thing out and add some new records do i have to loop over every record and call delete (which will take LOTS of time).
I do hope that I'm missing something obvious.
Any help would be highly appreciated.
Thanks.
I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.
Any suggestions.
Thanks
Richard
Issue
I need to write VS2005 C# code using SQL OLE DB to access SQL Server 2005. I have no choice in that matter. I can create a database user with a password like COMPANY", which is a string of uppercase characters ending in an unmatched double-quote mark.
Using Microsoft SQL Server Server Management Studio I login using Windows Authentication, create an account with the password, COMPANY", check the "Enforce password policy", click "OK", and then exit.
Setup
I launch Microsoft SQL Server Server Management Studio again, select "SQL Server Authentication", type in the account name and the COMPANY" password, click the Connect button, and I'm in.
Problem
Now, I need to connect programmatically and run a stored procedure. The password is stored in clear text in hte Registry€”not my choice, it's a legacy application, and changing that is not an option open to me. (We have probably all seen company safes where the combination is scribbled on the wall in case you forget it!)
Here is the code I use to run the sproc:
DBCONNINFOLib.DBConnectionInfoClass DBConnInfo1 = new DBCONNINFOLib.DBConnectionInfoClass();
String strConnString = DBConnInfo1.GetConnectionString( "" );
OleDbConnection con1 = new OleDbConnection( strConnString );
OleDbCommand cmd1 = con1.CreateCommand();
cmd1.CommandType = CommandType.StoredProcedure;
String sCmdText = "sp_SomeStoredProcedure";
cmd1.CommandText = sCmdText;
If I set the connection string in the registry to COMPANY", I get an error like this:
Server Error in '/' Application.
Response is not available in this context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: Response is not available in this context.
Source Error:
Line 60: catch (System.Exception e)
Line 61: {
Line 62: Response.Write(e.Message.ToString());
Line 63: }
Line 64:
Source File: C:sourceProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs Line: 62
Stack Trace:
[HttpException (0x80004005): Response is not available in this context.]
System.Web.UI.Page.get_Response() +2077605
SiteIQWeb.CommonCode.Common..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs:62
SiteIQWeb.MasterPage..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebMasterPage.master.cs:21
ASP.masterpage_master..ctor() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 255b545c8a400c7App_Web_hfj8popy.0.cs:0
__ASP.FastObjectFactory_app_web_hfj8popy.Create_ASP_masterpage_master() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 255b545c8a400c7App_Web_hfj8popy.3.cs:0
System.Web.Compilation.BuildResultCompiledType.CreateInstance() +49
System.Web.UI.MasterPage.CreateMaster(TemplateControl owner, HttpContext context, VirtualPath masterPageFile, IDictionary contentTemplateCollection) +250
System.Web.UI.Page.get_Master() +48
System.Web.UI.Page.ApplyMasterPage() +18
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +685
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
The closes to success I have been able to come is to set the password to "COMPANYNAME""" or 'COMPANYNAME"', which results in a System.Data.OleDb.OleDbConection Exception with a HResult of 0x80040e4d, and the message Login failed for user 'username'.
Questions
Can the password be formatted differently in the registry, or somehow processed after retrieving it, so that SQL Server 2005 will accept it?
Is this a bug in .NET Framework or SQL OleDb?
Is this simply a case of "Is it hurts, don't do it"?
Thanks in advance. As with all postings, my job, promotion, product success, company future, or some combination thereof is on the line.
I have an app that I created and I am trying to upload the MS SQL DB to my web host. I downloaded MS SQL Server Database Publishing Wizard and then open up Visual Studio 2005. I right click my database, and then try publishing it. I convert to a MS 2000 DB (was originally 2005). I save the .sql statement and try copying into the host's (Godaddy) query analyzer. (It's only about 400k).
Well, I tried cutting and pasting the doc so I could see exactly where I get the error. I get a good part of it successful, but then I try the pasting the code below (not modified at all) and get the error about "unclosed quotation..." I skip this and go to the next process, and is successful, but often, I am getting this error.... WHY?
Please help.
Code copied directly from generated script from MS Publishing wizard:
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END'
END
GO
***************************ERROR:****************************
Unclosed quotation mark before the character string 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranS...
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int ASBEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSESET @TranStarted = 0 DECLARE @ErrorCode int DECLARE @RowCount int SET @ErrorCode = 0 SET @RowCount = 0 SELECT @UserId = u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE u.LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName IF (@UserId IS NULL) BEGIN GOTO Cleanup END -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set IF ((@TablesToDeleteFrom & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V'')))) BEGIN DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set IF ((@TablesToDeleteFrom & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set IF ((@TablesToDeleteFrom & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set IF ((@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0Cleanup: SET @NumTablesDeletedFrom = 0 IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCodeEND' ENDGO
Thanks in advance,
Rob
Hello,
Can anybody help me with this error , System.Data.SqlClient.SqlException. "Line 1: Incorrect Syntax near :'m' ."
I have a textbox named DESCRIPTION and am trying to insert the values entered by user in this textbox.
It works well if a user types " I am unable to " but it throws this error when a user types " I'm unable to "...because of I'm ..
How do i fix this?
My Query:
strSQL = "Insert into ABC(DESCRIPTION) values('" & strDescription & "')"
When i try below query in query analyser , am getting this error ....
Error 1: [Line 1: Incorrect syntax near 'm'.]
Error 2: [ Unclosed quotation mark before the character string '.]
update ABC set DESCRIPTION = 'I'm unable' WHERE ABC_ID = '142'
Thanks to reply.
<WebMethod()> Public Function getCertificateInformation( _
ByVal cerNumber As String, _
ByVal StudentID As String) As DataSet
Dim cnn As New SqlConnection( _
"user id=sa;password=;" & _
"initial catalog=uni2;data source=(local)")
Dim strSQL As String
strSQL = "SELECT cerNumber, name," _
& " address, stId, year FROM Msc" _
& " WHERE cerNumber='" & cerNumber _
& "' AND stId='" & StudentID
when running this i am getting an error "System.Data.SqlClient.SqlException: Unclosed quotation mark before the character string '12'". isn't the sql statement correct? need help plz!
** 12 one of the input
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedHello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedWe have the same application installed on a few different environments with similar servers and similar hardward. The only difference is the versions of SQL and the colations.
Is SQL 2005 a lot faster that SQL 2000? Could colation type make a big effect on performance?
ScAndal
HiI want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.Inside the loop i am opening the connection and closing after use. The sample code is belowfor(int i=0;i<1000;i++){ sqlCmd.CommandText = "ProcName"; sqlCmd.Connection = sqlCon; sqlCmd.Connection.Open(): sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?Please clarify me these question.. Thanks in advance.
View 1 Replies View RelatedI have a following problem with SQL performance:
this line 'select * from [viewUserLatestFee]' executes instantly (in Query Analiser)
this line 'select * from [viewUserLatestFee] where orgID = 1' takes up to 30 seconds for 1000 rows (still in Query analiser)
can anyone please help - I seem to have ran out of ideas
I have a feeling people might be curious about the view so here it is:
SELECT dbo.viewUserPosition.id, dbo.viewUserPosition.username, dbo.viewUserPosition.password, dbo.viewUserPosition.title,
dbo.viewUserPosition.firstName, dbo.viewUserPosition.lastName, dbo.viewUserPosition.email, dbo.viewUserPosition.address1,
dbo.viewUserPosition.address2, dbo.viewUserPosition.suburb, dbo.viewUserPosition.postcode, dbo.viewUserPosition.country,
dbo.viewUserPosition.state, dbo.viewUserPosition.mailAddress1, dbo.viewUserPosition.mailAddress2, dbo.viewUserPosition.mailSuburb,
dbo.viewUserPosition.mailPostcode, dbo.viewUserPosition.mailCountry, dbo.viewUserPosition.mailState, dbo.viewUserPosition.birthDate,
dbo.viewUserPosition.joinDate, dbo.viewUserPosition.lastUpdated, dbo.viewUserPosition.orgID, dbo.viewUserPosition.positionID,
dbo.viewLatestPaidFee.feeID, dbo.viewLatestPaidFee.mshipID, dbo.viewLatestPaidFee.name, dbo.viewLatestPaidFee.[desc],
dbo.viewLatestPaidFee.terms, dbo.viewLatestPaidFee.period, dbo.viewLatestPaidFee.periodType, dbo.viewLatestPaidFee.fee,
dbo.viewLatestPaidFee.startDate, dbo.viewLatestPaidFee.endDate, dbo.viewLatestPaidFee.deleted, dbo.viewLatestPaidFee.feePaidID,
dbo.viewLatestPaidFee.paidDate, dbo.viewLatestPaidFee.effectiveDate, dbo.viewLatestPaidFee.approved, dbo.viewLatestPaidFee.optionID,
dbo.viewLatestPaidFee.paidAmount, dbo.viewLatestPaidFee.feePaidEndDate
FROM dbo.viewUserPosition LEFT OUTER JOIN
dbo.viewLatestPaidFee ON dbo.viewUserPosition.id = dbo.viewLatestPaidFee.userID
Here is viewUserPosition:
SELECT dbo.tblUser.id, dbo.tblUser.username, dbo.tblUser.password, dbo.tblUser.title, dbo.tblUser.firstName, dbo.tblUser.lastName, dbo.tblUser.email,
dbo.tblUser.address1, dbo.tblUser.address2, dbo.tblUser.suburb, dbo.tblUser.postcode, dbo.tblUser.country, dbo.tblUser.state,
dbo.tblUser.mailAddress1, dbo.tblUser.mailAddress2, dbo.tblUser.mailSuburb, dbo.tblUser.mailPostcode, dbo.tblUser.mailCountry,
dbo.tblUser.mailState, dbo.tblUser.birthDate, dbo.tblUser.joinDate, dbo.tblUser.lastUpdated, dbo.tblRelPosition.orgID,
dbo.tblRelPosition.positionID
FROM dbo.tblUser INNER JOIN
dbo.tblRelPosition ON dbo.tblUser.id = dbo.tblRelPosition.userID
and viewLatestPaidFee:
SELECT dbo.tblMshipFee.id AS feeID, dbo.tblMshipFee.mshipID, dbo.tblMshipFee.name, dbo.tblMshipFee.[desc], dbo.tblMshipFee.terms,
dbo.tblMshipFee.period, dbo.tblMshipFee.periodType, dbo.tblMshipFee.fee, dbo.tblMshipFee.startDate, dbo.tblMshipFee.endDate,
dbo.tblMshipFee.deleted, fp.id AS feePaidID, fp.paidDate, fp.effectiveDate, fp.approved, fp.optionID, fp.paidAmount, fp.endDate AS feePaidEndDate,
fp.userID
FROM dbo.tblRelMshipFeePaid fp INNER JOIN
dbo.tblMshipFee ON dbo.tblMshipFee.id = fp.feeID AND fp.endDate =
(SELECT MAX(fp2.[endDate])
FROM [dbo].[tblRelMshipFeePaid] fp2
WHERE fp2.[userID] = fp.[userID])
We used a stored proc to pull totals from a database. Everything was fine until the table grew and started to time out. So we created a temp table to populate with a range of data and then pull the totals from there. Everything was fine until the table grew and started to time out. Any suggestion?
View 3 Replies View RelatedHi,
I am newly joined as SQL DBA. I want to check the Physical disk Performance. we have RAID 5 with 5+1 disks. I calculated NO Of IO's Per Disk. But how do we know what is actual limit of IO's per disk.
Thanks
Praveen
What's my best bet in getting better performance out of one of my database servers? Currently we have 1 set of Raid5 disks partitioned into 2 drives. This houses everything (system, database, and logs) If that server has 2 slots left for drives I was thinking of putting 2 mirrored drives and getting the logs off the main database space? (Make sense?) This is a vendored application so working with new indexes etc. isn't something I should do wo/ the vendor's interaction. Will what I describe above help?
Thanks
hi,
i am using to move data from oracle to oracle.
i have used stored procedure in oracle for the update/insert .
the dts calls the stored procedure for each record, due to this the performance has gone down. how do i increase the speed of data xfer.
has any one done any thing similar ?
Tushar