Hi all, I am trying to create a relationship with a table in another database, but none of the existing mechanisms(data diagrams, etc) seem to let me. Any ideas?:? Thanks, Gordon ...more >>

Hi Newbie to SQL 2000, and i'm having a bit of a problem I have a varchar column containing data such as '99 702 556' However when i use the following select to retrieve and sort it, it's sorted alphabetically, instead of numerical -- Star select homedirsiz from dbo.tblhistor where homedirs...more >>

Hi all Is there any way to give a select in MS SQL server with a where clause covering all the fields in the table ( To search in all the fields Eg : Select * from fAdrBook where <AllFields> = ‘John†I want to know what keyword to use instead of AllFields and how to specify the select and ...more >>

Hi. Newbie to SQL 2000, and i'm having a bit of a problem. I have a varchar column containing data such as '99 702 556'. However when i use the following select to retrieve and sort it, it's sorted alphabetically, instead of numerical. -- Start select homedirsize from dbo.tblhistory whe...more >>

Hi All I know I've got to post my schema, etc, but I think its more of a logic thing than table-specific. If you can imagine how an Excel pivot table would display it, I want all of my sales people listed down the left hand side of my report, all my stock categories listed along the top of ...more >>

I'm trying to generate a mailing label list but the host application has an odd requirement and I'm not sure how to achive this Hopefully this explains: I have two tables: tblCompany --------------- CompanyID {pk} Company Address sample data: 1 | xyz company | 456 anystreet...more >>

Hi, How can I "compress" a database and it contents without loosing data or damaging anything? I would like to speed optimize, compress, re-index and so and make a job that runs every month or so... What do you recommend? Thanx! Jakob Denmark ...more >>

Hi, How do I copy a complete database, with everything in it, tables, stores procedures etc. to a NEW database (for testing) on the same (local) server? It has to be created on another drive that the default (C:\,,,,,SQL dir)... Meaning translog and data files needs to be places on another lo...more >>

Hi I am creating a report in tabular format using Reporting Service which I need to group and page break by on of the field in the data. However, the group by field is required to display on the page header. But when I do so, the field shows the same value on each page. Can anyone suggest me how t...more >>

HI I have a stored procedure that has a parameter. I would like to pass the parameter I have set up in Reporting Services to pass that whenever the SP is being executed. However, I am getting the following error "... Procedure 'sp_GetDate' expects parameter '@Project', which was not supplied." H...more >>

Hi All, I have to develop stored procedures that work on a distributed environment with a lot of SQL servers spreading out all over the country. In addition to firewall/security problems, I have to deal with SQL error. First of all, I build a view that query data from all SQL Servers, and...more >>

I have the following query, which currently returns an empty set (0 rows) if the WHERE criteria is not met, and 1 row (with a currency value in a single column) if the WHERE clause matches at least one row: ========================= -- This is used as a subquery SELECT MAX(Levels...more >>

I have several tables where the primary key is a guid. The tables are ususally accessed to retrieve a single record based on the primary key value. Generally, the guids are created before the record is added vs. using newid(). In this scenario, does it make any sense for the guid primary key to...more >>

Hi All, I have a UDF which returns table, let's say dbo.TableFunc(@id int). The table returned contains ID field. I have another table Item which also contains the field named ID. When I try to use the following query SQL server reports a syntax error. SELECT i.ID FROM Item AS i WHERE ...more >>

Is there a way to pause execution of a query and resume it, or set the priority of a query? I have a query that I run periodically, but it's very large and bogs my server down. It's not time sensitive, so I don't care if it takes an hour to run, I just want to make sure my other applications ta...more >>

how can i select the name of the database i am working with?? i have a stored procedure that needs to select the name of the current database in order for it to execute properly. tia ...more >>

I want to insert records from one table (table A) into another table (table B). - Table B contains an IDENTITY column. Table A contains the same columns as Table B. - The Table A column that matches the IDENTITY column in Table B are unique (they do NOT appear in Table B). I wan...more >>

2 Questions below: Suppose I have two stored procedures (spA and spB), and I want to execute spB from within spA. QUESTION 1: What syntax can/should be used? Can I simply put this line in spA? Any better syntax? exec spB @someParam1, @someParam2 QUESTION 2: Is it generally conside...more >>

Hi, I'm using SQL 2000. I'm looking for a way to compare string values and return the difference between the 2. Eg 1. String 1 I can walk String 2 I can walk to home Return Results to home Eg 2. String 1 Darrin can walk String 2 can walk Return Results Darrin ...more >>

Hello all, I have this following query, this takes 3 seconds when I run first time, but when I run 2nd time it takes about 55 seconds. Any thought would be greatly appreciated Select lii.ms_num, lii.pn_parcel_num, lii.co_county From List_info_inactive (nolock) lii, Lis_info_Active ...more >>

Hi All Does anyone know how I can achieve the following in a cursor select... THIS IS MY SELECT - the variables fetch column and table names from other cursors ---------------------------------------------------------------------------- -------------------- Set @SQL = 'SELECT COUNT(' + @...more >>

Hi, I have a SQL server agent job which is scheduled to run at particular intervals. I have a table which is being updated when this job runs at the scheduled time. The table has a column "NextRunTime", which holds the next run time of the scheduled job. But my problem is that since I am updat...more >>

I did table Horizontal partitiong and created view on them. Tables are created with check constraints, but when I query the view with check constraint column in where clause, why it is scaning all these tables Thank -Reddy...more >>

Hello All, Curious to know if it is possible to disable a trigger on a view, rather then dropping it? I know it can be done on a table, but, am unsure of the syntax for a view. This works for tables: ALTER TABLE tblOrders DISABLE TRIGGER ins_A1 Thanks in advance, R ...more >>

Hi, My Scenario: I am working on VB 6.0 and SQL server 2000 and the communication between the Front end and the Back end is through HTTP. I am having an ASP page in the server side and executing the query/stored procedure by connecting to this page through a HTTP request. The response to th...more >>

I have two tables... One has ad information (pk, adid, title, date, vendor, etc) the other stores what website displays that ad.. (pk, adid, pageid) I'd like to build a query that would return the title, vendor, and total number of time that the ad was displayed.... any help would be most ...more >>

What would be the best way to store HTML in MS SQL Server? text, varchar(xx), other?...more >>

SQL Server 2000 with Service Pack 3A. I'm working on a script to update a series of databases. Basically I need to add a series of Tables, UDT's, Defaults and UDF's. Everything was going along fine until I tried: if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DEF_...more >>

HI There, I am relatively new to SQL Server and would like to know how I can only constrain the rows returned from a query when the date is less than 3 days from the current day? Could someone possibly give me a little code snippet? Thanks in advance for your answer Sean ...more >>

I have got three tabels as below: User: ---------------------- UserID GUID UserName Varchar Study: --------------------- StudyID GUID StudyName Varchar Response: -------------------- ResponseID GUID UserID GUID (Foriegn key to USer) StudyID ...more >>

Is there a tool that will offer suggestions on how to optimize SQL code?...more >>

does anyone know how to update a comma delimited list? Thanks Terry...more >>

Hi trying to do: declare @myvar varchar (59) set @myvar = (select name from myFirstTable where number=2) exec ('select * from mySecondTable where name = ' + @myvar) --(code is for testing... doesnt really do anyting, but does it in a strange yet interesting way) but I get Invalid c...more >>

Hi Freinds, SQL 2000 I have a DTS where reads a file from other server: \\server2\uploads\tree.csv When I'm inside the DTS and running it, then everything is fine. Even if I right click on DTS and run , works fine. The problem comes when I schedule the DTS as a job. JOB Fails : INVALID...more >>

H I am integrating to Oracle 8i from sql7 using linked server. I use openquery in stored procs. I query procedures thar return results fron Oracle. I use oledb provider for odbc. I have a sqljob executing a stored proc once a minute to run the query. I use a cursor to run stored procs that execute...more >>

Hi, We are experiencing performance problem because of high impact queries and we would like to be alerted by sql agent upon performance issue. During definition of performance condition alert, which Objects and Counter are the best to monitor high impact queries? Is it possib...more >>

I've written a Stored Proc where I pass in 5 variables and I need to write my select statement based on if those variables actually contain values. Something like this: SELECT Field1, Field2 FROM Table1 WHERE Field3 = Variable1 (if Variable2 <> '' AND Field4 = Variable2) (if Va...more >>

Hi I have a query that returns integers, but instead of returning those numbers, I want to return the OR operation for each one of them. For example: MyTable --------- 12 11 100 4 I would need to have function like this (similar to the count(*), sum(..) .... ) Select BitWise(N...more >>

I have the following table: =================== CREATE TABLE Accounts ( AccountID char(5), CONSTRAINT PK_Accounts PRIMARY KEY CLUSTERED (AccountID) ) INSERT INTO Accounts VALUES (AB101) INSERT INTO Accounts VALUES (AB102) INSERT INTO Accounts VALUES (AB103) INSERT INTO Accou...more >>

I know there is a procedure that you can call where you pass in a sql statement that builds another sql statement and it executes the resultant sql. Does anyone know the name of that procedure? Thanks, Doug ...more >>

I am trying to get a table of data that has values for some records but puts null values against those that do not exist eg year month gear value 2004 1 1 1 2004 1 2 null 2004 2 1 null 2004 2 2 5 I have the table with all the real values in, ...more >>

Hi Is it possible from script to obtain a Username or Password or Authentication settings?? -- Kind Regards Rikesh (SQL2K-SP3/W2K-SP4) ...more >>

Hi, Is there a way to directly dump the resultset of a SQL SELECT statement to a text file on a local drive (ie. C:\) TIA, Eric...more >>

I am trying to dump out scripts for stored procedure and wondering how I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output. I gather this option becomes part of stored procedure script on creation time...! I couldn't find any flags in SQL DMO that could be used to ignore th...more >>

Hello, Does anybody know how to convert an varbinary (that contains a double value written by a C++ application) into a SQL server float ?. This conversion is not supported by the "convert" function. I.e: 1.0 is stored as 0x3FF0000000000000 in a C++ double and SQL server float. Best ...more >>

I need to know if a simultaneous access between Backup and Restore on the same backup log file can damage the file. I have a server that every 2 minutes backup the transaction log on a file, then another server restore a standby DB, reading the same file. The backup start at even minutes, the restor...more >>

Good day I have a stored proc that is used for searching customers, i.e. it returns a resultset of customers based on some input parameters. But i need to add functionality that limits the number of rows returned, i.e. returns only first 100 rows. Now, the question is how do i let the user know th...more >>

I'm tossing this question out there to see if anyone has come accross a similar problem. Suppose I have a table that is date sensitive containing Date/Entity/RowValue. I want to build a query that gives me a simple growth calculation from rowvalue1 to rowvalue2 for each Entity using the ...more >>

CREATE FUNCTION TF ( [id] ) RETURNS @tbl TABLE ( Col1 INT, Col2 DATETIME, ) AS .... CREATE TABLE tbl1 ( [id] INT, [name] VARCHAR(50), [description] VARCHAR(255) ) SELECT * FROM tbl1 JOIN dbo.TF(tbl1....more >>

Hi We can able to see some dead lock error in Sql Server Log of Enterprise Manager But users are telling that they don't receive any error. Error Handler were already declared in the application Why its not throwing out any error to the user Pls help us Regards Sa ...more >>

I pass some parameters to a stored procedure. I need to build a select query with this parameters to open a cursor. For example I pass the parameters A,B and C. I need to build a query in this form: Select * from MyTable Where A = 1 and B=2 and C=3 with this query I will open the cursor. Like you...more >>

I need to select the most recent entry for each unique item stored in a table. For the purposes of this posting each item is stored multiple times with a date time stamp. I want to be able to select only the most recent row for each item. So far this is the best I have come up with: SELECT ...more >>

Here's a little problem I'm stuck with tonight. To give a very simple example and synopsis of the problem, I have a large quantity of unrelated pools of money that I need to distribute evenly to people (can anywhere from 1 to 32 people per pool). The solution I'm seeking is how to deal with thos...more >>