I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans; insert into table1 values (1, 'test'); insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10 commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
we have an update sp that must call an insert sp after the update. The update and insert must act like a transaction, ie all or none.
We believe that wrapping most of the update (including call to insert sp) sp in a begin tran block would guarantee the all or none behavior.
However, we're not sure what would happen if one of our developers calls the update sp from within a transaction scope that expects yet something additional to be included in the transaction. Would the begin tran block (assuming no errors in that block) in the sp commit both the update and insert regardless of what happens in the rest of the .net tran scope?
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++; } }
Hi have have two linked SQL Servers and I am trying to get things workingsmootly/quickly.Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?Basicly, these SPs update a local table and a remote table in the sametransaction. I cant have one table updated and not the other. Please dontsay replicate the tables either as at this time, this is is not an option.I have for example a number of stored procedures that are based around thefollowing:where ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
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 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
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?
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
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
Hi,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
I 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
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
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
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.
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.
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!)
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
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'
I have a transaction log that is over f gig in size....what can be done with this..and what are the pros and cons if I delete it...also how can I keep this from getting that big in the future. Thanks!
On my SQL 6.5 box, I have a corrupt Tran log. I do not use my Tran log but now I am getting an 1105 error, that the log is full. I run Dump tran with no log but it does not work. I cannot perform any other function without getting the 1105 error. Now I tried to reboot and now it is hanging during reboot. It is hanging while checking the partition where the tran log resides. I went in to VGA Mode. Any ideas would be appreciated.
<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
Does this seem right? We have our transaction logs set to "Truncate Log on Checkpoint" and they still grow over 1GB. Is it possible that one transaction (to a checkpoint) generates this much logged information? Will transaction log backups every 5-10 minutes help me out better or is this just a poorly written application?
Help. I have a database with high transaction rates. THe log is 300 mbs. No matter what i do I cannot get it below 64%. I have dumped and trucated the log yet it will not budge. Being friday and it being a time card application this is my heaviest transaction day. Please help
