I'm having an issue when doing a bulk insert on a SQL 2000 server. I need the table rows to load in the exact order that the rows in the text file are sequenced. So far, the bulk insert appears to be loading the file in 2 different orders. One of the orders is the same order as the text fi...more >>

I am trying to create a statement that has a case in the where. I have it working but when I add in a IN... to the case statement it doesn't return anything. Sample. Select * from dbo.Sometable Where SomeTable.SomeField IN (case @Variable when 1 then 'sometext1' When 2 then 'sometext2...more >>

I have a very easy query that isn't working and is driving me mad. I wish to select from a list of sales promotions that are current for this day. SELECT * FROM SALES WHERE PromoEffective>=GETDATE() AND PromoExpires<=GETDATE() The SALES table is defined with PromoEffective - dateti...more >>

Hello, everybody I have just instralled SQl Server 2005 Dev Edition and would like to hide some databases from my co-worker. We are about to upgrade our SQL Server 2000 and checking some issues. I know that with DENY VIEW ANY DATA\BASE I can hide every user databases but I 'm wonde...more >>

I'm planning on building a new desktop box for developing apps with VS 2005 and SQL Server 2005. Would it make sense to have two SATA drives, say C: and D: and install SQL Server on D? What would you recommend would be a reasonably good configuration - RAM, CPU, video, HDs, etc consideri...more >>

Hi, I am getting the following error from the query below against SQL Server 8.00.2039 (SP4) Error: ==== Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'd' does not match with a table name or alias name used in the query. Select Statement: ============= select .... fro...more >>

Hi all - I'm sure this is a silly, obvious question, but I'm having some trouble.. Let's say I have three tables: Events, People and EventsPeople. EventsPeople is a join-table that represents the people associated with each event. I have a GetPerson stored procedure that takes a personI...more >>

Hi, i have a Sqlserver 2000 database on a webserver. Now i want to move this database to another provider (server). I have restricted access to the database. What is the best way to retrieve all the data to a local pc ? What i need is a sort of Backup to local pc. I have asked the provider ...more >>

Hello all! I'm currently working on a project where we have several customers with the same application. The database is constantly being changed and it's hard to keep track of all the changes from all the versions in the customers' systems. Usually I create the changes script every time...more >>

I'm in a situation where I can't alter how data is being collected, and it's an extremely large set of information. The table all of this data is being dumped into is a transaction style table with heavy null propogation. The only "real data" out of the table is the last not null value ent...more >>

Quick question... If an application is developed using VS 2005 and SQL Server 2005 in the development environment, will it be possible to run it in a production environment that utilizes a version of SQL Server which is earlier than SQL Server 2005? If not, I guess we will be forced to go ...more >>

Obviously the below Case expression does not work. I have conditions on two fields. If it is true then proceed. CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN GETDATE() ELSE tbl.end_dt END Please Help, Culam...more >>

SELECT HC.ComponentID, CCS.String AS CategoryName, CDS.String AS ComponentName, ADS.String AS AttributeName, dbo.ConvertAttribute(HCA.IntValue,HCA.TextValue,CAC.ConversionID,@inLanguageID) AS Value FROM datHardwareComponents HC JOIN defHardwa...more >>

Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no one else can make any changes to it; however, we'd like to allow others to be able to still have read permissions during the lock. How do we accomplish this? Thanks. ...more >>

I believe I am missing some columns from information_schema.Tables view. Is this fixable? Where do the information_schema views reside? Is it possible to copy one of these views from another DB? I looked in master, nothing. What can I do? Thank, Rich...more >>

I know it's possible to view AD data via a linked server & using LDAP queries from SQL. But I'm wondering is it possible to update the data in AD? More specifically - I've got a sql server database table of user information (userid, password, etc.) - when the user modifies their password in th...more >>

I have a function which i need to pass different data types and number of arguments. Is it possible to have a SQL Clr function with variable number of arguments ? thx ...more >>

I have the following code (see Example 1) that I use to poll the various SQL Servers for available space. The code runs successfully when I either double-click it or right-click and select open while in Windows Explorer. If I embed it either as a step in a DTS package or as a step in a SQL A...more >>

I know I'm probably going to get rapped in the mouth for my stupidity but, in SQL Server 2000 Price is money(8), Qty Is int(4) Discount Is int(4) - the program that it feeds into doesn't accept fractions of a discount The statement: SELECT Items.Price, Items.discount, ...more >>

Is there any way to have a job in SQL Server start Excel on a PC? Thanks in advance, Raul...more >>

Hi All, I am getting "XML parsing error: A name was started with an invalid = character." error returned from "sp_xml_preparedocument". The XML text I = am passing to the stored procedure is well-formatted in VS.NET, so I = don't see a reason why text should be the problem (which the error = me...more >>

I need help to understand how to minimise or completely stop blocking. any help, tips or links will be highly appreciated. I have gone thru couple of article about blocking but not specified how to stop blocking. i will appreciate if some one can give me concept about blocking & how it can be st...more >>

Hi, How can I use the In function with NULL ex: Select constate('NJ',NULL) does not work. Is there something missing? Thanks Dib ...more >>

I have a simple objective that I need to meet ASAP. I need a simple encryption on a password column in a SQL Server 2000 table. It doesn't matter what algorithm or hash is used, as long as it's done ASAP. We only use "Classic" ASP, so it has to be implementable in that, and it must be free. I'...more >>

Is there a way create a user defined function so that you don't have to reference the owner to use it? Reason, using sql server has a QA source which is Oracle in Prod and needing to call a function that SQL Server doen't have. So I created the function in SQL Server, but need to reference...more >>

Hi I have a problem with a trigger and hope someone could help me out. I have two tables: CREATE TABLE a (ID int, Reference nvarchar(10)) CREATE TABLE b (ID int, Reference nvarchar(10), TableA_ID int) INSERT INTO a (ID, Reference) VALUES (1, 'ROW 1') INSERT INTO b (ID, Reference, TableA_...more >>

If I rename a table on my SQL Server 2005 database say from Carriers to Partners, will it update the stored procedures that reference the old table name "carriers" to the new "partners" name? I am talking about using the rename function in the rename function in the micosoft SQL Server managme...more >>

Hello, I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT statement over a table. In the table I have two 'text' fields with the same properties, with just one difference: one field allows nulls, the other one does not. Well, one field actually accepts only the first 255 ...more >>

Hello, I have a problem turning a SELECT statement into an UPDATE, SQL Query Analyzer gives me a syntax error. Any help would appreciated. Here is the working SELECT statement: DECLARE @Today Char(10) SELECT @Today = CONVERT(CHAR(10), GETDATE(), 103) SELECT OrderID, (CASE WHEN TOT...more >>

Hello, A program I've written creates a parameter to be passed to a stored procedure based on a user's report selection. A user can select one, two or three locations. That parameter is used in an IN clause. "ZMCC.WERKS IN (@Locations) " If a user requests a single location, it works ...more >>

I have a query that is returning incorrect results. It is reproducible on SQL 2000 SP4 (version 2040) and SP3a (version 760). It tried on 6 different machines, all with different operating systems/hardware/etc. Is there a hotfix available? The following should not return any rows, but 1 ...more >>

Here is DDL in my way. Other information is masked. Table a sri int, PK, Clustered num varchar(7) Nonclustered .. .. .. p uniqueidentifier Nonclustered Table s num varchar(7), PK,Clustered ssi tinyint --can have either 1 or 2 IF EXISTS ( SELECT sri FROM a WITH (NOLOCK) ...more >>

Hello there Can i use a COM object from Store Procedure? Thanks- -- <Harvey Triana /> ...more >>

I'm looking for different solution people have implemented to solve this problem. Problem: People need to work remotely. While working remotely they want to gather information. This information might be survey info, or inspection info, etc. This information is to be stored remotely on a ...more >>

Bonjour à tous, Comment créer un nouveau champs dans un fichier SQL ? 'Connexion de la base 'Microsoft ActiveX Data Objects 2.1 Library Public DB As New ADODB.Connection DB.Open "Provider=SQLOLEDB;Data Source=" & strSERVER & ";Initial Catalog=" & strDatabase & ";U...more >>

I have the following query: SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID FROM dbo.cbt_BillingAddress WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110') ORDER BY ProductCode, BillingCustID, BillingPeriod which returns the following columns: Bil...more >>

The following query results in the below error msg, althoug it worked just a few days earlier and the data hasn't changed since then(notice the date range). Can someone put me on the right track? SELECT AVG(Arrival_to_Closed_numeric_seconds) AS 'Q115c- Average Time Arrival to Clear' FROM ...more >>

I am told that percentages should be stored in a raw format like 0.12 and displayed as 12.00% on the front end. That means, I should multiply by 100 when I am pulling the data from the table and divide by 100 when I am storing the data in the table. This does not make sense to me. How abou...more >>

Hi, I'm having an urgent problem that I'm having a hard time figuring out. I extracted the code below from a stored procedure that gets different results on two servers (identical data). The statement in question is "SELECT MANAGERID FROM #MANAGER". When I use this select in the "in" clause (th...more >>

I making a migration from Sybase SQL Anywhere to SQL Server 2005. Is it possible to convert the following trigger in SQL Server without to use a Cursor. create trigger DBA.ZOC_1001_01_update before update order 1 on DBA.ZOC_1001_01 referencing old as old_name new as new_name for each row beg...more >>

Hello, I have this sql statement wich I'm able to run statically, but when I try to run it from a variable it returns me the same error two times: "Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'V'." Here is the dynamic code: DECLARE @v_dataini datetime DECLARE @v_dataf...more >>

Hi All, I’m stuck on a stored proc. query and could use a little help if anyone knows a better way. I need to take an n-row random sample of a table. This in itself is not a hard thing using "SELECT TOP" and "Order By NewID()" The problem I have is that I need the sample to include at ...more >>

Hi, Thanks for you time reading this. I got a website running www.aspapp.com that send an order confirmation when an orderr is placed. The query that is used is: SELECT ecOrderInfo.OrderNo, SKU, Price, Qty, ItemDesc, Options, OrderTotal, dtInserted, Bill_Company, Bill_FName, Bill_LName, ...more >>

We need to do auditing of column-by-column changes to tables through triggers so we can catch edits even by interactive users. We also want to capture only those columns that changed, saving both the before and after column values. Columns that were not actullay changed would not be audited....more >>

When to use DBCC INDEXDEFRAG and STATISTICS (create or update). For what kind of tables we need to use it if i have tables with 60-70 millions records with nonclustered indexes. Will be there any performance issues....more >>

I have 2 table around 60 million records and grow substantially. The table is created with non-clustered index (int datatype). Jobs are scheduled and running in the background. Is there any way to see in the profiler what queries are being run. I could not see the queries as the jobs are runni...more >>

I have two tables with same datatypes. I need to view all the rows from both tables and check to see if they exist in a third table while viewing the data. My plan thus far is to use a union query and a function that returns a bit showing if they are in third table. Should I use this approach...more >>

I have a column in a table that is a datetime data type. Some columns are NULL some are not. So, a sampling of data could include: NULL 2005-06-06 12:32:53.000 2005-04-12 11:32:53.000 NULL NULL 2005-12-22 12:32:53.000 When I select from this column, if the value is NULL, I need to rep...more >>

I am implementing a UDT which has three internal fields, two integers and a byte[] array. There will be certain instances of this type which occur with high frequency. For instance, the pattern Integer(0) = 1, Integer(1) = 7, byte[] = {b0, b1, b2, ...} may occur in 40% of the records in the ta...more >>

Hi, I have this scenario that keeps coming up on different models and I'm yet to find a solution. In this model I have Customers, Customer Recognitions, Sectors and Divisions. Sectors and Divisions are related and reference data. Customers become recognized in sectors, and then within divi...more >>

I ran a massive DTS job (consisting of other DTS packages running in parallel) taking ODBC data into my SQL server 2k DB when I realized that there was far too much data (millions of rows) and that my server's disk space was being gobbled up at an alarming rate. I cancelled the DTS job with a...more >>

Dear all, How do I such thing? I've got a table with 140 columns and I should need to introduce a comment. Is there any programatically way for that? I know that ORACLE uses COMMENT ON <FIELD>... Thanks for any help, Enric...more >>

Hi folks, I'm using Teradata SQL asistant and I was able to do this: SELECT ACC_SHT_NM FROM Accounts WHERE ACC_SHT_NM LIKE ANY ('%LNA', '%PLNB') Rather than having one wildcard clause with LIKE, then OR one after the other I can't seem to make this work in SQL server 2000 Is it poss...more >>

I need to restrict a select query to return the first account repayments in a history of collections. I have a list of account numbers but they make Monthly/Weekly/Daily repayments and all the accounts repay on different days. This pseudo code returns all the collections between specific d...more >>

We have a database in application in SQL 2000 Server (Standard). We want to configure a table so that when there is add, delete, or update in the table, an Outlook calendar of a user should automatically be updated. The user is a member of the same domain as the service account and as the Wind...more >>

I have a "flat" CSV file that I need to import into a set of 3 relational tables. As a "programmer", I thought this would be trivial, so using VB6 and ADO, I created a recordset of all the import rows and then iterated through the recordset extracting the data from the fields and putting th...more >>

Hi, I have used an access table to create a chart report to show how many calls were handled in a month. How do i conver the Access sql to MS sql server view. Here is the access sql: SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count] FROM [Complaints] GROUP BY (Fo...more >>

Hi all! I am about to create a procedure which will create tables with dynamically specified tablenames. Something like this DECLARE tname CHAR(30); SET tname = GETDATE(); #or any string function CREATE TABLE tname ( #columns cpecification ); But it doesn't works.......more >>

Hi, Any one know about Linked server setup in sql server. I want to know more detail on setting up a sqlserver to connect it directly to an oracle server. So that I can directly access data's from Oracle using Stored procedure. Regards, Sasikumar Balasubramanian ...more >>