I have a large SQL script that creates about 20 stored procedures. It worked fine up until our dev SQL server died and was rebuilt. Now, when I try and run the entire script, it complains that I am redeclaring the parameters multiple times in a batch:
The variable name has already been declared. Variable names must be unique within a query batch or stored procedure. (see snippet below)
My feeling is it is a setting on the server but I didn't find anything on Google. Anyone know what it might be? How can it be matching 2 parameters in DIFFERENT stored Procedures?
Thanks in advance.
-------------------SNIPPET------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_PartyInviteMessage_Create
@PartyInviteID int,
@PartyInviteTypeID int,
@PartyInviteRecipientTypeID int,
@PartyID int,
@TemplateSetID int,
@FromEmail varchar(255),
@ToEmail varchar(255),
@Subject nvarchar(255),
@Body ntext,
@Enabled bit
AS
BEGIN
INSERT INTO PartyInviteMessage (PartyInviteID, PartyInviteTypeID, PartyInviteRecipientTypeID, PartyID, TemplateSetID, FromEmail, ToEmail, Subject, Body, Enabled)
VALUES (@PartyInviteID, @PartyInviteTypeID, @PartyInviteRecipientTypeID, @PartyID, @TemplateSetID, @FromEmail, @ToEmail, @Subject, @Body, @Enabled)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_PartyInviteMessage_Delete
@PartyInviteID int,
@PartyInviteTypeID int,
@PartyInviteRecipientTypeID int,
@PartyID int
AS
BEGIN
DELETE FROM PartyInviteMessage
where
PartyInviteID = @PartyInviteID
AND PartyInviteTypeID = @PartyInviteTypeID
AND PartyInviteRecipientTypeID = @PartyInviteRecipientTypeID
AND PartyID = @PartyID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm upgrading an application that uses a VBScript/ASP front-end and MS Access Backend. The application has many points expecting fields to be true or false..
E.g. SELECT * FROM MyTable WHERE Active=True
However, since SQL Server requires 0 or 1 for bit fields, this query keeps failing. SQL Server (2005 Express) thinks True is a column name and is not automatically converting "True" to 1.
It would be so difficult to re-write the application to do this at the VB level, is there a way to make SQL server do this conversion automatically ?
I hosed myself, please help me somebody.We have a development server (SQL7) where the database is a restored copy ofproduction (where we use replication, but not in the developmentenvironment).While we rebuild the server recently (so we could move off of NT onto 2000),everything looked to be working fine.But I recently, messed up and did a massive update filling the transactionlog of the development server. Now I can't update data in development.I tried to do my usual:BACKUP LOG LAMS WITH Truncate_onlyonly to get a message:The log was not truncated because records at the beginning of the log arepending replication. Ensure the Log Reader Agent is running or usesp_repldone to mark transactions as distributed.Did a little research into BOL and tried to call the following two procs:exec sp_repldone@xactid = null ,@xact_seqno = null ,@reset = 1exec sp_repltransBoth of which gave me an error of :The database is not published.Which it isn't as we don't run replication in the development environment.I stopped and restarted the SQL Service (as that has cleared issues in thepast), but didn't reboot the server.How can I make the database not think It's replicated (or that its caughtup?).I was thinking, would I be even be able to do another restore of a morerecent copy of Production?Sincerely ...Depressed in Delaware
DECLARE @UserEmail as Varchar(50) DECLARE @UserZipPassword as Varchar(50) DECLARE @CoupMember as int DECLARE @member As Bit --Check if Email Exists…. If yes Then -- SELECT @RewardsMember = COUNT(*), @member =CASE M.EMAIL WHEN ((LEN(EMAIL)) <> 1) THEN 1 WHEN (LEN(EMAIL)) IS NULL THEN 0 END FROM MemberTable M Trying to get information from table for User based on data provided. What I am trying to do up there is check to see if User provided correct Email, if User provided right password and if User provided right zip code. Basically, I need to return bit values to my client application from this. Ran into some problem with my SELECT CASE statement up there. Need help please. Thanks in Advance.
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code: SqlCommand command = new SqlCommand(); SqlConnection conn = database.TypeLibraryDatabaseConnection; command.Connection = conn; // create the command to create the stored procedure command.CommandText = database.GetProcedure(node.Name); // create the stored proc in the database try { command.ExecuteNonQuery(); } catch { } command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
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.
Is there a way in SQL Server to setup Global Server Variables or Constants
We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Im using the SQL Server destination object to load some data (about 10gb) into a table. However I would like to use a variable to define what table I load into. All the tables have the same structure. I dont see a way to bind a configuration to it, or to use a variable as the loading table name.
Today is day two of using SSIS and having some issues adjusting. In DTS I would simply populate my global variables via an Active X step or pass them in at the job line.
I am having a hard time being able to do either or in SSIS. Really all I want to be able to do is pass in a server name to the Connection of an execute SQL task from a scheduled job line.
Same goes for any variable really ... how do I pass them in, and how do I assign them to specific areas of a task?
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
They are sent to the sproc as null. Since they could be null I need to exclude them from posting to a temp table
Example
DECLARE @QuantityID uniqueidentifier, @LengthID uniqueidentifier, @CostID uniqueidentifier SET @CostID = NEWID() SELECT @QuantityID as ID UNION ALL SELECT @LengthID UNION ALL SELECT @CostID
Two values are null. I want those excluded from this table
Here is the example of what I am trying to do:
DECLARE @QuantityID uniqueidentifier, @LengthID uniqueidentifier, @CostID uniqueidentifier DECLARE @Temp as Table (id uniqueidentifier NOT NULL Primary key) SET @CostID = NEWID() INSERT INTO @Temp SELECT @QuantityID as ID UNION ALL SELECT @LengthID UNION ALL SELECT @CostID
I understand that for SQL Server rtrim is not needed in where clause equates because SQL Server
automatically trims the spaces. Is rtrim necessary when comparing to a host variable since rtrim is a deterministic function? It it needed in the following example Select .... where TEXT = rtrim(:I--HV-001)
TEXT is a varchar column in the SQL Server database and it is getting compared to the host variable.
I need to check if the IDs in table A on the server X exist in table B (if not check table C, if not in C, check table D ) on the server Y. The problem is that the table name changes every year and I need to use the variable for the reference table name in LookUp component in the Dataflow Task.
I have three variables that are stored in an SQL Server configuration table. When I execute the package, they do not seem to be getting updated. At what point do the configurations get loaded?
Hi, What is the difference between CTE and table variables. I read that CTE is used so that the tables do not get persisted in the database. Do the table variables have the same efficiency? i.e. not persisted in the database. Can you throw some light over here if you understand the two concepts clearly please?
any shortcut strategies for table variables similar to shortcut strategies for temp tables? For example, the following SQL will create a temp table named #MyTable with all of the columns from student and then select the top 10 rows of student data into #MyTable:
select top 10 * into #MyTable from Student
Is there any type of similar or equivalent shortcut for table variables like:
select top 10 * into @MyTable from Student
if I want to select rows from Student into a @MyTable table variable then I need to create the @MyTable table variable with a column structure like a normal table which is more time-consuming than using the temp table approach.
I have a report that I'm trying to automate. Basically I want to create a cursor of containing authors, I want to run my report for that particular author, save the report, and move to the next author in the cursor. I want to continue this process until there's no more authors left in the table. Is this possible with reporting services 2005? If so how do I communicate the value from my cursor, to my report?
I have built the following query in SSMS, when I add it to an Execute SQL Task in SSIS. I get this error -
"[Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error:
"Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Query:
use design
drop table tmpNCPCNCDownstreamMaxUtilization3wks select node, max(utilization) as max_Utilization, DATE into tmpNCPCNCDownstreamMaxUtilization3wks from stage_ncpcncdownstream_temporal WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE()
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint DECLARE @Instance varchar(100) set @Instance = 'The Instance' IF (EXISTS (SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a)) SET @Found = 1 ELSE SET @Found = 0
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar= pub_name FROM publishers WHERE pub_id= '999' Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar=(Select pub_name FROM publishers WHERE pub_id= '999') Select @testvar
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.