I've been working with a client who has hard-coded the database name throughout the code, both client-side inline code (c#/asp.net) code and plsql. In other words, "select blah from MYAPP.dbo.tablename..." instead of "select blah from dbo.tablename". There is only one db in use. This has c...more >>

Hi Hope someone can help me with this problem as it's driving me nuts! We have an app where the user can define the number of decimals that they want to use. We store the value (between 2 and 6) in a table. Let's call it tblSetup and the column holding the number of decimals we'll call Qty...more >>

Hello: I have a table in SQL server called Employee. Have 3 Primary keys: Name Phone Sex I insert records using VB6. If I have duplicate values, I like to capture the error, so I continue writing into the table without inserting the duplicate values. Where do I capture this error, in VB6 or in ...more >>

Hi, I have the following code that i can save as a .sql file, but not as a view: SELECT Clients.C, SUM(CASE WHEN A = 0 THEN B END) FROM Clients INNER JOIN Customers ON Clients.C = Customers.C GROUP BY Clients.C; Any help would be appreciated ! Thanks....more >>

Sorry, I know absolutely nothing about SQL other than how to set up a server for it. If I have a server named ABC with a database named 123 on it, and I want to run a query on the database to find out how many records have the field "day" with a value of Monday, how do I run the query? Thank ...more >>

this doesn't compile - error near "case" what's missing ?? CREATE PROCEDURE spxx ( @ID int, @Number int, @Type nvarchar(20) ) AS CASE WHEN @Type = 's' THEN INSERT INTO Users ( userID,S_Number) VALUES ( @ID, @Number ) WHEN @Type = 'c' THEN INSERT INTO Use...more >>

Hi, All I have a table as following: CREATE TABLE [dbo].[tblPatient] ( [VisitingID] [bigint] NOT NULL , --Key [MedicalHistoryID] [int] NOT NULL , [AdmitingDate] [datetime] NOT NULL , [DischargeDate] [datetime] NOT NULL , [PatientCategory] [varchar] (5) COLLATE SQL_Latin1_General_CP1_...more >>

Hello, This works fine as long as the dates are as follows. set @start = '20040106' set @finish = '20040116' but as soon as I use a different date format and try to convert it I get an error. Can anyone suggest why ? Thanks, Andrew. *************************** declare @start dat...more >>

who is dbo? ...more >>

Hi, I've some trouble to insert records in linked tables with unique identifiers (ID) from SP. How to process that kind of insert? Any idea or basic sample? Thanks...more >>

Hi Freinds, SQL 2000 I have a .jpg ot .bmp file and want to save into a image type filed. How can I save and read back the image from table ? Thanks in advance, Pat ...more >>

Hi there, just a quick one.... I am trying to write a View in SQL Server that originated from an Access Query. The problem i am having is using criteria in the view, relating to a column value - Column Table Criteria Or vchrChannel tbl...more >>

Hello There I want to generated a csv file from sql server table, in which I would = like to create a asp page which display a field list with a check box. = Then whatever check box I selected, and click on generate button, then a = CSV text fill will generate depending on sellected field with...more >>

Hello everyone, I need to do an availability check based on a date period. I have a table which holds two lots of availability. 1. The available period 2. The unavailable days within the available periods. The available periods are tagged by a "0" in the stopStart column. The unavailable...more >>

Using SQL Server 7, SP4, Windows server 2000 SP4 I have a weird problem with values stored in our production SQL server. Simplified, I have the following table: create table orderitems (orderid int not null,barcode varchar(13),rrp smallmoney) In Query Analyzer, the following query: "sel...more >>

Hi ! I am trying to connect to the sqlserver "yukon" instance on my local machine using the foll. code ... ---------------------------------------------------------------------------- ------------------------------------------------------------------- Public Function doConnect() A...more >>

I deleted records in a table. I used Truncate table to delete the records in the table because I wanted to restart the Identity column number assignment. But after I did this what I get. Additional information: Column 'tblRedRiver_CDR_PK' is constrained to be unique. Value '1' is already prese...more >>

My data looks like this: ID SEQ1 SEQ2 AMT 1 1 1 20 1 1 2 10* 1 2 1 50 1 2 2 00* 1 3 1 08 1 3 2 30 1 3 3 ...more >>

Hi, How do I format the below number to a more readable format. 4516785 into 4,516,785 Thanks, culam...more >>

We've recently experimented with using output parameters instead of result sets, and we're seeing unexpected results. Our test sproc returns about 20 values, which we turned into output parameters, and this caused our processing to drop from 400 batches per second to 135. The slowness doesn't se...more >>

Greetings, I need to delete a duplicate row, a row with one duplicate field but with a second field that is less than the second field of the duplicate row. CREATE TABLE [dbo].[tbl1] ( [fld1] [nvarchar] (1), [fld2] [int] ) Insert Into tbl1(fld1, fld2) Values('A', 1) Insert Into tbl1...more >>

Hi, I have a VB.NET app that writes data to a series of tables with a transaction. The transaction functionality is new, the code operates fine outside the transaction scope. The problem seems to be that when the VB code executes outside of the transaction scope, it works. The very first...more >>

Hello I have the following view in a SQL Server database SALES_OPPORTUNITY_ID CREATE_DATE CAMPAIG 1 1/1/2003 We 1 1/10/2003 Telemarketin ...more >>

Hi. I have a problem that I'm hoping someone can help me with. I'm needing to pull data from a table, but at design time of the query I don't know the name of the table. For instance lets say I have a table like the following Categories id name tablename ---------- -------------- -...more >>

Hello all, I get the age using this, I would also like to get the Average age of the result age Column? select dob, datediff(year,dob,getdate()) - case when 100*month(getdate())+day(getdate()) <= 100*month(dob)+day(dob) then 1 else 0 end as Age from tmpesc ...more >>

Hi: I am very new in SQL Server. I am using VB6 to insert values into my table in SQL Server. I like to create a Trigger for my table that will not allow duplicate values being inserted into the table. The primary keys are: FirstName LastName Phone Any help to create this trigger will be ap...more >>

Hello, I have some sql statements which make use of functions: select a, b, fn_c(a) from xy fn_c calculates a value from a table. I want to optimize this by first calalculating the value as usual, but additionally I want to save the value to a table, so subsequent calls of the function ca...more >>

Hi, declare @var varchar(100) set @var = '120000-004721-002198-0507' what is the sql to break down the whole string into 4 columns each of one would be separted by '-'. so the result for the above example would be: A = 120000 (length= 6 ) B = 004721 (length= 6 ) C= 002198 (l...more >>

Is it possible to use a loop to copy items from column-A to column-B in the same table? Thanks in advance. Geo ...more >>

how do i convert following date to this format? 2004-01-15 11:13:43.000 ---> 01/15/04 12:59AM ...more >>

Hi al I am wondering if it is possible to pass a database field as a parameter to UDF.. Normaly I use UDF like SELECT * FROM somefunction('teststring' In a SELECT Statement with some joins etc. I use also a UDF and I would lik to pass a db field like (tablename t) --> somefunction(t.Fld_Date ...more >>

Hello, I'm wondering if its possible to update a linked server when the update query has an inner join with an sql table. Any ideas would be appreciated, the sql update query is below. update [accesstable] inner join [sqltable] on [accesstable].id=[sqltable].id set [accesstable].testfiel...more >>

what exactly is the difference between a clustered index scan and clustered index seek?...more >>

Hi, Can I get trigger's table(s)name? Example: --************************************************ CREATE TRIGGER TBL1_INSERT ON dbo.TBL1 FOR INSERT AS DECLARE @TABLE_NAME varchar(128) .... --************************************************ In this case I want set @TABLE_NAME = 'TBL1'...more >>

Thanks....more >>

Hi, I have a query below that do the count for each source code in my where clause. My next step is to calculate count and Sum(amount) for each group. Example Group 1:Source in 'satd','satf','sati','satw','acha','achb' Group 2: Source in('atmp','debp','dedx', 'patd','patf') Group 3: Sourc...more >>

Hi, I need to copy missing records in a dest table from a source table - both tables are alike and both contain the same data except that tbl2 is missing some records that exist in tbl1 - they need to both have the same amount of records - how to populate missing records in tbl2 in one ...more >>

Please tell me all the differences between table datatype and temporary table....more >>

Can someone suggest good MS SQL Server books on the programing side (DTS, Triggers, Stored Procedures...)? And then a good Adiminstration book as well? Thanks ...more >>

I have a stored procedure within my app which works perfectly when running my VB6 app from Windows NT, however I have now had to move my project over to Windows XP. When it gets to a certain point where it needs to open up a recordset relating to this certain stored procedure it just exits my ap...more >>

Is there any way in SQL Server to use Standard deviation as an aggregate function. STDDEV -- Is a numeric expression. Aggregate functions and subqueries are not permitted Thanks ...more >>

Hi there, We have always used the TSQL debugger in the Query Analyzer. Nevertheless, we have installed a new instance of SQLServer and the debugger seems not to work properly from a client when debugging SPs in the new instance. What it does is that you launch the debugger for the selected S...more >>

Is there a way to script a proc to optionally not pass a parameter? I want to impelment a new version of an SP. The sp is a wrapper for an XP. The xp is xp_NewRecID, for that reson it lives in the Master db, however due to performance hits and errors on the main db server I've moved the xp ...more >>

I am attempting to pull the top 3 most recent dates from a table (from a date/time field called stamp) and I am having a little trouble getting the values. Here is the query that I am using.... select distinct top 3 rtrim(ltrim(str(datepart(month, stamp)))+'/'+ltrim(str(datepart(day, stamp)))...more >>

Hi, I have to ;-) wonder about that case: I have quit a big tab with have over 1,400,000 rows each of about 30 columns. One row is for example CaseName with is indexed, nonclustered and is nvarchar(255) as a new type TName, with is NOT NULL. This column is also nullable - what do you say abo...more >>

I'm having some troble inserting the data from a XML file to my SQL Server 2000. I've created a XML-Schema, and a VBScript that creates a SQLXMLBulkLoad objekt and inserts the XML data into the database. When I execute the script I get no errors, but the data is still not coming into the da...more >>

When I run a stored procedure, it use up 100% CPU time for a long time. I have two databases for two departments. One is suffer from the sharing of CPU time. Can anyone help me. Thanks a lot ...more >>

Mike, I haven't received any information back from the Microsoft sqlServer newsgroup on how to get the text field working so I'm going to reprogram things to use external files for the technotes unless you have a problems with that. I'm going to save files within the web directory structure. T...more >>

I am using this code and confused as to why some results are being returned Select * From Table1 Where Field1 LIKE 32[11-22]% Why would I get a result back where the field is '332913' and '332510' *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in US...more >>

Hi, I wish to be able to add tables to a sql server database at runtime from my asp.net application. As well as creating fields I also wish to be able to create indexes on selected fields and to assign user permissions. (I need to assign permissions to the table object as I will be using sp_e...more >>

I want to check out the Reporting Services for SQL Server. Can someone point me to the download (if there is one) or any whitepapers? Thanks. Brian ...more >>

Hi to all I've a great problem with sql server 2000. The identitity field of a table is not sequential, but jump, in some cases, between crescent number non sequentially. May you hel me ? the question is very urgent MORBILLO ...more >>

I am trying to return a max amount with the corresponding date. The problem is due to the one-to-many relationships the max amount gets returned but if there are more than one amount/date then I get the max amount returned multiple times. For example, John Doe as 3 records. 25,000 01/...more >>

I am doing an Outer Join between two tables on an ID number, and I'm trying to do a conditional statement inside my query such that if there is a matching row, I set a column as a name (e.g. InnerTable.Match as MyCol), but if there isn't a matching row, I set a column in my Outer table as ...more >>

Hi, I'm using sp_columns to return a list of columns. I want to limit the result to only the column names. Any ideas? Tim - More info- I'm building a VB .NET application that queries my database and dymanically fills in combo boxes with the database info (table names, then column...more >>

I have three tables. all linked by fieldLicence I need to find out which licences do not appear in all tables Ie Licence Table1 Table2 Table3 111111 YES NO Yes 212121 NO NO Yes ...more >>

hello! i will to write a stored procedure that will download a csv file from a http url like this http://www.web.com/file.csv i want to put those csv datas in a temp table or real table it does'nt matter. how do i go about this? AP!...more >>

Dear All I have a store procedure which will insert records. Lately we have had an incident where the next id for indentity fields were spoilt so we were getting integrity constraint errora, The SP should of tripped it and sent a note off to a table. The code is as follows... DECL...more >>

This is a select statement to find each row where it does not exist in the next period of the same year. eg This exists in the table Year Period Cost_Centre Account 2003 1 G3565 A1111 BUT 2003 2 G3565 A1111 Does not. So the select statement 'creates' the missin...more >>

I'm looking for a way to take a list of values (comma separated) from an ASP Text Form, and have them added as individual rows of a table. For example, if a person selects four colors, I want each ColorID to be added as it's own individual row....more >>

Folks, I'm sure this has been asked before. But, is it possible to INSERT only the date into a smalldatetime or datetime field. When I try it, SQL Server inserts the time as well. I only want to insert the date. Using SQL 2K. Thnx Roz ...more >>

Hi All, I have a requirement of moving report generation logic from c++ code to a strored procedure in sql server 2k. Report is generated as follows in c++ code. Begin Open AdvReportRS() For each record in AdvReportSetRS Open activeCarrSetRS() For each record in activeCarrSetRS ...more >>

Hello all, I have table named TableA Entity : TableA Fields : Srno int Name varchar(20) There are existing 10 records in TableA. Now i want to know when i.e. DateTime these record where created in SQL Server 2000 Database. Does SQL Server maintains internal log of datetime whe...more >>

Hi there When I tried to execute the following line in query analyzer on my local database BULK INSERT table1 FROM 'C:/folder1/filename.txt' WITH (FORMATFILE = 'C:/folder1/filename.fmt' ) It gives me the error: Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert because...more >>

If I Updated some data to a table, and want to restore the old data How can I do?? Thank You...more >>

hai, I need to write a select query in the following way select date from employee where empaction ='HIRE' and date between 15-01-2004 and 14-12-2003. here the date field should be dynamic ,i have to compare the date with sysdate and fetch the data according to that can anyone help on th...more >>

Hardware includes: IBM server x220, PIII, 1.2 GHz, Xeon, HDD 36 GB, RAM 1 GB. Software includes: Operating System: Windows 2000 server; Database server: MS SQL 2000 standard edition server. Application includes: developed on VB Script, ASP technique. 10 modules having about 1500 files each (15...more >>

Hello everyone I am having problems on setting 'dbo use only' option for a database on SQL Server 2000. I have a VB6.0 application that connect to a database using 'sa' as userid and then executes the following configuration commands (in that order) through a DSN 1) EXECUTE sp_configure 'allo...more >>

I need to access multiple stock databases using the same tables from one main database (company). How will this function perform with a large (300000+) number of stock items in about 10+ warehouses. Is there an alternative way of doing this. I DONT want to use EXEC('') !! Thank you ALTER FUNCT...more >>

I'm using a cursor in sql 6.5 to transfer 4million records from one table to another with checkpoint so that the log doesn't fill up and doesn't need to be unecessarily big. Problem is it's taking 5 hours!...anything quicker I can use?...more >>

Forgive me for asking a question that gets asked a lot, but I don't know where the archives of this list are stored. Is the following code the best way to lock a table in a stored procedure while I do other queries on the locked table and other tables ? If I use a table hint of "TABLOCKX" w...more >>

I am a newbee and want to know what is the diff. between indexes and indices. Help appreciated. ...more >>

I have some which can be done by DTS as well as with SP, I just want to know which is faster among sp and dts. ...more >>