Hi all, I need to get the desire results below but get stuck and on the sequel statement. Any help would greatly appreciate. Also there is a business rule describe below. DROP TABLE #Temp go Create table #Temp ( Lead_History_id INT NULL, Header_id INT ...more >>

I'm trying to finish this stored proc Typically flu shot are give usually from Sept to Feb. When Patient come in for a visit, the flags are updated on a fly. I need to alter this stored proc for due date to start in September if the patient comes this month for example June. Can anyone help wi...more >>

O.K., here is the problem. I am setting up performance monitoring for various 2000 and 2003 boxes at my enterprise. I have got Performance Monitor recording the data into a sql2000 database. The problem I've got is the way that it stores data required me to write TSQL to Pivot the data becaus...more >>

DO NOT say "Visio". This is not for a client project of any kind. I just want to experiment. If my experiment is successful, or leads me in a direction that whaps me on the head and says, "SQL Server", then it will go into SQL Server. Otherwise, I will build the thing in Access. WHY? Be...more >>

Create table Test (Text1 varchar(500)) insert Test values('I love SQL') insert Test values('SQL rocks') insert Test values('SQL rocks in 2005') insert Test values('MS rocks too') insert Test values('MS is short for microsoft') So i want to run a query where I would like to group by some k...more >>

Do you have any suggest or guide lines to help me to understand the better way for naming tables & fields ? Normally, I name the pk of all tables with : ID and the fk with : ID+TableName Example: Table: Authors id (pk) AuthorName Table: Book id (pk) idAuthor (fk) Title It's c...more >>

Hi, I'm trying to log on to a web site and I'm getting a message on the site that says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 I have no idea what this means or how to allevi...more >>

I am writing some code generation stuff and I am trying to get a script like this to work: IF (something) BEGIN CREATE PROCEDURE Whatever AS SELECT 1 as one END But it complains about this, so I am guessing that I can't put the create prodcedure in an IF block. Does anyone kn...more >>

Hi there!. I'm having a simple problem. I have a table with like 20 fields , this table is always growing since it's a price table acumulator for a set of products (with their features) , this is loaded from a text file. When a new text file arrives , I upload it to a temp table prior to...more >>

Hi, I have a table which has a field defined as Image. I guess it was defined that way to hold any value or type. From what I am reading it seems that I can cast from image to binary and then from binary to varchar or int.... Whenever I try to convert I keep getting zero as a value. I set...more >>

I have this data time sale_id sales_person_id start (date) 1 12 2/5/2003 2 12 3/5/2004 3 12 12/4/2005 4 14 ...more >>

Hi everybody, I should begin to write a DLL library for Sql2000 server. The functions I like to implement are mathematical functions, like standard deviation, and similar, nothing really complex. Often I have to use more then one standard deviation inside the sama function, using subset of a re...more >>

Hello there One of my clients has complain about using parallelism that cause self block for processes. It seems that the problem started at sp4. Is it true? ...more >>

Hello, Does anyone know how to save a query plan into word doc? Thanks, Lianne...more >>

I rewrote several Access programs to run as a single SQL stored procedure on SQL Server. A job created in DTS executes the SQL stored procedure. This job is executed from a batch file as listed below: ....... Dim RetVal Dim strPathFile strPathFile = "C:\BTExe.cmd" RetVal = Shell(strPathF...more >>

Hi, I am going to be writing an application where a server has MS SQL 2K on it with some tables that I will need locally on some notebooks when they aren't connected to the server. I need to also create other tables for my application so I figured I would create tables to match the ones I ...more >>

Hi, Is there a way to do maintenance like integrity checks if there is still a (sleeping)connection to a database? My maintenance jobs where you need to be in single user mode fails. In our multi-tier environment we use an applicationserver which uses connection pooling and a databaseserver...more >>

Can someone please send me some sample code on how to connect to the database from the script component? I tried using the connection manager to establish a connection and then use it inside my code in VB.NET but it did not work. Please send me some samples. ...more >>

Greetings See data below...platform is SQL 2005 What I need to do is update the third column of Holder, dev_workitem_int, with an INT value converted from the dev_workitem_char column ONLY IF the data in the CHAR column can be converted to an INT in the first place... In other words, I n...more >>

I have an application (userx is the username) that creates tables on two different databases. However when it creates it on the first database, it creates it 'dbo.table1', whereas on the second database it creates the same table named 'userx.table1'. We captured the script that creates the tab...more >>

This is a followup to my previous question. Example SP: CREATE PROCEDURE [dbo].[ChangeWorkspace] @UserName varchar(32), @Workspace varchar(32) WITH EXECUTE AS CALLER AS BEGIN update table1 set workspace = @Workspace where username = @Username; END In the DB table1 is in a schema...more >>

Hi, It's got to be a simple question. I'm about to write a relatively simple queries to manipulate data in a MS SQL database. I've got some experience with DB/2 and MySQL. I've been going through some samples/tutorials and noticed that object names are quite often surrounded by '[]'. Co...more >>

Hello, I've a table with the primary key on 2 columns (qus_id,ans_id). When the user updates the database, i want these 2 values to be created as hidden fields and be returned to the updated ASP page. However the condition for updating is, where qus_id==ans_id. Only the rows satisfying this con...more >>

I am using select * into #mytemp ...... select .... from tempdb.information_schema.columns where table_name like ''#mytemp%'. However, select will select all temp tables with name begin with #mytemp__ if other sessions run the same SQL statement. Any solution? I must use select * int...more >>

We are using Websphere Application Server and SQL Server together and having a problem with transactions never timing out. WAS is on a separate box from SQL Server, and if the WAS Server crashes during a transaction with the SQL Server, the locks on the tables in the database never go away. A...more >>

I am trying to create a stored procedure with the following code snippet (using some dynamic sql): @sql_string = 'INSERT INTO ' + @archive_db_name + '.dbo."' + @archive_table + '_Archive" SELECT * FROM current_table' then I run EXEC (@sql_string) When the stored procedure is run, the f...more >>

Hello - I have a feeling I'm going to be told this can't be done, which will be bad... but here goes. I've build a DTS package that gets daily shipment tracking nos. and order nos. for customers. The table also has the customer's email. I've used the web wizard to fill in an htm form ...more >>

I have this SQL...Why can't I use the derived column "no_of_days" in my WHERE logic??? And how do I get around this??? Also, if we want to take in account weekends, is there anyway just to filter out and get a result table within the last 11 BUSINESS days??? SELECT GEN.loan_num, FUN.fund...more >>

Hello, I am trying to do a simple insert of records that are "new" to the source table with the following query, but I'm not getting the expected results. There should only be 5 new records inserted into the table yet I get 59 INSERT INTO dbo.WorkOrderMailCellXRef SELECT DISTINCT a.emM...more >>

Hello! Im trying to create a stored procedure in SQL Server 2005 to insert a new record in a table. This stored procedure will be used by a SQLCommand class in my application. Here is the SQL Insert plan to use: INSERT INTO [LibGest].[dbo].[Books] ([ISBN] ,[Title] ...more >>

I've tried to enumerate sqlservers using two different methods and have the same results... only the server name is returned, I am unable to get the instance name so user will be unable to connect to selected server if it is a named instance (i.e. "Server"\SQLEXPRESS)... Dim dt As DataTabl...more >>

I was trying to calculate the difference between set-based solution vs row-by-row solution by looking at the execution plan. For my row-by-row solution, I have the following (i won't show you the set based since it is irrelvant here) SELECT Vendor_ID, (SELECT TOP 1 CONVERT(VARCHAR, Note_En...more >>

i have a table with sale_id, date, sales_person_id i need to find out the sales_person_id's who did 1 sales every month from jan 2003 and another query who did a sales every quarter. I need to find out how many sales people (count) made atleast 25 sales each year from 2003 - 2005 ...more >>

I need to audit Insert, Update and Delete on tables in the database. But the sysadmin of the app can selectively enable and disable auditing on tables. So I need to be able to switch the auditing on and off. Is there any built-in function in SqlServer 2005 that I can use to track changes? ...more >>

We can use this to change the setting SET IDENTITY_INSERT T1 ON How can I find out if it is on or off ?...more >>

Dear All, I have 2 tables as INV & PAY with columns as INVNO & INVTOT IN INV TABLE & INVNO, PAYMENT & ADJUST IN PAY TABLE FOR EXAMPLE TABLE INV INVNO INVTOTAL 10 100 11 50 12 60 13 150 TABLE PAY INVNO PAYMENT ADJUST 11 ...more >>

i have a table with sale_id, date, sales_person_id i need to find out the sales_person_id's who did 1 sales every month from jan 2003 and another query who did a sales every quarter. ...more >>

All, I am using Microsoft Management Objects (SMO) to create a database with it's associated tables. All works well until I try to define more than one index as a primary key. See code below: // Add two primary key indices Index pkIndex = new Index(myTable, "PK_MyTable"); pkIndex.IndexKe...more >>

I am trying to write a procedure to merge all the duplicate entries in a table and insert them into a new table. Hence clean a table and port the data into a new table. I get the following error:- Server: Msg 403, Level 16, State 1, Procedure table_clean, Line 85 Invalid operator for d...more >>

Hello, currently I am using a cursor to loop through the contents of a table variable and calling a stored proc for each row, passing the appropriate parameters. The table contains records that errored out earlier in the processing cycle and the stored proc logs these errors. There can someti...more >>

Hello, I need assistance I would like to know if it is possible to do the following Corp Source Category Description_1 Count_1 Cycle 7801 Internal 52 Day Precall Left Message (Answering Machine) 92 A 7801 Internal 52 Day Precall Left Message (Answering Machine)...more >>

Hi, I'm creating an application that will allow users to view information about certain SQL Server instances and Databases. Are there some system stored procs or tables where I can find the following information. Server Collation Product Version e.g. 8.00 Product e.g. Standard Edition...more >>

How can get the current user name/id of the connected user in Sql Server 2005? I am using windows authentication. If I use USER or CURRENT_USER function it always returns DBO and I am looking for the network sign on which in my case would be URSC. Thanks. ...more >>

Hi, I have a table with the following usage Reserved Data Index_size Unused 13924080 KB 4957464 KB 2392624 KB 6573992 KB I tried to claim the 6573992 KB unused space using the following but still the unused space is same. DBCC CLEANTABLE('DB','TBL') Is there ...more >>

Hi All, I have a SQL SERVER 2000 instance that holds various databases, each representing a student on my course. They have an associated Windows User account, but they have individual sql server logins. All of these details are created by a single batch script, which creates their windo...more >>

I tried something as follows, SELECT t1.F1, t1.F2, t1.F3, t1.F4, t1.F5 FROM t1 WHERE EXISTS ( SELECT t2.F1, t2.F2, t2.F3, t2.F4 FROM t2 WHERE t2.Complete = 'Y' AND DATEDIFF (day, CompleteOn, CURRENT_TIMESTAMP) <= 0) whereby t1 and t2 are tables, t2 is the "parent" of t1, F1, F2, F3, F...more >>

Is it possible to debug stored procedure with SQL 2005? And how? I've installed my SQL 2005 developer edition and i can't find the way to debug stored procedure. Can you help me? THANKS!...more >>

Hi! Anyone knows how to select only certain data from one data field? Lets say i have this field which captures Member Name and ID. The ID is in parantheses "()". I only want the Member name. How do I select this from the table? E.G.: Field: Data MName: John Doe (123) I need to sele...more >>

I've got a customer telling me that some data is taking many hours to show up in their database. This data is entered via a PDA. The PDA has a wireless connection and direct access to sqlserver database so the changes should show immediately. What I'm guessing is a transaction is not committed...more >>

Hello there On many of my views it use PARALLELISEM. One of my client ask to cancel this action. Is it is possible? ...more >>

Hi all, Can anyone tell me how I could manipulate the case of the characters passed in to a stored procedure as a parameter? I was hoping there is a function that can turn a parameter into all lowercase letters, for example. Many thanks in advance Simon...more >>

I want to create a table with variable name but can't? how declare @k as char(100) set @k = 't1' create table @k << create table with name=@k thanks tarvirdi ...more >>

Hi All, I need to do some calculation on the "date" data. Kindly please advise me how to compose the SQL query on these: 1. How to get the last day of each month? 2. How to get the date of the first Sunday of each month? 3. How to get the date of the last Sunday of each month? ...more >>

I got the following error from the server 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.' I used the same code with MSDE and it works fine Is the regional settings then how can I change it? Thank you, Samuel ...more >>

Hi, I need some help in order to generate an XML out put. The following Query generates the xml in the following manner , But What I need is something like the one i shown as desired output Query SELECT 1 AS Tag, NULL AS Parent, BookID AS [Book!1!BookID], Title AS [Book!1!Tit...more >>