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,
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.
Any ideas on how to keep the data updated on the website?
Dear All,
Before i get crazy please help me out, i have creatd a linked server between sql 2005 and sql 2000.
The security of linked server is how,
that i have used a window authitcation accout like domainuser and i m using this account as impersonate and with the option that "be made with the login's current security context".
Now the problem is when i used the application it is working fine, but after if login the applciation with a break of 15 mins or half an hour, i get the error
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
I am unbale to undersatnd that why it so, y it fails after few mins, and if i check the particular Sp from backend it is working fine.
Moreover if a again create the link server with same setting as mentioned above it will work fine, but again after half an hour it will come up with same problem.
But if i create linked server with sql account that is available on both server using option "be made using this security context" every thing is working fine.
please help why problem in 1st case, and if my concept regarding linked server security is confusing do let me know any good link
Thnx l
Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.
hi, this stored procedure below takes 25 mins to execute. is this normal? and is there a faster way to do this? its not like i have millions of records, i only have about 170.
Create Procedure sp_PayrollComplete
AS
declare @weDate nvarchar(10)
Select @weDate= we FROM cbetts.tblArchive
WHERE we = (select max(convert(datetime,we)) from cbetts.tblarchive)
declare @uid int
declare @job nvarchar(10)
declare @name nvarchar(30)
declare @Location nvarchar(30)
declare @Direct float
declare @LBO float
declare @g1099 float
declare @agency float
declare @nonbill float
declare @holiday float
declare @vacation float
declare @total float
delete cbetts.tblpayroll
Declare payroll_cursor CURSOR FOR
SELECT distinct userid FROM cbetts.v_PayrollTotals
where (we = @weDate)
ORDER BY userid
OPEN payroll_cursor
-- Perform the first fetch.
FETCH NEXT FROM payroll_cursor into @uid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @location=location from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @name = name from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @Direct =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='direct'
and left(jobno,3) != '900'
select @LBO =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='LBO'
select @g1099 =
sum(totals)from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='1099'
select @agency =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='Agency'
select @nonbill=
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and left(jobno,3)='900'
select @holiday =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and (jobno='90010' or jobno='90011')
select @vacation =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and jobno='90020'
set @total = ISNULL(@direct,0) + ISNULL(@lbo,0) + ISNULL(@g1099,0) + ISNULL(@agency,0) + ISNULL(@nonbill,0) + ISNULL(@holiday,0) + ISNULL(@vacation,0)
set @nonbill = ISNULL(@nonbill,0) - (ISNULL(@holiday,0) + ISNULL(@vacation,0))
insert into cbetts.tblpayroll (Name,userid,location,direct,lbo,g1099,agency,nonbill,holiday,vacation)
values (@name,@uid,@location,ISNULL(@direct,0), ISNULL(@lbo,0), ISNULL(@g1099,0), ISNULL(@agency,0),ISNULL(@nonbill,0), ISNULL(@holiday,0), ISNULL(@vacation,0))
FETCH NEXT FROM payroll_cursor into @uid
END
CLOSE payroll_cursor
DEALLOCATE payroll_cursor
-- get rid of the 0 hour entries
delete cbetts.tblpayroll
where total = 0
GO
I need to show the count that occurred during the previous 30min. I've been told the following query doesn't work.
SELECT COUNT(*) FROM sampledata
WHERE (id = 254 AND sub_id = 731) AND (sampledate >= DATEADD(mi, -30, GETDATE()))
The following transaction runs, then reverts back after 10 mins.
begin Tran [UpdateSundays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-2,BusinessDate)
where datepart(weekday,BusinessDate) = 1
commit tran [UpdateSundays]
begin Tran [UpdateSaturdays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-1,BusinessDate)
where datepart(weekday,BusinessDate) = 7
commit tran [UpdateSaturdays]
How can i plan the execution of a package ?
For example, i want to execute the package every 2 minutes for all the time.
I do not want to use SQL Server Agent to do this. I want the package to self contained to do it.
Thanks
Rohit
Hi,
Can anybody pls help me with this,
i have to migrate data from one sql server(1) to another sql server(2) using SSIS,Which is a bit easy,the concern here is that after doing it the data in server(1) is getting updated due to inserts,now i need to bring the updated data alone.Is anybody having experience in this?can anyone pls help me or give me sugessions on how i need to proceed.
Thanks in advance.
Regards,
Sg
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 Replies View RelatedI 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
Does any one have sample script which notify if "XYZ' job takes more than 5 mins to complete then needs to notify about the job status.
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 do not know SQL but learning fast and furious.I am programming an agent and working with a group of existing databases.I would like to able to compare the database before and after an update.The testing databases are relatively small.I have no problem programming some compare but how do I go about it.Should I do this in SQL duplicating the database.I would be happy to write some SQL and dump the databases and do the compareexternally.I would appreciate any suggestion.Andre
View 4 Replies View RelatedI am new to SSIS (though have a decent amount of experience with SQL Server 2000), and am trying to design a data warehouse and eventually a comprehensive reporting solution.
Here is our setup:
- We have ~150 studios all operating software which we designed (ie. we have control over the system that will be the Data Warehouse source).
- All studios use merge replication back to Head Office (we maintain a duplicate of each of their databases).
From my limited understanding of Data Warehousing methodology, I believe that I should prepare a 'staging area' which contains all needed data from these databases. My question is how to do this in an efficient manner (without resorting to software-maintained timestamps in all the source tables - this would require significant modification to our software).
Since replication is already identifying which rows are new or updated, I'm wondering if there is some way to use this information to limit the information processed during the Data Warehouse updating process. I realize that I would still need to determine whether the row was updated or new, but it would cut down immensely on the number of rows processed.
I feel like I could be missing out on a simple way to do my data extraction from the source DBs. Does anyone have any advice?
Thanks in advance for your help.
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_
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 RelatedThis has been driving me crazy.
First noticed it when I went from JIT compiling of my .net app to pre-compiled via the command prompt, although I can't see that that's go anything to do with it...
My database is around 2gb and I'm using service broker.
If I drop a new version of my app into production, sqlserver.exe goes nuts and eats up the cpu.
Tonight, I stopped IIS and SQL Server, then dropped a new version in. I hadn't re-started IIS at this point... As soon as I started SQL Server, off it went again - eating up the CPU. I quickly launched a trace via the Profiler - no activity showing.
So, I started IIS after a couple of mins (still with the high cpu) and then just waited. After about 10 mins, SQL Server settled down and everything was normal again.
What is it doing when I start the SQL Server service?
Si
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View 3 Replies View RelatedMy package sets a variable in an ExecSQL task. This variable is then used as a parameter in a DataFlow task that follows. Normally everything works just fine. However, sometimes if the package fails inbetween the step that sets the variable and the DataFlow, the default value of the variable is stored in the checkpoint file. (It is not failing in the step that sets the variable) When the package is restarted I can see that the variable is not set to the data value in the database, but rather it has its default (design time) value.
Anyone else see this bug?
but 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.
i have a time series table which generates a flag for every 10 mins. the table looks as below. I am trying to write a sql query which gives me the start date and end date of a particular flag, each time the flag starts. i tried to do it using cursors and actually joining the table to itself based on a 10 min offset but couldn't get the required results.
startdate enddate
flag
2009-11-02 23:30 2009-11-02 23:50
-999
2009-12-08 04:50 2009-12-08 04:50 -990
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field.
Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource.
If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query.
Can anyone help.
Trigger not running when trying to select data from a column that is being updated?
View 1 Replies View Relatedhi,
I created a SSIS package. It imports data from a flat file and then transfer to different data types and load it into destination table. I use look up transformation. Actually before I created final table, I created another intermediate data table for references.
Now I get a new source file once in a month. Then I'm supposed to connect new file and run the package. only difference in new source file is the data not data type. But when I connect the new flat file, package does not work. first one and fifth one are red when I run the package.
Can anyone help me to fix this?
Thanks
p/s;
I get the following error messages in execution result page.
[Lookup 5 [541]] Error: Row yielded no match during lookup.
[Lookup 5 [541]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup 5" (541)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (543)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup 5" (541) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
[Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.