Server Thinks I Am Redeclaring Variables

Apr 22, 2008

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


-----------------END SNIPPET-----------------------------------

View 8 Replies


ADVERTISEMENT

SQL Server Thinks True Is A Column Name

May 12, 2007

Hello SQL Server Experts,

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 ?

Thanks for any assistance available.

Sunny

View 1 Replies View Related

HELP! Non-Replicated DB Thinks Its A Publisher

Jul 23, 2005

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

View 1 Replies View Related

SELECT CASE Problem... Me Thinks!!

Jun 11, 2008

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.

View 3 Replies View Related

Code Thinks Deleted Stored Procedure Still Exists?

Feb 13, 2013

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?

View 3 Replies View Related

SQL 2005 Thinks A SQL 2000 Backup Is Corrupt, But SQL 2000 Restores Just Fine

Jul 19, 2007

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.

Any ideas?

Thanks
Mike Mattix

View 8 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

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.

João





View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Global Server Variables

Apr 1, 2004

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.

Any ideas.

View 2 Replies View Related

SQL Server Destination Using Variables?

Jul 27, 2007



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.

Thanks.

View 4 Replies View Related

How To Use Variables For Server Connections

Dec 21, 2006

Hello,

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?

Thanks!

View 21 Replies View Related

SQL Server 2012 :: Compare Two XML Variables?

Mar 19, 2015

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.

compare xmls by their "semantic value" ?

View 8 Replies View Related

SQL Server 2012 :: Variables That Are Not Null Put In TVP?

Sep 17, 2015

I have three variables

DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier

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

How do I insert into @Temp only non null values?

View 5 Replies View Related

Does SQL Server Take Advantage Of 'bind Variables'

Jul 20, 2005

Using prepared statements like Oracle does? This way in a high transactionsystem you do not have to recompile queries every time?

View 3 Replies View Related

SQL Server Do Host Variables Need Rtrim

Aug 13, 2007

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.

View 5 Replies View Related

Look Up The Table In The Different Server And The Name Should Be Passed By Variables

Aug 16, 2007

Hello,

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.

Can anyone help me?

View 1 Replies View Related

Variables Not Getting Updated From SQL Server Configuration

Nov 13, 2007

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?

View 4 Replies View Related

Sql Server 2005 - CTE Vs Table Variables

Nov 16, 2006

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?

Thanks

View 5 Replies View Related

URGENT HELP!!! To Print Variables More Than 255 Chars (SQL Server 6.5)

Oct 23, 1999

Is there any way to print variables more than 255 from ISQL on DOS


eg:-

DECALRE @Var1 (255)
DECALRE @Var2 (255)

SELECT @Var1 = 'a long line of 255 chars' -- a long line of 255 chars
SELECT @Var2 = 'a long line of 255 chars' -- a long line of 255 chars

-- I wish to print

PRINT @Var1+@Var2 -- that is 510 chars

-- (i dont wish to use SELECT @Var1+@Var2)


any body???

thanks in advance

domini

View 2 Replies View Related

SQL Server 2008 :: Shortcut For Table Variables?

Jul 27, 2015

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.

View 7 Replies View Related

SQL Server 2012 :: Using Variables To Generate The Command?

Sep 17, 2015

I am trying to use variables to generate the command:

USE DATABASE
GO

Code below:

DECLARE @DBName_Schema varchar(500)
SET @DBName = 'Test'
EXEC ('USE ' + @DBName )
GO

It does not seem to be working.

View 5 Replies View Related

SQL Server Data Types For Local Variables

Jul 20, 2005

Is there ANY data type other than varchar that is valid for localvariables and allows more than 8000 characters?

View 3 Replies View Related

Passing Variables From SQL Server To Reporting Services

Apr 6, 2007

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?

View 4 Replies View Related

SQL Server 2012 :: Putting Multiple Values In The Variables?

Jan 28, 2015

how can i put multiple values in the variables.

for eg:

Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.

I am getting syntax error(,)

View 5 Replies View Related

SQL Server 2008 :: SSIS - Using Variables In Execute Task?

Mar 3, 2015

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()

[code]....

View 1 Replies View Related

SQL Server 2008 :: Compare Up To 9 String Variables For Similarity?

Mar 6, 2015

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?

View 2 Replies View Related

SQL Server 2012 :: String Variables Comparison Function

Aug 10, 2015

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.

View 9 Replies View Related

SQL Server 2012 :: How To Do OPENROWSET Query Within Cursor Using Variables

Oct 22, 2015

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

PRINT @Found

View 2 Replies View Related

SQL Server 2012 :: Finding Procedures That Use Declared Table Variables?

Oct 22, 2014

know a way to find all stored procedures that use declared or temp tables, i.e

Declare @temptable TABLE as....

Create table #temptable

View 8 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

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

View 4 Replies View Related

SQL Server Admin 2014 :: SSIS 2012 Environment Variables Are Not On Sort Order

Feb 10, 2014

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.

View 1 Replies View Related

@@ Variables

Jul 13, 2007

What is @@variables means in sql server?

View 3 Replies View Related

Using LIKE With Variables

Jan 21, 2004

Is there a way to use the LIKE keyword with variables like below?

DECLARE @Name CHAR(10)
SET @Name = 'MyName'


SELECT * FROM table
WHERE my_name LIKE @Name

This won't work, but you get the idea of what I want. Any thoughts?
Thanks,

View 1 Replies View Related







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