Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login
Mar 3, 2014
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Need to create same login on 100s of servers using local admin login create on the OS. (for xp_cmdshell proxy setup)
Normal command works fine but I meed to make <svr_name> a variable.
CREATE LOGIN [<svr_name>DBALocalUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO
When I try various efforts to pass in the <svr_name> portion it fails every time.
declare @cmd varchar(200), declare @username varchar(50) set @username = 'DBALocalUser' set @cmd = ' CREATE LOGIN ['+@@servername+''+@username'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
I am trying to enclose a create procedure in a begin...end block and get the following:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.
If i execute the create w/o the begin..end it executes correctly. I am at a complete loss. The greater plan is this: when we roll out an updated version of our software we update many things in the database, I need a way to halt and rollback any database changes if there is an error. If anyone has a better suggestion of help with resolving my error I would greatly appreciate it.
Here is my statement:
begin
CREATE PROCEDURE dbo.LMS_InvTurnsEOMUpdate
AS
-- EOM Script to update the InvTurnsEOMCostHistory and InvTurnsEOMInventory tables.
-- these are used for the Inventory Turns Report.
-- Add this script to SQL jobs to run on the last day of the month.
-- kaj 1/08
--exec LMS_InvTurnsEOMUpdate
--select * from InvTurnsEOMCostHistory
--select * from InvTurnsEOMInventory
declare @PeriodID varchar(10)
declare @PeriodDisplaySeq numeric(7,0)
declare @LMSDateCur numeric(7,0)
-- get current system date in NG format
--select cast(getdate() as smalldatetime)
set @LMSDateCur = ((year(getdate())*10000+month(getdate())*100+day(getdate()))-19000000)
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
I have some problems getting my SSIS package to run in a transaction, I wonder if anyone can assist.
What I want to do is run one package, which consists of a
- 1) create staging table SQL statement
- 2) read (all) from Access MDB to staging
- 3) copy (only new) from staging table to real table
- 4) drop the staging table
I changed the package to "require" a transactions, and left all containers in it to "supported" (default, I think). I left all the transaction type to default "serializeable".
The package does not work, since the table I created on step one is not "seen" when step 2 wants to insert into it. If I set the package transaction back to "supported" (default) the package works, but an error on step 2 or 3 will not rollback changes and not remove the staging tables...
So, my question really is: How do I make step 1 results visible to step 2 in the same transaction? Or do I need to take a completly different approach for this?
Stupid question I think, but I seem to not be able to find the right way to handle this situation...
I have written stored procedures to create a database with db name as input parameter
I need to create a batch file to run the stored procedure with input value given in the command prompt along with the batch file will be my dbname, which will be used by the stored procedure to create the databse.
and also few sql statements to insert data into those tables after creating
Plz could any one help me out to create code to create a batch file
Does anyone have any SQL that will look at a DB and dynamically generate CREATE INDEX statements? I know I can use EM but I want to make this a scripted process and you can only generate CREATE INDEX statement if you script out the tables too.
MySql has a statement like: SHOW CREATE TABLE tablename;
that returns the precise CREATE TABLE statement for the specified tablename.
Sql Management Studio also allows scripting Create Statement for any object by right-clicking it. But I want to do this programatically, and fetch CREATE statements for Tables, Procedures & Views.
How can I retrive CREATE statements for Database objects progrmatically in Sql Server???
Although writing a parameterized SQL statement has been simplified using the asp:parameter options, it still may benefit to use the old fashioned method of writing a sql statement using an input string. I have noticed this for wanting to make a parameter to select which table I want to pull from. Here is some code I wrote to pull information from a database based on input from a search box and write it to a gridview. Partial Class Private_SearchResultsInherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim Table As String Dim SearchString As StringSearchString = Request.QueryString("SearchString") Table = Request.QueryString("Table") If Len(Trim(SearchString)) > 0 ThenSelect Case Table Case "Plant" SqlDataSource1.SelectCommand = "SELECT PlantName as 'Plant',PlantAddr as 'Address',PlantCity as 'City',PlantState as 'State',PlantCountry as 'Country',PlantZip as 'ZIP' FROM Plant WHERE PlantName LIKE '%" & SearchString & "%' OR PlantCity LIKE '%" & SearchString & "%' OR PlantState LIKE '%" & SearchString & "%' OR PlantCountry LIKE '%" & SearchString & "%' OR PlantZip LIKE '%" & SearchString & "%' ORDER BY PlantName" Case "Contacts" SqlDataSource1.SelectCommand = "SELECT ContactPosition as'Position',ContactTitle as 'Title',ContactLast as 'Last Name',ContactFirst as 'First Name',ContactPhone as 'Phone No' FROM Contacts WHERE ContactLast LIKE '%" & SearchString & "%' OR ContactFirst LIKE '%" & SearchString & "%' OR ContactPosition LIKE '%" & SearchString & "%' OR ContactPhone LIKE '%" & SearchString & "%' OR ContactTitle LIKE '%" & SearchString & "%' ORDER BY ContactLast" Case "Events" SqlDataSource1.SelectCommand = "SELECT EventName as 'Event',CONVERT(varchar,EventStartDate,101) as'Start Date',CONVERT(varchar,EventEndDate,101) as 'End Date',EventNotes as 'Notes',EventNoAttendees as 'Attendees',EventType as 'Event Type' FROM Events WHERE EventName LIKE '%" & SearchString & "%' OR EventStartDate LIKE '%" & SearchString & "%' OR EventEndDate LIKE '%" & SearchString & "%' OR EventNotes LIKE '%" & SearchString & "%' OR EventNoAttendees LIKE '%" & SearchString & "%' OR EventType LIKE '%" & SearchString & "%' ORDER BY EventName" Case "" Label1.Text = "Nothing Selected in Drop Down Box" End Select Else Label1.Text = "No Search Parameters Entered" End If GridView1.DataBind()End Sub End Class
Here is also my code to the front end of the page...
Need to create a user defined role with grant permissions for below .
View Definition Execute all Function Grant View Grant Synonym dbo View Definition Not getting grant statements for above permissions.
I mean like below.
----------------------------------------------------------------- CREATE ROLE [Role1] GRANT EXECUTE ON SCHEMA ::dbo TO [Role1] -----------------------------------------------------------------
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???
I need your help again. I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.
i have two input parameters (@fname, @lname). Thanks.
i m new in the world of database so please help me to create a perticular store proc
i want to get data from 3 tables and 2 field of a table is match with the same field of another table and it cant work i write that code so u can understand easily but it dont work so please modify that and make it workable
select a.a_dis_id,b.name as fromname,b.name as toname,a.a_dis_km,c.a_all_name,c.a_all_rate from a_distance a,levels b,a_allowance c where a.a_dis_from=b.cid and a.a_dis_to=b.cid and c.a_all_id=a.a_all_id
I'm new to this forum and looking for helps tip on SQL Server. How to create a store procedure? Here's the proper information that below: 1)In house data, have 2 tables called: Advo and Advosum;included Excel sheet (ChampaignAddress.xls) have raw data rows (Latitude1,Latitude2,Longitude1,Store). 2)I have a query statement to retrieve data from "In House Data" which in Where Clause using (latitude between # and #)& (longitude). Get each rows data in column from excel(ChampaignAddress.xls)to populate into Where Clause that when it execute the query. 3) SELECT CASE WHEN deliveryTypeCode >= 'A' AND deliveryTypeCode <= 'H' THEN CONVERT(char(20), 'R') ELSE CONVERT(char(20), 'B') END AS RBDI, case(left(advo.crrt,1))when 'B' then convert(char(20),'BOXHOLDER') when 'C' then case when (deliveryTypeCode>='A') and (deliveryTypeCode <='H') then convert(char(20),'RESIDENT') else convert(char(20),'BUSINESS OWNER') end else 'RESIDENT' end as Title, case (left(advo.crrt,1)) when 'B' then convert(char(64),( rtrim(StreetName)+ ' ' + rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' + rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' + rtrim(alternateTopLine) + ' ' + rtrim(AptNum)))else convert(char(64),(rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' + rtrim(StreetName) + ' ' + rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' + rtrim(alternateTopLine) + ' ' + rtrim(AptNum))) end as Address, cityName as City, State,advo.ZIP, advo.Plus4, convert(numeric,ltrim(Walkseq)) as Walkseq, advo.Crrt,('******************ECRWSS**' + advo.Crrt)as Endorse, cityRuralFlag as City_rural,convert(char(2),replace(dpb,' ','0'))as dpb,dpbc,null as primaryPreName, null as PrimaryFirstName,null as PrimaryMiddleInitial,null as PrimaryLastName,null as PrimaryPostName,updateDate FROM advosum squareRadius join Advo On advo.crrt=squareRadius.crrt and advo.zip=squareRadius.zip WHERE latitude between 40.44294591 and 40.48836091 AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3)))) AND advo.crrt LIKE 'C%' OR latitude between 40.44294591 and 40.48836091 AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3)))) AND advo.crrt LIKE 'B%' OR latitude between 40.44294591 and 40.48836091 AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3)))) AND (advo.crrt LIKE 'R%' OR advo.crrt LIKE 'H%' OR advo.crrt LIKE 'G%')
ORDER BY advo.zip,advo.crrt,walkseq +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
Hi All! I have Store Procedure: If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum') Drop Procedure Forum_Topic_SelectFromForumgoCREATE PROCEDURE Forum_Topic_SelectFromForum( @ForumID varchar(10)) AS BEGIN TRANSACTIONSELECT * from Forum_Topic where ForumID=@ForumID Order by Tmp DESCIF @@ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION Now, I want to Add 2 Variables: @Offset int, @Count int . With @Offset: the point of data, @Count: sum of row will get. when get data I want it get from @Offset to Added @Count. Help me to rewrite this store procedure. Thanks
Dear all, i want to create a storeprocedure that may acept value such as 0,1, 1a, 2, 2a etc.. from a dropDownList. But i always get an error. below is sample of my SQL1 DECLARE @val varchar(5) 2 DECLARE @sql varchar(4000) 3 4 SET @val = '1a' 5 SET @sql = 'select * FROM [dbo].[vwFormNAllAnswers]' 6 7 IF @val NOT LIKE '0' 8 SET @sql = @sql + 'WHERE QuestionCode LIKE '+ @val 9 10 EXEC(@sql)
hello all.., i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy" i have 2 database like: bank.dbo and games.dbo in games.dbo, have a table name is order(user_id,no_order,date,totalcost) in bank.dbo, have a table name like is bill(no_bill,balance) this is a list of bill table no_bill balance 111222 200$ 222444 10$ this is a list of order table user_id no_order date totalcost a 1 1/1/07 50$ when customer insert no_bill(111222) in page and click a button, then bill table became no_bill balance 111222 150$ 222444 10$ when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" is procedure can take data from 2 database?mystore procedure like:ALTER PROCEDURE [dbo].[pay]( @no_bill AS INT, @no_order AS int, @totalcost AS money)ASBEGIN BEGIN TRANSACTION DECLARE @balanc AS money SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill) UPDATE [bill] SET [balance] = @balanc - @totalcost WHERE [no_bill] = @no_bill COMMIT TRANSACTIONEND it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[pay]." plss.. help...
hi, i tried to create a sql authenticated login by right click security,new login and follow the steps needed but once i finish creating, i could not login using the account. Why is it so? Can help me? Let my user name = " pe " and my password= " 123456 ". Help me. Thanks
This error message : Cannot find the certificate 'AccountingCorp', because it does not exist or you do not have permission. This Code USE master CREATE LOGIN Dave FROM certificate AccountingCorp; GO ****************** AccountingCorp was created wiht this code: USE Frontier_equipment; CREATE CERTIFICATE AccountingCorp WITH SUBJECT = 'Frontier Accounting Records', EXPIRY_DATE = '10/31/2009'; GO
Hi all As you know in [Enterprise Manager] , in the left pane and in SecurityLogin section we can create Logings for users by just Right click in the Right pane and select [New Login...] But i couldn't understand one thing !!! when i create a new login with specifying ONLY: 1) Login Name 2) Sql Server Authentication Mode and without specifying Server Roles and Database Access, the created Login has Default Access to System Databases such as Master,MSDB and TempDB databases !!! is there an way to revoke this Access permission from Created Login? if so how?
By the way i have anotehr Question. when you are assigning Database Role to a login there are some Fixed Database Roles such as db_owner but we have also db_DataReader and db_DenyDataReader i want to know why do we have both db_DataReader and db_DenyDataReader roles at the same time ??? nothing only curious !!!
I have a dts set up to take info from our as/400 and load it onto into SQL 7.0 server. I would like to within my select that I'm using to load up the data calulate a variable or create a temp table with one row that has the current fiscal year. This fiscal year would then be used in my select to determine what data to bring in. But it doesn't seem like I can use a table on the server within my select statement on the destination portion of my dts. This is what I would like to accomplish select * from iptsfil.ipwkhst ,swoolib.DSSCNTL where KYR#=select max(fiscyear) from fiscalyear(this is my temp table that I have already created) AND KWK# = STRNUM AND KCT#=1 AND TBLNME = 'WEEKHST'
the dts doesn't seem to like me using both file from the server and the 400 at the same time. Is there a way to do what I'm trying to accomplish
