I need help with a query. Here are my tables: [ServiceCategory] [ServiceCatID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY) [ServiceCategory] [char] (50) NULL , [Services] [ServiceID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY) [ServiceCatID] [int] NULL , [ServiceName] [char] (...more >>

I have a linked server and I want to create a function that returns a table from the results of openquery I want to be able to pass variable to the openquery command i.e. create function myFunc (@var1 smallint,@var2 vharcha(10) returns table as return ( select * from openquery(linkedServ...more >>

Hi, I am building in a data mart using SQL server DTS. I have confusion that Example Suppose we have text file for Cust table. It has 3 customers A, B, C This information comes in text file from legacy system. We load this data in cust table in staging. Now we get a new te...more >>

Hi, I have a stored proc where I reference another table in a different DB as follows: SELECT ... FROM databaseName.dbo.tableName My question is how do I reference the table if its on another server? For example: SELECT ... FROM serverName.databaseName.dbo.tableName Thanks. Khurr...more >>

Hi! I'm looking for a sql-statement which returns the minimum of one or more columns per row. For example: ID col_1 col_2 col_3 1 1 2 3 2 3 4 5 3 3 2 3 select ID, mininum_statement (col_1, col_2, col_3) as minimum from table should return: ID minimum 1 1 2 3 3 2 Thanks, ...more >>

Hi all, I should input chinese in the SQL database. However, the database cannot save any chinese word. I would like to ask do I need install anything in order to save the chinese word in the database? Thanks a lot, Ray ...more >>

Ok im calling a T-SQL query with 2 "params" in the query analyzer and it takes 1 seconds to execute. I put this query in a store procedure, execute this sp in the query analyser with the SAME params and it takes 100% CPU for 18 seconds or so and then pop this error msg: [Microsoft][ODBC SQL Serve...more >>

I'm trying to clone the source table and put it in the [tempdb] database. The code works fine, calling the Script and executing it. The new name is a valid qualifier for a local temporary table, but each time I'm trying to reference the temp table variable, i get a message that the server cannot...more >>

Hi, I have a varchar field in a database representing the current = quarter for the current year, for example '1-2004' <-- First Quarter 2004 '3-1999' <-- Third Quarter 1999 This was just handed to me, I would have done this differently, but ... = I just have to use this. Basically I hav...more >>

A report views fine to screen but when sent to printer the follwoing error appears. The text, ntext, and image data types cannot be used in an ORDER BY clause. Why to screen and not to print? Any ideas? TIA Warway ...more >>

Hi all, Is it possible to update/edit a UDF through code? Or must editing a UDF be done in Enterprise Manager? Thanks. -- Lucas Tam (REMOVEnntp@rogers.com) Please delete "REMOVE" from the e-mail address when replying. http://members.ebay.com/aboutme/coolspot18/...more >>

Hi, I've got a procedure which calls other procedure. The inner one executes a select statement which returns precisely 1 value. How can I assign this value to some variable in the outer procedure? Piotrek ...more >>

Hello, How can I do to restart the identity counter of a table after deleting all its data? Thanks in advance. Adriana S. L. ...more >>

Hi, I am trying to acheive the following .... If a query is runs long (I have a timeout parameter) then I want to cancel the execution of the query. Not that I am not bothered about the query result only, but I want to make sure the server thread processing the query also is stopped. I h...more >>

Hi I have a call log database in MS SQL 2000 and I am trying to contruct a query that will show me the most recent calls to each telephone number. So I have a database which is just a list of all the calls made on our phone system. I would like to constuct a query that will only show me a li...more >>

Hallo zusammen, kann mir jemand folgendes Prozedere erklären: SQL Statement: SELECT ID,BILL FROM TABELLE WHERE NOT SUBSTRING(BILL, CHARINDEX('@', BILL)+ 1, LEN(BILL)) IN (SELECT STRID FROM TABELLE2) Läuft unter SQL 2000, SQL 7.0 stirbt!!!! Vielen Dank MfG Peter Trube ...more >>

I am trying to delete specific rows from a distributed database table using the opendatasource command using a join with a local table and a column alias for the dustributed database table as follows: delete s from opendatasource('SQLOLEDB', 'Data Source=server;User ID=sa;password=wha...more >>

Hi Guys, I want to know how SQL server handles and performs better in the following case. I have a big a stored proceudre with IF ELSE clauses. Like this Create Proc p_test(@p1,@p2) AS IF @p1 = 1 Begin labl..labla end else if @p1 =2 Begin --- End Else If @p3 =3 Begin End ...more >>

Hi there! :O) is this a good way to get the select list of a table ? --- use pubs go declare @table_name varchar(1000) declare @column_name varchar(255) declare @column_list varchar(1000) set @column_list = '' set @table_name = 'authors' declare cur cursor for select col_name(objec...more >>

If this is in the wrong newsgroup I do apologize. I am working my way along here learning SQL, however, I was thrown into being the Admin of a SQL Server 2000. Anyways, we were given step by step instructions on how to change some tables in the database, which we did and it corrected the proble...more >>

Hi folks! :O) Let's say I create a stored procedure which needs a table name as parameter, what data type would be best for this type of information ? Since sp_columns (and some others) uses nvarchar(384) for a table name, i guess we should use the same thing ? and btw, anyone knows why thi...more >>

I need help with this kind of query... USE Northwind Go SELECT det.ProductID, det.UnitPrice, ord.OrderDate FROM [Order Details] det JOIN Orders ord ON det.OrderID = ord.OrderID WHERE det.ProductID <= 5 ORDER BY det.ProductID, ord.OrderDate This query returns 124 records from Northwind.[...more >>

When running a test insert routine as follows: DECLARE @counter INT SET @counter = 1 WHILE (@counter < 1000000) -- 1 million rows BEGIN BEGIN TRAN INSERT INTO test11 (col1, col2) VALUES (@counter, 'USER' + CAST(@counter AS VARCHAR(6))) COMMIT SET @counter = @counter + 1 END ...more >>

Hi All Has anyone created a field in SQL to hold bar codes? If so, what kind of field should it be and how do you get the information into it. I know on the bar code tag, there is a bunch of numbers as well as the bar code itself, I'm assuming that the numbers co-relate to the bar code. Is th...more >>

I know what does ANSI_NULL OFF/ON do, but i fail to understand the use of it. I mean can anybody give an instance as to where can we use this. When i was going through BOL for answer, i came accross a Statement 'If SET ANSI_NULLS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables w...more >>

Select * from tab1 where name = 'xyz' Select * from tab1 where name like 'xyz' Is there any difference between the two ? Thanks Swati ...more >>

Hi everyone! Okay I can get an outer join to work correctly with three tables, but how do I get BOTH matching and non-matching rows to appear in the final output? 1) Each table is essentially a sub category of its parent Table1 -> Table2 -> Table3 2) and I c...more >>

I've just been trying to do this. I looked on Google and it seems to be a common problem with no obvious solution. I've seen various solutions which don't seem exactly elegant, so I figured I'd post the solution I came up with. It's effectively a single line solution, albeit with various embed...more >>

SQL Server 2000. Here's some code giving an example of the type of thing I am trying to do : == create table parentTable (pp_pk int primary key, pp_code varchar(20)) create table childTable (cc_pk int primary key, cc_pp int, cc_code varchar(20)) insert parentTable values (1, 'p1') insert ...more >>

Hi, I run sp_help 'table' and it returns a bunch of indexes named 'hind_xyz...', nonclustered, hypothetical, auto create located on PRIMARY. I assume that were created when I used index tuning wizzard. All these indexes will not show under the Tools-->manage indexes of the query analyzer menu. H...more >>

Hi All , I have a problem for which i researched and was not able to find a proper solution . This is my task . I have database called "Source" with some 50-60 tables . Some tables have a column called company Now my task to create a new database from the "SOurce Database " . Cop...more >>

Hi I am using SQL 2000 and trying to write what sounds like a simple store proc. I want to be able to select a specifc column based on the value of a parameter. In this case I am passing a specific month to my select statement and only want that month in the result set. The table has ...more >>

Hi, I wanted to know if this can be done. Getting the current data of a column just before doing UPDATE. And No, I do not want to do a separate SELECT before that. Any ideas TIA...more >>

I have two queries running, the first is select * from <table name> and the other is select distinct <list of column names> from <table name> where both table names are the same. I get the following error Server: Msg 3628, Level 16, State 1, Line 1 A floating point exception occurred in...more >>

Using SQL server 2000... I am attempting to loop through a table containing names of views and update a column with the current row count of the view. I am getting the following error message Server: Msg 245, Level 16, State 1, Procedure sExceptionCounts, Line 34 Syntax error converting t...more >>

When running stored procedures, osql prints a blank line between each set of results in a batch. Is there anyway I can get rid of that blank line? It is causing problems for me when I export the result set to an Excel sheet and try to run another process on the Excel file. For the second process I ...more >>

Our application has a query which we cannot get to run quickly. The whole scenario is too complicated to post here, but it seems that the performance depends on a small part of the query, and hopefully someone can help with that. We have a table CREATE TABLE tblActuals( BookingCode ...more >>

I am using an SQR program to insert records into a PeopleSoft table (PS_JOB) Sometimes the program errors out with an SQL Error 8162, when it is trying to insert one of the records. The error is irratic since it does not always occur. We have been unable to determine exactly what this erro...more >>

Hello, I have been asked to do a view, where a bit value needs to be converted into a text value (amount lots of other fields). My current idea is to use a function to say... if @Check_Value = 1 Return 'Yes' else Return 'No' Please note I know its rubbish code but its just ...more >>

Hello, I would like to retrieve rows in an specific way. I want to list a maximun of 10 rows in a window. User will ask for next 10 rows or previous 10 rows. But I found only "TOP" clause. How can I do it efficiently? Thank you!!...more >>