Using Quotation Marks In A Msdb.dbo.sp_send_dbmail Query

Oct 31, 2007

Hi There,

I'm having a little trouble with a large query I have written to be sent as an email.

I want to send it as a csv so I've added commas in between each value in the query.

This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'

,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '

View 1 Replies


ADVERTISEMENT

Linked Server Query With Quotation Marks

Jul 6, 2007

Hi,
My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer.
Here is my problem query:
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'

I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.

I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.

Please help me.

View 12 Replies View Related

Quotation Marks In Xml

Nov 25, 2007

I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.
this section shows the problem, i am loading xml from a file and trying to insert this into the sql database
XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close();
pls help

View 6 Replies View Related

Quotation Marks In SQL Server

May 3, 2006

 I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website.  The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site.  These announcements can be clicked on to display further detail.  When announcements are clicked on a javascript popup window is generated that displays the strings.  All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code?  (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.

View 7 Replies View Related

Msdb.dbo.sp_send_dbmail Error

Feb 28, 2007

I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'saly',

@recipients = @email,

@subject = @subject,

@body = @body;

Go

And when EXEC it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

View 9 Replies View Related

Query With Quotation Mark.

Jan 18, 2006

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.

View 5 Replies View Related

Find Highest Marks In Student Table Query

Oct 9, 2007



Hi Everybody
I've one table named Student. Here is data







Name
Subject
Mraks





Prasad
English
80

Tushar
English
79

Sunil
English
78

Prasad
Geometry
80

Tushar
Geometry
81

Sunil
Geometry
79

Prasad
History
82

Tushar
History
81

Sunil
History
80


Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this





Name
Subject
Mraks





Prasad
English
80

Tushar
Geometry
81

Prasad
History
82

So will anybody help me to write a sql query that acheive the same output

View 5 Replies View Related

Sp_send_dbmail Query Option

Sep 22, 2007

Hello,
I'm using sp_send_dbmail with query option. Is there a way to not have "1 rows returned" in the email?
Thanks!

View 3 Replies View Related

Sp_send_dbmail. How Can I Use A Query For @recipients

Feb 5, 2008

Hi everyone,
i'd like to assign "@recipients" for sp_send_dbmail derived from a query as follows:



Code SnippetDECLARE @mailist VARCHAR(2000)
SET @mailist=
'select email
from
server.db.dbo.table
where
lastname=' +''''+'aaa'+''''
exec msdb.dbo.sp_send_dbmail
@profile_name='myprofile',
@recipients=@mailist,
@subject='email address through a query',
@...



when i run it i dont get any error message but mails to dot arrive.
any suggestion why ?
Thanks a lot

View 5 Replies View Related

Attach Query Results As Xml With SP_SEND_DBMAIL

Jun 10, 2008

I'm trying to get sp_send_dbmail to attach the @query result set as an xml file.

The email is being sent and the attachment is present, but rather than ascii xml, it contains a chunk of nonsense like this
0x440352004F005700440470006B00650079004409660069007200730074006E0061006D00650044086C006100730074006E0061006D0065004410700068

I've tested the query with the first bit of code and sent the email with the second bit. I think I can jimmyjack it to write the xml to a temp file on the server and send that file using the @file_attachments parameter, but I'm hoping someone out there knows how to get this one to work.

Thanks in advance for any help you can give me.

Here's my code:


-- test that xml output is being generated
DECLARE @OUTPUT xml
SET @OUTPUT = (SELECT * FROM database..table FOR XML PATH('ROW'), root('ROOT'))
SELECT @OUTPUT

-- run the email proc
EXEC msdb.dbo.sp_send_dbmail
@QUERY= 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT'')'
,@RECIPIENTS= 'userid@companyname.COM'
,@subject= 'Subject'
,@attach_query_result_as_file= 1
,@query_attachment_filename= 'ATTACHMENT.xml'
,@query_result_width= 32767
,@query_no_truncate= 1
,@append_query_error= 1
,@query_result_header= 1

View 2 Replies View Related

Sp_send_dbmail @query Results Truncated

Sep 21, 2007



Hi, sending a mail using sp_send_dbmail (as below) with attachment works. However the characters in the attacheted file gets truncated to 256. The file is populated with the results from the @query string.


EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'SQLAdmin'

,@recipients = 'a@a.com'

,@subject = 'Test'

,@body = 'Test'

,@body_format = 'TEXT'

,@query = 'SELECT distinct column FROM Table'

,@execute_query_database = 'PULSE'

,@attach_query_result_as_file = 1

,@query_result_header = 0

,@query_result_width = 32767

,@query_attachment_filename = 'a.pdf'

,@exclude_query_output = 1

,@query_no_truncate = 1

The value of MaxFileSize is 1 Mb when executing sysmail_help_configure_sp. It seems as if the @query_no_truncate value is being ignored by sp_send_dbmail procedure.

Any help would be appreciated.

View 2 Replies View Related

Sp_send_dbmail: Need Output To A Query Without The Messages.

Feb 27, 2008

I am writing a stored procedure which finally has to send mail to the customer with a list of phones that have been added to his account. I am getting these phones from a SQL Table in the form of a query, and including the output of the query(list of phones) , in the body of the mail.

There are two issues that I am unable to resolve,

1. I just want the output with no messages.



eg. 8887775567

not


8887777767 ( 1 row(s) effected)



2. One phone number per row.



8887775567
8009978776
6679800077

NOT


8887775567 8009978776 6679800077


Thanks in advance.




View 8 Replies View Related

Service Broker Is Disabled In MSDB Or MSDB Failed To Start

Nov 21, 2005

Whenever I start my SQL Express 2005 database, I get the following in the logs :

View 10 Replies View Related

Asking For Quotation

Nov 24, 2006

What is the difference ',"?

View 4 Replies View Related

BCP For Quotation

May 15, 2008

I'm using BCP for some command line SQL statement. The file I'm generating is comma delimited. Some of my records contain comma's in them and require them to be in quotation marks. What is the parameter for BCP to put those entries that have commas in them to have those records be in quotation marks?

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Predict Future Job Schedules From MSDB Tables

May 8, 2014

I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).

If I have 3 jobs with...

Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM
Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM
Job# 3 scheduled to run every minute between 4 PM and 4:15 PM

my output should look as below ....

View 1 Replies View Related

Tick Marks

Jul 25, 2000

I'd like some ideas on how to help out my VB staff when it comes to the tick ( ' ) mark being used in front end applications. When users enter data into the front end, and they include tick marks (eg: Sam's Auto Store), this causes trouble in how the app uses that data in a WHERE clause and other places. Does anyone have any slick ideas or experience in handling this. We have experimented with search & replace, double tick marks ( '' ), etc...

View 1 Replies View Related

Tick Marks In Sql Statements

Feb 5, 2008



I am working on a project to import information from one database table to another. This info includes names, addresses, etc. I am running into a challenge whenever one of the names, such as O'Brien, causes problems in the sql statement. Is there a way for me to tell it to ignore the ' in O'Brien?

Thanks,
Brian

View 8 Replies View Related

Right Single Quotation Mark Errors

Jul 20, 2005

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

View 3 Replies View Related

Apostrophes Replaced By Question Marks Problem... Somebody Must Have A Fix

Dec 7, 2007

Hi I have an asp.net 2.0, web app with a sql 2000 db.  I am having a problem when users cut and past content from Word and Excel and past it into a textbox apostrophes are replaced as question marks when saved to db.  I have set the following line in my web.config which did not resolve the problem<globalization requestEncoding="iso-8859-1" responseEncoding="iso-8859-1" fileEncoding="iso-8859-1" culture="en-GB" uiCulture="en-GB"/> I have also read many posts, but have not found a resolutionhttp://www.codingforums.com/showthread.php?t=124609I cant do a find and replace of the character  before insertion into DB, as this would involve visiting over 90 pages and changing every insert statement. Is there a way I a can force my sql server to only allow characters in a certain format, eg iso-8859-1 to resolve this problem Please help somebody must have a resolutionMany thanks in advance
 

View 2 Replies View Related

SQL INSERT Changes Carriage Returns Into Question Marks???

Jan 8, 2008

Hi all,I have an old ASP .NET 1.1 application that I haven't had time to rebuild with .NET 2.0. No changes have been made to the application or the SQL Server the application uses. I have a web form where users can type multiple lines of text, and it is entered into an SQL database (SQL 2000 Enterprise) into a column with a datatype of "text". Recently (it seems out of nowhere), If my users enter a carriage return into the webform, it becomes a question mark in the sql database. It's really a bit funny, but also annoying, lol. Does anyone have any idea why this might be happening? Thanks so much! 

View 3 Replies View Related

Unclosed Quotation Mark Before The Character String ')'.

Mar 2, 2008

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; }

View 11 Replies View Related

Unclosed Quotation Mark Before The Character String ''.

Apr 10, 2008

 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>
 

View 8 Replies View Related

Reserved Symbols / Punctuation Marks In Passwords

Dec 9, 2003

Please can anyone help me - I am trying to establish whether there are any reserved symbols in SQL passwords

eg % £ " $ ( / < * etc

The SQL BOL has a list of reserved words but I cannot find a list of reserved symbols.

We have a problem where by for example if the main part of the password is "test" and my user wants to add a punctuation mark into this we get "You are attempting to use an invalid character message"

Specifically

"test$" is accepted "$test" is not accepted
None of "te+st" "+test" "test+" are accepted.

Has anyone else come across this and found a list of the logic that produces these results so I can point my users in the right direction?

Cheers

View 3 Replies View Related

Why Column Name On View Require Double Quotation

Dec 2, 2013

I have a view. When I select data, I have to use double quotation on selected column name, for instance:

select "assy_no" from myView;

if I try

select assy_no from myView

I will get error: ORA-00904: "ASSY_NO": invalid identifier

When I query on table, double quotation is not required.

Why the column name on view requires double quotation?

View 1 Replies View Related

Unclosed Quotation Mark After The Character String ')

Apr 1, 2008

Hi,


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.

View 6 Replies View Related

How Can I Ignore Accsents And Diacritical Marks In WHERE Statement?

Oct 26, 2006

How Can I Ignore Accsents and diacritical marks in WHERE statement?

i use sql statment for searching and i wont to search in text filds where some accent and diacritical marks have been enterd.

like: i want to search for 'e' or 'o' ... letters in this text: "éüöä"
if i used: "Select * from table where fild like 'e' " the result will be 0 items.

Also, same proplem in Arabic accents for eg: if i want to search for 'موت' in this text 'مَوتÙ?' , using select where statement. what can i do?

View 9 Replies View Related

Find Average Marks At Various Intervals Of Serial Number

Nov 24, 2013

I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.

S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)

So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.

View 12 Replies View Related

Sp_send_dbmail

May 9, 2006

When using "sp_send_dbmail", messages are queued through Service Broker. Other than the system views, "dbo.sysmail_...", in MSDB, is there another way to know if an email was sent successfully?

Also, in 2000 you did not need to supply the whole email address in order for xp_sendmail to work. For example, I could use my name, "RGioia" as the recipient and it would send email to rgioia@<my current domain>. Does anyone know of a work-around for this in 2005 or do you just have to ensure that full email addresses are used?

Thanks in advance!!

View 1 Replies View Related

SP_SEND_DBMAIL

Aug 8, 2007

I wrote a trigger as follows:

CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'

SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';


The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'

Please help.

View 1 Replies View Related

Need Help W/ Sp_send_dbmail !

May 1, 2007

SQL Server 2005Help. I have a problem:On a 2000 box, I am calling the sp_send_dbmail stored procedure on a2005 box (they are linked servers).This is how I'm calling the proc (from the 2000 box), which usessp_send_dbmail to send the mail.EXEC My2005Server.DatabaseName.dbo.SendNotification@recipients = 'me@yahoo.com',@subject = 'SENDING @TOTAL,@body = @text1 + @text2 + @text3 + @text4 + @text5@text1 - @text5 are varchar(8000).As you can see I'm trying to take advantage of the varchar(max) that@body allows.However, I get this error:Server: Msg 170, Level 15, State 1, Line 22Line 22: Incorrect syntax near '+'.I need to be able to concatenate these somehow... as I cannot put theminto one large variable from the 2000 server side.Can you see the problem here?Help.Thanks

View 1 Replies View Related

Sp_send_dbmail

Oct 31, 2007

Hello all,

I'm new to this forum and have the same question on SQLTeam.com, where I'm a frequent forum user.

I'm trying to send the results of a table to a group of people by email, see code used below:

exec msdb.dbo.sp_send_dbmail @profile_name = 'DT', @recipients = 'isantos@foo.net;', @subject = 'QA Results', @body = 'Some Text', @query = 'select * from [db].[dbo].[table]', @execute_query_database = 'db', @exclude_query_output = 1, @append_query_error = 1;


Error message I get by email:

Msg 15404, Level 16, State 19, Server TAKKARA, Line 1 Could not obtain information about Windows NT group/user 'WECLICKisantos', error code 0x5.

If I remove the @query, @exclude_query_output and @append_query_error options, I can send the email without a problem, just whenever I try to add a query to it that it gives me this error message.

I'm a sysadmin, I can run that query, I have access to the DatabaseMailUserRole under msdb and the profile that I'm using is public... I'm kind of stuck, let me know if you can help.

View 3 Replies View Related

Unclosed Quotation Mark Before The Character String 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]

Feb 7, 2007

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

 
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved