DML Against Remote Tables (MSSQL To DB2)
Oct 31, 2005
I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME
I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?
View 1 Replies
ADVERTISEMENT
May 16, 2008
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
View 1 Replies
View Related
Jun 21, 2006
Please bare with me as I do not have much knowledge in this area with MSSQL.
I have installed MSSQL 2000 Developer and I have setup a database which I am able to connect to and work fine with from the actual server MSSQL is installed on.
The problem comes in when i try to connect to that same database from a different remote server. It will not connect to the DB from this remote server but I know all the connection information is correct so its something on the MSSQL DB server causing this and not on this remote server.
Someone I know has had this problem and has fixed it but they wont tell me how. All I know is they said something about manually opening the ports on the MSSQL server and he also said when you set it to dynamicly determine the port that MSSQL is listening on it errored out. however if you set it staticly to 1433 it worked fine.
Ive also installed SP4 in attempt to fix this but no luck with that either. And I can also get the same exact remote server to connect to completely different DB server without issues.
If anyone can please help walk me through this I would be greatly appreciate and I am also willing to pay some money if it will make my life easier.
View 6 Replies
View Related
Jan 28, 2008
Hi there everybody
I am a novice in using MSSQL and Windows 2003 solutions so I am having some problems.
I have made an excel addon in VSTO that connects to a MSSQL2005 server running on Windows 2003 on our internal domain, and it works great. Connection when I specify the domain adress is flawless. The thought is though that the addin is supposed to been used also outside our domain, so I have opened up our router and redirects all traffic to and from port 1433 to our MSSQL-server. This part should be ok.
I can then not connect to this server with our external ip specified. The error message Visual Studio produces is "An error has occured 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: TCP Provider, error: 0 -A connection attempt failed because the connected party did not properly respond after a period of time, or established connecteion failed because connected host has failed to respond.)"
I have read around and tried to fix this myself. I've specified to accept "Locale and remote connections" in the Surface Area Configuration Tool that shipped with MSSQL on both TCP/IP and named pipes. In the SQL Server Configuration Manager is the protocols Shared Memory and TCP/IP enabled. I have enabled all the IP-adresses under TCP/IP and specified 1433 as the port to use for all of them.
As for possible other problems I can see:
I have read a couple of places that people have tried to connect with the MSSQL server through telnet when specifying the port 1433 (telnet mydomain.net 1433). I have tried this but telnet is not getting a connection. Dont I have to open/forward more ports on the router to get telnet through?
I cant ping our external ip. Isnt this also a problem that is related to that I have not opened/forwarded ports on the router to our MSSQL server?
Do anyone of you have any tips or steps to take to narrow the problem down? This is not the forum for VSTO but thats not really where the problem is either, I think. It is probably somewhere in the configuration of MSSQL or Windows 2003. The server machine is set up as domain controller and DNS server for our local net.
I will appreciate all input, I am stuck at the moment...
Stein Agnar Sannes
Programmer (c, php, perl, java)
View 1 Replies
View Related
Nov 8, 2007
I am using MSSQL Server 2005 Express Studio and tryig to connetch to the remore server but does not work.
I get this 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.
Though I have read like child and made changes according to it.
http://support.microsoft.com/kb/914277
Still says same error.
I am clueless how to do it.
Do any one know web based mssql editor where i can connect mssql?
Thanks in advance.
View 2 Replies
View Related
Jan 4, 2007
Hello,
I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.
My firewall is disabled.
I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.
In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.
In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.
Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)
When I look in the error log, it shows...
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 127.0.0.1 <ipv4> 1434].
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 'any' <ipv4> 1433].
I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??
Thanks
Foley
View 3 Replies
View Related
Nov 26, 2002
hi
im using ms sql server 2000 with the enterprise manager
i want to make a relationship between to tables.
let say first table has 2 column: ID, RegionName
the other hase 3 column: ID, employeeName, RegionName.
i want that while im trying to add new record from the enterprise manager to the second table, when i will try to assign value for the RegionName i will get a combobox with all the RegionName that the first table contains.
thanks a lot
Dovalle
View 4 Replies
View Related
Mar 19, 2006
Is there a way in SQL 2005 Express Edition to import/export data from another MSSQL Server? Something similar to the right click/ import table functionality of MSSQL2000.
I've read similar threads which all recommend using MS Access but there has to be a better way, right?
regards,
dj
View 9 Replies
View Related
Dec 26, 2007
Hi all,
Iam reading this forum from some time but its my first post here
Today i found by accictend this really nice PDF poster with SQL 2005 internal System Tables and thought that maybe someone here will be interested having it.
Poster is really huge, just like db schema on it ;)
But ok no more words, just grab it from here if You want http://rapidshare.com/files/79265626/sql.2005.sys.tables.pdf
Regards, Tom
PS.
1. If You can't read PDF after download with FireFox (like me) turn off pdf plugin and then download starts normally
2. If someone from admins think that its not good forum for this post pleas move it to somewhere that it best fits.
View 1 Replies
View Related
Jul 20, 2005
Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
View 1 Replies
View Related
Nov 9, 2004
Hallo everyone
Now I have to analyse so many Software systems that my company is using and I have to extract the Datamodels behind these systems.
One of these systems is developed with a MSSQL server backend. Now I have to open this server tables and see the table structures and data directly how we see in Access.
Please help me by telling the way to open this database.
any help is very much appreciated.
:) thanks in advance.
View 4 Replies
View Related
Nov 29, 2006
menardo writes "Hi there!
I'm a very newbie in SQL programming. Can anybody help me how do I start to learn scriptings.
Can you give me the process or scripts on how to export it??
Thanks in advance.
-menardo"
View 2 Replies
View Related
Apr 5, 2006
Hi everybody:
I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.
Do you have any solutions for this??
Thanks a lot
View 4 Replies
View Related
Oct 26, 1999
after upgrade sql from 6.5 to 7.0 , there are some new system
table in system database , all are begin with sysremote_....
such as sysremote_tables, i can not find any information about
them , does anyone know what these tables for and whether
these tables will affect the new sql7.0 application database ?
thanks in advance .
regards
zg
View 1 Replies
View Related
Jul 5, 2005
I'm using net2.0 and SQL server express locally and have succeseffully placed my connection string in my sebconfig file and made connection and grids and detail etc, etc. Now I want to publish what I've done. So I got a free account at maximumasp beta 2.0. They give some room on a sql 2000 server and a empty database. Now I want to add the Northwind table to that DB but I don't have Enterprise manager and have no idea how to add the tables.Can someone please help?Thanks!Rich
View 3 Replies
View Related
Jan 4, 2006
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?
View 4 Replies
View Related
May 22, 2008
hi,
I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.
I am using SQL Server Management Studio Express to connect remotely to the server.
I have 2 databases. Currently all data is stored in database1
I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server
I have already generated a script which creates the schema for the tables on
database2. The problem is moving the data. The web Host does not allow DTS
Here are the things they won't allow:
You can use all features of SQL 2000 (it is 2005) except for the following:
- DTS Packages
- Database Replication
- Mail Services
- XML Support
- Distributed Transactions
- Database Maintenance Plans
- Web Assistant
- Multiple Instances
I can't for the life of me figure out how to transfer the data.
I have tried copying and pasting the data and run into problems with Identity keys and constraints.
I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.
Bottom-line I can't import and export directly. And I desperately need to.
Thanks in advance!
View 14 Replies
View Related
Nov 21, 2006
Hi all,
I would be very glad if someone can suggest me what techniques I should use in the following scenario:
I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.
Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:
DB1.users, DB1.orders,... etc are managed by "webapplication"
DB1.products ... are not managed by "webapplication" : in fact only used to read from
DB2.products, DB2.customers, .... etc are managed by "winapplication"
DB2.orders,....are partially managed by "winapplication"
Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :
- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)
- the products data is only (at the moment) added, edited and deleted on the local server
I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?
Any suggestions are appreciated!!
O.
View 1 Replies
View Related
Jul 23, 2005
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
View 4 Replies
View Related
May 7, 2008
Hi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter
View 3 Replies
View Related
Jan 5, 2007
I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?
-Mathminded
View 4 Replies
View Related
Feb 14, 2008
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
View 3 Replies
View Related
Feb 13, 2006
Hi all,
I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .
my web site is ready
I didn't have problem to upload my site to my hoster.
Now I want to upload all my tables and my stored procedure create locally with VWD express
How can i do it ?
NB: I know i can't design DB (create/modify tables and stored proc) with express edition
thank's for your help
View 1 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Feb 10, 2008
Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
View 1 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
Mar 24, 2008
Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
View 2 Replies
View Related
May 3, 2008
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
View 6 Replies
View Related
Oct 26, 1999
Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
View 1 Replies
View Related
Jun 15, 2006
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
View 1 Replies
View Related
Nov 17, 2007
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
View 3 Replies
View Related
Jun 2, 2015
- 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?
View 4 Replies
View Related
