Mike sorry for posting directly to you, but you gave me some good advice a couple days back and I have a few questions for you. Original Thread: > Hi and TIA! I have an ASP.Net page that when the user clicks a button a > recordset is passed to a stored procedure(this is what I'm trying to do...more >>

Hey all, I'm trying to figure out how to compare one character string against a list (of other strings) and then select a 'best' match. I'm not really sure how to go about doing a 'best' comparison/selection. No doubt I'll have to tweak the logic for 'best' as I work with this over time. Th...more >>

I have Visual Studio .NET and SQL Server Desktop Engine on my computer. I have created an empty database using Visual Studio .NET's Server Explorer. However, I am having trouble connecting to the database using ASP.NET. I think the problem is somewhere in my connection string, but because I do no...more >>

Hi guys I am trying to get 'create table' statements for all tables in a database. I know I in QA can right click a specific table and get QA to generate a script for creating that table. However I have a long list of tables that I need to script so does anyone know how to generate these sc...more >>

Is case statement a ANSI-92 standard? Where can I find the list of ANSI standard anyway? Thanks Ed...more >>

Hi, I'm quite new to SQL. I've created a new table & need to populate it with some cols from another table. I want to add an incrementing value to a NOT NULL col of the new table for each row added. I'm trying to do it like this: (This is eqivelant to the value I'm inserting as a sub query...more >>

I need to convert a mailbox to a SQL database, I need to have all the fields including the attachments...more >>

Folks, Is it possible to write a trigger that updates time/date stamp for the record that was just updated? I'm a bit new to all this and the simple trigger I wrote updates time/date for ALL records if one is updated. That's not what I need. Thanks! Konstantin ...more >>

In query analyzer I do this ---------------------------------------------------------- Create Procedure stp_TestProc @i int As Select * From tbl2 Where fld1 = @i Go --------------------------------------------------------- but I get the following error message when I try to run the foll...more >>

I am trying to use a dynamic query within a stored procedure to copy tables from one database to another. I am simply using the "Select * Into database2.dbo.DynamicTableName From database1.dbo.DynamicTableName" query. The problem is that my indexes don't follow using this method. Is there a...more >>

I execute this syntax in Query Analyzer. SELECT * FROM TB1 WHERE NO_ID IN (SELECT NOID FROM TB2) In the past, I always executed this syntax, and it's ok. But now, I get error message: Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation. ...more >>

Hi, I just find out something very interesting, maybe it is just my misunderstanding When I use Profiler to capture the Stored Procedure and sql statement, i use SQL: StmtCompleted RPC: Completed SQL: BatchCompleted SP:Completed and I go to the front end application and try to call t...more >>

Hi, I am writing a form that is based on a Stored Proc. I have several search fields that the user can or cannot enter, in any combinations. I want to send those to the stored proc and return the rows that match the search fields they entered, or get all rows if they do not enter any. I ...more >>

Scenario: In a table I have two columns MyDate (datetime) and MyValue (varchar 20). I have the following data:- MyDate MyValue 1/1/2005 Test 2/1/2005 Test 2/1/2005 Test2 2/1/2005 Test3 I have a constraint that insists MyDate and MyValue combinations are unique. I want to in...more >>

I'm trying to write a SQL stored procedure using the editor in the SQL Query Analyzer, does anyone know how to enable the Line Numbering within the editor? -- Regards, ConCatto...more >>

I have an sql to select 'customers', something like Select * from clients inner join cases on clients.id = cases.clientid The client may have multiple cases and if I run the query like that I may get the same client multiple times, how can I only display the client once when there are multi...more >>

Hi All, I had 2 tables with paent child relationship. When I wanted to link them I got following message 'MYPARENT' table saved successfully 'MYCHILD' table - Unable to create relationship 'FK_MYCHILD_MYPARENT'. ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constr...more >>

I was trying to export some data using BCP to a text file. The command line prompt was running locally and the DB server is remote. The line I was running was: bcp.exe dbname..tablename out /t"\t" /r"\n" /o c:\test\bcptest.txt /S servername /U sa /P password When I run this, the file is c...more >>

The sp returns 1 int value that I will convert into one comma delimited string (eventually the sp will calculate 3 separate int values) - varchar so that I don't have to use a table to collect the int values. I run the sp in query analyzer below but only get a 0. How can I get the string? ...more >>

Group, When we run an ALTER TABLE query, is there anyway of specifying where the column is to be placed? Normally, any new column is appended to the end of the table, but is there a way to place a new column to the beginning of a table definition without dropping the table and recreatin...more >>

I am not sure if this is the right forum for my question but here it is anyway: I am using Microsoft SQL Server 2000 JDBC Driver with my java code. I found a bug in this Driver. Suppose I update a table through this Driver and this table has trigger (INSERT/UPDATE) on it. If somehow the trigge...more >>

I have a base table and a view that I need to put an index on. The TSQL for the table and view are thus... CREATE TABLE dbo.NAICS ( sector char(2) NULL, naicsCode char(4) NULL, naicsName varchar(150) NOT NULL, sort_exception bit NOT NULL, sel_exceptio] bit NOT NULL, sector_referral...more >>

I need a strong sturdy speedy can-do-it-all laptop that works well for programmers doing sql & .net. Any advice, suggestions? What to get, what not to get? Thanks and I am cross posting on the dotnet.framework.aspnet group too. ...more >>

Is there a way to ensure that SQL Server performs a query by scanning each row in my table, key by key sequentially? Either programmatically or by configuring my table? My customer table (10,000 rows) has a customer ID field which is a unique number. I want to display the table on a web pag...more >>

Hi, I am encountering some strange losses of datetime precision when calling a stored procedure through ADO.NET. I'm using ExecuteNonQuery in the Microsoft Data Access Application Block for .NET, which simply creates an ADO.NET command, and attaches each of the parameters, before calling ...more >>

Hello Sql Gurus, 1. Is it possible to create a static view in an sp. sql 2000 sql 2005 2. Is it possible to create a dynamic view by passing a parameter to an sp. sql 2000 sql 2005 Create Proc testproc ( statecd char(10...more >>

I have an sql to select 'customers', something like Select * from clients inner join cases on clients.id = cases.clientid The client may have multiple cases and if I run the query like that I may get the same client multiple times, how can I only display the client once when there are mul...more >>

I'm using a SPROC that works great except when I try to filter by date. In CODE 1 below, the @sFilter param works fine, but in CODE 2, the @sFilter returns no records from Northwind prior to 1/1/1998. What syntax is SQL wanting from me? I've even tried the @sFilter = OrderDate < CONVERT(DAT...more >>

I have an output stored procedure on Sql Server 2000. It works fine in query analyzer. ------------------------------------------------------------------------------ create procedure stptest @count int output as select @count = count(*) from (select fld1 from tbl1 where fld1 not in (sel...more >>

is it possible to define a stored procedure parameter to act as both input and output? I have a requirement to pass a value in but on some conditions return a different value in the same parameter. BOL appears silent on this issue. Thanks, Michael...more >>

Hi, I'm sure this question has been asked a hundred times but a quick search in this newsgroup hasn't come up with any answers. I want to create a formulated column which gives me the result of the difference between 2 datetime columns in a decimal format (e.g. 24/06/2005 02:00:00 - 23/0...more >>

Hi Experts, I need to insert 60 millions records or 6GB Fixed width text File into SQL Server 2000. There are some problems with data, like date columns are in 6 char (mmddyy format) which needs to be convert into mm/dd/yyyy format and etc. Currently we have SQL scripts which import all d...more >>

Hi everyone, I have question about database ownership. We have a database called "price" "price" db was created by someone (John Doe) before, who is no longer working for our company. So in enterprise manager, I right-clicked on database "price" and go to property page, I see owner a...more >>

>> Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near ')'. << Here's the query select count(fld1) from (select fld1 from tbl1 where fld1 not in (select * from tbl2) or fld1 is null) Based on the data below, if I run just select fld1 from tbl1 where fld1 not in (s...more >>

Is it possible to list all tables in my 2000 database that have identity fields? I need to change some before replication. Thanks. David ...more >>

I'm getting a different resultset for queries using BETWEEN than I do using the less than and greater than operators. My BETWEEN resultset has 0 records and the resultset using the operators has 2500. Any thoughts? CREATE TABLE [dbo].[Prospects] ( [ProspectID] [int] IDENTITY (1, 1) NOT FOR...more >>

Hi I would like to import data from a TXT file, and insert the data into a table. I would like to do that with osql Can you help me ? best regards Frank...more >>

Hey all, The first 8 lines are fairly self-explanatory and work just fine. Line 9 is where the issue lies. What I'm trying to do with the case statement is produce a query wherein IF t3.van_type LIKE '%REF%' THEN the WHERE clause should read as "datediff(day,t2.maxi_dt,t1.sched_deprt_dt) > -1" ...more >>

Hi, Are there any tools which can dump data from Prod db into Test db, or any other method so that the data in Test db is same as Prod db. Thanks Vijay...more >>

Hi, Lets say I have the following simple table.. create table foo ( bar integer not null ) How can I select so that I get a single comma seperated result of all the values? i.e. if ... insert into foo values (1) insert into foo values (2) insert into foo values (3) and yet I ...more >>

Hello: I realize that this is "off of the subject", but I do not know where else to go. When I try to pull a field onto a Crystal report and then refresh the report, I get a SQL ODBC error that says "Incorrect syntax near the keyword...". The field is a two-word field in SQL called "P...more >>

I am looking for a to remove a whole database altogether programmatically. I am talking structure and data. I want nothing left. I realize that I have to stop the service first but after that I am at a loss thanks in advance, RogueIT...more >>

Hi, does anybody knows how I can name a resultset in a Stored Procdure. I would like to be able to type this i C# : Dataset.Tables["MyResult"]... instead of Dataset.Tables[0].... It could be some thing like this : (Select * from SomeTable) as MyResult I hope s...more >>

I have 2 tables: Suppliers: SupplierID(key), SupplierName, SupplierAddress, … Receipts: SupplierID(key), ReceiptNumber(key), DateTime, PartID, Quantity I want to select the last 5 receipts for each supplier and return the following information: SupplierID, SupplierName, ReceiptNumber, Date...more >>

Can anyone tell me if these are possible in SQL and if so how do I declare them. thanks...more >>

I have a table with UserID, "other values". I have sorted this result set in UserID order, therefore I have a result set: UserID 11111, .... 11111,..... 22222,..... 33333,..... 33333,.... Now, I want the FIRST value from each UserID field; i.e., drop duplicate UserID rows. How? ...more >>

Hi I'm migrating database server from one server to another. During this process i'm unable to host my database to the new server. I often get "PERMISSION DENIED (Error 229)". Can any one suggest to me? ...more >>

Dear all, When I try to open some stored procedure i receive the following error: “Encrypted object is not transferable, and script can not be generated” What the hell is happening? I might say that everything is fine but... Thanks in advance and best regards,...more >>

Hi everybody, When i run this query Select * into #user_tbl from OpenDataSource('SQLOLEDB','DataSource=DBServer;User ID=sa;Password=sa').USERDB.dbo.user_tbl from a Query Analyzer window it runs fine. However when i use the same statement in a stored procedure using dynamic sql like...more >>

Hi, In a stored proc, I have the following in a SELECT statement, CAST(dbo.tblPackaging.pkgPCLength AS varchar(18)) + (SELECT CASE WHEN dbo.tblPackaging.pkgUM = 'F' Then ' Ft.' ELSE ' In.' END) AS txtPcLength pkgPCLenght is defined decimal(12,6) - what the above returns is 1243.00000...more >>

I have a table of sales information: EmployeeID int Sale single Date date I want to create a recordset, based upon a YEAR date range: EmployeeID Sum of Sales Year Sample input records: 11111,10.00,#4/5/2004# 11111,4.95,#1/15/2005# 11111,6.00,#6/6/2005# (obviously, LOTS of em...more >>

Server = SQL Server 2000 SP3a + Windows 2000SP4 Is there a way to view who / what and when database objects have been modified. Im trying to figure out if someone is messing around with things like triggers on tables, as simple things like a trigger "After Insert, Update" strangely chan...more >>

Hi I want to be able to select 10 random records from my table for display on my asp.net page. The idea is that each time the page is viewed, a different selection of records is viewable. How can I achieve the select 10 random records sql statement? Cheers Dan...more >>

Hi, May i know the below sentence is correct: select [a.column/c.column] AS D From a,b,c where a=b and b=c If it is not possible, what is the correct way..?? thanks in advance. Daniel....more >>

Hi, I have a SQL Server setup to use windows authentication to accept adodb connections from an intranet in IIS. While SQL/IIS are on the same machine it uses the following connect: Provider=sqloledb Data Source=(local) Initial Catalog=Intranet Integrated Security=SSPI Remote users f...more >>

Suppose I have a master Table A(EmployeeID,EmployeeName) and transaction Table B(EmployeeID,TransCode,TransDate,Amount) I want to to create sql that returns each employee with his last transaction and transaction amount and code The Final result must be a datasheet like This: EmployeeID ...more >>

after delete the table (declared varible), i want to set the field with identity property to 1,is there any system procedure can do this? RGDS bill ...more >>

I'm trying to execute an exe file using the stored procedure "xp_cmdshell". The exe file is a simple program creating an icon in the system tray. I found that after executing the command " exec master..xp_cmdshell 'test.exe' ", the program is lauched in the background which can be found in the ta...more >>

Hi, How can I access Image/text fields from the deleted/inserted tables in a trigger? I want to access both if it is a update. Thanks Henrik ...more >>