Accessing SQL2K Using MS ACCESS
May 3, 2007When I access SQL2K using ODBC (linked table manager), I am not able to display any data. The columns show #Deleted as their data values. How do I display data in ACCESS from SQL2K tables?
View 18 Replies
ADVERTISEMENT
Accessing Mirrored DBs On SQL2k5 (x86) From SQL2k
Sep 14, 2007Hi
I'm trying to select data from a mirrored database situated on a SQL2k5 server, via a linked server on SQL2k.
I can select the data fine using SQLOLEDB but it's not aware of the mirroring so if the DB fails-over it errors.
To get around this, I installed SQLNCLI.msi to install the SQL2k5 provider and created a linked server like this:
exec sp_addlinkedserver @server=N'TEST2',@srvproduct=N'SQLNCLI',@provider=N'SQLNCLI',@provstr=N'Server=server1;failover partner=server2;'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST2',@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='password'
Executing the statement: select * from test2.database.dbo.table
gives the following error:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'IsActive' (compile-time ordinal 2) of object '"database"."dbo"."table"' was reported to have changed. The exact nature of the change is unknown].
If i change the statement to
select * from openquery(test2,'select * from database.dbo.table')
It works. I don't really want to have to go and refactor all the SQL though!
I've seen similar posts related to Oracle and SQL2k5 64bit but they don't seem to be relevant to this situation.
Here's the version info:
2000 server (SP3):
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2005 mirrored servers (SP2 3054):
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I've also tried applying the following to the 2005 servers: sp2(3159) sp2(3161) but makes no difference.
Any ideas?
TIA
Restrict Access To DB On SQL2K
Nov 12, 2001Hi,
Is there a way to prevent new users from accessing a SQL2000 db (like to put it into DBO mode) while leaving current users finish their running jobs.
I tried Alter Database and sp_dboptions and both in SQL2000 requires to close all current users. It was working in SQL7 but not in 2000.
Thanks
AAA
Can Msdb From SQL2K Personal Edition Be Restored On SQL2K Standard?
Oct 12, 2007The company for which I work did not have a DBA until I started a few weeks ago. Whoever installed SQL2K used the wrong CD so they have been running Personal Edition on their servers. I have installed a new SQL2K standard instance and have restored everything except the jobs and DTS packages. Can the msdb from the Personal edition be restored to the standard instance?
View 3 Replies View RelatedDowngrade SQL2K Enterprise To SQL2K Standard
Mar 15, 2007Is it possible to downgrade SQL from Enterprise to Standard Edition, or do you have to remove the previous installation (uninstall) and reinstall. Meaning you would also have to restore all user databases? Thanks.
View 1 Replies View RelatedRestrict MS Access From Accessing SQL
Dec 7, 1999Is there any way of not allowing users with MS Access from using it to connect to a SQL 7 server? Basically, no connection to SQL through MS Access should be allowed. Any ideas?
View 2 Replies View RelatedAccessing MS Access When Running On Server Fails
Feb 5, 2008Hello:
I have a package which runs fine from the client but not the server (see error below). I am logged into the client with my domain account, and the package runs under a proxy using that same domain account (for now). The MS Access db I am trying to pull from is on a non-domain computer, and I have created a Y: mapped drive on both the client and server that access a share on that machine where the Access database lives--I created the mapped drive using the "different user" option, and used a local account on the non-domain computer to create the mapped drives.
On the server, when logged in as my domain account, I can use the y:MyDB.mdb you can see below in Start | Run to hit the Access database. Why can't the package also see this file when running on the server?
Code Snippet
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'y:Mydb.MDB' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error Error: 2008-02-05 02:35:56.26 Code: 0xC020801C Source: Cost Cost [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyDB" failed with error code 0xC0202009. There may be error messages posted before this with more informat... The package execution fa... The step failed.
Thanks,
Kayda
Accessing A Microsoft Access Database From Within Visual C++
Aug 14, 2006Hi there guys, I am currently trying to achieve a seemingly simple task in VC++ 2005. I have made a very simple form in Microsoft Access which I wish to serve as the beginnings of something greater. I created a db in MS Access named links.mdb containing on table-> Table1. Table1 contains 1 column, "Links", and i wish to read these strings into variables in my Visual C++ Windows Forms Application.
What I have done so far...
In Visual C++, I clicked on Data->Add new data source, and followed the wizard to add the microsoft access database to my application by the name, "linksDataSet". I can see the table in my left hand "Data Sources" pane in VC++. All I need to know is how to access my database from here so that I can read these strings stored in my table. Also, would be possible to schedule my application to log on to a http server and retrieve these links every time the application is executed? How would I go about doing this?
Thank you very much for your time
Regards
Linden.
Accessing Remotely Located SQL Server Via Access
May 18, 2007Hi,
I need the code to create a connection between the Sql server using ACCESS form.
Can I do this. If yes . can you help mw with it.
Currently I am accessing the server with remote desktop connection
Regards,
Jay
XP_CMDSHELL- Access Denied Accessing Remote Server
Jun 20, 2007I am using XP_CMDSHELL to run 2 Windows commands (from a SQL 2005 SP2 server) that access files on a remote server (called webserver). The two commands are:
1. cscript.exe \webserverwwwrootEur_SaveProductImages.vbs
2. dir \webserverwwwroot
Up until very recently this worked fine. Now I am getting Access Is Denied errors:
For #1:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
CScript Error: Loading script "\webserverwwwrootEur_SaveProductImages.vbs" failed (Access is denied. ).
For #2:
Access is denied.
This happens if the above commands are run through a job, or directly with Query Analyzer as SA.
I understand that XP_CMDSHELL commands are run in the security context of the SQL Server startup account. The SQL Server startup account is called SQLServer and this account exists on both webserver and the database server. On both servers the username & password are identical and the user is in the local Administrators group. The two servers are in a workgroup.
Things I have tried (with no effect):
1. Restarted the servers
2. Deleted and recreated the SQLServer login on both servers
3. Changed the startup account for SQL Server through the SQL Server Configuration Manager to another account and back to SQLServer.
The most revealing tests are these:
1. If I log into the database server myself using the SQLServer account I can run the above commands in a Command Prompt with no problem
2. I installed SQL 2005 Express SP2 on webserver using the same startup account (SQLServer). If I run the same XP_CMDSHELL commands as above but accessing files on the database server, the commands work!
So, an XP_CMDSHELL command run under SQL Express using the local SQLServer account to access a remote server works, but an XP_CMDSHELL command run under SQL Server using a local SQLServer account (that is supposedly identical to the same named account on the other server) does not work !!?!?!?
It is almost as if XP_CMDSHELL commands run under SQL Server are NOT using the SQLServer startup account, but using some other weaker account. How can I tell if this is true?
How can I get this back working (as it was a week ago). The only thing I can think that happened on the servers to cause this is the installation of Windows Updates.
Thanks
Problems With MOD-Function By Accessing MS-Access Database Via SQL In Delphi
Aug 23, 2005Hello,the following problem:I use Delphi 6 to access a MS Access Database.In short, the Delphi Code looks like this:ADOConnection1.Open;ADOQuery1.Close;ADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3]);ADOQuery1.Open;Label5.Caption:=ADOQuery1.Fiel*dByName('value4').A sString;Now I only want to select these rows, where the Integer value3 ends on1. So I need the Modulo-Function. I found in the Internet these twofunctions:MOD(x,y) und x % yBut I get an error if I want to use one.For example, If I changeADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3])+' AND MOD(value3,10) = 1;Delphi shows me a Syntax Error in this statement.Thx for help!
View 2 Replies View RelatedHow To Avoid Athentication Prompt When Accessing A Report Through URL Access Using Web Service
Nov 23, 2007Hi,
I am accessing my reports through one aspx page designed by me.
in that i am using webservice to render the report and
linking the url of the report to reportviewer
it is working fine
but for the first time it is asking username & password of the reporting server to render the report.
i am using Reporting Services 2000
plz help to avoid this user name & password prompt.
ODBC/OLEDB Failure Accessing MS Access Attachment Field In Query
Mar 12, 2008We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers)
DB contains a table with a field of type "Attachments" (which is new in MS Access 2007)
DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."
Error using OLEDB (using "RowSetViewer"):
================================
select * from Query1 gives the following error:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: €œNot implemented
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CRowset.cpp
Line: 616
select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] €œUnspecified error€?
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CCommand.cpp
Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
Thanks,
Joe
SQL Database In My Application With Windows Authentication Rejects Accessing After Deploying And Remote Access
Dec 13, 2007SQL Database in my application with windows authentication rejects accessing after deploying and remote access
1- My web applications databese access is windows authentication
2- In developing area there is no problem for accessing database
3- After deploying to another server there is problem accessing database:
Cannot open user default database. Login failed.Login failed for user 'BRCK231ASPNET'.
How can I solve this problem?
Thanks.
I Am Accessing Data Using Data Access Pages In IIS 7 To SQL Server 2005 Authentication Is Failing
Feb 5, 2007is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?
May 8, 2015we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Data Access :: Users Get Wrong Data Records - Cross Coupling When Accessing Files From MVC App
Aug 7, 2015I have an MVC asp.net application that stores many records in a table on sql server, in its own system. Â used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. Â one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. Â no effect; Â the user makes their own login id but that does nt matter either.
Excel To SQl2K
Nov 30, 2001Hi,I am having a problem in moving data from execl to sql2k.
The data looks like 16.70% , 12.5% etc and when I move it it changes to 0.16700000000000001 etc..
any idea what datatype I should use so that it don't changes it's mode...
Thanks in advance
Installing Sql2k
Apr 6, 2001Hi,
I am new to sql 2k.
Iam been asked to install sql2k on a new box .
What all things I should keep in mind when I go for the installation of sql2k?
Any / All suggestions appreciated.
Jai
From SQl7 To Sql2k
Apr 6, 2001Hi,
I have to move 2 fields from sql 7 to sql 2k .
the field is text.
feild a is text
field b is text.
I have to add field a and field b and move it to another field (say field c which is also text )in sql 2k.
How to go about it?
TIA.
Jsi
Timeout In SQL2k
Jun 23, 2001I moved my DB from SQL7.0/NT to SQL2k/WIN2k.
Everything was fine from the last few months but suddenly I start getting timeout errors.
I checked and rebuild all the indexes - there are no deadlocking at all - everything looks quite normal to me - I am all lost now as what to monitor and where to see for this timeout error messages.
Anyone has any idea as what is going on what should I check to see if that's the cause.
I also ran Profiler but there too nothing too long as to timeout.
My sql box is attached to IIS box and pager is set on IIS box.
The problem is not with IIS box cuz when I shift the applications to another SQL box everything runs fine and there is no timeout messages.
Any thoughts appreciated.
Restoring SQL2K% To SQL 2K - Possible?
Jan 23, 2007I have been sent a backup file from a supplier that I have been trying to restore. I have succesfully installed it on my SQL2K5 instance, however, we need to put it on our SQL Server in our DMZ . . . Which is running SQL2K . . . Supplier was informed of this and says that you can restore to a SQL2K DB as long as you use SQL Management Studio . . . Tried that and got an error message Saying That Restore Headeronly is terminating - Too many backup devices specified. . . only 64 are allowed. . . .Supplier suggests that its a SP4 issue . . . Installed SP4 and tried again . . . Got the Same Error again (SQL Server Error :3205)
Am I out of my gourd I am sure that restoring a SQL2K5 Backup to a SQL2K instance is not possible?
I should say that I am Running SQLExpress as my 2K5 Instance and MSDE as my 2K instance . . . Before I invest more time on this and trying it on a development server running Standard edition.
Thanks All
--
Regards
Tony The DBA
Sp_addlogin (SQL2K)
May 9, 2008
Hello,
I am trying to script out a new login in SQL 2000. I can script the login, but I am trying to assign the correct permissions, (e.g. db_datawriter, db_owner) and I am have trouble scripting the permissions. Here is a snippet of what I am looking to accomplish in my script. Remeber, this is 2000 not 2005...
-- This works
exec sp_addlogin @loginame = 'LoginName'
,@passwd = 'Password'
,@defdb = 'Database'
go
-- This doesn't. I am not even sure if this is what I need...
exec sp_addrolemember @rolename = 'AdminCVB'
,@membername = 'db_owner'
go
SQL2K Login Issue
Aug 31, 2004hi,
i'm hoping somone can shed some light on why this isn't working. basically i have a webform which that passes data back to a database (SQL Server 2000) and uses Windows authentication to perform the UPDATE, INSERT, whatever.
i've defined a role within the database, specifically for the application. for my tests, i've added a global security group from my Windows domain to the logins on the SQL instance. this group has been added to the custom role in the application database.
since i'm a member of this global security group, i was under the impression that my credentials would pass through and everything would be ok, however, it's not recognizing my login - or others in the group as well.
the application web (in IIS5.0) has been set to Windows Authentication, and i've flagged the impersonate=true attribute in the app's web.config file.
lastly, my connection string for the sqlconnection object grabs my user name (or the current user for that matter) in the format of DOMAINaccount. for the sake of argument, i was able to successfully connect using a custom SQL login that was assigned to the custom role.
the error that's returned specifically states that the "Login failed for DOMAINaccount"
any thoughts on what's up here? i guess i'm not understanding SQL Server security to well. thanks in advance.
Register SQL2k From SQL7
Apr 6, 2001Does any body know if it is possible to register SQL 2000 server from SQL v7.0 enterprise manager.
Thanks
SQL7/SQL2K On Same Box: Compatable As 6.5/7.0?
Jan 4, 2001I don't have any extra machines, so I'm thinking of evaluating SQL2K on one of my existing boxes with SQL 7 already installed. My experience is that SQL 6.5 co-existed extraordinarily well with SQL 7. Does the same apply to the SQL 7 / SQL 2K combination? Thanks!
View 1 Replies View RelatedSQL7/SQL2K On Same Box: Compatable As 6.5/7.0?
Jan 4, 2001I don't have any extra machines, so I'm thinking of evaluating SQL2K on one of my existing boxes with SQL 7 already installed. My experience is that SQL 6.5 co-existed extraordinarily well with SQL 7. Does the same apply to the SQL 7 / SQL 2K combination? Thanks!
View 3 Replies View RelatedTransaction Logs In Sql2k
May 24, 2003I am new to sql2k
previously i used to work with sql7
how do i clear the transaction logs in sql2k
pavan
Encryption/decryption Sql2k
Sep 17, 2003I am planning to use XP_CRYPT for encrypting and decrypting cc#'s, passwords etc., at database level. Any suggestions or experiences on this. More info about this product at
http://www.activecrypt.com/faq.htm
SQL2K Bind Failed
Jun 11, 2004I tried to install SQL Server 2K on a W2K server running IIS and SSL. The SQL installation failed until I deleted the certs from my system. Now, the SQL logs say "SuperSocket Info: Bind failed on TCP port 1433". I have tried everything on the MS KB, TechNet, and MSDN sites that I could find. I have also uninstalled IIS and Certificate Services from the server. Since there are no certs left, I suspect that SQL Server is getting in it's own way on startup. Does anyone know of a way to trap/log this kind of conflict?
View 14 Replies View RelatedSQL2K: Deadlock On Lock
Sep 10, 2007Hello,
I have scheduled a job using Job Agent in SQL2K (Management). Every once in a while i get an error when i view job history:
Executed as user: xxx. Transaction (Process ID 53) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
The process ID changes for every error. Sometimes its 51, 56, or 54 as well.
I have checked several times, but there is no other process that runs at that time. I even rescheduled it for a different time, but even then it fails about once every week or two.
How can i check what is the other procedure it is deadlocking with, if it is? OR what exactly is the problem, if it is not with any other process?
Any help and/or guidance is much appreciated.
Thanks,
J
EM Won't Connect Locally On SQL2k Box
Jan 24, 2002Here is a strange one. Have a sql2k box that I upgraded from 6.5 back. Conversion went fine, everything was ok with the sql install. Created and scheduled maintenance jobs while logged on to the box. A few days after the conversion, Enterprise Manager cannot register the server(error message is 'Data source name not found and connection libraries do not exist'). This inablilty to register the server only occurs when working on the server itself. You can register the server while connecting from another box with EM.
So, do some checking and find that the box itself does not recognize SQLSRV32.dll (etc) as even existing on the box BUT IT DOES EXIST in the normal places, it was a working install just a few days prior. As far as the box is concerned, the network libraries do not exist. Therefore, cannot register the server locally, cannot create a Data Source using the SQLSRV32 driver, cannot start the Sql Server Agent(so no scheduled back ups, etc).
Went so far as to re install the connectivity and client tools, which did not fix the issue.
Does anyone have any ideas?
Installing A SQL2K Cluster On W2K
Jan 26, 2002I am trying to configure a 2 node SQL cluster. MSCS is configured and is failing over correctly, however when I start the installation of SQL Server, and specify the IP address of the virtual server and hit next, the installation hangs, and stops responding. I have configured MS DTC and verified that it shows up in the cluster administrator. I am not sure what else to do, please help.
View 2 Replies View Related