I need a little help with this query. I am querying the value of field, but I need to add to the logic that if there is no record then the value = 1 This is the query as I have it: ------------- SELECT * FROM dbo.ParametersUsers WHERE UserId = 1713 and ParamName = 'showpopups' ---...more >>

Hi all, I found this trigger and modified it to suit my needs. The original post can be found at: http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/efa94006811f18cf?hl=en Basically, what I am trying to do is get the Qty_To_Ship amounts of each line item in the s...more >>

I've been playing with a couple of ways of doing this, based on the ideas in the article "The Cost of GUID's as Primary Keys" : http://www.informit.com/articles/article.asp?p=25862&seqNum=1&rl=1 and a couple of other online sources and I've put together the following: create view builtin...more >>

I've been told that Oracle is a better choice than MSSQL for really large data sets and/or serious security concerns. But that was told to me by Oracle people. Without trying to start a war, can anyone give in a nutshell the relative merits of the two products? ...more >>

Hi All, This sql isn't working and I'm not sure exacly why. update verninfo.dbo.person set contno = (select contno from #UsersInVern where (upper(rtrim(#UsersInVern.first)) + upper(rtrim(#UsersInVern.last))) = upper(rtrim(verninfo.dbo.person.fname)) + upper(rtrim(vernin...more >>

I've made "crosstab" queries in Access - it this doable in sql?? My table has: ID TestNumber Score Each user can have 1-3 records, one for each of the 3 tests. I need my output to look like this: ID Test1 Test2 Test3 --------------------------------- 1 score1 sc...more >>

I have a union on two select statements and the rownums obviously repeat. Is there any way I change the rownum with a forumula? I'd like to make 1st set of results to only be even numbers and the other odd numbers. Also if possible can I sort the entire set of results by date. Select r...more >>

I have a union on two select statements and the rownums obviously repeat. Is there any way I change the rownum with a forumula? I'd like to make 1st set of results to only be even numbers and the other odd numbers. Also if possible can I sort the entire set of results by date. Select r...more >>

I have some bcp archive files without the data format. Is there a way to get the data format from the archive files. Thanks...more >>

The SQL below doesn’t work but may give an idea of what I’m trying to achieve. That is to obtain the count of a non-distinct combination of columns and a count of a distinct combination of columns SELECT count(cola, colb, colc) FROM tab1 SELECT count(distinct cola, colb, colc) FROM ...more >>

I would like to write the output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line of the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK) exist I append this code to my existing script. Please help me create th...more >>

I am trying to consolidate some code that I had replicated in many places in my DTS package by placing it in a Dynamic Property that sets a global variable. I can reference this variable through ActiveX Scripts with no problem. However, I can not figure out how to access it in an SQL query i...more >>

Let's assume I have a table with the following fields: City, ZipCode, Total ... and the following data: Roswell 32032 100 Roswell 32032 120 Roswell 34589 75 Atlanta 31902 345 Atlanta 35865 200 Atlanta 46321 110 Denver 31411 25 Denver 31411 ...more >>

Excuse me for my english. Let us start with the base. I work with SQLServer 2000. My table has 12 columns of the type varchar Ex.:(Condition1_Min, Condition1_Max, Condition2_Min, Condition2_Max etc....) There are values which must be interpreted like the numerical one and of other as alph...more >>

I have a stored proc that returns information depending on the particular column slelected by a passed in parameter. For Instance: Passed in parameters: @Rep = 876 @RepType = 2 SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.Id = T2.Id WHERE CASE WHEN @RepType = 1 THEN T1.Rep1 ...more >>

Need help writing a query, I have the following so far: select device_id as MachineName, "name" as "FileName", path from RIM_Admin.FileAudit where "name" not like 'No File%' and path not like 'C:/REC%' What this query is doing is looking @ a table that has scanned machines in the company ...more >>

Need help writing a query, I have the following so far: select device_id as MachineName, "name" as "FileName", path from RIM_Admin.FileAudit where "name" not like 'No File%' and path not like 'C:/REC%' What this query is doing is looking @ a table that has scanned machines in the company ...more >>

I messed up an insert script somewhere and need to correct my data. The table which needs fixing is: Table Mailings AddressID int PersonID int NextMailDate datetime Sample data: AddressID PersonID NextMailDate 123 4 ...more >>

I create a table by sending a CREATE TABLE command to the database. The create is successful but when I look at the table in Enterprise Manager the order of the columns is in alphabetic order and not in the order I specified when I issued the CREATE TABLE command. Have the columns really bee...more >>

Hi All, I am trying to do a update on one table where the table that I am trying to update has matching columns in another table. Something like this. I have a table with these headings (Code, Age, Months, ID) I want to set the Code field to null where age, months, id match correspond...more >>

How can i backuplog on Tempdb using T-SQL? Its full...more >>

Overview of problem I am getting anomolies with certain combinations of SQL Server SP4 and Windows 2003 where my query is grouping by two different columns, but the resultset returns the first of the two grouped columns displayed in both columns. If I reverse the order of the fields in the...more >>

Is there a script to do a count(*) on all tables in a given db? Thanks. Archer...more >>

Hello all, I am trying to find the instances in a field containg specific keywords or strings of information. My table name is History, and my field name is Notes. So what I am trying to do is find every record where History.Notess conatins; 'chrom' or 'cell' or 'lab' I think I need...more >>

Is it possible to insert data into a table within a scalar UDF? I have an encrypted field in a table and a special UDF that decrypts the data. I want to be able to log each time the decrypt UDF is accessed. Here is a snippet of code that I would like to use within the UDF. The CardAcce...more >>

Hello, I would like to find out if it is possible in SQL Server 2000 to limit the number of concurrent database connections per user. I am new to SQL Server, and not sure if it is possible to limit it through some sort of parameter. Any help would be greatly appreciated. Thank you, Dmit...more >>

Hello, Will changing the stystem date back on a server for testing affect my data?...more >>

I have a quetion for one of the guru's on here... You've never failed me yet so I'm hopeful you might help me out again. Here's the scenario... (sorry, copy and paste with courier font for fixed spacing if it doesn't look right) Date EquipId Reading 04/01/05 12345 12 04/02/05 12345 1...more >>

Fresh post after Razvan cleaned up my DDL below. Given; declare @searchstr varchar(10), @vintageyr int set @searchstr = '%Lag%' set @vintageyr = '2005' SELECT * FROM VintageEst V WHERE EXISTS ( SELECT * FROM Block WHERE BlockName LIKE @searchstr ) AND EXISTS (SELECT * FROM...more >>

Hi! I want to run a sql script in the job using T-SQL against a user database. I tried to copy and paste the whole script in the T-SQL window of the job but bc of length of the script (it is too long), it truncated half of the script. How do I do that? Can we call this script using xp_cmdshel...more >>

Hi, Can i set the SET NOCOUNT ON option at the database level for all the stored procedures iin my database instead of modifying the exising stored procedures. I am using Sql Server 2000. Prasad...more >>

Hi; Can MS-SQL support Arrays? I need a float that occurs 72 times in a table and I don't want to have 72 seperate fields but can't see a way of creating an array? Thanks in advance. David *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi everyone, I am writing an application for which there are several large internal collections whose contents have to be filtered and sorted in various ways. To really take the cake, the contents of the collections may change during the process (objects coming from other threads). The ...more >>

hi everybody I have a question about the sum() function. when I join two tabeles and one of them is the main table which I used in the from statement, sum function I used for the joined table is giving the sum incorrectly(it is governing times the other joined tabele). how can i eleminate t...more >>

The following example results to differnet results depending upon whether I'm using Not exists or Not in. I'm sdoing the same thing in 2 different ways. Is there an explanation for this or is this a bug in SQL Server? Current Version SQL 2000 SP3a Example: Declare @temp1 table (...more >>

Hi Guys, Just wondering if anyone knows how to copy a SQL Table into the same database, whilst still maintaining all if its extended properties. I tried migrating from Access XP with different table name in Access, however it doesn't like it with regard to ext. properties, indexes, etc. ...more >>

Hi, We have SQL Server 7.0 running on NT 4.0 ( I know we should have moved on but that's another story). We also have VB6 apps accessing the SQL Server using Windows authentication. Until now only users within our domain needed to use the apps. There are a number of NT security groups to w...more >>

I have a table representing a tree structure - self referencing table. For any node, I need to traverse upwards until root and retreives all the traversed nodes. With SQL only, how could I do this? In Oracle, there is START WITH, CONNECT BY clause to help me. What is the equivalence in sql ...more >>

What to do when the database log file is full? TIRislaa ...more >>

I've got 2 tables, Item and ItemDetail. An Item is considered to be complete when all of its ItemDetails have a value in the SomeOtherID field. An Item with no item details is considered to always be complete. In the sql below Items with ID 1 and 3 are complete because ItemDetailCount = Comple...more >>

Hi, I am trying to do a query where I want to show not only the relevant results but the previous line as well. eg. select * where msg='crash' I have a rowno in there but I am pretty new to this stuff so not real sure. Thanks in advance Tim ...more >>

Hi Let's suppose we have the following design (everything on the same server): Database1 (Publisher) Database2 (Publisher) Database1 and Database2 have the same structure. Now I create 2 subscribers on a database called 'DatabaseDest' Subscriber 1-> Source: Database1, Destination: ...more >>

Hi, Could be a really simple question.... I have several tables that look very similar, I need to extract an identical recordset from each one based on table name passed to stored procedure, use cursor and do some heavy calculations. I need to be able to declare a cursor based on table name...more >>

hello to all i have a procedure blow in my sql server database and i userd it in a crestal report v9.2 -------------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROCEDURE STTESTP @stklist as text AS exec ('select * from stunt...more >>

I have two tables that I would like to insert values into at the same time and would like help with the SQL statement to do so. One table (Member_Info) has a PK and the other (Image_Info) a FK. The relationship the two tables share is through the (E_Mail) column. Example structure: Member_Info...more >>

Hi, I have searched the very useful database you have here and although given some direction i still have a problem with a query i am working on. any help given would be greatly appreciated! the premise of query i am working on requires that a user be prompted for a variable found on table 3. ...more >>

What type of Query should I use to combine two sets of data into the same recordset. Note that none of the companies in TableA are the same as the companies in TableB (distinct sets of data). TableA Company, Sales, Members Table B Company, Members, Vacation Query Should Produce Co...more >>

Hi, is there a performance difference between using a View and a Stored Procedure (both returning the same result set)? Thanks a lot Patrick -- Please reply to group, rather than mail ad patrickwolf - net ...more >>

I need a little help with this query. I am querying the value of field, but I need to add to the logic that if there is no record then the value = 1 This is the query as I have it: ------------- SELECT * FROM dbo.ParametersUsers WHERE UserId = 1713 and ParamName = 'showpopups' --...more >>