I want to post all the data in my company branch office in Lahore to the Head office in Peshawar. This will be done at the end of the day and the two SQL SERVERs synchronize data between both points over internet. what procedure should i use? and how the two computers will know the ip addresses of each other while connecting?
View 3 RepliesHi,
I am new to SSIS. I need to read the files in a remote server folder and ftp them to the other remote server folder.
I know , I have to set up FTP task and FTP task must use a variable to provide the path information. But I am not sure how I can do this. Anyone suggest me with the script task to populate the variable and steps involved in this.
Thanks,
Gish
I now have two instances one with my source queue, and the target on a seperate instance. I have endpoints setup and have granted connection to the endpoint to the sql service account from the source system and vice versa. I have routes in the source system pointing to the endpoint on the target system. I have a route on the target system pointing to the endpoint on the source syste. I have routes in msdb that point to local with the correct service name.
Now, when I try to send a message, it fails and I get an error back in the source queue. I setup tracing on my target instance and added all the broker events. I get an error saying that "userid 0 does not have send permissions for the target service". User ID 0 is, of course, public. When I trace for security events I can see a DB obejct Access event that shows the loginname is sa and the dbusername is public. Now I have to say that this seems very strange for the server login to be mapped to sa, but use a database principal of public. When I grant send on <my servicename> to public it all works.
So I have two questions,
1) I don't have to grant send to public to make the cross instance communication work do I?
2) What did I miss in config that is causing public to be getting invoked as the db user in my target database.
Thanks!
Hello,
I mistakenly deleted my source table data.
Now i want to again migrate the data from MS Aceess table to the SQL server table.
Please tell me
As far as i remember I used some export or import command.
Thank You
please dont send me link...i have to do it now so no time to read
I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
[code]....
I am trying to truncate data from one server to the other (remote server)
it works from query analyzer like this:
exec RemoteServerName.DatabaseName.DBO.sp_executesql "truncate table DBO.tablename"
it does NOT work from query analyzer like this:
Declare @cmd varchar(2000)
set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'
exec xp_cmdshell @cmd
Produced this error:
'RemoteServerName.DatabaseName.DBO.sp_executesql' is not recognized as an internal or external command, operable program or batch file.
Hi All:
I'm looking for some advice. We have multiple remote SQL Server 2005 systems at various clients that all contain the same WorkTrack database. We need to send this data on a daily basis from all of these remote servers to our main server (ChemWeb/Spindle) in our corporate office. The thing is that we need to do this over standard HTTP - no VPN connection, etc.
So I'm thinking of using SQL 2005 Web services to do such. Here's my plan (the name of the Database on the client side and server is WorkTrack, and SDP is the acronym I am using for a Spindle Data Packet which contains the data to be updated from the client)
1. Every day update a WorkTrack_SDP database on the client server that will contain all of the data from WorkTrack (name of Spindle Client Database) that has changed since the last SDP transfer. This WorkTrack_SDP database will only contain the tables from the WorkTrack database that are necessary for transfer to the ChemWeb/Spindle server
2. The WorkTrack_SDP database will have the same schema as the WorkTrack database with one additional Customer_No column in each table.
3. All of the tables in WorkTrack_SDP database will be truncated before insertion of updated data.
4. An acknowledgement of the previous SDP transfer and ChemWeb/Spindle Server update will be required before this process will proceed.
5. There will be an additional table in the Client WorkTrack database to track SDP transfers.
a. Date/Time range of updated data of each transfer
b. When the transfer was sent
c. When the transfer was completed.
6. Create SPROC’s on the ChemWeb/Spindle server to serve as SQL Endpoints (Web Services) to update/insert data from client systems to WorkTrack_SDP database on the ChemWeb/Spindle Server.
7. Create SPROC on ChemWeb/Spindle server that will transfer data from WorkTrack_SDP into WorkTrack
Any thoughts & comments are greatly appreciated.
TIA,
Ken Blum
Client side:
1. my DSN is CMDB.dsn on a mapped network drive(only way for now that I get a connection)
2. My client does not fail to connect, but I am having a hard time
linking my database tables to each other. Through Access 03.
3. My client is remote
4. I can ping the server,
5. but I cannot telnet
6. My application is Access 2003,
7. I am on a different domain
9. I have table names that match,
10. I do not force encryption.
Server side:
1. SQL SERVER 2005
2. Standard Edition
3. TCPIP
4. The server starts fine
5. The SQL browser is enabled YES
6. Domain Account
7. Not applicable
8. NO
Platform:
1. Client runs Windows XP, Server runs Windows Server 2003
My question then is this: Does anyone know of code that can be used to import data from a CSV file to a server on a different domain?
This is tricky cause the connection is not really allowing this kind of access. Which is my next question: How do you get these two applications to connect?
I don't want to do it with ADP, and linking the tables I always seem to have to place the DSN on a mapped network drive to get results. Then after that I go
to link tables from the SQL Server and the tables I created there are no where to be found. Why is this happening? It gives me a list of System tables but none of my
I want to connect to company's SQL Server from home and to connect to home's SQL server from company remotely.
The new released SQL Server Web Data Administrator by Microsoft seems solving my issue.
I use it to connect to different SQL servers in different domains company wide successfully, but I failed to connect to home from company( I have static IP on the home server).
Also I want to know how to configure the sever name if I want to connect to company's SQL Server from home. The company SQL server is inside the firewall with a internal IP. How it works?
Any ideas?
Thanks!
I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.
However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?
I am an authenticated user of a remote data base
I can log in and add tables and even I can back up the database .
Good.
But I want to get a copy of the database to my local computer
Is there any way to do this?
_sujithsql_
Dear friends, We have a server put at US side and we are trying to load the data into another related database in India site, now the thing is that we want to update India data at an interval of 5 mins?
Is there any way to do it without effecting the performance of the US server?
thanks,
I am making a WPF app, and I want to use SQL Server Compact 4.0 for database management, but it doesn't support Remote data access, on the other hand SQL Server Compact 3.5 does support it but I can't use it in WPF ( I've installed it but can't add it in my project, it's not there in the templates). So is there any alternatives, for a light DBMS, that are not installed separately?
View 3 Replies View Relatedbut i am able to login to the same remote sql server through SQL server managment studio express using the details i am using in the connection string in the "test.aspx" page. ERROR:An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)When this page is run, i want all the user's UserName and Password to be dispalyed.However, i am getting the above mentioned error.what could be the problem? This is the code i am using in my test.aspx page to connect to "Login" table in my database. 1 Imports System.Data2 Imports System.Data.SqlClient3
4 Private Sub GetAllTheComments()5
6 Dim MyConnection As SqlConnection7
8 Dim MyCommand As SqlDataAdapter9
10 MyConnection = New SqlConnection("server=XXX.X.XXX.X;uid=X;pwd=X;database=X")11
12 MyCommand = New SqlDataAdapter("select UserName, Password from Login", MyConnection)13
14 ds = New DataSet15
16 MyCommand.Fill(ds)17
18 DataList1.DataSource = ds19
20 DataList1.DataBind()21
22 End Sub
Hello, everyone:
I have a local transaction,
BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
Thanks
ZYT
how to copy a content of a table from one remote server to another,. server A does not see server B (B doesn't see A) - I cannot even ping to one from another.I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.So what I did so far is to periodically:
1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like
DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above
6. INSERT INTO
SELECT
ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );
so it works. now there is a requirement to schedule this update to run periodically and I need to implement it..
I have two win2003 servers. One is IIS and the another is SQL server. Ican use MyConnection=newSqlConnection("server=SQLServerName;database=myDBName;UID=sa;PWD= mypwd")to access my data. But I can not use MyConnection=newSqlConnection("server=SQLServerName;database=myDBName;IntegratedSecurity=SSPI") to access it. If I put both IIS and SQL Server in onemachine, I can do anything. How can I do something to allow trustedauthority work ?Thanks
View 1 Replies View RelatedI'm using SQL Server 2005 Express, Windows XP SP2. I connect using Windows authentication.
The data source that I'm trying to hit is on a remote server. It's an OpenLink ODBC Data source, and the server is in another location. SQL Server 2005 Express and the ODBC connection are both installed/configured locally on my machine.
I can connect to the remote server. The DSN exists as a System DSN.
The DSN has its own proprietary configuration utility, which I can configure so that the server name is XPLILLEYGSQLEXPRESS. The configuration utility doesn't care; I connect successfully one way or the other.
When I open Management Studio Express, I can see my database (CMS) listed under the "Databases" folder, but there's no tables in there, other than the system tables.
So my question is, what am I missing?
Thanks
Geoff Lilley
I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig dbÂ
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
Hi Dear All!
I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.
I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.
Please help me if I can look similar packages performing?
Thank you!!
Hello friends....
my question is as follows:
How do I send the data from the server side to the client side specifically in what format and what kind of connection do I use?
waiting for answers.....
I am thinking about moving some backups from the local machine to a remote server. Right now, I am using Ola Hallengren script for backups. The script performs checksum on the backup while it's being written on the disk. I am going to move the files using Robocopy utility. How do I check the data integrity once the backups are transferred to the remote server?
View 5 Replies View RelatedWhen copying data to a remote SQL2K5 destination from a SQL2K5 source database, both using mixed sql server security mode, my job generates the following error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
This occurs after the destination database tables have been truncated and replacement data from the source would begin to copy.
The same process can be successfully completed from the Management studio with a simple data export process. However, when I run the saved package again from the BI interface, I get this error.
My search engine searches have yielded numerous hits of others having the same problem with one microsoft rep indicating it was a bug and would be resolved in sp1. I am working with sp1. Oddly, there is only mention of this in the forums. No KB article from MS addresses the problem and I do not see it addressed elsewhere at sqlservercentral.
It appears that others have switched to Integrated Security and resolved the problem. However, I do not have that option with a remotely hosted database.
Does anyone have any information concerning this problem?
I want to SQL server data backup from remote server. I can able to access remote server through query analyzer only. How can I Possible do that through my local query analyzer and remote my local system?
Thanks for advance
Hello
I have VS 05 professional connected to SQL server express. I am trying to make a simple console connection to northwind database to show the results of a simple procedure.
The code is as follows:
using System;
using System.Data;
using System.Data.SqlClient;namespace Chapter6
{
class CallSp1
{static void Main()
{
// create connection
SqlConnection conn = new SqlConnection(@"server = .centrinosqlexpress;
integrated security = true;
database = northwind");
try
{
// open connection
conn.Open();
// create command
SqlCommand cmd = conn.CreateCommand();// specify stored procedure to executecmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_select_employees_details";
// execute command
SqlDataReader rdr = cmd.ExecuteReader();
// process the result setwhile (rdr.Read())
{Console.WriteLine("{0} {1} {2}",rdr[0].ToString().PadRight(5),rdr[1].ToString(),rdr[2].ToString());
}
rdr.Close();
}catch (SqlException ex)
{Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
}
}
The build indicates no errors but the dos output gives the following error:
Error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, BooleantrustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Chapter10.ConnectionSql.Main(String[] args) in D:PROG_DEVVb.NET_devConsoleAppconnectVerifyAppconnectVerifyAppProgram.cs:line 18Connection closed.Press any key to continue . . .
BOTH THE CLENT AND DATABASE IS ON SAME MACHINE.
THE DATABSE IS centrinosqlexpress as shown in the code.
I
Is it possible for retriving data from a remote Sql server database and storing it in a local sqlserver database.
View 1 Replies View Related
hello there! i have a problem importing data from a remote connection using Sybase ASE ODBC driver(the sybase odbc driver is the only way we can access the database from the remote connection, we've already tried this in crystal reports). in the sql server import export wizard i chose the .net framework data provider for odbc. then below the wizard requires the following info: the connection string, DSN and driver. i specified the correct driver and dsn. in the connection string i specified the user id and the server name but it still produces error. the error says:
the operation could not be completed.
additional information:
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
i need your help guys... thnx
- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
Hi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
This is my procedure which I need to send the Data into Database, But it does not generate any error and it does not send the information into Database, May I have done wrong, any one to look on that please.
Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs)
Dim FVProductID As Label = FormView1.FindControl("ProductID")
Dim FVProductName As Label = FormView1.FindControl("ProductName")Dim FVProductPrice As Label = FormView1.FindControl("ProductPrice")
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim sql As String
Dim SQLAddString As String
DBConnection = New SqlConnection("Data Source=MANDARISQLEXPRESS;Initial Catalog=SHOES;Integrated Security=True")
DBConnection.Open()
If Not Session("OrderID") Is Nothing Then
sql = "SELECT Count(*) FROM ShoppingCart " & _
"WHERE OrderID = '" & CType(Session("OrderID"), String) & "' " _
& "AND ProductID = '" & FVProductID.Text & "'"
DBCommand = New SqlCommand(sql, DBConnection)
If DBCommand.ExecuteScalar() = 0 Then
SQLAddString = "INSERT INTO ShoppingCart (OrderID, ProductID, OrderDate, ProductName, ProductQnty, ProductPrice) VALUES (" & _
"'" & Session("OrderID") & "', " & _"'" & FVProductID.Text & "', " & _
"'" & Today & "', " & _"'" & FVProductName.Text & "', " & _
"'" & FVProductPrice.Text & "', 1)"DBCommand = New SqlCommand(SQLAddString, DBConnection)
DBCommand.ExecuteNonQuery()
End If
End If
DBConnection.Close()
Src.Text = "Item Added"Src.ForeColor = Color.FromName("#990000")
Src.BackColor = Color.FromName("#E0E0E0")
Src.Font.Bold = True
End Sub
Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs)
Dim FVProductID As Label = FormView1.FindControl("ProductID")
Dim FVProductName As Label = FormView1.FindControl("ProductName")
Dim FVProductPrice As Label = FormView1.FindControl("ProductPrice")
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim sql As String
Dim SQLAddString As String
DBConnection = New SqlConnection("Data Source=MANDARISQLEXPRESS;Initial Catalog=SHOES;Integrated Security=True")
DBConnection.Open()
If Not Session("OrderID") Is Nothing Then
sql = "SELECT Count(*) FROM ShoppingCart " & _
"WHERE OrderID = '" & CType(Session("OrderID"), String) & "' " _
& "AND ProductID = '" & FVProductID.Text & "'"
DBCommand = New SqlCommand(sql, DBConnection)
If DBCommand.ExecuteScalar() = 0 Then
SQLAddString = "INSERT INTO ShoppingCart (OrderID, ProductID, OrderDate, ProductName, ProductQnty, ProductPrice) VALUES (" & _
"'" & Session("OrderID") & "', " & _
"'" & FVProductID.Text & "', " & _
"'" & Today & "', " & _
"'" & FVProductName.Text & "', " & _
"'" & FVProductPrice.Text & "', 1)"
DBCommand = New SqlCommand(SQLAddString, DBConnection)
DBCommand.ExecuteNonQuery()
End If
End If
DBConnection.Close()
Src.Text = "Item Added"
Src.ForeColor = Color.FromName("#990000")
Src.BackColor = Color.FromName("#E0E0E0")
Src.Font.Bold = True
End Sub
I need to send an email for each row in a dataset. The data set will contain the order number. I need to first look up the order detail based on the order number and put it into an e-mail. Should I use the OLE DB command object for this?
View 3 Replies View RelatedI have set up a multiple server merge replication publication using the following:
SQL server running SQL Server 2005 (SQL server ce server tools installed)
Web Server running IIS (Required replication elements installed)
All went smoothly with the installation and I was able to running the diagnostics report from my Pocket PC by browsing the web page url that contains the slqcesa30.dll
http://www.myserver.eu/repdata/sqlcesa30.dll
I then ran the configure web synchronization wizard and setup basic authentication (I am using this purely for test purposes and will reconfigure the security when testing is complete) I then created a user on both the SQL server and the Web Server (identical on both machines) and gave this user the required rights in all objects. I have set up a trial application using visual studio 2005 vb.net on a windows mobile 5 device. When form1 loads the following code is fired:
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'' Configure the DataAdapter for use in UI binding
cnField = New SqlCeConnection("Data Source =documents and settingsMobileData.sdf;")
daField = New SqlCeDataAdapter("SELECT Id, LatLong, Dateandtimeoffix FROM FieldData", cnField)
'' Configure the update comand
Dim cb As New SqlCeCommandBuilder(daField)
Merge()
End Sub
Private Sub Merge()
Const URL As String =€? http://www.myserver.eu/repdata/sqlcesa30.dll€?
'' URL to Agent
Const InterNetUser As String = "Fielddata" '' InternetUser
Const InternetPassword As String = "Password" '' InternetPassword
Const PublisherServer As String = "MyServer" '' Publisher server
Const PublisherDatabase As String = "Table_Test" '' Publisher Database
Const PublicationName As String = "RepData" '' Publication name
Const SubscriberName As String = "Testing" '' Subscriber name
Const LocalDB As String = "Data Source=documents and settingsMobileData.sdf" ''Connection string to local database
Using rep As New SqlCeReplication(URL, InterNetUser, InternetPassword, PublisherServer, PublisherDatabase, PublicationName, SubscriberName, LocalDB)
Try
If Not System.IO.File.Exists("Documents and SettingsMobileData.sdf") Then
rep.AddSubscription(AddOption.CreateDatabase)
End If
rep.Synchronize()
Catch ex As SqlCeException
DisplaySQLCEErrors(ex)
End Try
End Using
SetupDataSetforUI()
End Sub
When the code executes I obtain the following error:
Index #0
Microsoft SQL Server 2005 Mobile Edition
Error: A request to send data to the computer running IIS has failed for more information see HRESULTS.
I have looked this up and it appears to be to do with certification? Is this correct I am using HTTP not HTTPS?
Any assistance appreciated
joebo