Could anyone help me with this Please, below is the code I've been trying but it's not working. A user selects a start date and an end date. Table has a BeginField and EndField. If dates selected by the user exist in the database, the prompt user(Dates not available). Dim strSQL As String Di...more >>

I've added an audit table to an existing application, each row of which contains: the name of the user that made the modification, the time, the table that's affected, insert, update or delete, the old row as a comma-delimited string I'm a bit unhappy with the guts of the trigger that pop...more >>

I think I have discovered a bug in the way SQL server handles date searching. If I formulate a query to get all records between dates for a smalldatetime column: select * from table where date_column between '1/6/04' and '1/6/04' I will only get records for '1/6/04 00:00:00'. Records ...more >>

I have 2 tables, table1 and table2. I want to use only 1 table in my statement to display records. for example: Part Number, Description, and price Part Number and Description is in table1 but price is in table2. Part number is the comon column. I want create a column in table1 which...more >>

Hello. We use SQL Server 2000 and IIS (ASP) to create our web (distributed) applications. A hot issue came up among our programmers as to which method is best to use in an application: calling Stored Procedures or executing run-time, dynamic statements coded in the ASP page and then execute...more >>

If I have a table called "UserName" and has only one field that looks like: UserName -------- JSmith MMary BChao How do I write a single SQL statement so it returns a single string like: <td>JSmith</td><td>MMary</td><td>BChao</td> I've been looking for an "aggregate" function (like ...more >>

I am writing an ActiveX script (VbScript) for a package I have. And I am trying to run a task from that script. What I have so far is: dim pkg dim task ' get package object set pkg = DTSGlobalVariables.Parent ' find desired task by its description For Each task In pkg.Tasks ...more >>

Hi, How do I set up a many to many relationship between two tables do I need an intersection table? what are the ways of doing it. Thanks in advance. SA ...more >>

Hi All, This is my first message ever on news groups :) I am quite shocked nobody has spotted what I am gonna say, so please do = comment if you disagree... When you join 2 tables, the index of the table on the LEFT of your join = is going to be used. So if one of the 2 tables is massive...more >>

Any help generating a select statement that would get every columnname/datatype/size for every column in a table, given a tablename. I know about sp_help, but it returns 3 sets of data for my table, I'm really just interested in the one that shows me columnname/datatype/size. Thanks for any h...more >>

Hello I was wondering if you guys could help me with this simple one I have a Results table with Teams and their points. And i want to find out the teams rank. Team Points Player A 1 AA A 4 AB A 2 AC B 5 ...more >>

Hi guys, I'm making a decision whether to use money or numeric data type for currency fields. Does anybody has an idea which one is better? Thanks guys! Leo ...more >>

Hi I have two fields Fy_yr Fy_mnth 2003 9 2004 12 2003 10 2002 12 2003 11 I have convert the data in those two field to the following format Fy_yr Fy_mnth ...more >>

any ideas why i can't get the following to work and/or suggested workarounds? declare @s char(10) set @s = 'test' select @s -- everything works fine so far set @s = @s + ' more' select @s -- i just get the original string -- 'test' - what th...more >>

I would like to Backup Database A on server A1 and Restore the Backup on Database B on server B1. Please help me create T-SQL statement to complete this task. Thank You, Tim ...more >>

Hi I am trying to design a web form that has many check boxes. I was wondering what is the best way to design databases to acomodate check boxes. As check boxes are, a person can check one checkbox or all of them. For the user who checks one check box, the database would have null values f...more >>

Hello all, As I have never worked on this kind of query before, I am finding it increasingly tricky to address the issue. I have a raw table which appends every month with a set of rows. I run a import job that converts rows from this raw table into a end-user transaction table for repo...more >>

Hi I need to edit a DTS package saved as Structured Storage File. Is there anyway to get this file restored into a tool able to edit it? Regards Dennis ...more >>

Hi , I have difficult to use xp_sendmail , the Mail icon is disappeared and I can not setup the mail profile for SQL mail services. My server is win2000 with SQL 2000, the computer is a stand along server and does not join in any domain yet. Any expert has some idea to do it. Thanks for you...more >>

I using SQL Server 2000. After entering text into a varchar(8000), some of the text is cut. I put the text into MS Word for counting the characters with spaces. It totals 7717. Could this text be getting cut because I'm near the limit? Thanks, Brett ...more >>

Hi, I have an urgent matter that I need assistance with if any are able to help. I have been getting an error message intermittently and with a recent surge in traffic on our web site the error is occurring much more frequently, but the error is still intermittent: [DBNETLIB][ConnectionOpen (C...more >>

We are trying to find a way to declare variables where the actual variable name is created dynamically. Here is an example of what we would like to accomplish from our old system. Declare @column_name varchar(30) Use a cursor to Select column_name into @column_name from import_layout whe...more >>

Hi I am attempting to devise a web solution for my company: We need to develop a website that will be hosted by a remote webhost that will hold small amounts of confidential client data which can be updated by the client using an SSL login. Our local SQL server 2000 database holds the sam...more >>

hi all sql2k dev ed I got the following table ID PatentID Title 1 0 headertext 2 1 sub header 3 2 sub sub header 4 0 header 5 3 sub sub sub header 6 5 sub sub sub sub header 7 4 sub header what I want to do is specify an ID...more >>

When you run an sp_who2, the output shows several pieces of info. including the name of the program running a process. However, the name does not always appear. The same goes for HostName. Is there anything that can be done to change this Thanks in advance Drew...more >>

How can i run the query below AND get the results in proper date order? Thanks 1> select distinct datename(month, transdate) +' '+ datename(year,transdate) as x from ar6_transpaymenthistory order by x 2> go x ---------------------------------------------------------- -------------...more >>

I have a question. I have a select statements that is getting fields from a function(table). The function has 2 paramenters select r.a, r.bas , r.c, r.d, r.e, s.f from ufn_s (r.a,'A') s Inner join ON s s.id = r.id My problem it doesn't accept another column name as the parameter. ...more >>

I have Stored proc that is taking much longer (2min 25 seconds)to execute. If i run the code within the SP, then results come back within 2 seconds. I have clear the proccache and rebuild the index, as well as updated the stats. After that I have recompiled the SP also. Any suggestions?. T...more >>

Does anyone know how to copy a stored procedure from one database to another using T-SQL? Thanks ...more >>

HI, I have a little problem. In a table for my calendar i have a datetime column called: StartTime When i want to diplay my caledar i need all records from a certain date dtDisplay ( dd-MM-yyyy ). How is it possible to copare StartTime the dtDisplay date directly.? Now I convert my dtDisplay...more >>

Hi all, I'm using this query to pull records, if there is another record with the same "Full_name" but different date I only want to show the record with the oldest date. the "Examdate" is Datetime Column and the data looks like this "1999-03-12 00:00:00.000" Select Full_name,Examdat...more >>

Noticed it on a couple of KB articles. What is it? ...more >>

Hi, I'm creating severals stored procs and have many duplicated code that can be moved to user defined functions for reuse. My questions is : can I freely create user defined functions to reuse code or it is better to duplicate code in wathever stored proc needs it? I have other questions...more >>

Hi, I successfully added a message to 'sysmessages' with SP 'sp_addmessage'. I added the message both in US English and French with number 50001 and severity 10. This message is 'Here is %s number %d'. However when I execute: DECLARE @var1 VARCHAR(100) SELECT @var1 = FORMATMESSAGE(50001...more >>

Hi Guys, I can't remember any SQL function that would return the day of week from a date? Datepart() only returns the day of the month. Does anyone know of a built-in function to do this? Thanks in advance :) Kind Regards, Steve ...more >>

Hi, What're the SP to list all the users? Thanks... ...more >>

Hello: I have created several sql scripts that I routinely execute from within VS.Net. I would like to create a "master script" that, when executed, executes all other scripts but can't seem to locate the proper syntax. Is it possible to execute a script from within another script? Thank...more >>

I am trying to read a large table (+500K) records. I keep getting the above subject message after about 30 seconds. I have already done the following, increased the server timeout variables, increased the clients virtual memory and the DSN timeout to 99999 miliseconds. The query log indicates t...more >>

Hi All I get this error when i open a specific table from enterprise manager and try to update values in it. Data has changed since the results pane was last updated. Do you want to save your changes now? Click Yes to save your chnages and update the database. Click No to discard your changes ...more >>

Hello, I'm running this: begin declare @error int declare @dbname varchar(128) set @dbname = 'db200461155' set @error = 0 exec @error = master.dbo.sp_certify_removable @dbname, auto if ( @error = 1 ) begin print 'failure to set to offline mode' end else begin pr...more >>

I have this Stored Proc: CREATE PROCEDURE spAllocationSelectByClientDate ( @ClientSurrogate INTEGER , @StartDate DATETIME , @EndDate DATETIME ) AS SELECT @StartDate = CONVERT( DATETIME, CONVERT( CHAR, @StartDate, 106 ) ) SELECT @EndDate...more >>

Is it possible to run a big .sql file - SQL script to create a database from a ASPx page? If yes, please advice how can I achieve this? Thanks! LIN ...more >>

How can I write a query that will return a list of dates, given a starting and ending date? starting with... DECLARE @start_date datetime DECLARE @end_date datetime SET @start_date = '1/6/2004' SET @end_date = '1/20/2004' SELECT mydate = /*????*/ and the result set should look like ...more >>

We are in the design phase of a project and want to import data from our exchage server (contact information). One of the team members is claiming that you cannot set up exchange as a linked server and I can't seem to find any information on it. Is it true that this would have to be do...more >>

Hello: I have two tables: 1. Transaction_History: This contains three columns, Card_No and Amt_granted and Date_granted. 2. Summary: This contains a summary record for each card. This has three fields, Card_No, No_Txs, and Total_Amt. The summary stores the Amt_granted at the hard lev...more >>

I have table and sample data: CREATE TABLE [dbo].[cpoTimeSlot] ( [ts_id] [int] IDENTITY (1, 1) NOT NULL , [ts_startDate] [datetime] NULL , [ts_endDate] [datetime] NULL , [ts_medId] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[cpoTimeSlot] WITH NOCHECK ADD CONSTRAINT [PK_cpoTimeS...more >>

In the code that follows, How do I enclose Q103 , Q203, @BIFF(2nd occurence), and BASE_1 in quotes in the "dynamic code" portion? I need it to behave exactly as the "desired result" portion DECLARE @VSQL VARCHAR(1000) DECLARE @BIFF VARCHAR(30) DECLARE @i INT DECLARE @i2 INT SET @i = 1 SE...more >>

Dears, I have a table with SQL Commands in it, and I want to create a result set with the values powered by these commands. I'm intending to create a stored procedure with a temporary table with that values and use it as a result set. Is it possible to make it using EXECUTE (@SQLCOMMAND...more >>

hi all I am using table variables instead of creating a temp table because it seems to be faste But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that ex: with temp tabl create ta...more >>

do you know a simple and cool program? ...more >>

Hello, Someone knows if I could use a size for a data type like is nvarchar greater than 4000 ??? Currently i am using: Declare @LsCMD nvarchar(4000); .... PRINT 'Query: @LsCMD --> : ' + @LsCMD; EXEC sp_executesql @LsCMD; but i need to store 8000 characters there. How I could red...more >>

I've seen the documentation that says that you can't use GETDATE in a UDF because it is not deterministic. I don't see any explanation for this limitation and don't understand the need for the limitation. I am trying to migrate Oracle stored procedures and functions and Oracle allows...more >>

I have a vb.net application that I have distributed that sues SQL Server as its backend. AN individual got a hold of this program and is basically bombing my server with repeated requests. Basically just sitting at his computer and transferring large amounts of data through button clicks as it...more >>

Hello all, I have a sproc that I need to determine what values were passed in and then query using only the values passed in. I did not allow any nulls in the database and it is a relatively simple database. There are five tables with four of them being lookup only. I don't know how to alte...more >>

I am creating a procedure to compare like names so I can group by them. Is there a way to only take letters out of the name? ex. pizza-hut I want it to come back as pizzahut I know I can do this with Charindex, but I have every imaginable character in these names, and sometimes more t...more >>

I am loading my MS SQL tables. I have Identity=Yes and Identity Seed =1 on the key field to the table I am loading. I delete the table each time I run my load program. My problem is the table is remembering the last value that was in the identity field. I want the records I insert each...more >>

I have the following sql: SELECT ClientName, Month(EnteredDateTime) as M, Day(EnteredDateTime) as D, COUNT(ClientName) as Orders, SUM(Amount) as Amount, SUM(Cost) as Cost FROM vTracking WHERE ClientID = 1409 AND EnteredDateTime > '01/01/2003 12:01 AM' AND EnteredDateTime <...more >>

Hi All I have a whole lot of databases with many tables. The tables in each database are updated regularly. I would like to know when any such change is made to any data in a database. I would like to do this without having to create a trigger on each table. How else can I detect if any chang...more >>

Hello, In Oracle you can set up a database link, this effectively allows you to refer to a different database (either on the same or different server) using a synonym. This means that even if database names are different between say production and test you can rely on the database link n...more >>

I know this is not crystal user group, but I know there are lots of crystal guru here. I have a text object on report, when I right click on this and set object size and position. It moves minimum 10 pixels , I want to move this in set of 5. (say I am changing it from 110 to 105 either...more >>

Ive been writting an extended stored procedure using VisualStudio .Net C++, when starting the project in SQL commandline mode all works fine, however when using enterprise manager to access the server we get 'spid 54 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x22A2BA0E' The spid...more >>

I have two tables which have a foreign key relationship between them. While deleting a record in the master table it gives an error. However, if we give 'CASCADE DELETE'; it is deleting the child record without any alert message. I need to have an alert message or an error message saying ...more >>

Can you reference a field from a UDF ? For example, in the following snippet how could I reference Surname and FirstName WITHIN the IF EXISTS (SELECT...) statement: If EXISTS(Select Surname, FirstName FROM tblPeople WHERE Surname = @Surname) *** How do I reference Surname and First...more >>