we use an E-office application where server is SQL server 7.0 and client side its MS access In our VB program we use synchronization to update client database from server and each table is updated afresh . This program was running fine and all tables were being Updated until number of ...more >>

Hi, I have an employee table. It has fields id, type, ctype1, ctype2. the data in the table can be like this 1 A 1 1 2 A 1 2 3 A 2 1 4 B 1 1 5 B 2 2 I need a query that would give the below result 1 A 1 1 2 A 1 2 3 A 2 1 5 B 2 2 Get all rows of type A and missing rows from typ...more >>

Hi, I have a Table of data (see below). I want to combine the duplicate fpartno and add the qty together and leave the resultset in the same structure. Any ideas? Data Now: fpartno fdesc fqty P450 TE CYL 1 P425 SH 1 P420 SH ASSY ...more >>

Hello, I have a stored procedure that calls itself recursively. At each step a new result set is generated so when the query completes I get a bunch of individual result sets, each containing one line. The problem is that the query takes quit a bit of time(about half a minute) and I get a "...more >>

I got a problem with my CLR C# stored procedure. The proc have just one param VARCHAR(8000) which I define in C# as String. Works fine until string exceed 4000 bytes . Instead of String I tryed SqlChars and SqlString - doesn't work. In this case I get error "MDX statement was expected. An MD...more >>

Hi, when trying to make my query a little faster I found this, but I cant understand why this happens: {select} {rest of query} = 3 results, 21 seconds. {select} top 100 {rest of query} = 3 results, 2 seconds. why is the query with the top clause so much faster? since the result set never...more >>

Hello, Can you add a column to an existing table and tell it that you want it between existing columns as opposed to the end in an sql statement? Thx ...more >>

I currently use xp_filexist and xp_getfiledetails for some a few SQL applications I've built; however, I heard that these are going away in 2005. Does anybody (preferably MVP's) know if these are going away and if so, what options do I have? d...more >>

Hi, Using ADO in VB classic, if I use T-SQL to restore a database, the .mdf and .ldf files are held indefinitely by the application's process. If I try to call DELETE DATABASE it fails saying the database is in use. If I try to get a handle on the file using the CreateFile API function, it ...more >>

I have a table called summary. This table contains calldate, projects, hours. I need to create a report/table that shows the date down the left column and the projects across the top. Date ProjectA ProjectB ProjectC 20060401 12 0 2 20060402 2 ...more >>

Hi have a simple table with columns like UserId FromDate ToDate 1 1/1/2005 6/1/2006 2 2/5/2005 6/1/2006 From date is the date the user joined the company and todate is when he left. I need to find out all the users who were employed by the company on 31/05/2005....more >>

I need to run a select statement in a sproc and at the end insert into a history table without having the insert return anything to the sproc is is embedded in. How do I do this? Thank you. ...more >>

Hi All select Num,Aname,AllTax From select Num,Aname,ChkTax(tax) as alltax,Pass from Table3 LEFT OUTER JOIN SELECT Table1.Num AS Num,Table1.Name AS AName, dbo.Tax(dbo.Table1.Num,Table2.DateVar) AS Tax FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2 ON dbo.Table1.num = dbo.Table2.num GROUP...more >>

I can execute the following: exec sp_help This gives me the Name, Owner, and Object-type columns. If I do the following: select Owner from (exec sp_help) I get the following error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'exec'. Any suggestions on ho...more >>

I created view with schemabinding: CREATE VIEW dbo.test with schemabinding as select p.[ID]as pID,n.DATE_ID,n.weekday,sum(case when p.excluded=1 and n.excluded=1 then 0 else quantity end) as quantity, count_big(*)as number from dbo.tableP p INNER JOIN dbo.tableN n ON p.media_ID=n.ID_MEDIA AN...more >>

We could do this Insert into tablename (field1, field2) values ('apple','23'), ('organes', 34), etc. How can we pass the parameters so I can do that in stored procedure? Thanks. -- Grant Who gives a {censored} if I am wrong. ...more >>

Hello, how come I can't qualify a column with its table alias in a case clause this works: select tasks.taskid,tasks.status,machinename= case when machinename is null ( select machine from queue where taskid = tasks.taskid) else machinename end from tasks but...more >>

I would only like to see the latest calendar Sale per #c1 record as (select top 1 * from calen order by ondate desc) It doesn't matter if it's type is RA or DS, just the latest ONE. set nocount on -- Hist select id = 2 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '03/06/2006' ,Actv =...more >>

I receive an existing date variable, which I copy to my @today variable. I need to reset the day of the month to be the first of the month, leaving all other aspects of @today alone. Any suggestions? -- simulate the date I get, which I have no control over declare @date datetime set @date ...more >>

Hello everyone, I'm writing you because I need help in something that's taking too long. = I want to build a Stored Procedure to be accessed by an ASP page and I = need that Stored Procedure to do a couple of things: 1.. Depending on parameters sent by the ASP page, do a SELECT = statement ...more >>

Hi, Maximum Job History Log size is set to 1000 and Maximum Job History Rows Per Job is set to 100. There are some some jobs that are running every minute. In less then five minute the Log Size grows to 45 from 0. So I guess the jobs that are running every minute are filling up the History Lo...more >>

hi all, i m new on Sql and i have a project related to C# and Sql. i have to do a project which must work on LAN, and there must be only one database file on main computer(Server Computer) and other computers(client computers) must connect database on main computer. i m preparing my proje...more >>

SQL Interview Questions And Answers I have listed SQL interview questions and answers in my website [url]http://www.geocities.com/sql_interviewquestions/[/url] So please have a look and make use of it. -- forsoftwareprofessionals ------------------------------------------------------...more >>

Hi is it normal for T-SQL to ignore spaces at the end of a WHERE expression - e.g. SELECT * FROM mytable where mycolumn = 'test' and SELECT * FROM mytable where mycolumn = 'test ' returns the same rows - i.e. as if the second one did not include a space at the end. Is this something...more >>

OK here's the scenario. I've got an order #1....it has a parent order of 2 which has a parent order of 3. I've got another order #4 which has a parent order which is also 3. Given this, is it possible to form a query that can return a list of just the OrderID's for all children of order...more >>

Hi all, Can anyone tell how safe (or precise) is the use of Ident_Current in = this case?=20 Also, when INSERT INTO...SELECT executes (for the Table in my case), = does that lock the whole table, or does it reserve the rows (identities) = depending on the number of records coming from the SELEC...more >>

hello everyone, is it possible to list multiple tables in a grant statement. so instead of using: GRANT ALL ON AuditColumns TO ADMIN GRANT ALL ON AuditConfig TO ADMIN i can just use: GRANT ALL ON AuditColumns, AuditConfig TO ADMIN thank you for your help, abraham luna ...more >>

PRODUCTs -------- P00111 P01234 P12345 0P1234 1P1234 2P2345 I have a table named ProductTable which has a column named PRODUCTS as above. I need to write a Stored Procedure similiar to the following whcih accepts a numeric parameter and return records as defined in @str. The followin...more >>

In a stored procedure I am selecting "top 1" out of a table that has several columns. I would like to take the vaues from the columns I have retrieved and set them to some variables I have. How do I perform this? ...more >>

If it is, then what is the criteria for determining whether a post stays or goes? I ask because I posted a question on Thursday March 30 and I don't see it in the list. I thought it was appropriate for this group. -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Kno...more >>

How can I export data from a table to a CSV file? I am working with an Account package with multiple companies. Each company has its own database and all the tables are named the same thing. I want to export all the data in a table called Account for each company to a CSV file. There are about...more >>

Hi Freinds, SQL 2000 I would like to capture all the changes ( updates. insert, delete) in my tables. this is a type of audit and capturing the changes of data. we can't do this in GUI section, so I need to develop smt to capture the changes. Does anybody did smt like this before? any he...more >>

I am running a T-SQL query and if there are one or more records returned by it I need to run another query. How does a newbie like me determine this or how do I get the value of the COUNT function into a variable I can use elsewhere in my sproc? After I see that the first query has rec...more >>

We are considering using BINARY_CHECKSUM() to conditionally update with stored procedures based on whether the parameters really represent a change in column values. For instance, if a user executes an update that changes the last name from 'Smith' to 'Smith' we don't want the update statemen...more >>

Can you use an aliased column in the Where stmt. I get an invalid column error? Select last + ', ' + first AS FullName WHERE Fullname like 'SomonesName'...more >>

I have a query against a payroll system that is actually going to be output for a report. part of the data is tax deductions. Sample codes are FED, OASDI, MEDI, KSSWTX. I know I can add a field in the deduction codes table to speicify a sort order and sort on that field, but is there a way in ...more >>

I have a table that contain the following colums charge_type, Charge_amt There are 14 possible charge types I am tring to write a query that returns the following result set sum(charge_type_1),sum(charge_type_2),sum(charge_type_3),sum(charge_type_4),etc. Any suggestions would be appreciat...more >>

I have a varchar field containing dates that I am trying to convert to a smalldatetime but I keep getting the following error message: Syntax error converting datetime from character string. Here is the line of code I am using: select convert(smalldatetime, DateReported, 101) from tblNam...more >>

I'm curious how connections are pooled under IIS. If you have several applications setup under your website, does each one get its own pool? Or is there only one pool created for all applications running under that instance of IIS? What if two config files have connection strings which sp...more >>

Hello all, I have this procedure declare @column varchar(200) declare @sql varchar(200) SELECT @column = lastname FROM Employee WHERE ID_employee = "3" SET @sql ='select * from '+@column EXEC (@sql) I have this mistake: Invalid object name '<@column>' Could someone tell me why? ...more >>

Is it possible to set set somehow value of a parameter delcared _outside_ of dynamic SQL from within the dynamic SQL e.g: .... DECLARE @can_continue BIT -- modyfing value of variable defined outside of the dynamic SQL scope. -- but how to do it ?? SQL_ToEXEC = '.... SET @can_continue = t...more >>

When should one specify tablock? I tried the following begin tran delete from requestlog where requestdate <= '2006-01-18' Requestlog consists of 67,263 total rows There is no index on requestdate, and the primary key is an identity field. The delete causes 68,787 locks, I believe o...more >>

I am trying to set up a WMI alert on SQL Server 2005 as described in the article, http://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/newsqlagent.mspx. I try running the following: USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Database Created', @enabled=1, @delay_betwe...more >>

Hi, I know it is not possible to define INSTEAD-OF-UPDATE-Triggers for tables that have foreign key relations using the "Cascade"-Option. But, in SQLServer 2000, I successfully used INSTEAD-OF-UPDATE-Triggers for primary tables being referenced by depending tables, where these relations use ...more >>

I am attempting to retrieve only the rows in a table that have the minimum value of the adr_numb column for each occurrence of the adr_code column. When I run the below query, it still returns all the rows within the table. Any suggestions on how to restructure this query to obtain the correct...more >>

Hi All, I have sometimes used the following sort of query to pull data from one table to another: INSERT INTO Table1 SELECT fname, lname FROM Table2 Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did someth...more >>

We are designing a security model for our application. The security is for controlling access to various functions(essentially buttons) on various screens per user group. The problem we are facing is that different screens support different functions and it is proving hard to come up with one se...more >>

Hi - What is the correct syntax to bulk insert into a link table. I want to link Person 1 to every Product, something like below. Thanks. INSERT INTO PersonProducts (PersonID,ProductID) VALUES (1, Products.ProductID FROM Products) ...more >>

Hi all, I haven't add indexes to a database for a long long time so I've completely forgotten how to go about designing an index. Say I have the following query (which is run often): SELECT bl.TotalRooms, bt.TypeName, u.UserWorkTel, p.DisplayAddress, p.idList, cy.CountyCityName, d.Dist...more >>

can replication between sql server 2005 standard edition and express edition...more >>

Hi, I am working with several tables and views. My goal is to create a view with critical reporting data from these tables and views. I have managed to get the majority of data but am having difficultly with the final step. For a record in the master dataview, add additional record informat...more >>

Hi, I have posted this question previously and for some reasion I cannot find the orignal post and I have to be honest I dont even know if anyone managed to answer it. I am using SQL Query Analyzer that is distributed with SQL Server 2000 and for some reason when I try to run the Help opti...more >>

Hi All There is Any Way To Calculate The Time To Run SP Or Select Statement Before Run It For Example SELECT * FROM stores WHERE (state = 'CA') How long Time Take This Query to Run Thanks ...more >>

Hi, What's the use of this option in a query? If my query is optimized by specifying OPTION (FAST 3), the first 3 record will be obtained at the best performance, but the query does not return until the rest of query is completed. If I need only 3 records, I'll use TOP n which is aware of thi...more >>

I have foll. query Select 2+5 AS Age,'Dirk' as Name I want to use 'Age' as follo: Select 2+5 AS Age,'Dirk' as Name, Age + 10 AS TotAge But it gives invalid column name error any workaround to solve it ...more >>

Hi All How Can I Do This DECLARE C Cursor For SELECT * FROM stores Open C Select * From C EFT OUTER JOIN StorView ON StorView.id = c.Id close c is it possible thanks ...more >>

I am executing a stored procedure and using SQL Profiler to see what is happening. When a look at the profiler results it shows some statements like: SELECT statman([LastPeriodId],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [LastNetworkBusyPeriodId] FROM [dbo].[#FinalLocations____________________00...more >>