I want to create a stored proc that updates some or all of the fields in a table, with the calling application sending through values for only the parameters it wants to change and NULL for those it isn't interested in. I can do this with dynamic sql, but was hoping to do it with a static UPDAT...more >>

How to declare variable to define it as a column? declare @Column AS (what should be here?) AS SELECT * FROM Users WHERE @Column like.... Hrcko ...more >>

Sometimes you start off on a path and neglect looking for alternatives. I believe i'm on such a path and need some help. I asked a similar question last week and got an answer that worked for SQL2005 (using "for XML path('') ). Unfortunately, I'm stuck with using SQL 2000 for now and need...more >>

I am trying to teach Statistics using SQL and RDB tables. http://www.amazon.com/Microsoft-Access-Data-Analysis-Unleashing/dp/076459978X/ I am looking for a books or resources like above either for ACCESS (<= 2003) or SQL Server Express. More suggestions and reference to resources is sought....more >>

Hi, We have two tables which as of now have ~5 Lakhs records. Once in three days we would get 20K to 1 lakh records pushed into this table using one SSIS package. These two tables are going to be used for generating different sort of reports for my top managment. So lots of SP's would be...more >>

Hello, I have about 40 plus queries that have Select with about 20 column names. example: Select column1,col2,col3,col4,col5,col6..............................,col20 from mytable where condition=1 (conditions vary for each query) The problem is everytime, I change the query to remov...more >>

Hi, we run a quite large intranet that uses sql server. Recently we migrated to 2005 (´running in 2000 mode due to some compiled sql that we cannot change). The new sql server is ofcourse a lot better in aspects of memory, processor etc We have two production IIS clustered servers runnin...more >>

I have a need to replicate a single table from one server to another. The data is read-only and it must occur once every night. I read briefly through SQL BOL and it seems like a snapshot replication might do the trick. The table does not have an identity column either and so the resulting tab...more >>

I'm trying to update Field2 only if the value of field1 has changed and should update to the value of field1 before the update. e.g of what I'm trying to do but doesn't work :) DECLARE @PrevAmtDue float DECLARE @TMPAmtDue float BEGIN TRANSACTION EditViolation SELECT @TMPAmtDue = AmtD...more >>

Query 1: SELECT Patrons.* FROM Patrons WHERE (AccountNumber = 14401158800) 1 row(s) affected Subtree Cost: 0.00640 IO: 1 scan, 3 logical reads Query 2: SELECT Patrons.* FROM Patrons WHERE AccountNumber IN ( SELECT pwn.AccountNumber FROM PatronWinetNumbers pwn WHE...more >>

Hi Guys, In my company, the database structure like: Database A replicated data into database B using transaction replication. In database B, there are some triggers on the tables which have subscription on them. the triggers put data that replicated from Database A into anther table A. the...more >>

Can anyone show a quick sample on how to add n columns to a table variable? I have a 7-row result of a Select and need to take each VarChar value and make a new VarChar column with that name in the table variable. Thanks. ...more >>

I have always been of the understanding that a JOIN is very expensive and that a subquery should always be used instead of a JOIN unless you need to return fields from the 2nd table in the SELECT stmt. Is this true, and should it be followed as a rule of thumb?...more >>

Hello, I was wondering if there is any way to create a simple password on a database or on the server. I'm looking at a solution using SQL Server Express and would like to grant access from the interface only and disallow anyone from accessing the database directly unless they have this pa...more >>

Hi ALL, I've got this code that crashes with "Object reference not set to an instance....." error. Is there a way I can test it to see if it actually is returning null? By the way, all the parameters I'm passing are valid and not null, and I don't appear to have any illlegal data. st...more >>

Hi, I have one query like below: Select state, Substring(zipcode,1,5), Count(state) as "Sales" From dbo.Table1 Sales Where conDate is not null and candate is null Group by state, substring(zipcode,1,5) Order by state, substring(zipcode,1,5) Go Output would be: -------------------- St...more >>

I'm experiencing an unexpected string truncation. Further, it is only happening on my Windows Vista client. It is not happening for a Windows XP or Server 2003 client. Here is the query: select case when 1 = 2 then 'NOTUSED ' else 'AddMor ' end + 'Test' Run it and then add the 'e' to 'A...more >>

Here are two functions in SQL 2005 SP1: Create Function ScalarFunction1(@Parm as Int) Returns Int As Begin If 10 = 10 Return 50 Else Return 60 End go Create Function ScalarFunction2(@Parm as Int) Returns Int As Begin Declare @Result Int Set @Result = 22 Return @Result End ...more >>

Hello All: Below is a copy of a stored procedure I am currently using. It works as is, but I'm looking for a shorter/more efficient way to write it. It seems like I have a little too much repetitive code. Any help or suggestions would be greatly appreciated. ALTER PROCEDURE sp_CrossTes...more >>

I am coming up with a semi automatic defrag system for SQL 2005, and I have a question about the index_id and number. Do a quick scan though, I have noticed a few identical index names with different index numbers. When I comes to defragging a index with the Alter index, it only asks for a name ...more >>

Hello Everybody, I am using SQL SERVER 2005 and I want to create XML out of the database table and put xml file into one folder on the server. How can I do it ? Pls let me know, Thanks. ...more >>

I am looking to populate a Schedule table with information from two other tables. I am able to populate it row by row, but I have created tables that should provide all necessary information for me to be able to automatically populate a "generic" schedule for a few weeks or more at a time. Th...more >>

I looked around for a while for some code that would help me write a trigger that would automatically log changes to table after an update. I wanted it to be dynamic enough where I didn't have to alter the trigger when the table schema changed. I didn't have any luck finding code out there so...more >>

While I'm used to using simple SQL queries to gather/insert/update data as I need, I have yet to become experienced in the use of more complex queries and stored procedures. I currently have three tables I intend to use for an employee schedule: 1 - Schedule - this table contains pk schedul...more >>

I have a server in which I want to give every domain user access to read/select/create table/connect on a single database. It is the only database they can have access to on that server. This is only for a few weeks until their separate server comes in and is installed but they need the inform...more >>

Hello - Our organization is migrating to Windows 2003 Server with XP for the users. I have a production database that's still in Sql Server 2000 and has been humming along beautifully. I am almost inclined to upgrade it to Sql Server 2005 at the same time as the migration, however, I a...more >>

Hi, Am trying to use Try Catch method to catch connection errors while running DTC queries using linked servers. Here is the code. declare HostInstnce CURSOR FOR select host,instance from <table name> where envmt <> 'DR' OPEN HostInstnce FETCH NEXT FROM HostInstnce into @host,@instan...more >>

Select * from tblx where EntryDate <= '12/31/06' returns 2100 rows with dates ranging from '12/31/06' to '1/1/04'. But other pulls will have different ranges like '12/31/06' to '3/20/03' Select top 1000 * from tblx Where EntryDate <= '12/31/06' returns 1000 rows as requested, but of the...more >>

Hi, I am designing a database that has Ship and Lifestyle tables. There is a one-to-many relationship between Ship and Lifestyle. So I have created another table called ShipLifestyles, which has two columns ShipID and LifestyleID. These two columns are sufficient to work as composit primar...more >>

Hi, I have a query that returns different values in two different sql servers, wth the same database. The query uses a user defined function, that returns a numeric value, in one server i dont get any error, but the query result is wrong. In the other server it works fine. The strange...more >>

Hi all, I am tried to return a single record set by combining 2 rows. Please see the result want below. Any help would greatly appreciate. Thank you. IF OBJECT_ID('Tempdb.dbo.#Phone', 'u') IS NOT NULL DROP TABLE #Phone go CREATE TABLE #Phone ( Person_id INT NULL,...more >>

What is the most reliable and easy method for capturing locks (i.e. object ID or name, type of lock, escalation, etc.)? Thank you in advance for your help!...more >>

HI, I need help fir the following query. The Query needs 3 minutes to execute by a 4 processor machine with 100% CPU usage. I want to show an Overview what Metered Product is installed on specified Collection Machines and was used in a specified period. The part which makes the problem is to s...more >>

Hi, I am designing a database to import and store data from our business partner. The data is in XML format. e.g... <Cruiseships> <cruiseship> <Name> <ID> <Ratings> <Dining Value="5.00" /> <Entertainment Value="4.00" /> <Family Value="3.00" /> </Ratings> </cruiseship> ....... </C...more >>

Is it possible to "set identity_insert on" onto a table across linked servers? For example, I have a session open on ServerA. I want to insert values into a table on ServerB, but I need to set identity_insert on first. Can I do that from ServerA? Thanks, Mitch...more >>

I have a problem with a particular SELECT after SET LANGUAGE ITALIAN; if I run the SELECT show in the example, I got the error: Msg 242, Level 16, State 3, Line 2 La conversione del tipo di dati da char a datetime ha generato un valore di tipo datetime non compreso nell'intervallo dei valori...more >>

Hi to evebody. I'm working with the transactional replication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher. When I do an...more >>

I have the need to create a scheduled process that will create and write to a simple text file. The creation of this file is used to trigger some other events. I am new to 2005 and was looking for the best way to do this. Any suggestions would be greatly appreciated. Thanks! ...more >>

I am using T-SQL and use set nocount on to suppress: x row(s) affected messages but this also suppresses my print statements, which I do want to know about. I tried shutting using set nocount off and then set nocount on in the code near the print statement, like so: ...more >>

We have a couple of stored procedures that runs through all our tables in our database and dumps the data into to text files as a series of inserts using xp_cmdshell. We then use more procedures and xp_cmdshell to load this data back into a blank schema. This process works fine but it can take...more >>

I have a table repeating columns like this: ID Agent Person SSN1 Spouse SSN2 Address 1 Joe ... ... ... ... ... 2 Frank ... ... ... ... ... 3 June How do I write a query write the data into one table like this ID Agent Person SSN Addr Type # Joe ... ... ... "Primary" # Joe ... ....more >>

I am responsible for the MIS within our organisation. An application programmer has created a table from a feedback questionnaire and has chosen to store all of the results in a single column using ^ as a delimiter for the questions. Example: 64 102725 2 Yes^Yes^Very good^Once every two w...more >>

Hi, I have a very serious problem, I have reinstall all softwares in my pc. Now I relize, QA is not working in my system. I can't open the QA at my system. Can anybody has an idea, why this problem arise. Rahul ...more >>

Why can't my stored procedures resolve table names to the current user's default schema? I have so-called "multi-tenant" SQL Server 2005 system where - each customer's data is in a separate schema. - each customer has a SQL login/user. - the customer's schema is set as their default sch...more >>

Hello, When trying to use the procedure I have created I am receiving the error (from Visual Studio 2005) - "Must declare the scalar variable "@empNum". The procedure is as follows. CREATE PROCEDURE sp_GetEmployeeByEmpNo @empNum varchar(10) AS BEGIN SELECT * FROM Employee WHERE empNu...more >>

Hi, I have created a report in sql server 2005 and wanted to deploy it but i cant. I dont know the what are configurations that needs to be set. even when i try to access the reporting manager from the IE Explorer. in getting this error message "The attempt to connect to the report...more >>

Hello, I have two problem concerning computed columns. Please help. Problem #1 ------------ I have created table with such columns: StandardPrice money AveragePrice money UseAveragePrice bit and I want to have a computed column Price = case UseAveragePrice ...more >>

Hi all, I ran few tests on my sql 2005 + sp2: I created a sql view named MyView EXEC sp_helptext 'MyView' shows : CREATE VIEW MyView ... = OK I renamed it to NewView in sql management studio => sp_helptext still shows: CREATE VIEW MyView ... = WRONG How can I change the name of the view ? ...more >>

Does Transaction large will affect the SQL server performance? my client 's database got 17GB log ,but the size is 400MB. He said it is not necessary to delete because it only occupy the harddisk space Can anyone give me some MSDN site or news which can prove "Larg log is not healthy" ...more >>