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?
Hi,I have two, similar SQL Server 7.0 databases (in the same SQL ServerGroup) that I use as the backend for two Access 2000 front endapplications - one is the live version, the other is the developmentversion.The live version works fine. But the other version is read-only, i.e.no changes can be made to the data in the tables, over the ODBCconnection.Both systems have similar ODBC connection properties, the permissionsettings on the SQL Server databases are the same - users, tableaccess, database access, etc. The connections use Windowsauthenication and the users are set up in SQL Server security loginswith public and db_owner selected.Has anyone seen or heard of this problem and does anyone have anyideas on how to fix it?I appreciate any comments or suggestions you may have.Thanks,Chris
Hi, I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur?
Edited 12:10 06/14/07 Some extra info. At the same time it goes read only, the link loses it's primary key.
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
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$]
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.
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?!
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?
Hi guys, Please can anyone show me how to connect SQL Server 2005 to ODBC Driver(Novell). The import/export wizard data source has no ODBC option.
My final goal is to extract data from an eDirectory into SQL table. I used Novell Client to create a link to eDirectory; with the ODBC driver as the data Source for the SQL Server to extract data from. Every help will be rewarded with appreciation. Thanks
Hi,I'm trying to find out if we can use bulkcopy via odbc in sql server 2005.With sql server 200 we could use the odbcbcp.dll.I can not find any info regarding this for ms sql server 2005.Thanks,Jos van der Velden
I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).
The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)
I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables. None of the required db1 tables appear.
under Security/Logins I've created User1: SQL Server auth. with password default db = db1 server_roles = none user mapping = map, db1, user1,dbo securables = none status = grant, enabled
on the access db, the odbc link was set up with default db = db1
HiI've got an Ingres database of some 200 tables which I need to importevery night into SQL Server 2005 for use by Reporting Services. Mostof the tables will come across unchanged (a few need massaging tohandle time intervals correctly), but the Import Wizard only seems towant to import one table (or more accurately query) at a time. I seemto remember the old 2000 Import Wizard handled multiple tables - isthere any way of processing multiple tables in 2005, or must I resignmyself to writing 200 import packages in SSIS.Chloe CrowderThe British Library
Hi, Am new to SQL Server 2005...actually this is my first day with it
am trrying to connect to SQL Server which resides on a 64-bit machine from a 32-bit machine using ODBC with a DSN aliasing thing it gives me error msg "login failed for user XXX" with error state indicating that password missmatch or sometimes invalid user!! i dont use the password i keep it blank and checked everything i could possibly think of...
I am the DBA for a product development company with an application currently connected to and running fine on SQL Server 2000. I have recently installed our product on 2005 sql server with compatability version of 90 and some folks have begun testing. Apparently, there are a lot of strange things happening and ODBC errors are frequent. The more they move around in the app, the more errors appear. Sometimes, they appear, sometimes they don't. The most common occurence is an error such as, x table is missing the following columns. Then the user does it again and does not receive the error. Another issues is "Application Terminating Due to Severe Database Error" and when we look at the logs our app generates:
08/10/2006 08:57:09 (01000/10054), [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). SQLColAttributes 08/10/2006 08:57:09 Table x.TS_X is missing the following columns:
The user has database role membership db_data_read and db_data_write access to the database. MDAC version - 2.81.1117 ODBC version - 2000.85.1117
Could this be a permissions issue or possibly more of an ODBC issue? Any thoughts would be greatly appreciated
An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?
Hi everybody I have installed SqlServer 2005 on my server but my Windows application on the client computers should connect to server via ODBC connection so what should i install on the client computers (that don't have any sql server installed) to enable them to connect? When i want to create ODBC connection on client computers through (Control panelAdministrative ToolsData Sources) i don't see SQL-Server 2005 ODBC driver in the list ....could anyone help me? Kind Regards.
I am trying to link tables from an access 2003 frontend to sql server 2005 backend.
I am doing this in a vmware test environment. I am using vmware server and running sbs 2003 and xp sp2 client. Both virtaul machine can talk to each other(using local host connection).
I have tried to create a dsn to the sql server but i cannot connect. I can ping the sbs server through the command interface but the error i get when i try to connect is :
Connection failed: Sql state: hyt00 sqlserver error: 0 microsoft odbc sql server driver timeout expired.
spent a day trying various combinations - still no joy.
Does the x64 version of SQL Server 2005 June CTP contain 64-bit versions of the SQL Server ODBC Driver and the OLE DB Provider? I am not interested in the .NET Managed Provider here, just the legacy ODBC and OLE DB drivers, and only 64-bit versions of those.
Hi, on a web application that I develop, I just upgraded to a new database server running SQL Server 2005 and Windows Server 2003 Enterprise Edition. The previous database server ran SQL Server 2000 and Windows Server 2003 Standard Edition. Since this upgrade, the application has been causing "timeout" errors all over the application. I've been stumped. Here's the error mssage:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired
I'm hoping there is some sort of a setting that can be modified to increase the maximum number of connections between the web server and database server. This is a fresh version of Windows Server 2003 and SQL Server 2005. Any support that can be provided will be greatly appreciated.
We have a legacy C++ application which uses ODBC to connect to SQL server. The application is a server process that opens a connection and caches it. Unfortunately on a connection drop, it does not refresh the connection. The standard installation is a 3 machine web/app/sql setup.
It has been working fine since last 4-5 years unless the SQL Server goes down. In that case, I keep seeing "Function sequence error" and "Communication link failure" errors. On one particular client installation on application server, I started getting this exception while SQL Server was up and running (on a different machine). I would see the connections randomly dropping in the activity monitor after 3-4 hrs of inactivity.
I wrote a .NET test app which uses ODBC drivers for SQL Server and makes a connection and then just waits. Out of 5 connections established by this application I saw one connection being dropped as well. Another test app used ADO.NET and it did not drop any connections. I asked the IT to check the network and they say its fine.
The servers are W2k3 SP2 and database is SQL Server 2005. IsAutoClose on all the databases is false. Nothing goes in the logs even after turning on traces using DBCC.
Since the connection drop was very random, I turned off connection pooling on "ODBC driver for SQL Server" on the application server and now it is working fine.
Can someone explain me what could be the reason? Also, is there a way to upgrade or downgrade ODBC drivers on W2K3 machines?
Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.
Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.
and how to use same password in linkedserver in 2005
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'> <a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File </a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
God morning! I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases. - I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange! - I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio! - Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection. - We have even tried an 10G version of Oracle with the same result.
Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?
Hi I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005. Thanks for any help
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?
I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.
I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.
I have application create by MsAccess and connect with Sql Server 2000 in x86
and now we transfer system to HP rx3600 Intel Itanium - Install Windows 2003 Server Enterprise (for Itanium) - Install Sql Server 2005 Standard Edition (IA64)
and now we try to create ODBC connection from client for testing.
But we can't not done it.
Anybody can tell us how to create connection to sqlserver 2005 (ia64)
I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?
