Creating Database From Stored Proc With Variable Holding The Database Name

Aug 16, 2007

Here is my code


ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar

SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)

SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END


I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?

Thanks
Ganesh

View 2 Replies


ADVERTISEMENT

Who Is Holding A Database Backup-file?

Jun 23, 2007

I don't know where to post this kind of stuff so here goes...



I have maintenance plans which sometimes fail because the delete step reports that the old backup file is "in use." I have no idea how to determine what Windows thinks is holding the file. HOw do you determine who is holding a file hostage??



Thanks.

View 3 Replies View Related

Database Design Question - Holding Additional Data

Oct 1, 2007

I've done some basic database design in the past, but am a relative newbie to design. I have recently come across a situation I'm not sure how to handle. Here's the situation...

Assume we've got a contacts table which holds information about our contacts. All contacts have the same basic information - name, address, telephone, etc. Each contact is of a certain type - let's just say a, b, and c, for ease. This contact type is stored in the contacts table. Now, on contacts of type b, I also have to store some additional data. What it is doesn't really matter. I found a way to set this up, but I'm not sure that I'm going about it the right way, and would love some advice on the proper way to do this. Basically, what I did is create my contacts table:
Contact_id, contactName, ContactAddress, ContactPhone, ContactType.
Created a contacttype table
ContactType, ContactTypeDescription, ContactAddInfo

What I've done is left contactaddInfo as a nullable field. When it has a value, that value is the name of a table which holds the additional information that I need for the contact... So when I'm running a query, I can tell if I need to reference another table by checking the value of ContactAddInfo.

I can't imagine that this is the best solution, but it was the first thing that popped into my head, and it's a really small database that's using it. However, I'm now being faced with the same situation in a much more important, larger database, and I'd love to know the 'right' way.

Thanks for any guidance you can provide!
Erin

View 4 Replies View Related

Want The Database Name From Stored Proc

Feb 18, 1999

From within the stored procdure, I would like to find out the name of the database in which the stored procedure is located. Is this possible?

Thanks,

Judith

View 1 Replies View Related

Stored PROC Is In Which Database

Jun 23, 2008

Hi Gurus,

I have partiular stored procedure.Now i want to know as this PROC belongs to which DATABASE.On a server we may be having several databases from those how can we know as this PROC belongs to particular database.Is there ay systable for this..?

Thanks,
ServerTeam

View 2 Replies View Related

Exec Stored Proc In Different Database

Apr 17, 2001

I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?

View 1 Replies View Related

Stored Proc To Create Database?

Mar 23, 2006

I was wondering if it is possible to create a stored procedure to create databases. The only parameter would need to be the name of the database. Do I have to use a bunch of EXEC statements, or is there a better way?

I've created basic stored procs before, but never one for something like this.

Thanks.

View 3 Replies View Related

Stored Proc Error By Northwind Database

Jan 14, 2008

i try create this example:http://www.codeproject.com/KB/webforms/ReportViewer.aspxI have Northwind database added in the SQL server management and i select Northwind databse in drop box and I push Execute!ALTER PROCEDURE  ShowProductByCategory(@CategoryName nvarchar(15)
)ASSELECT  Categories.CategoryName, Products.ProductName,        
Products.UnitPrice, Products.UnitsInStockFROM    Categories INNER JOIN
Products ON         Categories.CategoryID = Products.CategoryIDWHERE  
CategoryName=@CategoryNameRETURNbut error is:Msg 208, Level 16, State 6, Procedure
ShowProductByCategory, Line 11Invalid object name
'ShowProductByCategory'.on web not so clear what is issue pls. help 

View 2 Replies View Related

FWIW: Database Space Used (stored Proc)

Aug 2, 2004

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO



CREATE PROCEDURE sp_dbspaceused

as

declare @idint-- The object id of @objname.
declare@pagesint-- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)


set nocount on

/*
** If @id is null, then we want summary data.
*/
/*Space used calculated in the following way
**@dbsize = Pages used
**@bytesperpage = d.low (where d = master.dbo.spt_values) is
**the # of bytes per page when d.type = 'E' and
**d.number = 1.
**Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
**+ sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GO

View 3 Replies View Related

Search Database For Any Terms Stored Proc Help

May 28, 2008

hey all,

basically, what I am trying to achieve to 2 types of search functions...

Search for All terms (easy and complete) and search for Any Terms...

the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.

this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.

What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.

Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...

or any other hints/tips/tricks would also be helpful.

Thanks Everyone!

View 6 Replies View Related

Variable Not Holding Value For Select Statement

Dec 7, 2005

this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID  ))) but for some reason it will not work with the next select statement...
 
can someone make the pain go away and help me here..??
 
erik..
 
GOSET ANSI_NULLS ON GO
ALTER  PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
 DECLARE @SQL NVARCHAR(4000)  DECLARE @UserID INT  DECLARE @UsersMaxID INT  DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
 WHERE ' + @GenericColumn +' = ''' + @GenericValue  + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29
SELECT * FROM #UsersTempTable
 
 ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE

View 1 Replies View Related

SELECT A Field From Another Database (on Same Server) From Stored Proc

Jul 12, 2007

Hi

Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?

thanks

View 10 Replies View Related

Creating A Stored Procedure In Another Database

Feb 27, 2008

From within a stored procedure in an existing database I'm creating a new database from the scratch.

In the query window if I write the statement:
CREATE DATABASE [tomasdb]

USE [tomasdb]
GO
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = '123'
END

everything works ok and the database is created and the stored procedure Test1 is created within it.
However, if I want to do the same from another stored procedure (in another database), i.e. I write

EXEC('
CREATE DATABASE [tomasdb]
USE [tomasdb]
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = ''123''
END
')

the problem I get is that I'm not allowed to put USE to change the execution context into [tomasdb] before the CREATE PROCEDURE statement.
My question is: how can I change the context in this situation / or / can I create the stored procedure in the [tomasdb] database somehow from outside (another context).

Thanks,
Tomas

View 4 Replies View Related

Creating A Stored Proc From A Particular Template?

Mar 16, 2006

Hello,

I'm trying to get to grips with SQL Server 2005.

One of the things I want to do is provide members of my team with a stored procedure template that they can use which has special error handling code in it , etc

I found my way to the template explorer and created a new template that I want to use to create stored procedures in certain circumstances (but not ALL circumstances)

But now I can't figure out how to specify WHICH template to use when creating a stored procedure.

Like, when I click on the Programmability/Stored Procedures node and then right click and select New Stored Procedure... it just uses the Basic Template.. but I'd like to be able to elect to use my alternative template to create the stored proc.

So, what are the correct steps to follow? do i just double click my new template in Template Explorer? And then have to go Query/Specify Values for Template Parameters ? Or what?

If this is the way to do it then it seems very clunky really....

Thanks

View 1 Replies View Related

Creating A User Stored Proc

Sep 12, 2006

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it??

Thanks

View 7 Replies View Related

STORED PROC WITH VARIABLE MULTIPLE OR &<&>

Aug 5, 2004

I need to create a SQL Server Stored Proc that will handle a variable number of Or conditions. This is currently being done with a MS Access Query as follows

Do Until rst.EOF

myw = myw = "(rst!Field1 <> 0) OR (rst!Field1 <> 1) "

Loop

mysql = "UPDATE Table SET Field2 = 1 WHERE " & myw

The above code is very simplified.

I Want to create a stored proc to do this but I cannot send it the SQL to the Stored Proc (or can I) so I need to use parameters instead. I want to do something like

Do until rst.EOF

Set cmd = MakeStoredProc("sp_Table_UpdateField2_ForField1")
Set prmField1 = cmd.CreateParameter("Field1", adInteger, adParamInput, , rst!Field2)
cmd.Parameters.Append Field1

cmd.Execute

Loop

Again the above is very simplified. So how can you get the the SQL for the Stored Proc for something like the following from a loop

WHERE = (Field1 <> 0) OR (Field1 <> 1) OR (Field1 <> 2) ...

Thanks in advance for your help

View 1 Replies View Related

Select As A Variable In A Stored Proc

Sep 25, 2006

Hey,
I create a Select Statement in stored proc and I have printed the variable and it has the correct Select statement. My problem is now that I have the string I want how do I run it.
Thanks

View 2 Replies View Related

Stored Proc Bug With Datetime Variable

Sep 13, 2006

two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff

View 5 Replies View Related

Lookup A Variable From Stored Proc

Oct 31, 2007



Hi all,

I haven't been able to get a variable to get its value from a query using other variables as paramters. Is this possible?

Here's my situation:

I have a table workflow
(
id int PK,
Quarter int UK1,
Responsible varchar UK1,
Stage varchar UK1
)
The workflowId is a composite key of the other three columns to keep the facttable rows narrow.

And a stored proc GetWorkflowId that looks if a certain combination of quarter, responsible and Stage exists. If so, it returns the id, if not, it inserts the row and returns the Id. So i can;t use a lookup or merge join, becuase the workflow row may not exist yet.

Now i need this workflowId as a variable in my package. (First a sql task uses it to delete old values, then a dataflow task would use it as a derived column to insert the new values.

Quarter is a variable in my package, and i need to lookup/ create a workflowid with the stored proc using Quarter, and then get the return value into a variable WorkflowId. Can i do that?

Hope i've been clear, if not let me know.

Thanks in advance,

Gert-Jan

View 4 Replies View Related

Stored Proc For Creating And Inserting Into A Table

Sep 18, 2006

I am having some trouble populating a table with values from other tables:

I am creating the stored proc as follows:

CREATE PROCEDURE make_temp_stat (@from datetime, @to datetime)

AS

DROP TABLE tempTable

Create tempTable

(

NumApplications (int),

NumStudents (int),

NumTeachers (int)

)

//Then I insert the values into the table as follows

INSERT INTO tempTable (NumApplications) SELECT Count(*) FROM [dbo].[CASE_APPLICATION] WHERE (OPEN_DT>= @from AND OPEN_DT <= @to)

INSERT INTO tempTable (NumStudents) SELECT Count(*) FROM [dbo].[CASE_STUDENTS] WHERE (APP_DT>= @from_dt AND APP_DT<= @to_dt)

INSERT INTO tempTable (NumTeachers) SELECT Count(*) FROM [dbo].[CASE_TEACHER] WHERE (JOIN_DT>=@from_dt AND JOIN_DT<= @to_dt)

GO

Nothing happens when I run this stored proc. Can sombody point out what exactly is wrong over here?

View 7 Replies View Related

Stored Procedure && Referencing A Database With A Variable

Aug 16, 2006

Hi all,
I wonder if there is anybody that can help with this one.
As you know, you can pass variables into a stored procedure and then use these variables for table names, columns etc.
Now, you also know that you can access another database on the same server just by typing the name of it into your query.
The hard part:
Instead of hardcoding the database name into the stored procedure i want to use a variable and then pass this to reference the database name, in the same way you would reference anything else with a variable. I need to do this as i have to search for various results across multiple databases.
We currently use SQL server 2000 standard, though are looking into SQL 2005 - especially is this problem is easy to resolve in the latter.
Look forward to your help
Regards
Darren
 
 

View 6 Replies View Related

Transact SQL :: Database Role For Creating Stored Procedure?

Oct 2, 2015

I want to create database role which can be used for creating stored procedures like db_SPCreate.

User should be able to create SP on Schema PVR; and should not be allowed to create sP in otherthan schema and he can use any schema tables to create procedure.

[URL]

View 4 Replies View Related

Using Same Table Variable In Child Stored Proc

Feb 5, 2008

Hi
I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs)
now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error

Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".

Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.


Thanks,
BPG

View 11 Replies View Related

Stored Proc Output Parameter To A Variable

Mar 16, 2006

I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.

Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;

Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4

When this task is run, I get the following error:


0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.

View 4 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View 3 Replies View Related

Stored Proc Using Variable As Fieldname In Select Statment

Apr 20, 2001

Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.

I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?

Thanks,

Oliver

CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int

AS

declare @option varchar(900)


if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'

select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'

select * from #SavingsData1

View 1 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

Stored Proc To Assign Variable From Subquery Not Working -- Ugh

Jul 20, 2005

Hi, I'm trying to run a stored proc:ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust=(SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)When I remove the SELECT @varCust= I get the correct return. With itin, it just appears to run but nothing comes up in the output window.PLEASE tell me where I'm going wrong. I'm using MSDE, although I don'tthink that should matter?Thanks, Kathy

View 2 Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Jun 12, 2006

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View 3 Replies View Related

T-SQL (SS2K8) :: Changes Not Holding In Stored Procedure

Apr 8, 2014

I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring up a Stored Procedure and make a change.... I execute the Stored Procedure... after it finishes.... I exit out without saving to a file.... I go back in and my change was not held.

I can do the exact same process with an old SQL Server 2005 database. Is there a permission I am missing to set to be able to do this on the 2008 database.

View 9 Replies View Related

Guidelines For Creating A Database Snapshot On A Mirror Database

Nov 24, 2006

Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)



Best Regards,

Hans

View 1 Replies View Related

Creating CSV Files Using BCP &&amp; Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@

Jan 24, 2008

Hi all,

I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:


--scBCPcLabResults.sql--

declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".

=========================================================================================
--scBCPcLabResults.sql--

declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".

===================================================================

I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.

Thanks in advance,
Scott Chang

View 3 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related







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