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

Create Temp Table To Store Data From Database(mssql)

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

How To Create Store Procedure

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

How To Create Store Procedure

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

Create Store Procedure

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

Create A Store Procedure

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

Create Store Procedure To Paging!!!

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

How To Create Store Procedure To Use With Dropdownlist

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

Create Store Procedure To Put Information Of Running Code Into New Table?

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

SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?

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

Mirroring :: Store Transaction Log File / When Create Mirror Database

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

Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter

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

Granting Permission To Object In X Database From A Store Procedure In Y Database

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

Connect Same Database In The CLR Store Procedure

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

Inserting Data From A Store Procedure

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

Store Procedure For Returning Data

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

How To Use The Data From A Store Procedure Within A SQL Statement?

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

Store Procedure Data Type Problem

Nov 7, 2006

Hi anyone,

View 3 Replies View Related

How To Insert BLOB Data Using Store Procedure

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

Putting Data From A Store Procedure Into A Cursor

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

How To Dynamically Assign Database Name In Query Or Store Procedure?

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

SQL 2012 :: Store Procedure - Inserting Same Data Into Two Tables

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

How Do You Use Data From A Select Statement As Inputs For A Store Procedure?

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

Error Msg 6522, Level 16, State 1 Receives When Call The Assembly From Store Procedure To Create A Text File And To Write Text

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

Recovery :: Creation Of Store Procedure In AlwaysOn Database Infrastructure

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

SQL Server 2012 :: DB2 Store Procedure Returning Two Data Sets

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

Fail To Execute Store Procedure In OLD DB Source In Data Flow

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

Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login

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

Store The Data From A Database Into A File.

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

Store Procedure To Load Data From Flat File To Staging Table Dynamically - Column Metadata

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

How To CREATE PROCEDURE In Another DATABASE?

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

How Do You Create A Database Within A Stored Procedure

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

Stored Procedure To Create New Database

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







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