Hai all, I need to do the following... declare @strwhere varchar(100) set @strwhere = ' and col2 = 6' -- where columnname is an integer datatype. select * from <tablename> where col1 = 'B' + @strwhere but the above query is not working when @strwhere takes a where condition for an inte...more >>

I'm sure this is a typical braintwister. However, I have a table that tracks income by resident, source and quarter. I need to determine the total number of persons who have been employed 6 months or more (>=2 quarters). I use a quarter number system such as yyyyq (20052). When I run the follo...more >>

DECLARE @mymoney money SET @mymoney=5148.72846 SELECT CAST(@mymoney AS decimal(10,3)) AS "Decimal Money" The result of the above code is 5,148.73 but shouldn't the output be 5,148.729 since the scale has been set to 3? Thanks, Arpan ...more >>

What is the best way to unit test stored procedures? I've got some which generate anywhere from 250k to 3million records each. The ideas which come to mind are horribly inefficient. Any advice or URLs would be greatly appreciated. Thanks in advance. -- Regards, John MacIntyre http:/...more >>

Can anyone please help me with this sql. I get an error as [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. when i...more >>

Maybe it's a personality flaw, but I have always had trouble figuring out the meaning of sub-selects inside a WHERE NOT EXISTS clause. My brain just can't visualize it. SELECT Stuff FROM TableA WHERE NOT EXISTS ( SELECT OtherStuff FROM TableB ) I am gasping to find a rephrasing of...more >>

I need some suggestions how to handle data structure changes. For example, I have a column which is char(10) and it appears in many tables. Also, it has corresponding variables and parameters in stored procedures/triggers. I want to change that column to char(20) now and may be another si...more >>

I have two programs, A & B. 'A' creates large data files and 'B' takes those files and loads them into a SQL-Serverd atabase table. One of the fields in my table is a 600 byte binary field. The field stores a 600 byte data structure in my C++ program,' A'. The C++ structure is comprised of c...more >>

What does binary data mean & what are Unicode strings? RON ...more >>

There two tables (TB1 and TB2) .These two tables are related to each other using two Keys (Key1,Key2). I'd like to see how many records of TB1 have their counterparts in TB2. For scenarios like the above I mostly use left outer joins ,but I've noticed that inner join in more common (aparent...more >>

OK, I am trying to define a query where I can choose those customers that had an existing account more than 30 days ago that did not have a specific class of product more than 30 days ago and then did have that class of products within the last 30 days. Does that make sense? Basically, they ha...more >>

how can i copy all objects (empty tables, views, trigers ...) from an sql db and create with it a new db in another computer? thanks ...more >>

Can anyone provide suggestions/recommendations of using either the sqltransaction class for ado.net or a sql server stored procedure. we need to using begin and end transactions to update mulitple tables? asp.net app with sql server 2000 database...more >>

I have the following going on: A win 2k3 server running sql server and reporting services. I have IIS configured on it and can run reports internally on my network but when I try to access a report from the web I get nothing back. I can access Folders List and other properties of any selected r...more >>

I'm trying to run this query - essentially, an employee may not have a position, but we still want to see the employye listed - so we use an outer join to show all employees even if they don't have a position. If they do have a position, we want to join that position to another table to get t...more >>

This gives me three records (should be one) SELECT Opp_Country_Cd, '2005 YTD' AS Period, ReportStatus, Count(RespKey) AS [Total of Key] FROM Status WHERE Opp_Country_Cd = 'Canada' AND ReportStatus = '020DupsDead' GROUP BY Opp_Country_Cd, Period, ReportStatus ________________________...more >>

Hi, I have this SP, I need to check for customer info in 2 tables, if table 1 does not contain the info, look in table 2. what I have so far works only if the customer info exits in 2 tables or the 1 st table "tblArShipto" I am calling the SP through VB 6. Cn.CursorLocation = adUseServ...more >>

I'm sure the answer to this is very easy: I have a .bat file that calls a stored procedure. This stored procedure takes one input parameter. I have created a DTS package to call this .bat file. What I would like to do is to be able to pass the parameter to the stored procedure via the "EXEC...more >>

I need to create stored procedure, which is sending messages to MSMQ. I attempted to create project via SQL Project template in Visual Studio 2005. This project could be automatically deployed to SQL Server, but has some limitations. I couldn't add reference to System.Messaging assembly. Then I ...more >>

Hello: I am using this connection in my .NET 2003 web application , in order to establish a connection with my sql database , that is in a MS 2003 server. The database is defined in a INSTANCE "Server=SERVER\INSTANCE,port;Database=Your_Database_Name;User ID=Your_Username;Password=You...more >>

Hi there, I'm new to SQL server and I'm looking at this database on which a VB application is built upon in this company. I'm wondering if there is a way of defining my own enumuerated data type in SQL server so I don't have to use a table/hardcoding in the code??? Version of SQL is 8.0, if i...more >>

I'm experimenting performance problems in a process that manage 1M rows and updates several times some columns. The issue is that the whole process is under a transaction (must be) but there are some queries within the transaction that i don't need to keep logged, meaning that if the transa...more >>

Hello, I am trying to insert a record in an Access database using Asp.Net/Vb.Net. I am getting the error: "Operation must use an updateable query." How can I solve this problem? The code I am using is: ' Set Connection Dim connectionString As String = AppSettings("connectionString"...more >>

I have one text field called Qualifier which looks like this Qualifier a b c d e I would like to produce a text string which would be the concatenation of all fields, hence returning: abcde Is there a way to do this without using a cursor? Thanks Archer...more >>

I have a table that's getting stuff deleted somehow. So I've created a mirror-image table for it & put a trigger on the original table. This allows some me to do some diagnosis as well as recover records that should not have been deleted. The only problem is, my mirror table is never getting...more >>

Hi, I need to store email addresses in database and call them from coldfusion code. I need to do this as, in case any person is changed, I need not go to every place in the website to change the address to the new person manually. So if I have a database, then I can just change in one place...more >>

How do I add line numbers to a Make Table in Access? Is there a function that I can add to the make table query that will number the lines automatically? *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi, Is it possible for both SqlServer 2000 and SqlServer 2005 to coexist on the same box? Would there be any need to have them both running (like for backward compatibility)? ...more >>

I am getting strange results when I join two tables together. Actually, the results are fine but the time to get them are what is causing my concern. My test SQL is the following: SELECT A.NEWCOUNT, B.ACOST FROM IPPCDTL A INNER JOIN IPINVDTL B ON A.IPINVDTLGUID = B.IPINVDTLGUID WHERE A....more >>

Hi all, What I want to achieve is to create a SP which accepts a SP name as input and then declares and opens a cursor on that stored procedure, queries the output columns of the SP (using sp_describe_cursor catalog procedures) and then constructs a temporary table which is an exact represen...more >>

I want to mark the bit field for only the duplicates of duplicate rows below: CREATE TABLE tbl1( rownum int, fname varchar(50), lname varchar(50), yesno bit ) set nocount on insert into tbl1(rownum, fname, lname) values(1, 'joe', 'smith') insert into tbl1(rownum, fname, lname) values(2...more >>

Hi I try to write a C# program which will get an input string and I need to insert to the SQL database. I set the column type as Text. and when I insert the string variable, for exmaple, "Now", it gave the error " The name 'Now' is not permitted in this context. Only constants, expressions, or...more >>

Hi, I would like to know if I use the % Processor Time to watch the CPU Usage, can I tell if the CPU Usage is used by what application like SQL Server? Seems like I have struggled it for a long time to figure it out. Thanks Ed...more >>

Hi, I am actually trying to find the youngest child in the site using a SET based approach. I have attached some sample data and the result I am looking for. Thanks in Advance. Suresh IF OBJECT_ID('Site') IS NOT NULL DROP TABLE dbo.Site GO CREATE TABLE dbo.Site ( SiteID INT NOT NU...more >>

***Issue: My boss always seemed like the jittery type. Whenever we'd meet to discuss a project he'd always seem impatient or disconcerted in some way and would periodically glance at my hands. I typically didn't think twice about it. I discovered something recently though. When I hold my pen ...more >>

I will be clear this time. This is what i want. Col1 Col2 344 54353 0 5345345 233 12 543 435 13 0 my ultimate Final result should "12" which is minimum of both the columns. (and non-zero) (i didnt know h...more >>

Our LDF log files seem to be approching 10 gigs for a database that is only 32MB in size, however the the recover model is set for bulk-logged, I know full does a pretty much copy of the database into the log, but i thought bulk-logged was to take up less space? We back up the database nightly...more >>

Hi all, i do have a column called Item_Code, which iam refering in so many tables, i have set the foreign key relationship also. My issue, if i pass one Item_Code, i need to get a count how many times this Item_Code is referred in other tables. Thanks in advance thoms...more >>

I think it is a very simple question, but i don't know the answer. I am developing a web site in C# and ASP.NET . If an entry like "Here is Sam's Pub" is entered into a Textbox exception occurs. Since " ' " character causes problem. Are there any escape character? Or how can i solve this probl...more >>

Hi, This is my scenario .... I have 2 columns .. with values like this. col1 col2 3432 545465 0 6545656 543 54654 54 6456456456 54 45 i want to find out the non-zero minimum out of 2 columns. so i wrote a fn. which takes min(col1),min(col2) and...more >>

Hi, This is my scenario .... I have 2 columns .. with values like this. col1 col2 3432 545465 0 6545656 543 54654 54 6456456456 54 45 i want to find out the non-zero minimum out of 2 columns. so i wrote a fn. which takes min(col1),min(col2) and...more >>

I use vb.net and i have a table in sqlserver 2000. I'd like to update a value of an identity column. How can i update the value ? Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. how can do it ? what is the command ? Thank you Luca ...more >>

Hi, I am using one table called Employee_Worked and within that table I am using feilds called Day_Of_Week, and Session_Hours. I need to create a view which initially gets the SUM(Session_Hours) which is the total hours worked that week, then I need to get the total hours worked based on Da...more >>

I have two tables: PJPENT and PJTASK I have the foll colums in PJTASK= Project, Task and Subaccount. I have the foll column in PJPENT =Project, Task and I am trying to populate the Subaccount column based on a join with the PJTASK table. I am trying to update the subaccount field in PJ...more >>

Hello, What query more effective and why (using memory,time) : select a.* from table1 A left join table2 B on A.field1=B.field1 where B.operId is null or select a.* from table1 A where not exists(select * from table2 B where A.field1=B.field2) table1 about 1...more >>

hi whats the difference between where col is not null and where col <> null thanks Ichor ...more >>

Hi! I have a small string problem in a stored procedure @sql1='sqlstring to insert to another sqlstring('textstring')' @sql2='Select type, type2 from' + @sql1 + ' order by ...... exec(@sql2) How can I insert the ('textstring') into @sql2? I´ve tried ("textstring") and (textstring) b...more >>

Hello, department-table definition: dept varchar(6) mother_dept varchar(6) manager etc I want to make a query that gives me all the depts under a given dept. (all levels, not only the first) Thanks in advance Jan Aerts ...more >>

I have 3 columns that need to be summed up, but any one of these can contain a NULL value. Col1 Col2 Col3 Col4 A 2.67 4.7 2.8 B 6.2 NULL 2.6 C 12.4 NULL NULL How can I sum them up to look like this? A = 10.17 B = 8.8 C = 12.4 Thanks, Ninel ...more >>

Hi all, some time back I downloaded a script which will list down sizes of all the tabels in a database. now i need that one urgently but can't find it! can anyone of u help me with this? thanks in advance. Nadee. ...more >>

Hello! I am currently trying to import records from a (legacy file based DBMS) datafile into SQL Server. Unfortunately the sticking point seems to be determining the physical position number of the records in the file. In the past, a custom ODBC driver performed some kind of arcane wizardry to...more >>

Can anybody explain me syntex for passing parameter in Cursor ?? Thankx in advance ...more >>