How To Do Insert In Another Database When The Database Name Is A Variable
Mar 19, 2008
Hi,
We have different databases on the same server: Whrs001, Whrs002, etc. All databases have the same structure.
Suppose i have a table "location"
create table location
( location_id int not null primary key,
location_name varchar(50) not null )
I need to inserts data in "location" tables of different databases. But these inserts needs to be across databases. For example from the "Whrs003" database I need to insert data (location_id=123, location_name='markham') in "location" table of "Whrs001" database.
So my thinking is to defined a stored proc which has database name as an argument:
create procedure location_insert
@a_database_name varchar(100),
@a_location_id int,
@a_location_name varchar(50)
as
...
So for the above example the call will be :
exec location_insert 'Whrs001', 123, 'markham'
What would be the actual insert statement in this case? And what things i neeed to take care fo to make sure it always execute successfully?
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?
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
I am using two database server. Both are sql server.Â
My task is :I want to insert data from server 1 table to server 2 table and update same when modified the existing data from server 1. is it possible to do the integration in single package.
I know to do insert and update seperately by ssis but need to do same with single task.Â
I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.
I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.'Create(connection)Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)'open connectionconn.Open()However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.Sorry, I'm really new with VWD.
I have two sql servers one is Nt server another one is Nt workstation,i am able to admin from both sides.but i don't know how to insert/update one database table to another database table, pls. give me some ideas for this.
Insert into excelb.B.dbo.Emp(Employee_Name,Emp_addr) Values (@Employee_Name,@Emp_Addr) select * from excelb.A.dbo.Emp
excelb - server name
now my problem is a server to another server insert the data that not acces the data and i am using different password the servers- but same pasword are insert the data
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
Hi, I have a sql script that references a database explicitly, but the name of that database changes. Rather then have to find anywhere I reference that database and change it, i would like to just set it in one spot. For example.SELECT * FROM [V1_Database].[a_table]I then decide to upgrade from V1 to V2.Instead of having to find the above code in all of my stored procs and manually change it, I would like to do the following.SET dbName = 'V2_Database' (Database wide variable)SELECT * FROM [dbName].[a_table]But I don't want to have to make all of the places that reference a specific database into string executions. ( strSQL = 'SELECT * FROM [' + dbName + ']' )I hope i havent made this too confusing, but any help is appreciated. Thanks,Brian
I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement
For Example: Instead of using the following with each database hardcoded in the SP:
select @dataused = sum(b.reserved) from DBSglep..sysindexes b where b.indid in (0, 1, 255) and segment != 2
I would like to loop for every database listed in sysdatabases and do this:
select @dbname = @dataname+'..sysindexes'
select @dataused = sum(b.reserved from @dbname b where b.indid in (0, 1, 255) and segment != 2
I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).
Any assistance in resolving this problem would be greatly appreciated! :-)
I have a trigger which retrieves database names from a table. I need to use this retrieved database name in another sql insert statement as a variable e.g. set @mydbname = Select .... from.. (to get the database name) then.. insert into @mydbname.dbo.emplTable
At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds
I need the database name as a variable because i have to write to the correct database (there are 15) Any help appreciated
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I am trying to create a database View using Visual Studio 2005. I have joined the tables and it creates the SELECT statement and WHERE statement which says: WHERE (dbo.tblStoresStudios001.C_ID = @C_ID) When I try to save it message comes up telling me to declare C_ID. I have tried int C_ID before the SELECT as if it were a stored procedure but that is not working. What dumb mistake am I making? I have not worked with views before.
I need to call another database in the same server dynamically; creating a varible off it or somehow. Example:
-- In database DBaseA declare @dbname as varchar (30) set @dbname='[DBaseB].[dbo]' select * from @dbname.[table2]
I know this block of code doesnt work. And i need a way of making the database DBaseB dynamic. DBaseA and DBaseB are in the same server. I am using Sql Server 2000. How do i solve this? Solutions ?
I have a very simple Join Query like Select A, B , C From Database1.dbo.Table1 Inner Join Database2.dbo.Table2
Now problem is that Database name for the project freuqently changes though Table1 and Table2 names remain constant. How can I store Datbase name at a central place in a table or as a constant so that I can use them in above query. It will allow me to change them at one place without making changes in all the sps where they are used
what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database??
here is the script itself
thanks
km
Public Function AddList(list As String) As String Dim total As Double total = 0
If Len(list) > 0 Then Dim s As Object s = Split(list, ",")
Dim i As Integer For i = LBound(s) To UBound(s) s(i) = Replace(s(i), ":", ".") total = total + CDbl(s(i)) Next End If
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
i'm trying to read an image file from a database(ms-sql, .mdf) with type image. Anyone have any ideas on how to do this? I have a table adapter created but could not assign it to my byte[] variable. Thanks in advance.
hi im trying to code a login page using asp.net, vb.net. i have 2 web-controlled textboxes whose values i want to compare to userID and password stored on a SQL server database called users. When button clicked, call function checklogin.
heres the code:
Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then FormsAuthentication.RedirectFromLoginPage(userID.Text, False) Else error_log.Text = "Invalid Credentials: Please try again" End If End If
End Sub
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String Dim dbConnection As New SqlConnection Dim dbCommand As New SqlCommand Dim dataAdapter As New SqlDataAdapter Dim ds As New DataSet
With dbCommand .Connection = dbConnection .CommandText = "SELECT COUNT (*)Â?@AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))" End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)
Return ds
End Function
Heres the error i get:
Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near line {''1
Source error:
Line 75:. CommandText = "declare@userID As varChar; declare @password As varChar; End With .."Line 76:.. line 77:of SELECT COUNT(*) AS pass FROM tbl_users WHERE((tbl_users.userId=@userId)AND(tbl_users.password=@password)) Line 78: DataAdapter.SelectCommand=dbCommand Line 79: DataAdapter.Fill(ds)
ive just started using .net, im a lil lost...thanks for any help. cheers
hi. i'm building a news section for some friends of mine. i list all the news items on the main page in a gridview. i've made a custom edit linkbutton that sends the user to an edit page, passing the news id as a quarystring variable. on the edit page i first check if the querystring variable contains an id at all. if not, i redirect the user to the main page. if an id is passed with the querystring, i fetch the matching news item from the database and place it in a formview control for editing.so far, so good. but what if someone types a random id in the querystring? then the formview won't show up and i'd look like a fool. :) therefore, i need some kind of check to see if the id exists in the database. if not redirect the user back to the main page... so i started thinking: i could check the databsae in a page_load procedure. if all is well, then display the news item. since the formview is automatically filled with the correct data, does that mean that i call the database two times? i mean, one for checking if the news item exists, and one for filling the formview. logically, this would be a waste of resources.help is appreciated.
I want to create a SP that creates a new database, so I script-ed out the db and paste the script into new SP gui (SQL 2000). I want to pass it a variable for the data/log file location that is not the default location. The original script looks like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = N'F:SQL SERVER FILESDatabasesdbName.mdf' , SIZE = 3118, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = N'F:SQL SERVER FILESDatabasesdbName_Log.LDF' , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS. I replaced the path of the FILENAME variable like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = @DBPath, SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = @LogPath , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS, declaring the variables as char(500). The error I get is "Incorrect sybtax near '@DBPath'. Any ideas for workaround? Thanks, EJM
I have package there i have multiple tasks,I have used one User definied varible into my package level,So here my condition is i want to change my variable name from the package level,Here that variable used in different places in my package level,it has used in some places as well in my package level.
I need to change my Variable name after development of my package so how it will changed.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Hi, Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error : Must declare the scalar variable "@BookMarkArrayA". when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error This is the code : Public Sub NewCustomer() Dim temp As String = " " Dim ID As Integer = 1 'Restore the array from the view state BookMarkArrayA = Me.ViewState("BookMarkArrayA")
temp = BookMarkArrayA(6) Dim Customer As SqlDataSource = New SqlDataSource()
I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')"; SqlCommand exesql = new SqlCommand(sql, cn); exesql.CommandType = CommandType.Text; SqlDataReader rd1 = default(SqlDataReader); rd1 = exesql.ExecuteReader();
I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
