ODBC Link From Access To SQL Server 2005 Stored Function
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.
What am I missing?
Suggestions?
View Complete Forum Thread with Replies
Related Forum Messages:
Cannot Link To SQL Tables Using ODBC Link Table In Access 2003
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning. The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases(). The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source. Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade). Has anyone experienced anything like this? What's going on?
View Replies !
Help Setting Up An ODBC Link To SQL Server From Access 2003
PLEASE HELP ME I'm a pretty smart guy, but sometimes I end up feeling left behind with all of the terminology I don't recognize. I am running XP Pro, Access 2003, and SQL Server 2005 express. I want to set up a ODBC connection in Access to a table that has been created in an SQL Server 2005 Express Database. I keep getting the message connection failed, SQL Server does not exist or access denied. Can somebody here please help me?
View Replies !
How To Link SQL Server 2005 Into ODBC
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
View Replies !
How To Restrict Editing Of SQL Server 2005 Data Via ODBC Link?
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? Any help will be greatly appreciated. thulo
View Replies !
Access Can't Link To NEW LOCAL SQL Server 2005 Express Installation
I have just downloaded and installed the SQL Server 2005 Express version, enabled TCIP and Named pipes but when I try to link to the tables with Access 2003 or try to create a database connection I get an error 17 "connection failed - SQL Server does not exist or access is denied". I am able to open Management Studio Express and see all of the databases, including another SQL Server 2005 server that I have. I am able to Link to my other server but I can't get see my local install. What can I do? Thanks, Mike
View Replies !
ODBC For MS SQL Server To Access Stored Procedure
I have a stored procedure written in MS SQL Server2000 which takes argument(OUTPUT) as a cursor, and fills in the cursor with the record from the table. I have to run this stored procedure from my C application program running in SUN OS2.9 with the help of ODBC calls. Can anyone guide me through steps as to how to run the store procedure from my C program and receive records with the help of the cursor. The store procedure is as follows CREATE PROCEDURE testCursor @xyzCursor cursor varying OUT AS DECLARE temp CURSOR LOCAL FOR SELECT * FROM table OPEN temp SET @xyzCursor=temp RETURN(0) GO
View Replies !
Trying To Setup ODBC Link To SQL 2005 Express
Hello All I am trying to use SQL 2005 express as a datasource for a mailmerge with Word 2000. I have created a ODBC link successfully. I have enabled all the protocols. When I open the ODBC link, all I see is a ODBC drop down. I cant see or select any database tables? When I open the dsn I get a dialog caled Header Record Delimiters. I accept the defaults and then I get another Dialog called Mail Merge Recipients with a whole lot of connection info, but no sign of any database tables. Any Ideas?
View Replies !
Function In Access Vs. Stored Procedures In SQL Server
I am used to working in Access and just recently became somewhate proficient using custom functions in modules. I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access? Thanks for your help. Mike
View Replies !
ODBC Communication Link Failure To SQL Server
Okay-- if anyone can solve this they are truly the SQL genius! We are getting this error when we run a VB program that we use to access an SQL database on a server across our network on a workstation. In fact we get this same error when we even run the program on the server where the SQL database is running or on any of our workstations. Here is the error message: 08501:[Micorsoft][odbc sql server driver] communications link failure Now the odd thing is that many other functions in the workstation application work fine and retrieve data from SQL but certain data requests by the workstation application fail with the above error message and we get this message consistently. Even though it appears that different workstations running the identical Vb application will get this error consistently but in different locations when running the application. We were running SQL 6.5 on an old server, with the workstation application for literally years without any problems. We also decided to upgrade to a new server, installed server 2000 operating system and the latest version of SQl -- moved all the databases pointed the workstations odbc at this new server and get exactly the same error in the same location in the workstation applications. The programmer that wrote the application and designed the database in SQL can't find the problem and a number of other computer "experts" also could not find the error. We did add a new linksys DSL router/firewall but everything kept working after this installation for several weeks so I don't know if this is the problem on the network. THe programmer also noted that he had problems using terminal services on our network to connect to his office computer and decided that there must be some network issues that are causing the ODBC communicaitions to fail and also terminal services to fail-- or of course they may be unrelated. Has anyone ever seen this ODBC communication error in their travels through SQL implementations? Any help will be greatly appreciated. If we can't fix this we will have to abandon a software application that has been used for over five years and just too complex to rewrite. Jeff Kilpatrick
View Replies !
Using A Link Server Function...
I have a to retrieve some data from a link 2005 SQL database and load it on my 2005 SQL database. I have to use the function supplied by the db owner of the other database and I have to use a server link to do it because the net working group refuses to allow NON SQL servers to interface through this server's firewall. I have tried the follow: select * from linkName.databaseName.dbo.functionName('stringValue', 'stringValue', Null) This returns a "Remote table-valued function calls are not allowed." error I have also tried: SELECT * FROM OPENQUERY ("linkName", 'select * from databaseName.dbo.functionName(''stringValue'', ''stringValue'', Null)') Which returns both "Statement(s) could not be prepared." and "Invalid object name 'FusionDB.dbo.uspAPP_GetCustomerShipTo'. " errors Does anyone know how to do this on SQL 2005? I used to do it all the time on SQL2000 with no problems.
View Replies !
Microsoft ODBC Sql Server Driver Communication Link Failure
I get the message Microsoft [ODBC Sql server driver]communication link failure error message in my VB 6 application with MSDE engine. This error does not happen often. My connection string is as below. "Provider=MSDASQL;Driver={SQL Server};Server=" & mSvrName & ";DATABASE=cnsClient;uid=uid;pwd=pwd" Please help.
View Replies !
[Microsoft][ODBC SQL Server Driver]Communication Link Failure
All, I need a help from you all. I am getting this error if the users are running the application which is connecting from MSAccess97 to SQL Server 2000. Error: [Microsoft][ODBC SQL Server Driver]Communication link failure I would appreciate if you send me the resolution for this problem. Thanks, Reddy
View Replies !
[Microsoft][ODBC SQL Server Driver]Communication Link Failure
Several of our customers are getting a message intermitantly throughout the day where the connection is closed. The message being generated is [Microsoft][ODBC SQL Server Driver]Communication Link Failure. These messages started in the last two weeks. Thanks for any direction that can be thrown my way. David
View Replies !
How To Link To An Existing SQLExpress 2005 Table From Access 2003
I initially created an Access application within Access 2003 which I subsequently migrated to SQLExpress 2005 (Tables only) using Microsoft SQL Migration Assistance for Access 2003 (SSMAA) and continue to use Access 2003 as a front end, this really was easy and worked perfect. Now I added a new table to my SQLExpress DB and need to link to it from my Access application but can not find a way to do so in the same way that the SSMAA does. I need to run the Access 2003 application in share mode from multiple PC in the LAN. If I try linking to the table using an ODBC connection I can only access from the PC where the link was done! Any ideas? THanks in advance.
View Replies !
Can't Link To Sql Server With Ms Access Via Web
hello. I have a windows 2003 server with sql server 2000 and a public IP address and domain name when i am on the same network as the server I can link to the sql server with just the dowmain name www.omghelp.com but when I take that same access adp file or mdb file home it says "database can not be found " what do i need to get it to work...help please
View Replies !
Link Access To SQL Server
I have a problem that I want to solve by linking MS Access to SQL Server 2000 or 2005. The problem is a 3 party MS Access '97 application that is no longer supported so a new system is currently being built in SQL Server. The Access database has a UI for small amounts of input and viewing but most of the work is done through a WaveLink application. The Wavelink application allows users on the shop floor via wireless devices to pick and count product which then updates Access. There has always been record locking issues that caused some delays at the wireless devices but nothing dramatic. Until now. The wireless network and wireless devices have been upgraded and now record locking (I presume that whats causing the problems) is now causing large productivity delays requiring constant intervention. The Wavelink application is also being updated from VB6 to VB.Net to work with the new SQL Server application. One of the requests from the client is that the Wavelink upgrade somehow fix the problem with the Access database. My idea was to link the Access database to a SQL Server so that the wireless devices go through SQL Server to Access hopefully taking the load off Access. Is this possible? Would SQL Server prevent the record locks and provide a better multi user environment or is that something that is inherent in OLEDB Jet and linking is not going to circumvent this problem? Regards
View Replies !
ODBC Connection From Access 2007 Database To SQL Server 2005 Database
I need detailed instructions on how to connect to a database from a Microsoft Access 2007 database to a Microsft Office Accounting 2007 database. The accounting database is an SQL 2005 datbase. It has an instance name of "MSSMLBIZ". When I try I get an SQL error 53. Do not have permissions or database does not exist. Thanks in advance for any help.,
View Replies !
Link SQL Server To MS Access Frontend
Hi All, I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it? Regards: Prathmesh
View Replies !
Link Server To An Access Database
Hello, I am getting an error trying to select data from a Linked Server that is connected to a secured MS Access database. I have entered the login and password for an admin in the sytem but I am still getting the error below. It seems that all of the examples shown on the Online help are for unsecured databases. Can anyone help me with this issue? Thanks Will SELECT * FROM TMS_SECURED...Assignments OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TMS_SECURED" returned message "You do not have the necessary permissions to use the '\FileSrv-05ProductionTMSTMS-DATA.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TMS_SECURED".
View Replies !
Problem Updating Link Between MS Access And SQL Server
I'm trying to create a from a SQL Server 2000 Table to a MSAccess 2000 table. Using DDL, I have created a table in SQL Server but the table does not exist in MS Access Yet. I want to create a linked table in MS Access. This works great. Next I want to link to it. I use the following code. With tbl .Name = strTableName Set .ParentCatalog = cat .Properties("Jet OLEDB:Link Provider String") = strCnn .Properties("Jet OLEDB:Remote Table Name") = strTableName .Properties("Jet OLEDB:Create Link") = True End With cat.Tables.Append tbl <-- ERROR HAPPENS HERE cat.Tables.Refresh The code blows on the Append Line. Could not find installable ISAM. The connection string is "Provider=SQLOLEDB; Data Source=DIMENSION4100; Initial Catalog=DIGI; Integrated Security=SSPI; Persist Security Info=False;" This is the same string that was used to create the table in SQL Server. Does anyone have any suggestions. Note the table does not exist in Access when this code is run. Is this causing the problem. Do I first need to create a table in Access and then link it? Thanks in advance for your help!
View Replies !
How To Link SQL Server Tables To Access Or Oracle
Hi All My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries. I am thinking of linking server but I am not sure about that. Is someone can tell me what to do and explain me how. Thanks in advance Sincerely. David
View Replies !
Correcting Server Name In Access Link To SQL Database
I'm not sure if this is the proper place to post this question but thought I would start here. I have an Access link to live SQL data. We have since migrated to a new physical server and new logical server name. My question then is, how can I edit the pre-existing Access link to the live SQL data/database and make changes so that the new server is taken into consideration? Thanks in advance.
View Replies !
Upsized ACCESS Database To SQL Server 2000 With AS/400 Link
I have upsized an Access database into SQL Server and manged to getthe data in place ok. The wizard created an Access Project which Ihave started to modify.However, I am also trying to get data from an AS/400. Specifically fora currency/exchange rate file. I only want ot copy the records sincethe last copy otherwise there are a vast number of records.I have created a linked server to the AS/400 within SQL Server, andcreated a view, within my specific database which allows me to look atall the data in the table.However, my problem is that I want a VB routine, in the AccessProject, which lets me copy out specic records from the view into atable within the SQL Server database. Any clues?????My oroginal code used a link to the AS/400 and the folowing code:-s = "INSERT into zacjdf00 "s = s + "select CJCNCD, CJD5E9, CJCUCD, CJD5FF, CJD5FG, CJD5FH,CJD5FI, CJD5FJ, CJD5FK "s = s + "from XGHLDTM_ZACJDF00 "s = s + "where "s = s + "CJD5E9 = '" & group & "'"DoCmd.RunSQL (s)group is preset in the VB code.
View Replies !
Cannot Import/link Data From DBase With SQL Server DTS Nor Access Anymore..
Hi,SQL Server 2000 SP3Windos 2000 Server SP4I have a DTS package that imports data from a dBase IV databse withfiles located in two folders (dBF1 and dBF2). I use a transform datatask to transform the data.They were running properly, but last week we installed W2K SP4, andnow the transform task for files from dBF2 are not working properly.I have two tranform tasks to extract data from files in dBF2 folder.If I double click to open the transform data task of either of them,Enterprise Manager crashes with the errrormmc.exe applicatio ErrorThe instruction "xxxx" referenced memory at "xxx". The memory couldnot be read.Althoug the transform task for one of the files will run, the otherwill not run giving the messageError Source: DTS packageCatastrophic failureAlso, I have an Access database that has links to the same dBasefiles. For files from dBF2 folder, I'm able to see the data from oneof the files, but if I double click to see the data from the other,access crashes with no specific error message. Nothing has changed ondBase related files (permission wise).The transform tasks to extract data from the other dBase folder (dBF1)files are working fine, and data is accesible from Access.Any advice how to tackle this one?
View Replies !
ODBC Error When Running Stored Proc To Pull Sql2005 Data Into Access 97
I have a stored procedure the runs a query and loads data into a access 97 table. We recently upgraded the sql server database to SQL2005. If I check my odbc connection when creating it is test successful. however when I run the query in access to kick off the stored proc it gives me a ODBC Connection failure message. Neither the stored procedure nor the access database have changed. I am able to run the stored proc successfully from sql query analyzer. is there are compatability issue with Access 97 (they refuse to upgrade) and SQL2005??
View Replies !
ADO - Cannot Access The Return Parameter Of A Stored Procedure On SQL Server 2005
Hello, I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field. To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using. try{ pCmd.CreateInstance((__uuidof(Command))); pCmd->ActiveConnection = m_pConnection; pCmd->CommandType = adCmdStoredProc; pCmd->CommandText = _bstr_t("dbo.GetFlightPlan"); ............................ code here ........................................ pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin)); VARIANT returnVal; returnVal.vt = VT_I2; returnVal.intVal = NULL; pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal)); //Get Return value by executing the command //The return value should be the DB unique ID. pCmd->Execute(NULL, NULL, adCmdStoredProc); int uniqueId = returnVal.intVal; //pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc); //GetFieldValue(0,pRst,uniqueId); printf("The DB unique ID is: %i",uniqueId); return uniqueId; } Cheers, Seth
View Replies !
Convert Access Query Dateserial Function Into Stored Procedure
Hi, Below is a access query which is using dateserial function. can you please convert the below query into equivalent TSQL using function and calling that function into the stored procedure. "select DateSerial([cy],IIf([mois_activity_plan]="Janvier",1,Null,1) from Table" datatype for "cy" and "mois_activity_plan" are "text" in the table. Dateserial function has the format:Dateserial(year,month,day) please help. Many thanks, rakesh
View Replies !
Control Access To SQL Server 2005 Database Only Through Stored Procedures Issue
Hello, I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time. I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it. When I execute stored procedure defined in dbo with query against dbo tables, it works as expected. However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain. I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp. The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp. I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures. Thanks, IT
View Replies !
Problem With Link Server From Sql 2005 To Sql 2005 - Openquery Doesnt Works
I have created a linked server on which following query works fine. EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE] but the same statement executed with openquery select * from openquery([remote],'select top 10 * from dummyObjects') returns following error. Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
View Replies !
SQL Server 2000 Link Server Access
I created a linked server (PLNK Oracle 9i), and run the query: Insert into MSTBL1 select * from openquery(PLNK,'select * from v_ora_view1); The above query run success when I issue the query in Query Analyzer. But it got access denide after I copy it to a job. Any idea?
View Replies !
Calling An SQL Server 2005 Stored Procedure Within Microsoft Access And Reading Values
Goodday. I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way Code Block Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _ "Initial Catalog='DBName';Integrated Security='SSPI';" cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "sp_DefaultEntityData" cmd.CommandType = adCmdStoredProc Set rst = cmd.Execute rst.MoveFirst Do While Not rst.EOF lstEntity.AddItem (rst.Fields(0)), 0 'lstEntity.AddItem (rst.Fields(1)), 1 rst.MoveNext Loop The Stored Proc is as follow: Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_DefaultEntityData] AS BEGIN SET NOCOUNT ON; SELECT tblEntities.[Name], tblEntities.PrimaryKey FROM tblEntities ORDER BY tblEntities.PrimaryKey; END The table contains 24 entries As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data. I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work. Here are my Q's: 1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment? 2) How do I read the values into the listbox, without displaying the primary key in die box? Thank you in advance! Any help is very much appreciated.
View Replies !
Link Server In 2005
Hi, I have two database one on local system and another on web both are in MS SQL 2005 Now I want to link both of them by using Link server but the System procedure ask of oledb I am not understanding what to do. I searched the web but ..... So plz tell me some link or code ........ thanks
View Replies !
Link Server In MS SQL 2005
Hi, I have two database.and i want to transfer datafrom one to another so for that I need Link server. both the database are in SQL 2005 i have altered the store procedure and login but it give connection time out error. will u give me some link to understand and implement the same in my app. EXEC master.dbo.sp_addlinkedserver @server = N'vidyalink', @srvproduct=N'SqlServer', @provider=N'SQLNCLI', @datasrc=N'myb_29074' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'vidyalink',@useself=N'False',@locallogin=NULL,@rmtuser=N'mybe',@rmtpassword='########' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'vidyalink',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'use remote collation', @optvalue=N'true' Labour never goes waste.
View Replies !
ODBC Bcp_init Run Time Dynamic Link Library
Hi, I'm having problems successfully executing bcp_init through a function pointer retrieved at run time from sqlncli.dll. The call works when the library is statically linked. Can someone please point out the problem? The commented out code in green executes successfully when the library is statically linked. Code marked in red fails. SQLGetDiagRecW does not provide any useful information. Any help is appreciated. Regards, Venkat typedef SQLRETURN (__stdcall *SQLALLOCHANDLEPROC) ( SQLSMALLINT HandleType, SQLHANDLE InputHandle, SQLHANDLE OutputHandle ); /// typedef native function SQLSetEnvAttr typedef SQLRETURN (__stdcall *SQLSETENVATTRPROC) ( SQLHANDLE EnvironmentHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER StringLength ); /// typedef native function SQLSetConnectAttrW typedef SQLRETURN ( __stdcall * SQLSETCONNECTATTRPROCW ) ( SQLHANDLE ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength ); typedef SQLRETURN ( __stdcall *SQLCONNECTW ) ( SQLHANDLE ConnectionHandle, SQLWCHAR * ServerName, SQLSMALLINT NameLength1, SQLWCHAR * UserName, SQLSMALLINT NameLength2, SQLWCHAR * Authentication, SQLSMALLINT NameLength3 ); typedef RETCODE ( __stdcall *BCPINIT ) ( SQLHANDLE ConnectionHandle, const WCHAR* TableName, const WCHAR* DataFileNae, const WCHAR* ErrorFileName, SQLINTEGER Direction ); main() { // Load library and initialize function pointers. HMODULE hModule = ::LoadLibrary(_T("SQLNCLI.dll")); SQLALLOCHANDLEPROC fnAlloc = (SQLALLOCHANDLEPROC)::GetProcAddress( hModule, _T("SQLAllocHandle") ); SQLSETENVATTRPROC fnEnvAttSet = (SQLSETENVATTRPROC)::GetProcAddress( hModule, _T("SQLSetEnvAttr") ); SQLSETCONNECTATTRPROCW fnConnectAttSet = (SQLSETCONNECTATTRPROCW)::GetProcAddress( hModule, _T("SQLSetConnectAttrW") );; SQLCONNECTW fnConnectProc = (SQLCONNECTW)::GetProcAddress( hModule, _T("SQLConnectW") ); BCPINIT fnBCPInit = (BCPINIT)::GetProcAddress( hModule, _T("bcp_initW") ); // Allocate ODBC handles. SQLHENV m_SQLEnvironment; SQLHDBC m_SQLConnection; SQLHSTMT m_SQLStatement; SQLRETURN iReturn; iReturn = fnAlloc( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment); //iReturn = SQLAllocHandle( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment); iReturn = fnEnvAttSet(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0); //iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0); iReturn = fnAlloc(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection); //iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection); // Set up bulk copy. int nType = SQL_COPT_SS_BCP; SQLPOINTER pvAttribute = (SQLPOINTER)SQL_BCP_ON; SQLINTEGER nValue = SQL_IS_INTEGER; iReturn = fnConnectAttSet(m_SQLConnection, nType, pvAttribute, nValue ); //iReturn = SQLSetConnectAttrW(m_SQLConnection, nType, pvAttribute, nValue ); // Connect. BSTR ODBCSource = :: SysAllocString(L"SQL2005"); BSTR UserName = :: SysAllocString(L"sa"); BSTR Password = :: SysAllocString(L"xxx"); iReturn = fnConnectProc(m_SQLConnection, ODBCSource,7,UserName,2,Password,3); //iReturn = SQLConnectW(m_SQLConnection, ODBCSource,7,UserName,2,Password,3); // Init bulk copy. BSTR sTableName = :: SysAllocString(L"test"); BSTR sErrorFile = :: SysAllocString(L"c:\test4.txt"); int nDirection = DB_IN; RETCODE nRet = fnBCPInit( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 0 //RETCODE nRet = bcp_initW( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 1 }
View Replies !
How Can I Use Create Or Alter Statements With ODBC And Microsoft Access ODBC Driver (*mdb)?
Hi, I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)". When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE. How can I use DDL statements via ODBC? I would appreciate if you could help me to use ODBC for that - no OLE, no ADO. Thanks for help! Regards, Stefan D.
View Replies !
How To Link Two SQL Server-2005 Databases?
HI, I need to link two SQL-2005 server's databases. And both databases are on different machine and on same network. After linking I have to create Views. In other words : I need to create a View of One -Database in to Second Database . For this Do I need to link first? please let me know. ASAP.
View Replies !
FoxPro ODBC Link Servers And The Dreaded 7399 Error
(SQL 2K SP3a on W2K. VFP 6 data lives on W2K. GIGABIT BACKBONE, gigcards on the data servers)I'm sure anyone who attempts to retrieve or alter FP/VFP data througha SQL link server will commiserate.I'm getting sick of restarting my instance after attempting a simpleOPENQUERY statement on a VFP 6.0 DBC link server:select * from openquery(ofsdbc,'select onestinkinfield from mytablewhere onestinkinfield = whatever')and being rewarded withServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'MSDASQL' reported an error.[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed][OLE/DB provider returned message: [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed][OLE/DB provider returned message: [Microsoft][ODBC Visual FoxProDriver]Cannot open file \mydevboxmydbpathmyvfpdata.dbc.]OLE DB error trace [OLE/DB Provider 'MSDASQL'IDBInitialize::Initialize returned 0x80004005: ].The only remedy is to restart the SQL services. The link server thenbehaves for a variable number of queries, and then we do the danceagain.Come on, MS, this is unacceptable. VFP is your own technology and youcan't make SQL play nice with it? Pshaw. This gets old when you'refive or six layers into a BizTalk process trace. ("When you're up toyour arse in alligators, it's tough to remember that the objective wasto drain the swamp.")I RDP to the SQL box and can ping the datasource server all day long.I can open the folder in which the DBC and data live all day long. Ican run the DBC in VFP, FROM THE SQL BOX, all day long. I just can'tget a link server to live more than one or two days, or sometimes morethan one or two queries.NB I've already been through PSS for this garbage, and was essentiallytold that MS no longer supports ODBC connections to VFP data from SQLlink servers. Furthermore, they don't support inserts, updates ordeletes through same with the VFP OLEDB provider.Any and all war stories, tonics, suggestions, rants, corrections,upbraids, horse laughs, and snickers appreciated in reply.
View Replies !
Datareader Source Connects To No Longer Existing ODBC-link
I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ???? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).
View Replies !
|
TRACKER
