I want to be able to count number of rows with values > 75. For example, I have the following statement, select p.PositionID,jobTitle,Qualified=count(screentestscore > 75) from Position p left join applicant a on p.PositionID = a.PositionID group by p.positionID,p.JobTitle This gives me a...more >>

I don't have any troubles concatenating two fields together but so far, I haven't been able to concatenate more than two fields. (Order.FirstName+' '+order.LastName) as Name will work (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name will not work. Any suggestions?...more >>

Hi all, I brought this up some time back, but I have a trigger that I was tasked, begrudgingly, with converting from Oracle to SQLserver. After the comments I received, which were great, it has me concerned that I'm trying to convert line-for-line a trigger that might very well be poorly w...more >>

Hello, I would like to get the data in an XML column back from a SQL Server Web service to my C# app. I have defined the stored proc for the webmethod as: CREATE PROC dbo.GetJobCandidateResumeXml ( @JobCandidateId int ) AS SELECT Resume as ResumeXML FROM AdventureWorks.HumanResources.Job...more >>

I have the following tables and data: ********************************************* drop table jobs drop table apps create table Jobs ( PositionID int, Title varchar(30) ) Create table apps ( AppID int identity, PositionID int, userID int, resume smalldateTime, test small...more >>

Hi All, I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also UPDATED, DELETED. If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no. I would like know if have way to I specifique that DELETE can be used only in 'TPARENT' If user try use DELETE in CLILD...more >>

I would like the resultant column to be comma separated. Here is sample ddl set nocount on go create table z_my_tbl_del ( i int, n char(8) ) go insert z_my_tbl_del values(1,'john') insert z_my_tbl_del values(2,'mary') insert z_my_tbl_del values(3,'luke') insert z_my_tbl_del values(4,'...more >>

Hi all, Does someone have a script to copy OS backup files (.bak) to another location and rename that file with filename.DATE appended to end? This way no file will be overwritten as the copies are performed nightly... Thanks...M ...more >>

Hi all, How can I obtain the following results showing below. This is sort of tricky sequel statements. Any help would greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Prospect', 'u') IS NOT NULL DROP TABLE #Prospect GO CREATE TABLE #Prospect ( Person_id INT NU...more >>

Hello, Is there a Best Practice or a script I can run daily to monitor the rate of growth of about 12 databases? Thanks, Pancho...more >>

Hi, I have written a trigger that fires when a new row is inserted into a table. The table contains an identity column on the ID field and I need to use this ID value in another part of the trigger. I first tried to get the new ID using: SELECT @userid = ID FROM inserted but this ret...more >>

i'm considering implementing my own artificial key table that would work similarly to an identity value. insert transactions would first have to visit this artificial key table, lock the artificial key row defining the key needed by the data row to be inserted. after the table lock, it would get...more >>

table creation stuff first: CREATE TABLE [dbo].[PhoneCapability] ( [PhoneID] [int] NOT NULL , [MonoFormat] [smallint] NOT NULL , [PolyFormat] [smallint] NOT NULLNOT ) GO CREATE TABLE [dbo].[TypeLookup] ( [RowID] [int] IDENTITY (1, 1) NOT NULL , [TypeName] [varchar] (25) NOT NULL ,...more >>

SQL 2k sp4 Since I can't really share my ddl, I'm hoping someone has seen a situation like this who knows the answer to my question. Why would a query that contains a WHERE clause that says Field1 IS NULL return records where there is data in Field1? My query has a couple of JOINs, but ...more >>

Why are default parameters not working for functions? I get an error when I try to call my function with two parameters? CREATE FUNCTION dbo.GetReports ( @ManagerId varchar(50), @ParentOrganizationId varchar(50), @Exclude int = NULL ) select * from GetReports('KMD', NULL) Returns ...more >>

Hi all, I have the following queries and need some assistance from you. Please read the businss rules and desire result below. The Person_id and Source_id are always pass in as parameters. Any helps would greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Prospect', 'u') IS NOT NULL DR...more >>

SQL 2000. The CASE syntax is described like this: Simple CASE function: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Input_expression is "any valid Microsoft® SQL Server™ expressio...more >>

Hi all. I'm getting a weird error in a stored procedure: Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla, línea 65535 El nombre de objeto 'RegistrarTabla' no es válido. That's spanish but should be: Server: message 208, level 16, state 6, procedure 'RegisterTable', ...more >>

I need to create a stored proc that loops through records in a table, reads a field and updates that record with a new value. I can't seem to find any samples that do something similar. Can anyone help get me started? Thanks Bob ...more >>

I have a query that produce data for contacts, and I need this to end up in public folders and updates once a day automatically, since it is no direct access to outlook what I need to do? What kind of code would let me put my data to public folders?...more >>

Ok Gurus, How do you view a very long text field one that is longer than the viewable length in query analyzer? using cascading substrings did not seem to work as I am looking for the return command of </acord>. I may have used substring incorrectly. select id, quote_req, (substring(quot...more >>

Can I declare a TABLE variable and use another variable for a column name? For example, can I do this; Declare @FirstName nchar(10) Set @FirstName = 'John' Declare @NamesTable Table (@FirstName nchar(10) Not Null) I keep getting errors when I try to do this and it's crucial that I know w...more >>

I need some help creating a QUERY.. here's an example of what I need to do: say for instance my database contains 3 values: 4012345 3012345 4055555 I want to create a query that will count the number of records where the last 5 are unique. In the example that count would equal 2. I won'...more >>

Hi, Are there any operators/SPs/functions for shifting bits in SQL? TIA ...more >>

Hi All, I am currently trying to create some kind of Task-Storage (big TEXT field) to write data to. To try the WRITETEXT / UPDATETEXT functions I created the following sample in order to better understand what's going on inside of it. The Sample should write a certain amount of text into the f...more >>

We have few tables in our application. Basically they are used as temporary tables. Each day they start with no records in it(empty) and as the day goes they are filled with the data and at the end of the day they will be truncated to get ready for the next day. We have a high transaction rate...more >>

Hi USING SQL SERVER 2000 We have a clustered index in a Table with a 100 millions of records. This table grows 1.000.000 of records per month. We have a table of clients and another one of accounts. Both using Clustered Index. What index to use? why? We have performance problems!...more >>

Hi, I am trying to insert a record into an Oracle 9.2 instance from Microsoft's SQL Server 2000 using openquery, it is really slow, someone knows if it has to do with differences between SQL Collation setting and Oracle's NLS_SORT, my current setings are: SQL: SQL_Latin1_General_CP1_CI_A...more >>

In Enterprise Manager for SQL Server 2000, I used to choose "Open Table..." and then "Return All rows" to be able to update data directly into a grid. This tool was rapid and useful, but i'm not able to find a such tool in SQL Server 2005 environment... Is there a tool like this? If not, someb...more >>

I want to use a stored proc for this. I'm wanting to use the IN statement on a where clause using a declared vaiable, but when I use the declared variable, it won't give me the correct results. What can I do to get this to work? I can string together the sql and then EXEC the variable in th...more >>

Hi - any idea how to get a view like below using SQL2K, that signals when a column changes value in the next row? thanks hals_left Employee Project Check John Doe 4578j John Doe 86785x John Doe 543p 1 Jane Doe 87x Jane Doe 89976m ...more >>

Help! We do not use encryption at all on our stored procedure code, but all the sudden "WITH ENCRYPTION" has been appearing in our stored procedures by itself. I don't know if it is a 3rd party tool we are useing or what (we use a few sql tools from red-gate), but now we can not view or edit o...more >>

can i create a non-updatable view? i cannot use user level restrictions. ...more >>

Hi, I have code like this : CREATE PROCEDURE GET_OFFENDER_DATA @value1 VARCHAR(8),@name VARCHAR(30) OUTPUT,@sex VARCHAR(1) OUTPUT AS SELECT NAME,SEX from table1 where ...... Now I want to assign the value of Name and sex from the select statement to variables @name and @sex. H...more >>

Is it possible to use a stored proc which returns a data set as a data source in another stored proc? Or "Select" data from a proc which returns a data set? Thanks, Michael...more >>

I am trying to write my first OPENXML query. I have a table that holds the text of the XML and I want to write a query that maps that data into a table. Table_xml is a 1 column, multi-row (text) table. Is there a way to get that data into a regular table structure... SELECT openxml fro...more >>

Evening gurus 1. I need all the names in the table named "InstoPriority" IP even when there is no match. I have referred to SQL books online topic "Left Outer Join operator" but I am only getting matches Example: if I have written 2 notes on 2 different companies then I want to see th...more >>

Dear colleagues, I am doing a list of our DTS and would need to know (the ones are scheduled, of course) the time inverted in each launching from a concrete date Has anyone ever used or needed for this value? I am using VB as tool and exploring the dts with dtspkg.dll (DTS.CONNECTIONS, DTS.G...more >>

I have a query about writing code to use a sql server to loop through a record set in one table and then depending on the record insert a row into a new table. Each night I will receive a merge table file with new values in it. Depending on these values I will want to insert a record across ...more >>

CREATE TABLE [dbo].[FreeSpace] ( [Drive] [char] (1) not null , [MB_Free] [int] not null , [day_time] [datetime] default getdate()NOT NULL ) ON [PRIMARY] insert into FreeSpace exec xp_fixeddrives I have created this table to capture and monitor the diskspace available to me I am tryin...more >>

hi , Is it possible to use indexof , substring in trigger. I am using sqlserver 2005. If it is possible please help me how i have to do the same. thanks sathya narayanan narayanan@gsdindia.com ...more >>

Hello, Could someone tell me if there's a tool to automaticaly re-indent Transact-SQL code? I've got some huge scripts to re-indent, and I wouldn't like to spend to much time with that. Thx. JN....more >>

Hi guys, I would like to know if the following is possible and if yes, how: I have two tables: 1. Table1 with fields T1No (PK), SomeData1 2. Table2 with fields T2No (PK), SomeData2, T1 (FK) Is it somehow possible to insert data into both Tables which is linked with the Foreign Key by on...more >>

My database is much faster now because I found that I only need around 150,000 records in a table for any single query. The date selection will always be in a certain range. So if I break my tables up into smaller tables based on the maximum date range, its much faster. I don't understand w...more >>

Hello, I'm in the task of changing the data type of a column in a SQL Server 2000 database which already has default and check constraints. Being unable to directly issue an ALTER TABLE command, I guess that the only way will be to delete the check constraints, the default constraints, cha...more >>

Hi, I want to find the size of the MDF file for a select database. How can I do this? Thanks in advance. Nadee. ...more >>

Hi. When I do update queries, I use to get an error refering intercalation. I searched about this and I can fix that putting COLLATE Modern_Spanish_CI_AS in the WHERE: UPDATE ... WHERE field COLLATE Modern_Spanish_CI_AS = ... Is there a way to avoid that? -- Regards, Diego F. ...more >>

I've got a fairly tricky report to produce, so I'm trying to build it up bit by bit, but I've fallen at the first hurdle! I need to produce a view that lists all PartNo's and for each, what quantity is in what location, e.g. PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total WE1202...more >>

Hi. I'm writing delete queries and I have the following question: if a table has foreign keys, and I delete the fields in the tables that references the table I want to delete, can I delete the table or must I delete the restrictions too? I'm having errors and I'm thinking if that's the pro...more >>

Hi, If I have the following SP listed below. Then run with parameter like '', the LEN function in the SP is giving wrong result(it return 2). Anyone have any ideal why ? I encountered similar problem in SP written by my ex-colleague. Create procedure TestLen(@p1 char(50) as begin ...more >>