We recently upgraded a SQL 7.0 SP2 database to SQL 2000 SP2. The web server that connects to both databases (we have 2 up that we can switch back and forth) is Win2K with the SQL Server ODBC driver of 2000.80.194.00 (I'm thinking this came w/ W2K) But I think I installed the 7.0 client connectivity on the web server.
I have a few questions:
1. Any reason why this would happen with the SQL upgrade?
2. What is the ideal situation for client/server ODBC drivers / MDAC client.. If I have a SQL 2000 SP2 server do I install SQL 2000 client on the web server and also the service pack on the web server?
Thanks and please ask if you need more info..
-Kelly
I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.
We've run into a problem using SQLDescribeParam with the SQL Server ODBC driver (or SQL Native Client driver). The basic sequence that shows the issue is this:
SQLPrepare(select * from foo where col1 = ?) SQLDescribeParam(1) SQLBindParameter(1) SQLFreeStmt(SQL_CLOSE) /* NOT RESET_PARAMS */ SQLPrepare(select * from foo where col1 = ? and col2 = ?) SQLDescribeParam(1) SQLDescribeParam(2) ==> returns "Invalid Descriptor Index"
If I leave out the SQLBindParameter, then the last SQLDescribeParam works just fine. Also, if I make the SQLFreeStmt use SQL_RESET_PARAMS then it works fine. However, I don't have direct control over the ODBC code being used, so I wanted to see if this known behavior. I've tried a couple of other ODBC drivers that support SQLDescribeParam and they work fine with the above sequence.
I'm getting this "invalid descriptor index" exception while trying to fetch a record from the table. The query is "select * from <tablename> where <columnname> = 'xyz'". The column name is correct and indeed a record with 'xyz' value exists. The record is getting fetched too...! But I'm having this particular error while trying to retrieve a couple of fields with rs.getString(). The order of columns in the table is same as the order in which I'm retrieving them. And I'm not facing any problem retrieving another field which has width of 200 characters. I'll be very grateful indeed if someone can help me out of this particular problem...
there are two types of indexes in microsoft sql servera) clusteredb) non clusteredQuestioncan u create a primary key which is non clustered ?If yes what is the syntax ?Questioncan u create a compound primary key and if yes what is the syntax ?Please excuse me from asking basic questions ?
I have an ODBC 3.0 Application on Windows NT Server maintaining aconnection to a SQL Server 2000 database on the same machine. When anerror occurs, I'd like to be able to determine whether the error isserious enough that the database connection has been lost, so I canhave the application try to reconnect automatically. My understandingis that any error of severity 20 or above includes a broken connection,but I'm having difficulty retrieving the error severity through ODBC.So my first question is, how do I retrieve the error severity throughODBC?Also, as a test, I stopped the database while my application is stillrunning, and the first error I received had a native error code of 55.There is no 55 error in the sysmessages table, so I'm confused as towhat the severity of the error is. Can anyone shed some light on why Iwould be receiving an error code that is not in the sysmessages table,and where I might be able to find more information about the error?Thanks in advance,Abram
Hi, I run a nightly dbcc checktable on order_header and today I see this error occurs. Table error: Table 'ORDER_HEADER' (ID 203147769). Missing or invalid key in index 'PK_ORDER_HEADER' (ID 4) for the row: Server: Msg 8955, Level 16, State 1, Line 1 1. I have no idea what caused it? 2. How do I fixed this online as the application is 24/7? 3. I did try to dbcc reindex but it failed reporting that can't create index because of duplicate in primary index key????
We have such problems: DBCC results for 'TRAMES'. Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:34) identified by (RID = (1:3893993:34) ) has index values (PO_ID = 80123). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:48) identified by (RID = (1:3893993:48) ) has index values (PO_ID = 80095). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:79) identified by (RID = (1:3893993:79) ) has index values (PO_ID = 80123). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:92) identified by (RID = (1:3893993:92) ) has index values (PO_ID = 80095). There are 1703901 rows in 18180 pages for object 'TRAMES'. CHECKDB found 0 allocation errors and 4 consistency errors in table 'TRAMES' (object ID 1573580644).
And really I don't know what can I do to prevent such problems. I know how to fix it with a dbreindex, but it blocks the production during several minutes, and we have to restart the whole plant...
we are using SQL 2000 standard edition 8.00.2039 (with SP4)
Hello experts. Excuse my SQL newbie status. I was writing a stored procedure to crunch some data against an MS SQL 2000 database. I didn't entirely know what I was doing so I was using Crystal Reports 11 as an "application layer" pulling against a stored procedure. Well I guess ODBC connections don't support temp table output from stored procedures, or at least I don't know how to do it.
So I've rewritten my stored procedure so it inputs and outputs data using BCP, rather than just sending a simple select query.
My problem is that I cannot get CR to add the stored procedure back in to the report. Everytime I try I get an 'ODBC 24000 error - Invalid Cursor State.'
Where is this current set of records? How do I find it and close it? I've recreated my ODBC connection, every related table and SP to no avail. This is my crusial question.
Less crucial but helpful: How do I write an SP so it can be called from a report program like CR and will return its result set to the application? What's a good, simple "application layer" I can use if I'm doing all the logic in native SQL stored procedures? ASP.NET looks like a pain in the ass, and SQL Reporting Services requires Visual Studio .NET 2003. Is Perl my answer, since I can build web interfaces and forms in my sleep?
I am trying to set up a ODBC connection to a remote database using the ODBC data source administrator. I am getting an error message complaining of an invalid instance(). The database I am trying to connect to does have a default instance and a named instance. Do I need to specify the instance name somewhere in the connection settings? I tried specifying the server name as <Remote computer IP Address>/<Instance Name> but this didn't work. Any ideas?
Hello All,I am getting the following error when attemping to open a table inSQL2kSP3a.________________________________________SQL Server Enterprise ManagerDatabase Server: Microsoft SQL ServerVersion: 08.00.0760Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format_________________________________________I cannot find it in sysmessages, or on the web.Any ideas about how to resolve this? And how it occured...Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to connect to a tab-delimited text file using the ODBC data access. I set it up using Administrative Tools - Data Sources (ODBC) from the start menu, and clicked the 'Guess' button under Define Format.
It looks OK, but if I go back to Define in Administrative Tools and try to review it, I get a similar error message:
Ini File (or Registry) C:Program FilesCIMSschema.ini is corrupt. Section: cims_output_data.txt, Key: Col1.
Has anyone seen this before? There is nothing on MSDN as far as I could find. I read on another forum that if you replace the label 'Col1' with 'Col' the problem is solved but it still reports that the schema is corrupt when I try to open it in Administrative Tools.
Hi all,Here is a brief description of a problem I encountered, and how Ifound a work around after 3 long days.I have a VB6 app that uses ADO and ODBC to get communicate with SQLserver 2000 (sp3, running in win2003).Everything was running great for a few weeks, but one day an updatestatement that used to work just stopped working. It was a simpleupdate of 1 field in a table (about 30 columns, about 20k records).SQL server acutally hung while it waited for a response that nevercame, and everyone else on the network was also locked out of sqlserver. Everyone had to do a ctrl alt del to crash the programme.Steps I took...Rebuilt the database. Same.Restored backup. Same.Moved database to another server. Same.Checked for viruses, that no updates had happened, memory checks andso on.Still no joy.Someone then recommended tinkering with the indexes of the table inquestion. Which I did.I added indexes, removed them, and eventuall found a combination thatworked. And this is it.I removed the primary key, and replaced it with a clustered index.That's it. And now it all appears to be running OK.WHY WHY WHY?I just don't get it.Have I found a bug in sql server or odbc or ado? Is it a known issue?What is the impact of what I have done?Thanks in advance for your comments, and I hope this solution savessomeone else 3 days of hell!Tim
SSIS script task fail with error message-SSIS script task fail error message Error: Cannot load Counter Name data because an invalid index '' was read from the registry.
Hi all, We are getting "New request is not allowed to start because it should come with valid transaction descriptor" when using transactions against SQL 2005. As I understand it, it's a bug that occurs after some statement has terminated against the database; see http://lab.msdn.microsoft.com/productfeedback/ViewWorkaround.aspx?FeedbackID=FDBK46530#1
Now, I wonder if there is a hotfix coming soon on this?
Dear all,On Win2000 server with SP3, I am trying to access a SQL Server 7.0database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO2.7. In SQL Server Enterprise Manager, there is a login named "Tester".In its property window, NO "Server Roles" was assigned but its"Database Access" was set to "TestDB". This login was also made as theuser of "TestDB" with "public", "db_datareader" and "db_datawriter"selected as its "Database role membership". All the tables I am tryingto access in "TestDB" were created under "Tester".My code is like:Set conn = New ADODB.Connectionconn.Open "DSN=TestDSN;UID=Tester;PWD=test"Set cmd = New ADODB.Commandcmd.ActiveConnection = conncmd.CommandText = SQLset rs = cmd.Execute()If I set the SQL to something like "SELECT * FROM tbl_test", I alwaysget an error of "-2147217865" saying "[Microsoft][ODBC SQL ServerDriver][SQL Server] Invalid object name tbl_test". If I set the SQL to"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyoneplease kindly advise why the first SQL is not working? Or in otherwords, why must I prefix the table name with its owner while the DBconnection is already made under that owner name? Thanks in advance.Tracy
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated) Version 11.0.50727.1 RTMREL Microsoft .NET Framework Version 4.5.51650
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated) Version 11.0.50727.1 RTMREL Microsoft .NET Framework Version 4.5.50938
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
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.
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for XP. However, when I try to add a new data source through ODBC Connection Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the SQL Native Client to not show up in the list of available ODBC data sources?
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Hi all,I am having trouble getting linked Oracle 9 server in MS SQL Server2005 Express to work properly. My machine is running Windows XP.The Microsoft and Oracle OLE DB Providers have problems dealing withOracle's Numeric Data Type, so I decided to use Microsoft's OLE DB forODBC Provider and an Oracle ODBC source. When using the Microsoft ODBCfor Oracle Driver in my ODBC source I have inconsistent behavior.Sometimes my queries are processed properly, then other times I get thefollowing errorOLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC driver for Oracle][Oracle]".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL"for linked server "ODBCBEAST".I have no idea why sometimes I can connect to the linked server with noproblems andwhy other times it performs like this. I'm not changing anything aboutthe system I can think of. When I use an Oracle client (PL/SQL) I haveabsolutely no problems connecting. TNSPING returns that the connectionis good.This is unacceptable so I decided to try my luck with the Oracle 10gODBC driver. However when I use this and perform an openquery selectagainst the linked server I get back only 11 rows, when I know that thedatabase has over 100 rows (in fact when using the Microsoft ODBCdriver and it works that's what I get). I figured maybe the buffersetting needed to be raised in the ODBC configuration so I took it from64000 to 600000 (a magnitude of 10) but I still get back only 11 rows.I'm at my wit's end.Any suggestions on resolving one or the other problem would be muchappreciated.Thanks much
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
