i have a question on the above mention... say if i need to run a batch procedure with very complex and multiple set base CRUD functionalites, does it make any difference if i break them down into 1 store procedure per query or multiple query per procedure. Presumbly everything is set to run with ...more >>

i have a question on the above mention... say if i need to run a batch procedure with very complex and multiple set base CRUD functionalites, does it make any difference if i break them down into 1 store procedure per query or multiple query per procedure. Presumbly everything is set to run wi...more >>

Hi all What I want to beable to do is modify the where statement in a stored proc select field1, field 2, field 3 from TheDataBase where x=1 but I might also want the where statement to change dynamically such as select field1, field 2, field 3 from TheDataBase where (x=1) or (y=...more >>

Hi, I am Deep. I am new to SQL Server and want to obtain the MCDBA certification. However, I don't have any programming knowledge. Can anyone suggest how should I start to learn about SQL Server and SQL Server programming? Can anyone suggest some books to start with? Waiting for your rep...more >>

I know that this is just a very small point, but it bugs me and I wonder if there is a work around. When I use an old version of QA (version 8.000.194) keywords are properly highlighted. When I run a more recent version (8.000.760), some are, but some (FROM, WHERE, AND, etc) are not. Is there a ...more >>

Hi, I want to restore data from SQL-2000 to SQL-7 , my sql-2000 server and sql-7 are standalone. means there is no connectivity between them. Is it possible through DTS package, if yes, anyone can write some (major) steps to use DTS package to execute mentioned task. Thanks in advance. ...more >>

I'm writing a Stored-Procedure and Id like to pass a field name as a parameter. It seems simple enough but I can't seem to find the solution. Here is a shortened example of the procedure Select Profiles.Name, Profiles.State, Profiles.City, @ProfileColumn, Profiles.County FROM Profiles ...more >>

I have an application where new incoming records are bulk inserted into a table in one database and an ETL process reads those records and does some normalization before loading the new records into the production table. The problem I'm having is with one statement that updates a look-up tabl...more >>

Hi, I have taken sql-2000 backup , and try to restoring in SQL-7 , but it is fail, is it possible to restoring or not . Is there any other method ? Thanks in advace. Hardik Shah ...more >>

begin transaction create table t ( tuple varchar(30), ItemId integer, Name varchar(30), Weight integer, Price integer, Type varchar(30) ); insert into t values ('i1', 1, 'Soap', 4, 20, 'Non-Eatable'); insert into t values ('i2', 2, 'FacePowder', 250, 70, 'Non-Eata...more >>

Ok.. What am I doing wrong? Table: ID 1 (smallint, identity) ValidFor 4 (varchar 20) Description MyDescription Here (varchar 200) Query: create procedure stp_GetIDs @ValidFor varchar(20) as set @ValidFor = '1','2','3' select ID from table_above wher...more >>

How to detect if a schema if exists or not so that I will not create same schema again? AND create schema statement should be the first statement of a bach? --Frank, SQL2005dev ...more >>

Hi, i'm not so good in Sql so can somebody help me. Let say i have 10 records. Each records have a id 1, bmw 2, peugeot 3, ford 5, citrone 6, mazda 7, volvo 8, renault 9, chrysler 10, alfa Now i receive a txt with the id who are still valid (in stock) and have to remove the cars s...more >>

I have a SQL query but need an extra column to output: 1234 if column D contains 'ABCD', 3456 if column D contains 'CDEF' 5678 if column D contains 'FGHI' I have tried using CASE but I think that function needs an exact match Can anyone help? Thanks Kay ...more >>

Simple test table 1 PK, 1 data field and one unique identifier field (script to create below) CREATE TABLE [dbo].[TestGuid]( [Id] [int] IDENTITY(1,1) NOT NULL, [data] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Guid] [uniqueidentifier] NULL CONSTRAINT [DF_TestGuid_Guid] DEFAU...more >>

Hello, Is there some kind of information_schema.something which lists tables and the current count of rows in each table? Thanks, Rich...more >>

i've got a table with the datetimes that certain events happened, like so: create table events ( when datetime, what varchar(50)) and i'm looking to do some calculations of this data. for example, there are pairs of events A and B, and i'd like to get the min, max, and average datediffs...more >>

The following code which worked just fine with sql2000 sp3 no longer works with sql 2005. The error I receive says: "The file filename.mdf is compressed but does not reside in a read only database or filegroup. The file must be decompressed". This happens on the line of code where I wrote <---P...more >>

In Microsoft SQL Server, I have a documents table and a table which categorizes the documents, which we'll call categories. I tried running UPDATE statements on the categories table previously and I ran into a foreign key constraint. The error given was "UPDATE statement conflicted with COLUMN...more >>

This little batch: ============================================ create trigger x on T after insert, update as declare @z table (id int not null) select @z = (select col from table) go ============================================ seems to contain some invalid logic. QA returns ...more >>

Hi all, I would like know if have same way to SELECT and UPDATE at the same time, in only one statement. HAve same way to do this. For sample: CREATE TABLE sample ( --... processed BIT CONSTRAINT processado__lancamentos_clientes__df DEFAULT(0) NOT NULL, --... ) I want do this ...more >>

Hello, I am doing something like this CREATE PROCEDURE DEIADMN.SP_GET_DATA @XMLDOC TEXT, @DOCNUM INT, @RESPONSE TEXT, AS DECLARE @IDOC INT,@SID BIGINT, @USERID BIGINT, @SXML VARCHAR(8000), @IXML VARCHAR(8000) EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @XMLDOC SELECT @SID...more >>

Running Stored Proc inside of Access and getting "The stored procedure executed successfully but did not return records". When I run in Query Analyzer it runs just fine. The Stored Proc is as follows: CREATE PROCEDURE MAINT_EditorActivitybyTime (@Enter_Start_Date_ smalldatetime, @Enter_En...more >>

I've been trolling artound here for a couple of weeks and have found a lot of useful tips. I was hoping folks could post some of their favorite sites for SQL related information. I have a few bookmarked atm, and would like to have a one stop reference library when I am done. I'm not a newbie...more >>

Hello, I am doing something like this CREATE PROCEDURE DEIADMN.SP_GET_DATA @XMLDOC TEXT, @DOCNUM INT, @RESPONSE TEXT, AS DECLARE @IDOC INT,@SID BIGINT, @USERID BIGINT, @SXML VARCHAR(8000), @IXML VARCHAR(8000) EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @XMLDOC SELECT @SID...more >>

Ii, I have a text box in the body of my report containing: =Code.setMonth(ReportItems!textbox13.Value) but i want this to appear in another textbox in my header. I have you read you can't refer directly to a field. This is what I have in my Header textbox: =ReportItems("textbox14").Value. ...more >>

I have two field table Name : PTest Fields : ID BigInt ( Identity ) and Name Char ( 10 characters ) This code is under a button. The row gets created but the scope identity returns zero. If I remove the parameter and subsititute it with a literal then it works. Can someone try this out ...more >>

The stored procedure below started givin us problems eversince we upgraded to SQL Server 2005. It happended twice before (since we are on 2005) that the procedure all of a sudden just starts to execute very slowly. It is invoked by a C# application using the Data Access Application block. I...more >>

I'm trying to copy a row from one table to another for audit purposes using a 'INSERT INTO x SELECT y FROM z' statement. This works absolutely fine in query analyser, however, when running the exact same statement from code (.NET via oledb), it fails with the error: An explicit value for the ...more >>

I have a program built in VS-VB 2003 that uses MSDE. I am concerned about the 2 Gb limit. The program I have is doing some realtime data capture, and would like to know when I am getting close to the limit, so that I could either clear out some of the data, or start another DB. Seems like t...more >>

Are the 2 conditions below equivalent? Thanks dbo.tblBillingDetail.AuthNo IS NULL OR dbo.tblBillingDetail.AuthNo = 0 ISNULL(dbo.tblBillingDetail.AuthNo, 0) David ...more >>

Hi, I have a udf that returns a table variable. I would like to call this from a stored procedure using the returned table variable in the select statement of a cursor. Kind of like this, this is the stored proc where udfProductionsScheduleWork is the udf - the udf returns a table varia...more >>

i've read some articles in this NG about the above topic, but everything i found was for older versions of SQL Server. the database i administer has been having an increasing volume of deadlocks. i've been reading the BOL guide to troubleshooting deadlocks, and one such step has been to turn o...more >>

In a table, I have a 10 digit account number that is stored as a string (Example Acct number: 0601600030). However, in the stored procedure, when I try to store the account number in a local variable, it appears to be implicity converted to an integer because the leading zero is dropped...thi...more >>

Hi all! I'm runnnig the following query: declare @Action int set @Action = 2 SELECT * FROM estates WHERE ((@Action!=1)OR( est_ZipCode BETWEEN 12000 AND 12999 OR est_ZipCode BETWEEN 14000 AND 14999 ))AND ((@Action!=2)OR( est_ZipCode BETWEEN 16000 AND 16999 ))AND ((@A...more >>

hello, is there a possibilitie to get the date when a stored procedure / view was used The problem is that in a database there are a lot of views - which where created in a period of last 2 years from diffren user which had access to the database. Now i want to have a kind of report for...more >>

Hi How do I return a recordset or table from a stored procedure? Regards Kjell Arne Johansen ...more >>

Hi everyone, I'm having a small problem getting all the data I need from a table. I know the answer is going to be really simple so if anyone could help I would be very grateful. Lets suppose I have a Books table. The Books table has an AuthorID column to indicate the author of the book....more >>

Hi i have an database that looks like this: http://www.cyren.no/diagram3.gif And the output here http://www.cyren.no/ods (Se picture) I want to get an query where a user can search in a date range. The output should be like this: Department(ANavn) JANUAR 2003 VF54423(RegNr) 21....more >>

Hello, I tried to use the new row_number() function while adjusting my application to SQL 2005. For example, this query: --- use AdventureWorks select CurrencyCode, row_number() over(order by 1) from Sales.Currency order by 1 --- worked perfectly in the CTP release (9.00.1187.00). Strangely...more >>

Hey, im having problems figuring out this sp_executesql procedure. This works fine with the execute for dynamic SQL: declare @colname sysname set @colname = 'somecolumn' execute ('select '+@colname+' from sometable') but with the stored procedure: declare @colname sysname declare @Execute...more >>

Hy, i have a Problem with table 'aggregation'. here a simple example, say 2 tables user roles 1 mike 1 rolea 1 roleb so the result of a joined query is 1 rolea 1 roleb now a customer wants the user to appear only once, but needs the g...more >>

Dear all, I am building a news web site for oneof my customers and actually I have question regarding the way to store huge big text document. The web site is build using ASP.NET 1.1 I need to update or store time to time news information in an sql server database. For that I was wonderi...more >>

Hi, I'd like to create a foreign key constraint and store it in a different filegroup than "PRIMARY" For this, I've created a new filegroup named "INDEX" This is my script : ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY ( [FIL_I...more >>

Dear folks, I'm looking a sp which returns me all the dependencies of a table or any object. Thanks for that, Enric...more >>