I want to retrieve all those records from a column (that stores date values) where in the date is 1 + today's date. For e.g. today is 20/09/2005. I want all those records where the date is 21/09/2005. I want to send a reminder mail to all those records retrieved that they have to make the paym...more >>

Hi, I know the column name but I don't know in which table it is existing. It will take a lot of time if I search table by table.. Is there a way/query to find in which table a particular column is existing? please let me know if there is one.. Thanks Chandra ...more >>

I'm writing ad hoc queries and when I press enter to go to the next line, all my prior code is erased. What am I missing?...more >>

A column in a SQL Server7 DB table has the datatype smalldatetime. All the records in this column are stored in 'm/d/yyyy' format like 6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc..... Assume that two of the records stores today's date i.e. 9/20/2005 (20th September 2005). When...more >>

Is there anyway to CAST or CONVERT an integer within RAISERROR? I have tried all sorts of permeations to no avail, I have to declare another variable and CAST into that to use it in the RAISERROR. SET RAISERROR(101002,16,1,@errorstring, (SELECT @pid = CAST(@pid as varchar(15)),@uid) SET...more >>

hi, i have a query like select * from A, B where A.c1 = B.c1 and A.c2 = B.c2 and B.c2 = 'xx' and A.c3 = 0 there is an index on A(c1,c2) and B(c1,c2) . the query is underperforming. the plan shows that for the A.c3 = 0 condition (which happens to be huge), it is doing a table scan, a...more >>

All, Who and how writes very long scripts in T-SQL? Do we have anything more convenient than M$ SQL Query Analyzer? I finally started writing my scripts in WinWord because of the terrible despair. It at least allows me to use formatting, color selection, block separation, etc. Then I can co...more >>

I am trying to find missing Checks from Check Table (I know there are no tables). Here is a sample table (oops did it again) :): if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME = 'TestTable') DROP table TestTable CREATE TABLE [dbo].[TestTable] ( [SeqNumber]...more >>

I have a procedure that builds an update statement and then I execute the statement. I noticed that I'm getting results I didn't anticipate as follows: declare @TestNum as decimal(10,4) set @TestNum = 3.1234 print @TestNum print @TestNum * (50/100) The results are 3.1234 0.0...more >>

Here is the table: CREATE TABLE [child] ( [pk_child_id] [int] NOT NULL , [fk_parent_id] [int] NOT NULL , [code] [char] (2)NOT NULL , [dt] [datetime] NOT NULL , [newcode] [int] NULL ) There is a situation where there will be mor...more >>

field1 field2 field3 field4 field5 1 3 4 2 9 2 1 5 3 2 1 3 4 2 7 2 2 5 3 2 2 1 5 3 8 8 1 6 3 2 8 1 6 3 1 8 1 6 3 3 after quering, result below field1 field2 field3 field4 field5 1 3 4 2 9 2 1 5 3 2 2 2 5 3 2 8 1 6 3 2 i want to remain only row of same field1~field4. If field1~f...more >>

Hello I need to monitor creation/deletion/modification of objects in a database. I decided to use sql profiler with objects events auditing(create, delete) and TSQL events (SQL: STMstarting, SQL: batchstarting) for 'alter' statement. It's working, but I've encountered a few problems I can not r...more >>

using SQL SERVER 2K I am working on a data migration project and will be moving records with multiple SPs. I am looking for opinions, criticisms welcome, about my basic design. I have one sp (usp_Biggie) with a single transaction that executes multiple small sps that perform the actual...more >>

Hi All I have table Tab1 with a column Col1 of data type text. I want to replace every "The" string in that column with "As". How can i do that ? Below is column Col1 with 2 rows of data: Col1 -------------------------------------- <p> The boys are gone </P> <p>She left me by the door <...more >>

Hi, I'm using MS SQL 7.0 on NT4 server. There is a database call "db1" which only has statics information, no daily transactions happen. Recently I found the data is faily large and we short of disk space. db1 data is about 1GB, db1_log is about 5GB. First of all, I ran dbcc sqlperf(logspac...more >>

I'm trying to perform the following within a view. I'm not getting the values which I would expect. I'm assuming I have a data type problem where some precision is being dropped. Could someone give me a heads-up on what I'm doing wrong? I need full precision. SUM(((MarketPrice+Market...more >>

I want to use the same field in one table and return multiple columns for different criteria. In other words... First column SUM(Sales.NetSales) as 'Total Sales'). Then I want a second column as SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'. Is this reasonable? I ...more >>

I need to create a relationship between 3 tables,say x,y,z. y and z being the parent tables to x. there's an id field in all the 3 tables. i need to create a relationship between these 3 tables such that the id field in x is related to either y or z. in other words, sometimes the id field in t...more >>

I'm looking for the best free GUI tool to admin MSDE installation. Any help appreciated. Best regards. -- Gibe si è fatto passare perchè Rossi dietro è come una purga, prima o poi una cagata te la fa fare...!...more >>

I have two tables: TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id. TABLE2 has policy_id I'm doing a join, WHERE table1.policy_id = table2.policy_id. I need to display the value of many fields from both tables where table1.subm_no is the max value of that subm...more >>

How do I build a query using like, but the options come from a table? Such as Select lname from tblClients where lname like '%smith%' but the "smith" comes from another table, such as select lname from lnamelist. ...more >>

I am working on a MS SQL server DB where I have no control over the application code. We want to create a mirror table(say, TableA_mirror) for one of the tables that exist in the DB(say, TableA). Whenever, insert/update/delete is executed on TableA, the record that is changed is inserted int...more >>

Hi All, CREATE PROCEDURE dbo.GetNextItem @UserID INT, AS SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT TOP 1 @WorkItemIDOUT = WorkItemID From MyWorkItemIDsTable where IsInprocess = 0 ---value of 0 ensures that one else is working on it in a mult...more >>

For quite a while I've had pretty good success with putting most of my business logic in stored procedures and triggers. It's fast, relational, searchable, and updatable without any recompiling. At the moment I'm considering supporting both SQL Server and Oracle, and it seems to me that to ...more >>

Hello, how to know what's my database's recovery model? hwo to change it? Thanks. ...more >>

This works: declare @var1 char(30) select @var1 = ('text1') select * from <table> where <field1> IN (@var1) This works: select * from <table> where <field1> IN ('text1','text2') This doesn't work: declare @var1 char(30) select @var1 = ('text1','text2') select * from <table> wh...more >>

I'm using MSDE in VisualStudio.Net If I execute this simple stored procedure: ALTER PROCEDURE dbo.StoredProcedure1 AS Declare @rowcount int UPDATE t_prova SET testo = 'pippo', booleano = 1 WHERE numero = 4 SELECT @rowcount = @@rowcount IF @rowcount<>0 SELECT 0 ELSE SELECT -1 RETURN ...more >>

I'm unable to use a uniqueidentifier as an optional parameter to a stored procedure. I'd appreciate it if someone can confirm this. When I call a proc like this one from my program, Create Procedure MYProc @my_id uniqueidentifier = null as insert mytable (myID) values (@my_id) ...more >>

I want to extract variable data from files within folders and subfolders and output the results to a report, sorted by file name, variable1, variable2, variable3, etc....more >>

hello, i need to repeat the update statement for all days in the actual month example:if i run today (19.09.2005) the job - it must run 19 times for the value of GETDATE() to GetDate() -19. How can be done this in a loop. In the update statement is GetDate() used in the where conditi...more >>

i need basic sql syntax to accomplish the following: creat a cursor for select rows from table B in DB II that match a key value in table A in DB I. Then check to see if rows in the cursor match this key in table C in DB III. if yes then update, else insert. thansk for the help, mcnewsxp ...more >>

My simple join question for the day HOWTO: i have two queries the each product an atomic value. select count(a.usrexpdate) as activemem from usr a where a.usrexpdate > getdate() select count(b.usrexpdate) as activemem from usr b where b.usrexpdate < getdate() how can i join these qu...more >>

Hi, Does anybody know whether performance is affected if all database connections are done through the same database user name? Ie will performance be better if I had 20-30 different database users instead of one I am using at the moment. Personally, I am not expecting better performanc...more >>

Hi, I have a question about Microsoft Sql Server 7. Is there a way to find out which login who created a login? And how do I find out which login it created? ...more >>

Hi guys, Im new to SQL Server development in general...Well, I've been trying to work on an existing application that uses both ASP and SQL Server that happened to be raising an error that is logged on the Event Viewer 15 minutes after closing the browser. Here it is: Event Type: Informatio...more >>

Hi Guys, I need a solution in SQL Server 2000. I wanted to store the output of a select query into a variable. The Select Statement is stored in a variable. the sample is given below. declare @Tmp Varchar(100), @Tmp1 Varchar(100), @Tmp2 Varchar(15) set @Tmp = 'columntitle from ...more >>

I am using SQL Server. Having two tables Table T1: ID Name -------- 1 A 2 B Table T2: ID Description --------------- 1 a 2 b SELECT * FROM T1, T2 gives: 1 A 1 a 1 A 1 b 2 B 1 a 2 B 2 b =========================================================== Qu...more >>

Hi I am creating a cursor which runs through a table of database names and execute a script using xp_cmdshell on every database in that table. Declare @DataBaseName varchar(255) Declare DBcursor CURSOR FOR Select dbname From master..upgradedb Declare @command varchar(255) OPEN DBC...more >>

Hi, I want to list all available SQL servers in the LAN in my VB.Net application. Therefore I use SQLDMO and the following code: Private SQLServerDMOApp As New SQLDMO.Application Sub SomeSub SQLServerDMOApp = New SQLDMO.Application ...... ' get available se...more >>

hi all, i can import my database diagram from sql server using the data>reverse engineer feature the question is how can i send it back? -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787...more >>

I'm trying to build a command that gets a linkservername but gets this error msg:Incorrect syntax near the keyword 'Insert'. Can anyone help. Thnaks. set @cmd = Insert into Job_Results Select distinct '+convert(varchar(10),@batchid)+', '+@linkservername+', substring(j...more >>

I have implemented the Stored Procedure "RowCount" method of controlling paged recordsets in ASP as shown in this page http://www.aspfaq.com/show.asp?id=2120. I have had to make heavy alterations to the code in order for it to work with my application. It all worked fine until I tried to...more >>

I want to found another SQL Server on other machine. I know server named like SERVER2 and my stored procedure will insert any records to a table on other sql server but sometimes connection is break of. Then SP is interrupt a fatal error and don't continue. However transaction don't rollback. ...more >>

I have a table of events, which stores an event ID, event Name and event date. I want to write a stored procedure that returns the next 4 events starting from today, so far I have: SELECT [EventID], [EventName], [EventDate] FROM Events WHERE (DATEDIFF(dd, GETDATE(), Even...more >>

Hi, I would like to write a query that lists all the tables in a db and their size. I can't remember what the right join I have to make or the right sysobjects where to look at. Thank you in advance for any tip.... genc ymeri ...more >>

Hello We have small application writtten for Palmtop/Pocket PCs in Embedded VB for Windows CE3.0 When trying to run the same on Windows CE4.2, "MS embedded Visual Basic Runtime" occured. Can someone guide us how we can tailor the application suitable for Windows CE 4.2 and in future versions...more >>