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