using backslash ( \ ) inside Parameter

Hi
I am trying to use backslash as a part of a parameter at stored procedure.
And I am getting syntax error.

For instance, to execute like this..

EXEC spSetupPermissions
	CORP\ABC,
	Sale

with parameter I have set up like this..

CREATE PROC [dbo].[spSetupPermissions]
	@USERNAME varchar(50),
                @DEPARTMENT varchar(12)
AS
IF @DEPARTMENT = 'Sale' 
BEGIN
	EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
	EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
END
......etc...

I should I go about doing this?
I would appreciate if anyone would give me some lead.

Thanks.

0
Utf
2/19/2010 8:05:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
2962 Views

Similar Articles

[PageSpeed] 7

Enclose strings you are passing in single quotes, e.g.,
EXEC spSetupPermissions
'CORP\ABC',
'Sale';

Tom
"Justin D." <JustinD@discussions.microsoft.com> wrote in message 
news:A94D8321-C93A-4605-906E-3CA69D437666@microsoft.com...
> Hi
> I am trying to use backslash as a part of a parameter at stored procedure.
> And I am getting syntax error.
>
> For instance, to execute like this..
>
> EXEC spSetupPermissions
> CORP\ABC,
> Sale
>
> with parameter I have set up like this..
>
> CREATE PROC [dbo].[spSetupPermissions]
> @USERNAME varchar(50),
>                @DEPARTMENT varchar(12)
> AS
> IF @DEPARTMENT = 'Sale'
> BEGIN
> EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> END
> .....etc...
>
> I should I go about doing this?
> I would appreciate if anyone would give me some lead.
>
> Thanks.
> 

0
Tom
2/19/2010 8:14:30 PM
Hi Tom,

I am still getting error as
'CORP\ABC' is not a valid name because it contains invalid characters.

"Tom Cooper" wrote:

> Enclose strings you are passing in single quotes, e.g.,
> EXEC spSetupPermissions
> 'CORP\ABC',
> 'Sale';
> 
> Tom
> "Justin D." <JustinD@discussions.microsoft.com> wrote in message 
> news:A94D8321-C93A-4605-906E-3CA69D437666@microsoft.com...
> > Hi
> > I am trying to use backslash as a part of a parameter at stored procedure.
> > And I am getting syntax error.
> >
> > For instance, to execute like this..
> >
> > EXEC spSetupPermissions
> > CORP\ABC,
> > Sale
> >
> > with parameter I have set up like this..
> >
> > CREATE PROC [dbo].[spSetupPermissions]
> > @USERNAME varchar(50),
> >                @DEPARTMENT varchar(12)
> > AS
> > IF @DEPARTMENT = 'Sale'
> > BEGIN
> > EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> > EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> > END
> > .....etc...
> >
> > I should I go about doing this?
> > I would appreciate if anyone would give me some lead.
> >
> > Thanks.
> > 
> 
> .
> 
1
Utf
2/19/2010 9:16:01 PM
It appears that  "EXEC sp_addlogin" would not accept backslash "\".
http://msdn.microsoft.com/en-us/library/ms173768.aspx

I tried using "USE" instead, but it would not work in a stored procedure.
Error message: 
"a USE database statement is not allowed in a procedure, function or trigger."

Example:
	USE Sale 
	CREATE USER [CORP\ABC] FOR LOGIN [CORP\ABC] ;

Is there any other way I could apply in a stored procedure (applying 
different databases)?

Thanks.

"Justin D." wrote:

> Hi Tom,
> 
> I am still getting error as
> 'CORP\ABC' is not a valid name because it contains invalid characters.
> 
> "Tom Cooper" wrote:
> 
> > Enclose strings you are passing in single quotes, e.g.,
> > EXEC spSetupPermissions
> > 'CORP\ABC',
> > 'Sale';
> > 
> > Tom
> > "Justin D." <JustinD@discussions.microsoft.com> wrote in message 
> > news:A94D8321-C93A-4605-906E-3CA69D437666@microsoft.com...
> > > Hi
> > > I am trying to use backslash as a part of a parameter at stored procedure.
> > > And I am getting syntax error.
> > >
> > > For instance, to execute like this..
> > >
> > > EXEC spSetupPermissions
> > > CORP\ABC,
> > > Sale
> > >
> > > with parameter I have set up like this..
> > >
> > > CREATE PROC [dbo].[spSetupPermissions]
> > > @USERNAME varchar(50),
> > >                @DEPARTMENT varchar(12)
> > > AS
> > > IF @DEPARTMENT = 'Sale'
> > > BEGIN
> > > EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> > > EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> > > END
> > > .....etc...
> > >
> > > I should I go about doing this?
> > > I would appreciate if anyone would give me some lead.
> > >
> > > Thanks.
> > > 
> > 
> > .
> > 
1
Utf
2/19/2010 10:51:01 PM
Justin D. (JustinD@discussions.microsoft.com) writes:
> It appears that  "EXEC sp_addlogin" would not accept backslash "\".
> http://msdn.microsoft.com/en-us/library/ms173768.aspx
> 
> I tried using "USE" instead, but it would not work in a stored
> procedure. Error message: "a USE database statement is not allowed in a
> procedure, function or trigger." 
> 
> Example:
>      USE Sale 
>      CREATE USER [CORP\ABC] FOR LOGIN [CORP\ABC] ;
> 
> Is there any other way I could apply in a stored procedure (applying 
> different databases)?
 
In your previous post, you used sp_addlogin, now you are using CREATE USER,
those are two different things. Maybe it was sp_adduser to sp_grantdbaccess
you had in mind?

Anyway, CREATE USER is what you should use. A way to go is:

   SELECT @execsql = @DEPARTMENT + '..sp_executesql'
   SELECT @sql = N'CREATE USER ' + quotename(@USERNAME)
   EXEC @execsql @sql


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
2/20/2010 2:18:14 AM
Reply:

Similar Artilces:

How do i sort contacts using more than one catagory ?
I am trying to create lists which have contacts sorted by more than one catagory. So including contacts that are linked to two or more specific catagories. Can this be done, if so how? thanks "samong" <samong@discussions.microsoft.com> wrote in message news:01E99022-AB21-487B-9365-2E66BB0C3F1B@microsoft.com... >I am trying to create lists which have contacts sorted by more than one > catagory. So including contacts that are linked to two or more specific > catagories. Can this be done, if so how? The Category field is non-sortable because it is a multi...

population pyramids using bar charts with a secondary axis
I have a problem that I had given up as unsolvable, but after recently learning about secondary axes, I've made encouraging progress. However, I'm stuck on the last step(s), and I'm hoping that someone will have some suggestions. My organization has been producing reports that contain population pyramids. In the past, all of their reports have been printed, so it didn't matter that in order to get the desired look, they had to use two charts slightly overlapping each other. However, we are getting to the point where we would like our charts to be available online for download....

Using OLE DB in MFC Project
Hi, I have developed an MFC application. I now want to add database functionality to it. I have had a look at the various database technologies and have decided to try using direct OLE DB rather than ADO as it is faster. However I have noticed that in tutorials it is recommended to use the wizards in an ATL project. As i have said before my application has been written in MFC. I was wondering how easy it is to add direct OLE DB to my MFC application. I would appreciate suggesstions on how i can manage this, Thanks In Advance Macca ...

OWA from inside the LAN
Good morning all, I have an E2K3 server with an internal IP address, and do port forwarding from a firewall/router to it from the outside world. From outside the company, I can hit http://mail.xxxx.com/exchange (my A record public address) and get into OWA after entering name/password. When I am inside the company, if I try the same exercise, I cannot get past the login prompt! It keeps popping up and never lets me into the OWA. I have also tried entering <domain>/username and that did not work either. mail.xxx.com is resolving to the external address (verified by a ping). If I try fro...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

Word 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have created several files in Word 2008 using templates (.dot) files previously used with Word 2003/2007. Unfortunately, my customer keeps receiving an error when opening the new file I create. The new file (created in .doc format) has some hidden data that is pointing to the template folder on my Mac. The error looks like: Could not open: 'user:Libarary:Application Support:Microsoft:Offce:User Templates:template.dot My customer can fix this error by using the remove hidden data tool inside Office 2003, but that&...

How can I change the program used when following a hyperlink
I have a database which uses hyperlinks to take me to a set of image files, gif & jpeg. I have recently moved to Firefox. If I click on a link to a gif file it opens in Firefox, but if I do the same for a jpg it persists in using IE. The file association for jpg files on the computer are to Photoshop. But I prefer opening the links in the browser as I am only viewing, not editing, the files Anyone got any ideas JimR Use a button that when clicked calls this code (pass in the value on the hyperlink field): Public Sub OpenWebPage(url As String) Dim browser As Vari...

dynamic field content of parameter combobox
Greetings, I have to filter my Crystal 9.0 report with a parameter combobox field in Microsoft CRM. The data for this parameter field has to be loaded out of a database field dynamically. I have found the possibility to load database field values in a parameter field but this will not be dynamically. If there is a change in the database content the parameter field content will not be updated. I hope to create a combobox control is no problem. Is there a way to turn this into reality? Thanks for your help Thomas Ott (ITVT germany) ...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

scripting adding obj-users and mdb-use-defaults to exchange 5.5 users
I have successfully created a script for creating exchange 5.5 users. I can't however, figure out how to add the assoc-nt-account to the obj-users [pseudo attribute] or set MDB-Use-Defaults to True :-( Can anyone offer advice on how to do this [other than generate a CSV file and use the exchange admin tool to import it]? Thanks Wayne ...

keeping footer from resizing when using the fit to page function
How do I keep the footer from shrinking in excel when i use the "fit to page" option for my worksheets. I'd like it to stay the same size no matter what the font of the worksheet is. ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Database using Access 2.0
Our church purchased Church Windows software. When we asked if we could import our members from our existing database Greentree CDMS they said we couldn't because CW is an Access 2.0 database and we don't have that software. What would be the benefit of still using Access 2.0 for software development? Thanks, Linda Hi Linda I doubt there are many new programs being written in Access 2. It was a great piece of software, and many databases were writing using it. I don't know Church Windows, but I am guessing that it was written back in the Access 2 days (around 1994), ...

Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & DATEDIF(C6,NOW(),"md") & " D" I'd like a formula that can produce the same format (years, months, days) between now and a future date. Any ideas? Thanks in advance, Bart Hi Bart Try this with the date in A2 =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...

Creating opportunity using CRM api
Looking for an example of how to load the crm api to create an opportunity using C#. I'm way new to this and have downloaded the CRM 3.0 sdk but not sure how I access the information it contains. What I'm hoping to find is an example of creating an opportunity with just the necessary information so I can build on it from there. I appreciate any help! Hi Jeffa When you install the SDk you will notice there is a help file in the installed directory of the SDK, I think it is crmsdk3_0.chm Open the helpfile and search for create, there are lots of examples let me know if you get s...

Printout Method
In building a form to allow users to print a "blank form" I've constructed as an Access Report, I'd like the user to be able to select the number of copies to print. It looks like the PrintOut Method would be a good choice for this, but I'm not smart enough to know quite how to get the "Copies" argument to get its input from the textbox on my form, where the user will enter the number of copies they'd like to print. Suggestions? -- Thanks, Croy Does the report have a record source? -- Duane Hookom Microsoft Access MVP If I have helped you, please help...

Use of user's group with W2000
Two things: 1-If no MVP can answer a question, someone should tell the questioner so he/she can move on. Frustration is high enough without being ignored. 2-It is grossly unfair that I can't post questions because I have W2000 and am limited to Explorer6. I can't even get to this page. Microsoft should not squeeze us out of the loop. (I borrowed a friend's computer with XP and explorer7 to write this and to post my unaswered question.) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for th...

Using Regular expressions in Excell 2007
I have code written in excell 2003 where I use Regular Expressions 'heavily'. In Excell 2003 you had to import the RegExp library to be able to use it. Is Regular Expressions part of VBA in Excell 2007? or do you still need to import the RegExp library? Same as in Excel 2003, the library is part of IE not Excel or VBA. -- HTH Bob "ncunha" <ncunha@discussions.microsoft.com> wrote in message news:7EF31D63-6C38-451B-9CEF-2E091DA7B2E7@microsoft.com... >I have code written in excell 2003 where I use Regular Expressions > 'heavily'...

0.947694 Look Inside And Realize That Dream ... 0.2828604
1.178199E-02 Look Inside And Realize That Dream ... 9.059644E-02 0.947694 Now Visit http://nowclickhere.bravehost.com/index.html 0.2828604 ...

how to use "barcode" in excel 2000??
Dear all, i would like to set up an inventory system for recording the stuff. previously, i have a long table of data in excel. i need to do some tasks: 1)print the barcode out for each item 2)scan the barcode for checking the inventory. i have no idea how to start, install barcode fonts? what i have to buy and do i need to write "MARCOS"? Please help me! manymany thanks. gg It depends on how you intend printing the labels. If you're wanting simple all in one solution, 'Bizfonts' (http://www.bizfonts.com/) ca help. Another way is to use a 'bar code module (ht...

Use specific printer for reports in view mode
I designed a report using printer HP5000 PLC6 and the paper size is A3 in landscape. When I open the report in my laptop and I want to show the report in my resentation, it can not showed all contents of the reort, just a hals is shown, my question is how can we assign this printer to this reprot when I open it, although I do not need to print it, this is just for my resentation. My printer default in mypc is lHP desckjet 690c -- H. Frank Situmorang Might try installing the printer driver for the HP5000 PLC6. Might try suing the printer "Microsoft Office Document Image Writer&qu...

How do I create an A3 size document using Windows 7
A3 is not listed in the pre-set schedule of document sizes in Windows 7 (unlike previous versions of Windows). How do I create such a document using Windows 7? Does your printer support A3? Word/Windows will display the paper sizes supported by the default (or currently active) printer. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "A3 documents in Windows 7" <A3 documents in Windows 7@discussions.microsoft.com> wrote in message news:F2647F06-5560-41A6-9C2F-462E6198C989@microsoft.com... > A3 is ...

Will pay for need help and occasional advise on using excel ....
"sorry if this is off post ...will be my last" Thx in advance for your help and interest. i am an independant oil and gas consultant and have a fair amount of computer experience but, am absolutely lost when it comes to data bases .... i found a database online (in .php format) that contains names, addresses, phone numbers, fax numbers and email addresses of companies and people within the oil and gas industry ... i want to be able to utilize this db in excel so i can sort certain companies or people and use excels "mail merge" system to send them emails. ...

Using conditions in WF Sales Processes
I'm creating a workflow process that I am trying to implement in MSCRM. The problem is that within each Stage, I have activities that should be dependent on the completion of the previous activity. The easiest way to solve this is to go beyond the 5 step Sales Process and create a new Stage for each successive activity. However, in the Sales Process tab this is quite unsightly. I've tried using a normal manual WF as a subprocess to the Sales Process WF. This works, but it gives me 2 problems: 1. The WF Subprocess does not appear in the Opportunity Sales Process Tab. The customer want...

Not able to connect to outgoing SMTP server using POP account in Outlook 2007
Good Day, We have a user that is using Outlook 2007 and I am able to get incoming mail but when I try to send email I get the following error. "Outlook cannot connect to your outgoing (SMTP) e-mail server" I went into configure and clicked on more settings and checked the following Outgoing Server Clicked My outgoing server (SMTP) requires authentication and Clicked on Log into incoming mail server before sending mail I have also tried the other options on this list with No luck. Does anybody have any other suggestions that I can try. Thank You Ad...