Create Store Procedure That Take Data From 2 Database
Sep 7, 2007
hello all..,
i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
is procedure can take data from 2 database?
mystore procedure like:
ALTER PROCEDURE [dbo].[pay]
(
@no_bill AS INT,
@no_order AS int,
@totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @balanc AS money
SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill)
UPDATE [bill]
SET
[balance] = @balanc - @totalcost
WHERE
[no_bill] = @no_bill
COMMIT TRANSACTION
END
it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.
Transaction
count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 0, current count = 1.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[pay]."
plss.. help...
View 13 Replies
ADVERTISEMENT
Jun 26, 2007
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
View 2 Replies
View Related
Dec 6, 2005
Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???
View 8 Replies
View Related
Dec 6, 2005
Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???
View 1 Replies
View Related
Feb 14, 2006
I need your help again.
I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.
i have two input parameters (@fname, @lname).
Thanks.
View 8 Replies
View Related
Nov 22, 2006
Hello All,
I'm new to this forum and looking for helps tip on SQL Server.
How to create a store procedure? Here's the proper information that below:
1)In house data, have 2 tables called: Advo and
Advosum;included Excel sheet (ChampaignAddress.xls)
have raw data rows
(Latitude1,Latitude2,Longitude1,Store).
2)I have a query statement to retrieve data from
"In House Data" which in Where Clause using
(latitude between # and #)& (longitude). Get each rows data in
column from excel(ChampaignAddress.xls)to populate into Where
Clause that when it execute the query.
3)
SELECT CASE WHEN deliveryTypeCode >= 'A' AND deliveryTypeCode <= 'H' THEN CONVERT(char(20), 'R') ELSE CONVERT(char(20), 'B')
END AS RBDI, case(left(advo.crrt,1))when 'B' then convert(char(20),'BOXHOLDER')
when 'C' then case when (deliveryTypeCode>='A') and (deliveryTypeCode <='H') then convert(char(20),'RESIDENT')
else convert(char(20),'BUSINESS OWNER') end else 'RESIDENT' end as Title, case (left(advo.crrt,1))
when 'B' then convert(char(64),( rtrim(StreetName)+ ' ' + rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' + rtrim(alternateTopLine) + ' ' +
rtrim(AptNum)))else convert(char(64),(rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetName) + ' ' + rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' +
rtrim(alternateTopLine) + ' ' + rtrim(AptNum))) end as Address, cityName as City, State,advo.ZIP, advo.Plus4,
convert(numeric,ltrim(Walkseq)) as Walkseq, advo.Crrt,('******************ECRWSS**' + advo.Crrt)as Endorse,
cityRuralFlag as City_rural,convert(char(2),replace(dpb,' ','0'))as dpb,dpbc,null as primaryPreName,
null as PrimaryFirstName,null as PrimaryMiddleInitial,null as PrimaryLastName,null as PrimaryPostName,updateDate
FROM advosum squareRadius join Advo On advo.crrt=squareRadius.crrt and advo.zip=squareRadius.zip
WHERE latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'C%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'B%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND (advo.crrt LIKE 'R%' OR advo.crrt LIKE 'H%' OR advo.crrt LIKE 'G%')
ORDER BY advo.zip,advo.crrt,walkseq
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thank you to all for your helps
ryan,
RV
View 7 Replies
View Related
Aug 27, 2007
Hi All!
I have Store Procedure:
If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum') Drop Procedure Forum_Topic_SelectFromForumgoCREATE PROCEDURE Forum_Topic_SelectFromForum( @ForumID varchar(10))
AS BEGIN TRANSACTIONSELECT * from Forum_Topic where ForumID=@ForumID Order by Tmp DESCIF @@ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION
Now, I want to Add 2 Variables: @Offset int, @Count int . With @Offset: the point of data, @Count: sum of row will get.
when get data I want it get from @Offset to Added @Count.
Help me to rewrite this store procedure. Thanks
View 2 Replies
View Related
Apr 29, 2008
Dear all,
i want to create a storeprocedure that may acept value such as 0,1, 1a, 2, 2a etc.. from a dropDownList. But i always get an error. below is sample of my SQL1 DECLARE @val varchar(5)
2 DECLARE @sql varchar(4000)
3
4 SET @val = '1a'
5 SET @sql = 'select * FROM [dbo].[vwFormNAllAnswers]'
6
7 IF @val NOT LIKE '0'
8 SET @sql = @sql + 'WHERE QuestionCode LIKE '+ @val
9
10 EXEC(@sql)
View 3 Replies
View Related
Jan 20, 2012
I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 1 Replies
View Related
Nov 27, 2008
When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
View 10 Replies
View Related
Apr 11, 2007
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
View 6 Replies
View Related
May 16, 2000
I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion.
Thanks
Maroof Khan
View 1 Replies
View Related
Apr 3, 2006
Hi everybody:
I have a urgent problem, and can not google any useful answer. Please help me!
In my CLR store procedure, I have used a context connect, and I want to creat another connect to the same database. I have set the database permission to external, creat asymmetric key in my assembly, and creat login for that key. The dbo is the owner.
When I use this connect string as store procedure input, and I opened this new connect in the CLR sp, it throw an exception --"***.MDF is in used, failed to connect"!!
How can I do?
Thank you very much!
View 1 Replies
View Related
Oct 3, 2007
Hi,
This is more store procedure:ALTER PROCEDURE dbo.InsertSpecialOrders
(@OrderID int,
@DepotName nvarchar(50),@CustomerName nvarchar(50),
@OrderDate nvarchar(50),@TelephoneNumber nvarchar(50),
@ObtainedFrom nvarchar(50),@CustomerCanCollect nvarchar(50),
@DepositPaid nvarchar(50),@PriceQuoted nvarchar(50),
@OrderTakenBy nvarchar(50),@BalancePaid nvarchar(50),
@Status nvarchar(50),@TransferedBy nvarchar(50),@CarriagePrice nvarchar(50)
)
AS
Declare @LatestID Int
INSERT INTO Specials
(
OrderID,
DepotName,
CustomerName,
OrderDate,
TelephoneNumber,
ObtainedFrom,
CustomerCanCollect,
DepositPaid,
PriceQuoted,
OrderTakenBy,
BalancePaid,
Status,
TransferedBy,
CarriagePrice
)
VALUES
(
@OrderID,
@DepotName,
@CustomerName,
@OrderDate,
@TelephoneNumber,
@ObtainedFrom,
@CustomerCanCollect,
@DepositPaid,
@PriceQuoted,
@OrderTakenBy,
@BalancePaid,
@Status,
@TransferedBy,
@CarriagePrice
)
SELECT @LatestID = Scope_Identity()
INSERT INTO SpecialParts
(
OrderID,
PartNo,
Quantity
)
VALUES
(
@LatestID,
@DepotName,
@CustomerName
)
RETURN
Now I want to insert data and create the tables:
SqlDataSource ordersDataSource = new SqlDataSource();ordersDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SpecialsConnectionString1"].ToString();
ordersDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
So to insert data for the first field I need:
ordersDataSource.InsertParameters.Add("@CustomerName", CustomerName.Text) Is this right? Any help is appreciated.
View 2 Replies
View Related
Apr 19, 2006
i want to return certain number of columns frm table using store procedure .and i hav to load those returned data into text boxes which i created in asp.net page.is there a way
View 2 Replies
View Related
Dec 6, 2007
Is it possible to do something like this?
SELECT * from (
exec Store_Procedure
)
--StartDate is a column from "Store_Procedure"
where StartDate >= @Param
View 3 Replies
View Related
Apr 2, 2007
Hi,Can we insert BLOB data using store procedure using Oracle and ASP.Net.I have inserted BLOB data using insert command, now i want to insert that BLOB via store procedure....any links/tips will be helpful...
View 1 Replies
View Related
Jun 21, 2004
The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.
View 1 Replies
View Related
Sep 22, 2006
Hello,
I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?
something like
Declare @MyDatabaseName varchar(30)
set @MyDatabaseName = "MyDB"
SELECT * from MyDatabaseName.dbo.MyTable
Any suggestion? Please.
Thanks in advance
View 8 Replies
View Related
Nov 13, 2014
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
View 5 Replies
View Related
Dec 13, 2007
How do you use data from a select statement as inputs for a store procedure?
e.g.
Code Block
Select FirstName, LastName from Student where Grade = 'A'
And I want to use all the FirstName and LastName as inputs for this store procedure
Code Block
Exec StudentOfTheMonth @FirstName = FirstName, @LastName = LastName
Thanks
View 21 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Oct 2, 2015
I would ask if there some precaution on creating stored procedure on a database member of an always on group. I copied the stored procedure on all nodes but I'm not sure if this is enough to make it works in case of failover ?
View 5 Replies
View Related
Oct 13, 2014
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies
View Related
Jun 20, 2006
Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:
USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]
It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:
Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.
Please give me some helps. Thanks.
View 13 Replies
View Related
Mar 3, 2014
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
View 3 Replies
View Related
Mar 22, 2008
how to use class iDataReader's function getbytes(...............) to read binary data?
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Data.Common;using System.IO;
public partial class uploadFileToDatabase : System.Web.UI.Page{ string strUser; protected void Page_Load(object sender, EventArgs e) { strUser=Request.QueryString["download"]; if(strUser!=null) { string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; dbComm.CommandText="select * from userFiles2 where fileisn='"+strUser+"'"; IDataReader myReader=dbComm.ExecuteReader(); int strFileLen=0; if(myReader.Read()) { strFileLen=Convert.ToInt32(myReader.GetString(3)); FileStream afs; string fPath; fPath = Server.MapPath(Request.ApplicationPath) + "\upLoadFiles\" + myReader.GetString(1); afs = new FileStream(fPath, FileMode.OpenOrCreate, FileAccess.Write); Byte[] filedata = new Byte[strFileLen]; afs.Write(filedata, 0, strFileLen); BinaryWriter abw; int iBufferSize=1000; Byte[] outbyte=new Byte[iBufferSize-1]; long retval; long starIndex=0; abw=new BinaryWriter(afs); starIndex=0; int itemNum=4; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); //when i store large data into a file it display an error such as "Buffer offset '0' plus the bytes '82452' is greater than the length of the passed in buffer. abw.Write(outbyte); starIndex+=iBufferSize; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); } abw.Write(outbyte); abw.Flush(); abw.Close(); afs.Close(); myReader.Close(); dbComm.Dispose(); } } } protected void Button1_Click(object sender, EventArgs e) { string strFileName; string strFileType; int strFileLen; Random ran=new Random(); string strFileISN=DateTime.Now.ToString("yyyyMMddhhmmss")+ran.Next(0,999); if(FileUpload1.PostedFile.FileName!=null) { strFileName=FileUpload1.PostedFile.FileName; strFileType=strFileName.Substring(strFileName.LastIndexOf(".")+1); strFileName=strFileName.Substring(strFileName.LastIndexOf("\")+1); strFileLen=FileUpload1.PostedFile.ContentLength; string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; string dateTime=DateTime.Now.ToString(); Stream fs=FileUpload1.PostedFile.InputStream; Byte[] filedata=new Byte[strFileLen]; fs.Read(filedata,0,strFileLen); dbComm.CommandText="insert into userFiles2 (fileisn,filename,filetype,filesize,filecontent,uploadTime) values (@fileisn,@filename,@filetype,@filesize,@filecontent,@uploadTime)"; dbComm.Parameters.Add(new SqlParameter("@fileisn", (object)strFileISN)); dbComm.Parameters.Add(new SqlParameter("@filename",(object)strFileName)); dbComm.Parameters.Add(new SqlParameter("@filetype",(object)strFileType)); dbComm.Parameters.Add(new SqlParameter("@filesize",(object)strFileLen.ToString())); dbComm.Parameters.Add(new SqlParameter("@filecontent",(object)filedata)); dbComm.Parameters.Add(new SqlParameter("@uploadTime",(object)dateTime)); dbComm.ExecuteNonQuery(); fs.Close(); dbConn.Close(); dbComm.Dispose(); dbConn.Close(); } } }
View 3 Replies
View Related
Apr 9, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
View 1 Replies
View Related
Oct 2, 2007
Hi,
I need to write a script, which should run in a particular database (which name is not know, but I get it at runtime). It should add another database, which should be the same name with some suffix (i.e. "_Log"). And then it should create some tables and stored procedures there. The biggest problem is creating stored procedure, because CREATE PROCEDURE sentence doesn't allow to specify the database name, and it has to be the first statement in a batch (so USE doesn't help). I tried something like:
Code Block
DECLARE @logDBName nvarchar(100)
SET @logDBName = DB_NAME() + '_Log'
BEGIN TRY
EXEC ('CREATE DATABASE ' + @logDBName)
EXEC ('
CREATE TABLE [' + @logDBName + ']..Some_Log
(
id int IDENTITY(1, 1) NOT NULL,
timestamp datetime DEFAULT(GETDATE()) NOT NULL,
someText ntext NOT NULL
CONSTRAINT PK_Some_Log
PRIMARY KEY CLUSTERED (id)
)')
EXEC ('
CREATE PROCEDURE Add_To_Log
@someText ntext
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Some_Log(timestamp, someText) VALUES (GETDATE(), @someText)
END
')
END TRY
BEGIN CATCH
END CATCH
Of course this sample creates the stored procedure in the current database, and not in the newly created one. As I mentioned, if I add USE to the last EXEC, it doesn't work, because 'CREATE PROCEDURE must be first batch statement', and I can't write '[Some_Log]..Add_To_Log', because CREATE PROCEDURE syntax doesn't allow to specify the database name.
P.S.: I should also be able to alter the tables and procedures int he future from the CURRENT database, again by using
SET @logDBName = DB_NAME() + '_Log'
Thank you
View 5 Replies
View Related
Apr 20, 2004
I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'
select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)
create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )
select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)
end
View 3 Replies
View Related
Jul 20, 2005
Is there a stored procedure installed by sql server 2000 that I cancall and just pass in the name of a new database and have it createthe database for me? If not, how do I do it in sql? Thanks.
View 3 Replies
View Related