Can Distributed Query Read Sql Server 2000 From Sql Server 2005?
Nov 29, 2007
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
View 1 Replies
ADVERTISEMENT
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Feb 28, 2006
Hi,
I'm trying to upgrade from SQL Server 2000 to 2005. The problem I am having is that when I try to attach the existing db files I get a message that says "database cannot be upgraded because it is read only or has read only files...."
Thing is... there is no write protection on the files.
Can anyone advise me on how to overcome this problem so that I can attach the db, please?
Thank you
Robert
View 25 Replies
View Related
Apr 4, 2008
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Thanks
Sean
View 2 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
Aug 14, 2006
Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
View 13 Replies
View Related
Mar 19, 2004
Hi Chaps!!
I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
thanks in advance to give your time...
View 9 Replies
View Related
Oct 18, 2000
Three weeks ago we began a project that involved importing data from an AIX DB2 6 environment via a linked server configuration. Following the data import a second query was executed against the db2 environment using data that resides in the new table within SQL 7 in the join statement (a very basic example is provided below) This was all accomplished in sequence via a package.
SELECT F_NAME, L_NAME, PASSWD
FROM SQL7.LOCALSRV.dbo.NEWUSERTBL as new, DB2.SYSIBM.MASTERTBL.OLDUESRTBL as old
WHERE new.USER_ID = old.USER_ID
Originally we had no problems and while the execute time was not exactly speedy it was tolerable as we would revisit optimization after we established if what we were trying to accomplish was feasible. At the outset the first data import to build the local table was immeadeate and then the distributed query to retirieve more info to build another local table against the DB2 server took aproxiamtly 1 minute per user row returned. Currently we are looking at still having an immeadeate data import (a matter of seconds to build the first table)but now we are looking at more than 1 hour returned for 1 correesponding row of data off of DB2. We are utilizing the the IBM DB2 ODBC DRIVER.
Any input or suggestions as to what could be causing this or perhaps a more efficeint way to code the statement would be much appreciated. Thanks in advance.
Adrian
View 1 Replies
View Related
Feb 13, 2008
I am working on a linked server where a few of the queries use almost exclusively remote tables from 1 other server. I have read somewhere that there are options to specify where a query is performed but can't find it anywhere. Could someone tell me the command and how to use it or point me to something to read about it.
Thanks
View 1 Replies
View Related
May 5, 2008
Hi all,
I need your help.
I have 2 different databases. One of them is in the SQL Server 2000 and the other on is in the SQL Server 2005. I need to make a query out of these 2 databases.
Is there any way that I can do this?
Thanks,
View 12 Replies
View Related
Apr 13, 2007
I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...
Linked Server name: SQL2005
From the SQL Server 2000 instance in Query Analyzer....
-- this works fine
Delete from [SQL2005].myDB.dbo.myTbl
-- this just hangs
Begin Tran
Delete from [SQL2005].myDB.dbo.myTbl
It doesn't make any difference what provider I use on the Linked Server.
I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.
We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.
Any suggestions?
Cilve
View 2 Replies
View Related
Sep 19, 2007
Hi Folks,
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.
And the other way round:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
Cheers,
Crispin
View 3 Replies
View Related
Jun 21, 2006
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" 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 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
View 7 Replies
View Related
Oct 24, 2007
Hi, I am zeronet. I decide to set up our own web system application just like Ebay. Because of the low investment, I want to select a free DB for our data storage. For high performance, we want to set up a distributed db system. As a beginner of SQL server 2005 express, we want to know: if the SQL-server-2005-express has that ability to manage large amout data and to do with this work? I am eager to hear of your advice.
Thanks.
yours
zeronet
View 6 Replies
View Related
Aug 1, 2007
Hi,
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
Cheers,
Chris
View 4 Replies
View Related
Aug 1, 2006
Hi,
I have an issue with an application, and i want to know what sql queries are done by this application. So i need to read the .ldf file, i have do some search on google, but i find nothing thant can be relevant.
View 3 Replies
View Related
May 30, 2007
I am trying to configure distributed transaction and XA support using Microsoft SQL Server 2005 JDBC Driver. I have coppied SQLJDBC_XA.dll from XA directory and placed in my sql server binn directory and trying to run the script xa_install.sql from binn directory with command as below :
C:Program FilesMicrosoft SQL Server80ToolsBinn>
osql -U sa -n -P admin -S localhost -i C:JavaLibrariesMS SQL Driversqljdbc_1.2enuxa xa_install.sql
But I am getting error saying :
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
when I replaced local host with the machine name it gives error :
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
where in I am able to test connection from Websphere using the same connection.
Please help some one ....... I am in URGENT need.... I need to enable XA suport for my application to run........
Thanks ----
View 2 Replies
View Related
Nov 18, 2005
i have data on a spreadsheet and i need to read it to a table in SQL server ? how can i do that ? some one refere to me a method for that but i need to see what other think is the best option and the most effecienet way let us say!!
View 4 Replies
View Related
Feb 23, 2006
hi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.
View 6 Replies
View Related
Apr 20, 2007
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
View 8 Replies
View Related
Jul 11, 2007
I have a Login that I setup in SQL Server 2005. The only database roles given to the login are db_datareader and public. On SQL 2000 these permissions are sufficient to allow me to setup a read only ODBC connection using this login and creating an import table in Access. When using the login in SQL 2005 and creating an import table, I am able to edit the underlying data, something I am not able to do with a SQL 2000 ODBC connection. Is there something in SQL 2005 that I am unaware of that would allow this login to modify (update) the table?
View 6 Replies
View Related
Feb 10, 2006
Newbee question: I am trying to use some of the "101" samples in VS 2005 with SQL Server 2005. These samples use SQL server express MDF files.
The error is: "... you cannont open a database that is incompatible with this version of sqlservr.ext. You must re-create the database."
Do I need to install SQL server express? What are the issues with installing express when I am already running SQL Server 2005?
How do I update or "re-create" the express MDF database file in SQL Server 2005?
Thanks!
View 3 Replies
View Related
Feb 13, 2008
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
View 4 Replies
View Related
Aug 18, 2006
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
View 1 Replies
View Related
Jul 31, 2015
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
View 5 Replies
View Related
Sep 20, 2006
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->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)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
View 9 Replies
View Related
Nov 27, 2006
This just happened today and I dont know why. I have a database on a SQL server 2005 database, that has a table called photos. It contains image data, but not the column says <Unable to read data>. But before that I get an error when I try to execute the table to retreive the data. The error is below. SQL Execution Error:Executed SQL statement: SELECT ID, AlbumID, Caption, BytesOriginal, BytesFull, BytesThumb FROM PhotosError Source: System.DataError Message: Invalid attempt to Read when reader is closed. This happens with SQL Server Management Studio, and Visual Studio 2005 when I try to access it. I tried two computers so its not that. Any help or insight would be appreciated.
View 2 Replies
View Related
Jul 14, 2007
Hi I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure: SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle) And then using an .aspx page to Response.Write the data:Using conn As New sql.SqlConnection conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString Dim getLogoCommand As New sql.SqlCommand getLogoCommand.CommandType = Data.CommandType.StoredProcedure getLogoCommand.CommandText = "GetPicture" getLogoCommand.Connection = conn Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200) imageTitleParameter.Value = Request("imageTitle") imageTitleParameter.Direction = Data.ParameterDirection.Input getLogoCommand.Parameters.Add(imageTitleParameter) conn.Open() Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader logoReader.Read() If logoReader.HasRows = True Then Response.Clear() Response.ContentType = logoReader("MIMEtype").ToString() Response.BinaryWrite(logoReader("imageData")) End If End Using conn.Close() End Using Can anyone please help me with this?!
View 2 Replies
View Related
May 4, 2007
Hi
i want to access a CSV file using OpenRowSet function in SQL Server 2005.
Anyone having any idea; would be of great help.
Regards,
Salman Shehbaz.
View 8 Replies
View Related
Aug 10, 2007
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
View 1 Replies
View Related
Aug 10, 2007
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
View 5 Replies
View Related
May 31, 2006
Hi Guys,
I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!)
Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing.
A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005.
So after a number of retries in which the install program kept saying that there was nothing to install, I selected every option under Advanced in the intall process. This resulted in stuff being installed and I now have SQL Server 2005 running on my machine, but no SQL Server Manager icon.
My questions are:
Where can I find the SQL Server Manager executeable?
Has anyone installed SQL Server Express, SQL Server Mangement Studio Express, SQL Server 2005 and SQL Server Management Studio on a single machine successfully? If so, what order did you install them in? (I'm planning on uninstalling everything SXQL Server related and starting again.)
Thanks very much for your help.
Regards
Gary
View 5 Replies
View Related
