Hello All, I am getting the following error when attemping to open a table in SQL2kSP3a. ________________________________________ SQL Server Enterprise Manager Database Server: Microsoft SQL Server Version: 08.00.0760 Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format...more >>

Hi I have a "password" field in a Table. How should I define it, that its output is showed like "******"? Thanks!...more >>

Hi all, I would like to perform an INSERT INTO LINKEDSVR.dbo.xyz.abc SELECT ... FROM dbo.dfg where LINKEDSVR is a linked server on another machine. Both servers are running SQLServer 2000 and have the DTC running. When I run this batch from QueryAnalyzer without explicitly using t...more >>

Hi, We are in the process of selecting a database for a data warehouse type application. I want to get a feel for how big can a SQL Server database get. As per Microsoft, it can be multiple terabytes. Can you tell me What is the biggest size SQL server database you manage? (I understand big i...more >>

Hi I have a table that currently has 466 columns and about 700,000 records. Adding a new DEFAULT column to this table takes a long time. It it a lot faster to recreate the table with the new columns and then copy all of the data across. As far as I am aware when you add a DEFAULT column t...more >>

We have an application group that wants to pull date from SQL Server and = write it to text file on the server. They want the file format to be = 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a = second set...and so on. The '12100' is fixed, but the rest of the file = na...more >>

Hi, I'm no where close to a SQL Server expert but we're using it as a back end to another application. I lost a PC that had MS SQL Server 2000. I was able to recover the .MDF and ..LDF files but nothing else. How can I restore these files to another SQL Server installation on another PC. W...more >>

Newbie here...looking for help with a NOT EXISTS query or suggestions for a better method. I have 2 linked tables: CUSTOMERS and ADDRESSES common fields are CUS_NO and ADR_CD I need to find records where an address code (ADR_CD) entered into CUSTOMERS does not have that same ADR_CD existing ...more >>

On this site: http://www.sommarskog.se/dynamic_sql.html I have found an example how to use cursor with dynamic SQL: DECLARE @my_cur CURSOR EXEC sp_executesql N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN @my_cur', N'@my_cur cursor OUTPUT', @my_cur OUTPU...more >>

Hello -- We have annual values for several 'MeasName': Capital expenditure increment Growth rate Subscribers The table has these fields: Year MeasName MeasValue We want the result of the crosstab to look like: MeasName 2005 2006 2007 2008 2009 ------------------...more >>

I am trying to figure out the syntax for a query that will essentially give me the Percentage each of my areas contributes to the Whole. I know this can be achieved by multiple queries but I would like to keep it intact as one single query if possible. For Example I have the following data se...more >>

I am using SQL Server 2000. I am getting the following error when executing the following query. The query joins a view [Server2].dbx.dbo.vwreports that resides on a linked server. I can sort on fields on D table or R view individually, but some fields take a long time to sort even they are on...more >>

Hello: We have a a two tier application connecting to sql server through remoting. The business objects are single call objects. We are opening and closing connections using ADO.NET command object. All "ExecuteNonQuery" are closed in finally blocks. We are running out of sql connections because...more >>

I am looking into work-arounds for what seems to be a flaw, or "undocumented feature" of SQL Server replication and Instead of Delete triggers not playing together. It seems that if you want to use replication then you cannot use Instead Of triggers as it prohibits the replication triggers from...more >>

Env: SQL Server 2000 on in WIN NT 5.x Job: import mutiple flat files into several tables daily. Catch: one or two of the several flat files might be empty. First thought/test: Use [first row as fields] option for the import process. Problem, DTS can't complete (as a package). As an alterna...more >>

I want to create a stored procedure that will merge columns from two rows that contain duplicated contacts. I have can easily identify the duplicates and extract the UniqueIDs as parameters, but I can't figure out how to construct the actual update SQL. @KeeperID int, @DupeID int Update...more >>

I'm attempting to modify some Crosstab generating code, and I need some advice/examples. Currently, the code uses a single string variable to store the dynamically generated query (www.johnmacintyre.ca). The problem is that I am trying to pivot biological taxonomy information, and may end up ...more >>

I work with databases and some of my backup files are 30-100 gigs in size. I currently use the FastCopy utility to copy files from one server to another. (This is needed when I need to transfer a DB to a different server). FastCopy says its 30 to 50% faster than Windows file copy. I read som...more >>

Hi all, After merged two partitions into one (C:\ and D:\ into one C:\) by using Partition Magic, I can't start SQL Server 2000 which was previously installed in both C:\Program Files\Microsoft SQL Server\80 and D:\Program Files\Microsoft SQL Server\MSSQL The original files in D:\ (every th...more >>

PREDICATES Used as a clause. A. What does PREDICATES mean? B. What does it mean when used in a where clause? I checked BOL (Glossary) but get no explanation there. Thanks Jay ...more >>

Hey...newbie question: I've got three columns in my database, the third of which is blank right now, and I need it to equal the value of column one minus column two. While I can accomplish this in the .aspx page with a subroutine, I want to do it in SQL Server so I can simply read the data in ...more >>

We have a database on a shared SQL Server stored on our ISP's server. I can connect to this via Enterprise Manager from our office, but when I try the same from Enterprise Manager on my PC at home I get the following message: A connection could not be established to MFSERVER. Reason: SQL ...more >>

Hi, I am executing a call to "sp_helptext" from a web service, in order to obtain the text of a stored procedure. The call itself seems to be ok but eventually it throws an exception saying that the specified object does not exist in the database - which is a false statement, i've checked care...more >>

I need to create a relationship between a local table and tables on a linked server. I used the design table wizard and selected the relationship property wizard. In the reslationship property wizard, the tables that I need to get the keys from in the linked server do not show up. Is there a...more >>

I am working with a report generator that is based on SQL Server 2000 and uses ASP as the UI. Basically we have a set of reports that end users can execute through a web browser. In general the model works fine, but we are running into some scaling issues. What I'm trying to determine is, what...more >>

I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED Secondly, what is the optimum way to determine which action fired a...more >>

Hopefully someone will have some ideas on how to do this. I'm officially stumped. I have two entities to join. Simplified descriptions follow: The first has names and addresses (vwPersonAddress) keyed by PersonID (it is actually a view on two tables, but it works exactly as I want it to...more >>

I urgently need tom use SP3a upgrade the instance of SQLServer200 MSDE runing on my local machine but I am having problems in doing so. My first problem is that when I start the set up procedure I get the message "A strong SA password is required for security reasons. Please use SAPWD switch...more >>

Good day. I was able to connect to a database server using SQL Server Enterprise Manager. The Server name specified on the tree is JOMARGON(Windows NT). But no server was detected using either Visual Studio .NET and SQL Server's query analyzer. I highlighted one database (master) on the SQL Se...more >>

i am having trouble the with the line below: SELECT AVG(CAST(RATE1 AS Decimal(9, 2))) AS Expr1 i am trying to do an average on the column "rate1" and want only 2 decimal points but the syntax i wrote doesn't do it. is there something i am missing or doing wrong? when i run the above s...more >>

Is it possible to obtain a count of the number of rows that would be returned based on the following query. select tableA.*, tableB.fieldA, tableB.fieldB, tableB.fieldC, tableC.fieldA, tableD.fieldA, tableD.fieldB from tabl...more >>

Hi All, I'm trying to solve this for a few days now and I just can't figure it out... I have three tables set up, I'll simplify them for this question: Table 1: HOTELS Columns: HOTEL_ID, HOTEL_NAME Exmple row: 123 || 'Hotel X' Table 2: SERVICES Columns: SERVICE_ID, SERVICE_NAME Exa...more >>

Hi, We have a SQL 2000 server (Win 2003 server) latest service packs...connected to Exchange 2000. SQL Mail was set up ages ago and was working fine until 2 weeks ago when I stopped receiving alerts. The Mail session is started and seems fine but when I do a xp_sendmail to my email address ...more >>

Hi All - I am updating four values. What is the proper syntax to have the following 4 update statements as one statement? set objRec = objDB.Execute("Update orientform set session = '" & strSession & "' where id = '" & strid & "'") set objRec = objDB.Execute("Update orientform set fname = '...more >>

Hello I have a SQL 2000 server, the server setting is default language : French and all Collation names in French If i launch the 'Query Analyzer' from the SQL Entreprise Manager on my database, and run a T-SQL my dates are in English. If i do a : DBCC USEROPTIONS, my results are language...more >>

Dear Group, I am trying to create a view and keep getting the Incorrect syntax near the keyword 'Declare'" error. Here is the code I am writing. Create view fixed_airs (sid, fad_a2, fad_a3) as Declare @sid int, @fad_a2 int, @fad_a3 int sel...more >>

Newbie here. In my database I'm needing to automate some data imports. I have the import set up as a DTS package and it works wonderfully. But I'm having trouble kicking it off as a stored procedure, or even from the Query Analyzer. I used dtsrunui to get a proper connection string, but wh...more >>

Hello, update table set column = x where b is null I have the above update statement in a transact sql file. I would like to change it so that it will only update 1 of the records in the table, even if there are many records where b is null.... Any ideas would be great. Many thanks, A...more >>

Hi I have been working since 2 days to device a method to export sql table into csv format. I have tried using bcp with format option to keep the column names but I'm unable to transfer the file with column names. and also I'm having problems on columns having decimal data. Can any one suggest ...more >>

How can I get a stored procedure to accpet this? LIKE N'@numericvarhere%' I.E. list of numbers, I want any one starting with 382 and they can be any length 10-20 long. (BTW LIKE N'382%' works in querey, needs to work w/ a variable and in stored proc) Thanks! Jeff ...more >>

I need to write a query that tells me which string values are empty or blank in a table. Is it possible to return the length of the string contained in character field? ...more >>

Whats happening: Error#: 40002 Error Item: 01000: [Microsoft...][ODBC...][SQL...]The Statement has been terminated. > GETID 'INSERT INTO Table(Column1, Column2, Column 3, Column4, Column5, Column6) VALUES ("","Value1","Value2","Value3","Value4","Value5")' Error Message: 01000: Microsoft....more >>

I created a new SQL database using SQL 2000 SP3 on a Windows 2003 server. Now when my wed publisher changes his codes in ASP to point to my new server and SQL database we are getting the message this page can not be displayed when clicking on the web link. The old SQL database was created with...more >>

How can I order the results of my query in non-linear fasion. I have a field with these values: Reg S, 144A, US and want to order my results by US, 144A, Reg S. I would prefer not to create another field in the table if possible. ...more >>

Okay, most peoples answer to this may be "Gaaah. Why would you do this?", or the like, but here's the question anyway: Are there any stability issues if I mark one of my user tables as a system table (by switching xtype in sysobjects from 'S' to 'U')? I'm not doing this as "a cleved bit of s...more >>

Hello, I am trying to recover a SQL Server 7 database from another hard disk drive that has a corrupted Windows 2000 Advanced Server installation. I am not able to repair the corrupted Windows 2000 Advanced Server installation but the file system is intact. I have installed a new copy of SQL ...more >>

Im new to SQL Server and have a problem. I places many insert commands between BEGIN TRAN and COMMIT TRAN and expect all or none inserts to work. However, if 1 fails the rest work. Why is this? and how do i get round this? Thanks ...more >>

Let's say I have the following table: entry product quality 1 A 80 2 A 70 3 A 80 4 B 60 5 B 90 6 C 80 7 D 80 8 A 50 9 C 70 I'm looking ...more >>

From Enterprise Manager on my PC, how do I connect to a remote server over the internet - I have the IP address, User Name and Password. Thanks ...more >>

Hi all, Currently I administer an Access database used as an order-taking, PO and invoice generator, and sales reporting database. We are a retailer taking orders over the phone, and the db has been developed by a collection of students and self-taught developers over the past several years...more >>

Hi, all: I'm having trouble with something that probably has a simple solution. I have linking tables that can list a particular MemberID multiple times. Is there a way to run a query so that a specific ID will show up once? Here is an example of the tables I've set up -- MemberTabl...more >>

Hello, Is it necessary to upgrade the Client Connectivity Tools on all client machines after the SQL Server database server is upgraded from Version 7.0 to 2000? Thank you in advance! Eddy ...more >>

Howdy, Does anyone know of any packages that you can create a form and via a ODBC connection save the data to a table ? If it is possible to compile the form as I don't want clients to change the forms ? Any idea/thoughts would be most welcome ? Regards Andrew...more >>

i am doing some statistics for staff ratios and have had no luck finding any DBA's per sql server or DBA's per sql databases. If anyone has any numbers, i would greatly appreciate. Thanks! ...more >>

Ok, I know this is a stupid newbie question, but I'm a stupid newbie, so it fits. I've got a query that writes a basic report, and I need to be able to give it to a user who doesn't even know how to spell SQL to run. Basically, I want her to be able to double click on a bat file on her comput...more >>

I'm trying to generate scrips for a database, and everything so far has worked fine, except for the triggers. When I try and script existing triggers, all I get is a blank file - no SQL script. I tried single files for each object, all in one file, triggers only, the entire database. I can't fig...more >>

With respect to my (now not so recent) thread on Concurrency, I would like to run my idea past you gurus to see if its a runner. First, a brief recap: I have a single user system (one user, one copy of the software, one copy of MSDE, one machine) that I wish to convert into a multi-user/sing...more >>

I'm looking for a simple way of telling (inside a stored procedure) if I'm currently using MSDE or a full SQL server. Ideally, there is some pre-defined environment variable that won't cause me too much overhead. The reason I'm doing this is because my system "rolls over" databases when it...more >>

Hi, I am supporting an application that was converted from ACCESS to SQL Server 2000. My question focuses on two particuliar tables. The parent table has 14000 rows while child table has over 9 million rows. Referential integrity is set up between the two tables. The parent table has a com...more >>

I would like to capture about 20 rows from the sysperfinfo table every 30 secs on a production server. I am thinking of ways I can reduce the disk (not network) I/O overhead of this process. Instead of reading the table from a local SQL Agent job and writing to a local table, I am wondering if...more >>

Hi there - hoping someone can help me here! I have a database that has been underperforming on a number of queries recently - in a test environment they take only a few seconds, but on the live data they take up to a minute or so to run. This is using the same data. Every evening a copy of ...more >>

Is there any way to update a column in a clustered index without incurring the cost of reordering. Example: Create table TableX ( Col1 int, Col2 smalldatetime, Col3 varchar(10)) go create clustered indext ix_test on tableX (Col2, Col1) go update TableX set Col2 = '2004-12-07' -- Yes ...more >>

Dear All, This is a query surrounding a problem I encountered yesterday. In SQL Server, it is possible to write a procedure that has one or more select statements in it. The results from these select statements will all be individually returned to SQL Query Analyser where they can be vi...more >>

Hi We have a script that allows customers to change the users of the database including the database owner. This script can be run at any time. However, sometimes (and it really is only sometimes!) when the following statement executes: "exec sp_changedbowner @USER_OWNER" the following e...more >>