I have a local server with a linked server configured which contains the live data. The local server will run the stored procedure on a scheduled job to harvest the data from the linked server.
BEGIN TRAN
GO
INSERT INTO local_server...local_table
SELECT * FROM linked_server...linked_table AS lnk
WHERE NOT EXISTS
(SELECT * FROM local_server...local_table AS loc
WHERE loc.key = lnk.key)
GO
COMMIT TRAN
GO
I have read there are some pitfalls to moving data across linked servers.
Will I have any problems with this type of transfer and if so, are there any alternatives?
I have a script that inserts rows from a linked server. It basically looks like this:
use mydb; go insert into my.table (col1, col2, ...) select col1, col2, ... from LinkedServer.db.my.table;
The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).
Running my query raises an error:
Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060. The statement has been terminated.
Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.
Three pairs of eyes have confirmed that the table definitions are identical on both source and target.
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I am manually replicating parts of a SQL Server CE database (running windows mobile 5.0) to a centralized SQL Server 2000 database.
My program is throwing an exception whenever I try to insert an image data type into the 2000 server from the PDA. I am using parameterized queries.
Error is as follows: [error] System.Data.SqlClient.SqlConnection.OnError() at System.Data.SqlClient.SqlInternalConnection.OnError() at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run() at System.Data.SqlClient.ExecuteReader() at System.Data.SqlClient.ExecuteNonQuery() at PDASync.Database.ExecuteIDRemote() [/error]
The code for my ExecuteIDRemote method works fine for other queries. It also works if I remove the image column from the offending query.
I am trying to transition from Access to MS SQL. I downloaded and installed MS SQL 2005 Express and the Server Management Studio Express. Keep in mind that I am a total newbie to this database since I've always used Access. My question is, how do I insert data manually into my tables? I figured out how to set up a table and query a table, but is there somewhere I can just plug in data? In Access, I just open the table and type it in. Also, how do you set an auto increment numeric field in a table?- Jason
Hi All, i am new to programming, in my application i want to insert a record in sql server database using Ado.net for that i used SqlConnection cn=new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString); SqlCommand cmd; protected void btnInsert_Click(object sender, EventArgs e) { try { cmd = new SqlCommand("Insert into DeptInfo(deptid,deptname)values(" + TextBox1.Text + ",'" + TextBox2.Text + "')", cn); SqlDataAdapter da = new SqlDataAdapter(cmd); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); TextBox1.Text = ""; TextBox2.Text = ""; } catch(Exception ex) { }} But my requirement is when ever the Insert command is successfull it has to display some alert message(using java script) saying "RECORD INSERTED SUCCESSFULLY" if Record insertion fails it should display some alert message "INSERTING NEW RECORD FAILED" . Any help will be greatly appreciated Thanks,Vision.
I am currently struggling with a problem bulk inserting data into sql server.
The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).
What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.
1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.
2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).
3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).
Does anyone have any idea's on a cpu friendly approach to this problem ??
select @counter=300000 while @counter > 0 begin insert into Revenue (Instr_type, Tel_no, Phone_Id, Rpt_date, Pay_mode) values("PP0073", @counter, "080464", "19990901", 1) select @counter=@counter-1 end
HI there, I run the above statement in Query Analyzer and the expected result should be 300,000 records inserted into Revenue table. But unfortunately the actual records inserted were less than 300,000. I also realise that it insert different amount of record each time I run it. Can anyone please tell me why?
Could anyboby please tell me any way which can quickly inserting large data to SQL Server except using cursor?
Every morning, I use DTS to transfer around 100,000 data from foxpro to SQL Server 7.0 temporary table. Then I use cursor to check if the data already exist in the SQL Server table, then I do update; if the data does not exist in the SQL Server table, then I do insert into. In SQL Server database, each table already has over million record, also I have 12 table with the same situation.
Now I find cursor very slow to do above, can anybody tell me any way which can quickly to do update and insert?
Can anyone guide me how I can insert the rows in table from the table which is located on another server insert into table1 select * from serverB.database1.dbo.table1
Hi I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'abc'. ------ Error Message Ends
Consider login name is abc. Now this login abc has sysadmin rights on sql server 2005. The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options; 1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 . 2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help. Regards, Salman Shehbaz.
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
when inserting or updating fields in sql server and the field is blank sql still adds several spaces into the field. Then if I try to check whether a field is empty or not for display I get incorrect results because fields that should be empty have 2 or 3 spaces in them instead and therefor aren't selected as being empty. Is there any way around this? to force the fields to be saved as blanks or null?
Hello all, I'm using sqlserver 2005 express edition. I'm working in an application which has the functionality of inserting datas from the excel file to sql server 2005 database. Can anyone please guide me for performing this task. Thanks in advance. Ravirajdanasekaran
Hi, Just look at my Code:protected void Button1_Click(object sender, EventArgs e) {SqlConnection Conn = new SqlConnection("MyConnectionString"); SqlCommand Cmd = new SqlCommand();string linktext,headline, quote, para1, para2, para3; linktext = this.TextBox1.Text;headline = this.TextBox1.Text; quote = this.TextBox2.Text;para1 = this.TextBox3.Text; para2 = this.TextBox4.Text;para3 = this.TextBox5.Text;Cmd.CommandText = "insert into desieyenews(linktext,newsheadline,quote,para1,para2,para3) values ("+linktext+","+headline+","+quote+","+para1+","+para2+","+para3+")"; Cmd.Connection = Conn; Conn.Open(); Cmd.ExecuteNonQuery(); Conn.Close();
}
Details: In the above code I am inserting data from 5 textbox to my databse. It works fine but when I am trying to insert text like Tom's..., problem occurs. Whenever I am trying to insert some text with a single quote, the problem arises. Although the datatype of my database is text in MS SQL Server 2005. Please give me some solution so that I can get rid of this problem.... Thanks in advance.
Hi Guys,I need some help here. I've created a asp.net reception system for internal use. The problem I'm having is this, when I update a record all the dates are saved as 01/01/1900. I've had a look at a couple of threads on various forums and sites but none of them seem to have a solution for my problem... I have an AJAX calenderExtenders on the date text boxes and i have played around with all different date formats, currently on yyyy/MM/dd. I've also tried DateTime.Parse(txtStartDate.text) but doesn't work. I pass all the parameters to a stored procedure on the sql server in the correct order. I've included my code below, I have only included date parameters. SqlConnection conn = null; try { string connString = ConfigurationManager.ConnectionStrings["RCM"].ConnectionString; //Initialise the sqlConnection object conn = new SqlConnection(); conn.ConnectionString = connString; //Getting connection string to the db //Create the command SqlCommand command = new SqlCommand("UpdateClient", conn); command.CommandType = CommandType.StoredProcedure; //Opening connection to the db conn.Open(); //Setting input parameters command.Parameters.AddWithValue("@DOB", txtDOB.Text); command.Parameters.AddWithValue("@StartDate",txtStartDate.Text); //Execute T-SQL command.ExecuteNonQuery(); //Close connection command.Connection.Close(); conn.Close(); } catch (Exception ex) { lblError.Text = "An error has occured: " + ex.Message; }
I need to store a few thousand images in a sql server database. I've managed to write the asp code to retrieve images that are already in the pubs database, but haven't been able to find a satisfactory way of inserting new pictures in the first place.
At the moment we're using Notes and its great 'cos you just cut and paste them in. But now we need to take them all out and put them into sql server it's proving to be a nightmare. Any clues anyone??
Hello all.I am attempting to insert a row into a table with a datetime column:When the insert statement contains a value for the millisecond portionof the data time column: ie. {ts '2003-11-05 12:02:43:2960'}I get 'Syntax error converting datetime from string'When I insert a value like: {ts '2003-11-05 12:02:43'}with no millisecond value it succeeds.Any help would be appreciated.Thanks
Hi everyone!Hope that someone can help me solving this problem.I have a form where the user can register by putting his private data. Each time that he submits his data, if he is using Internet Explorer, it will insert blank data into sql server database. But if user is using Firefox, everything is working well, and all data is inserted.What seems to be the problem?Why in IE, data is inserted as blank?!Thanks for your possible help and attention to this issue.Hope that someone can help me.Best regards,Mesk
I've been doing this in Access, but cannot find the answer to how to do it with SQL Server. From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList. Clicking the Submit button writes the contents of the form to a database table. This works for Access: insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= datesArray.Item(i) Next i insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= "#" & datesArray.Item(i) & "#, " Next i It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." I have also tried the line: For i = 0 to datesArray.Count - 1 insSql &= satesArray.Item(i) Next i and get: "Incorrect syntax near the keyword 'VALUES'." I'm not sure where to find the information to correct my error. Any help would be appreciated. Tinker
hi I want to read data from XML file and insert that data from XML file into the Database Table From ASP.NET page.plz give me the code to do this using DataAdapter.Update(ds)
In my VS 2005 windows control I am inserting a record into a table using a proc. One of the fields "Accuracy" should look like this 66.4, but when I isnert it from the proc itlooks like 66.0. If I bypass my proc and use an inser statement from SQL quey analyzer it look like it should 66.4. What am I doing wrong in my proc....
CREATE PROCEDURE [dbo].[insMyLameProc] @PlayerName nvarchar(255),@Score int,@Rounds int,@Accuracy decimal,@CorrectPicks int,@IncorrectPicks int AS --Insert the new game score--===============================================================================================insert into wmTurnTileScores(PlayerName, Score, Rounds, Accuracy, CorrectPicks, IncorrectPicks) values (@PlayerName, @Score, @Rounds, @Accuracy, @CorrectPicks, @IncorrectPicks)--===============================================================================================GO
i use this statement to insert into sql tablecmdInsert = New SqlCommand("insert into empbill ([deptcode],[personNo,[entrydate]] ) values(" & Val(eno.Text) & "," & Val(TextBox5.Text) & ", " & dd.Text & "')", db) cmdInsert.ExecuteNonQuery() dd.Text value is 22/5/2008 it come from datetime calender the entrydate field datatype is datetime i found data always 1/1/1900 00:00:00 how i enter my date
This is my code: Dim myConn As SqlConnection Dim mycmd As SqlCommand myConn = New SqlConnection("Initial Catalog=science;" & _ "Data Source=localhost;Integrated Security=SSPI;") mycmd = New SqlCommand("INSERT into STEP1(firstname) VALUES('Amin')", myConn) myConn.Open() mycmd.ExecuteNonQuery() myConn.Close()
This is the error message I get: The name 'firstname' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Edited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs) Dim myCommand As SqlCommand Dim insertCmd As String ' Build a SQL INSERT statement string for all the input-form ' field values. insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);" ' Initialize the SqlCommand with the new SQL string. myCommand = New SqlCommand(insertCmd, myConnection) ' Create new parameters for the SqlCommand object and ' initialize them to the input-form field values. myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50)) myCommand.Parameters("@SectionName").Value = Section_name.Value
If New_window.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 1 End If
If Latest_news.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 1 End If
If Partners.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 1 End If
If Support.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 1 End If
myCommand.Connection.Open() ' Test whether the new row can be added and display the ' appropriate message box to the user. Try myCommand.ExecuteNonQuery() Message.InnerHtml = "Record Added<br>" & insertCmd Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
