What are the advantages and disadvantages of using GUID for primary keys of tables in SQL Server? One major advantage that occurs to me is that you can make human readable backups of data in each table, and then be able restore one or more copies of tables to do emergency recoveries of data th...more >>

I'm trying to index two columns, and can't figure out a clean way to do it. Any help would be much appreciated. Here's the table: CREATE TABLE companies ( name varchar, companyId int unique not null, parentId int not null ); Each company has a parent. ...more >>

A while ago there was a very long discussion about clustered index scans / table scans, whether the result was or wasn't guaranteed to be returned in index order when ORDER BY is not specified. The subject seems to be surfacing once in a while. I've done some work to try and clarify things, an...more >>

Hello, With the new SQL Server 2005 are there any changes for making an array / list of items to place in an IN clause. For example something like: (I know the syntax isn't correct) DECLARE @Items INT; SET @Items = 1,2,3,4; SELECT Customer_Name FROM Customers WHERE Customer_ID IN (@It...more >>

Hi I'm trying to do this select but I'm having a error 156, i'm stuck with this. Can anyone help? set @periodo_temp2 = (select SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim) Thanks ...more >>

A typical SELECT loads rows into memory and a determination is made that invokes a RETURN. I assume the rows are left in memory? If so is there a statement(s) to remove the rows from memory before invoking the RETURN? BEGIN TRY IF EXISTS ( SELECT LoweredUserName ...more >>

I have a SP where the first line of the code is BEGIN TRANSACTION and a single COMMIT TRNASCTION at the end if @@TRANCOUNT=1. It then goes though several UPDATE INSERT and DELETE processes. All along the way each U/I/D process checking to make sure @@ERROR=0 before moving to the next step, ...more >>

I have a large table(over 3 million records) with the primary key column defined as uniqueidentifer (guid). I want to iterate thorugh the table in increments of 50,000 rows and update the rows based on certain business rules.How do I do that? I know how to do with an integer identity key(using b...more >>

These are some of the promising titles I see. * Programming Microsoft SQL Server 2005 Andrew Brust * Inside Microsoft SQL Server 2005: T-SQL Programming Dejan Sarka * Programming SQL Server 2005 Bill Hamilton Recommendations please. *** Sent via Developersdex http://www.developersdex....more >>

Hi I need to create an .aspx (vb) based availability calendar. I need to track the availability of a certain product over a period of time (periodfrom and periodto) and the status (1/0) I then need to show the data in a report in the following format - using reporting server from ms. Jun 0...more >>

Hello, I have a column in a table that contains alphanumeric data like this abac-12310522A36565 nbtc-101404B726532 pqst-0912F23123 tsmp-0713039999G217917 All the expressions begin with 4 letters followed by a dash, followed by a numeric value followed by a single letter followed by an...more >>

here's what i have (northwind), sql server 2000: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetCustomers] @keywords nvarchar(256) = NULL , @customerid nchar(5) = NULL , @companyname nvarchar(40) = NULL , @contactname nvarchar(30) = NULL , @contacttitle nvarch...more >>

Hi, group. I am not entire sure if I am in the right place for this, if so, please direct me to the more appropriate forum, or a link to a good tutorial on this subject! I wrote the following two queries for Access a couple of years ago to get some statistics out of our manufacturing system. ...more >>

I got this error message when the porgram was running a pretty simple SQL command: Connectivity error: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv()) How should I understand it?...more >>

I have a set of customer IDs where they can be assigned any combination of letter codes. For example: code id ---- ----------- A 1 B 1 C 1 A 2 B 2 A 3 B 3 C 3 B 4 C 4 And would like to get a result of unique combinations (the pk_id is only to give th...more >>

Hello, I need to know how exactly the distinct function should look like in my SQL with the Count aggregate. Here is my SQL. How Exaclty should it look? Thank you so much! SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order table].SubTotal) AS SumOfSubTotal, Avg([order table]....more >>

I need a way to create sequential numbers which will be used as job numbers. That means, that there shouldn't be any gaps in numbers. If the newly issued number is 500, then that means there are 500 records with job numbers. No less and no more. My understanding is that T-SQL is not...more >>

The CASE function behavior (the result data type) in the example below seems contradictory to Books Online, and it seems strange as well. Does anybody have an explanation? DECLARE @Find varchar(8) DECLARE @Year char(4) SET @Find = '19%' SET @Year = '1976' SELECT CASE WHEN 0 = 1 T...more >>

Hi. This is what I am looking to do. I want to basically create look through either all columns in a SQL view, or a select number, and count the number of non-null values in each column. I don't need the results to show up in special way--pretty much just grouped by column name. I've see...more >>

Hi. I am trying to get a tally of a specific type of item purchased from an invoice and the sum function is not working, what am I doing wrong? * The first 2 item meet the specific criteria example: Ivoice number 1001 has 3 items. invoice_no ------------ item1 - ...more >>

I know that this topic is dicussed soften you guys are tried of it. But I have a new twist on it and could use some input. So the usual problem is that my data is shown by the customer name, one record per date, and I need that date to become a column name with the value for each month stor...more >>

Hola a todos, tengo el siguiente error al ejecutar una sentencia, y no logro entender porque. Hello to all, I have the following error, when executing a query, and I can´t find where the problem is Subquery returned more than 1 value. This is not permitted wh en the subquery follows =, !=,...more >>

Is there anyway to do this? I have a crosstab built with case statements and the calling client specifiec the date range to query over. The number of derived colums returned is fixed but the labels need to display the date for the column in question. This date is purely determined by user select...more >>

hello, I'm new at handling xml data. My problem: MyStoredProcedure @XMLData @XMLData = <?xml version="1.0" encoding="utf-16"?> <Item> <columns> <column header="my custom column1">0</column> <column header="my custom column1">Stuff</column> <column header="m...more >>

I have published an article about common mistakes in implementing business rules. I think the article is quite relevant to this newsgroup - I mean relevant to several discussions in the last few month. http://www.devx.com/dbzone/Article/31985 As usual, there is an error on the first page: ...more >>

I have a difficult query that I hope someone out there can help with. Given the following data in the db orderId itemId ---------- --------- 1 10 1 12 1 14 2 11 2 13 ...more >>

Hi, I inserted a value into a float field of a table of SQLServer2000 SP4. Example of value inserted is 3214.34 -When I execute by Enterprise manager a select to extract this value, the result is: 3214.34 -When I execute by QueryAnalyzer a select to extract this value, the result is: 321...more >>

I have the following query: SELECT TOP 100 PERCENT PID, Number1, Desc1, PID_Name, Primary1 FROM dbo.DIR_Assets a WHERE (Class = 'extension') AND (PID_Name IS NOT NULL) AND (NOT EXISTS (SELECT * FROM dbo.DIR_As...more >>

If I run this while loop in query analyzer: while (1=1) begin select getdate() select * from master..sysprocesses waitfor delay '00:00:15' end I would expect to see results printed every 15 seconds, but it doesn't print anything, even the date. ...more >>

I generated a report, and I want to display the parameters the user chose in the report. For example, the user selected a fromDate and toDate (as part of the query that displays the data in the report). Now I want to display these two values in the actual report, so the user knows what he/she...more >>

I want to Index Tiff images by using Sql Server 2000. I hv Windows 2000 and Office 2003 Professional. But i couldnot find IFilter for Tiff Images in Office 2003. Can anyone help? Does it exists in Office 2003??? ...more >>

Hi, I have a job with the following steps: * Step 1: Runs a procedure to create a table containing detailed data * Step 2: Runs a local package to export this detail to the network * Step 3: Runs a local package to export the summary of the detail to the network * Step 4: Renames the...more >>

hi, i have a query based on a view that performs very badly in sql 2005 and i have no clue why in sql 2000 this runs just fine the view : SELECT dbo.FormuleGrondstof.frm_PrimKey, X.fnm_Naam, dbo.FormuleGrondstof.frm_CNK, dbo.FormuleGrondstof.frm_Prijs, dbo.For...more >>

Hi, I run profiler and stored the traces to a table. From the traces, I'd like to determine the number of times a certain SQL stmt. had been executed, so that it displays something like this: Textdata Count sp_help 100 sp_who 50 ..... Any ideas how I can go abo...more >>

How do we convert a mainframe format S9(05) COMP-3 FORMAT to sql server datetime format. Its in a char field in a temp table. Is there any function available. The data looks like 1650513 1560407 1560407 1420808 Guessing its YYYYDDD ? Not sure. Thanks for any help. Yog ...more >>

Is it possible to create an auto-complete comboBox in Reporting Services? The data that populates the comboBox comes from a query. If it's not possible, can I use Studio .Net (2003 or 2005) in conjuction with the report I already made in reporting Services? The only functionality I need is th...more >>

I want a dynamic variable as Boolean expression in an IF condition. For example: DECLARE @codPprcComp VARCHAR(10) DECLARE @desPrpr VARCHAR(50) DECLARE @valPrpr VARCHAR(255) SET @codPprcComp = 'LIKE' SET @desPrpr = '0003.%' SET @desPrpr = '0003.2006' DECLARE @cmd VARCHAR(255) SET @...more >>

Hi, We have recently been having some problems with deadlocks, and to track down the causes of the problems we have turned on trace flag 1204. This produces output such as the follwing: 2006-07-19 14:53:53.71 spid4 Deadlock encountered .... Printing deadlock information 2006-07-19 14:53:53...more >>

Hi, To get rid of the time in my smalldatetime field (it has to be smalldatetime for other purposes), i do: convert(smalldatetime, convert(varchar, MyDate, 1)) but i want to keep it as a date type not as a string, and i also want to get rid of the 00:00:00 thing at the end.... Can you he...more >>

I want put the result of a dynamic select in a variable. For example: DECLARE @cmd VARCHAR(255) DECLARE @desColn VARCHAR(20) DECLARE @valColn VARCHAR(255) SET @desColn = 'DES_PRPR118' SET @cmd = 'SELECT ' + @desColn + ' FROM DOCUMENTO WHERE COD_DOCM_ID = 504' SET @valColn = EXEC(@cmd...more >>

Guys, I'm a newbie in SQL and will appreciate some help here... Querying using a subset is quite easy... %name% will bring me all xxx-name and name-xxx Now, my problem is I want to search a subset of my query name. E.g. The string I have in hand is Blue Dog. I want to find in my quer...more >>

SELECT CustomerId, (SELECT TOP (1) OrderValue FROM Orders WHERE (Orders.CustomerId = Customers.CustomerId ) ORDER BY Orders.PurchaseTime DESC) AS LastOrderValue FROM Customers This works... But if I try: SELECT CustomerId, (SELECT TOP (1) OrderValue, PurchaseTime FROM Orders WHERE ...more >>

is it possible to insert data from one database table into another database table, and inserting values at the same time ? - kind've like this ? (column1, column2, column3, column4, column5) (Select database2.dbo.table1.column1, database2.dbo.table1.column2, database2.dbo.table1....more >>

Hi NG, actually I want to call a webservice within a trigger. I just found one blog about this topic and there was written, that doing so will cause performance problems! Is that right? Or will it depend on the special case: I need a trigger for insert and after insert the trigger has ...more >>

When I use the following statement: SELECT * FROM myTable WITH (NOLOCK) WHERE isnull(column1,'')>isnull(@value,'') This works well for all dataTypes, only if column1 is decimal type, I get the following error: Error converting data type varchar to numeric. Why then I don't get this e...more >>

I m getting following type of recompile for a specific sp, Exevntsubclass: 3 that is Object not found at compile time, deferred check to run time. What it means? ...more >>

I Have a table in production, while creating that table ANSI nULLS were OFF. Now I m unable to create indexed view on it. How can i set ANSI Nulls ON for the table without droping the table. ...more >>