I try to run sql statement by placing them in a variable as given below but receive the following error message
set @sql_string= 'BACKUP DATABASE '+ @temp_db+' TO '+ @bk_device+' WITH FORMAT,INIT, NAME = '''+ @bk_name+''''
EXECUTE IMMEDIATE @sql_string
Error Message
Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'IMMEDIATE'.
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'BACKUP DATABASE DTSTEST TO bk_dev1 WITH FORMAT,INIT, NAME = 'Full backup''.
When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?
I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().
The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.
I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.
I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?
I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.
Heck, even if I execute this code through SSMS it does not work as I need it to:
USE OtherDatabaseName; EXEC DBA.dbo.usp_CreateSomeAdminObjects
I confirmed this by adding these statements to the proc:
/* Gets list of tables from the current database */ SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name
SELECT DB_NAME();
These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.
Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.
What am I doing wrong in the following snippet of code?-----------------------------------declare @FullPath varchar(255)set @FullPath = 'C:MyDirMyFile.txt'bulk insert Northwind.dbo.Sometablefrom @FullPathwith(FIELDTERMINATOR = ',')-----------------------------------This works when I write the path out instead of using the @FullPathvariable. I have a cursor that will Bulk Insert all files names from atable. The path will not change but the file name will. I guess I could usea system call to rename the file in use to a hardcoded name and then renameback to original name but I would like find a way to make the above conceptwork if it is possible.TIA,-p
I'm trying to run a statement against all of my servers - I've got about 10 statements working, but am having trouble with this one. It's in a for loop with a connection that is changed by a recordset which is a list of my servers. Here is the code - In the step before, I've created the tempdb.dbo.DBROLES table.
Code Block declare @dbname as varchar(200) declare @mSql1 as varchar(8000) DECLARE DBName_Cursor CURSOR FOR select '[' + name + ']' from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN Set @mSQL1 = 'Insert into tempdb.dbo.DBROLES ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '''+ @dbName +''' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @dbNAme+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+ @dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @mSql1 Execute (@mSql1) FETCH NEXT FROM DBName_Cursor INTO @dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor;
failed with the following error: "Must declare the variable '@dbName'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Put dbroles into table
The program '[10320] DBA_Repository_Load (1).dtsx: DTS' has exited with code 0 (0x0).
I'm going to try creating the procedure on each server - that will probably work - I just don't get why it's not recognizing that variable. The line it is referring to is FETCH NEXT FROM DBName_Cursor INTO @dbName. Thanks for any info on this!
Hi I am trying to include a string variable in a Select Statement. My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:) cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below): cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName I know there must be something wrong with my syntax ?? Thanks Jackson
HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew
We have unreliable circuits connecting our office to remote sql servers. At our office I need to delete/insert a bunch of records from those remote locations. Since the connection is unreliable what is the best way to check if a server is available before executing delete/insert statements. A few points to consider:
1. I want this to be done in SQL 2. I have a cursor that cycles through a tabe that contains the name of the linked servers. The cursure is used to delete/insert a bunch of records from each remote server. 3. If one server is unavailable the stored proc should continue to the next sql server (next record in the cursor.)
I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.
Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
Hi guys, Need some help in some query processing... Here goes,this is the results i have obtained so far |id |cat | name | 1 .....a... apple 1 .....b .. banana 2 .....a ...austria 2 .....b ...brazil 2 .....c ...china 3 .....a ...abraham 3 .....c ...clinton
Column cat can have at most 3 different types of values..{a,b,c}
I wanna write a query such that the result comes out as such <(....id ......a .......b......... c..) corresponding to these fields.>
|Col 1| Col 2 | Col 3 | Col 4| ....1 ...apple ...banana ....2 ...austria .brazil... china ....3 ...abraham ..........clinton
I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?
Hello guys. How is this declared in SQL? Select * FROM my_table if my_column = "1" UPDATE my_table SET my_other_column= "a" ////my_column and my_other_column belong to my_table else if my_column="2" UPDATE my_table SET my_other_column= "b" ////my_column and my_other_column belong to my_table
Hello I have an SQL Data Source i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris
I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!
I need to create a brief statement in Design View for SQL 2000.
I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.
Is there a simple statement I can write in "column" of design view that can get the results that I need?
I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.
I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code. @JOB1, @COLLEGEDATE smalldatetime, If(@COLLEGEDATE <> ''") @COLLEGEDATE smalldatetime, ELSE @COLLEGEDATE = getDate(), RETURN @JOB2
Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table. Is there a way for the insert statements to be merged so that I won't be executing two statements? Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn) Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)
Hello, how can i merge together several select statements? I have something like this: CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
I'm running a custom built report on .net page Basically there are a lot categories. I need to run SQL statements for and for each category, the catch is these statements are all in loops for i = 1 to 12. Therefore 108 categories X 12 iterations of SQL calls = a little more that 1200 sql calls from one .net page. There is obviously a major problem with load time and running these reports are painful to the user and the system.
Is there any easier way to possibly pre-compile all the data into a temp database so I wouldn't need to make as many sql calls??????? If so, would a stored procedure handle something like this. Sorry for being somewhat vague.
How do I handle and apostrophes and other punctuation in stored procedure Sql statements: SELECT L_ID, L_NameFROM tblHVACContractorsWHERE (L_Name = 'Mare's Heating & Cooling Services')
Hello, I have SqlDataSource to retrieve a data into DetailsView, or GridView, whatever. I want to use two Select command like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=AHMED-4714D04B3;Initial Catalog=mp;Integrated Security=True" <% if (( parID == 3) || (ParID ==4) { SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ParID] = @ParID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ParID" PropertyName="SelectedValue" /> </SelectParameters> } else {
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ChiID] = @ChiID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ChiID" PropertyName="SelectedValue" /> </SelectParameters> } </asp:SqlDataSource> Is it possible to do something like that ?
