in sql 2005, why do the table names have a group name in parenthesize after the table name like this? Product (Production) SalesOrderDetail (Sales) SalesOrderHeader (Sales) and so on. then when I create some sql in the sql designer I get a reference to the table name like this: SELECT ...more >>

Trace 1204 has narrowed the cause of deadlock to these delete statements. There are a total of 10 delete statements in this SP. create procedure del_emp @empid int Delete from tbl_employees where empid in (Select empid from tbl_location where empid = @empid) Delete from tbl_management w...more >>

we've got a SQL Server 2005 database which is updated on a daily basis (via SSIS) with data from another system. (basically it eitehr adds/updates the data). Even if no changes have been made, the system will update the data in the sql db. I need to build a solution, which is capable of de...more >>

Hi, Currently we defrage our indexes on a regular basis and defrag then when statics are at a certain level. What I want to find out is: Is there a way to add speed control during rebuilding and defragging process? What I mean by this is that typically I run this at slow times but if som...more >>

Having trouble setting access to specific schemas- here's my problem: I've created a specific schema that I only want certain users to control Problem: Even though I give them full access....the cannot create tables under that schema...my code is below (flyer is the schema, eflyerAdmin is ...more >>

Hello - I have to move a database to another server that is running 2005. I am receiving the following error after using the Detach/Attach method: The propopsed new database owner is aleady a user or aliased in the database Error 15110. How can I fix this? I created the original dat...more >>

Well, I didn't get any love on this post: http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/e68b6f55ee86fb88 So I am writing a followup, and dealing directly with the example most people are famaliar with. Namely, the Kimberly Tripp SalesDB example. I have code belo...more >>

Hi Everyone I need to insert pricing breakdown information into table B from table A Table A looks like this: ItemNumber BreakQty1 BreakQty2 BreakQty3 BreakQty4 BreakQty5 Discount1 Discount2 Discount3 Discount4 Discount5 Table B looks like this IdDiscount ItemNumber Quanti...more >>

I want to ensure that every empid in the Employees table is passed to the stored procedures? DECLARE @the_emp_id int SELECT @the_emp_id = empid FROM Employees EXEC usp_calculate_retirement @the_emp_id EXEC usp_calculate_benefits @the_emp_id EXEC usp_calculate_vacation @the_emp_id EXEC ...more >>

Is there a better way of puting If Else condition to generate SQL Statement. All I want is where clause when @UserId not equal to 1 ALTER PROCEDURE dbo.GetEventsByUserId @UserId varchar(9) AS BEGIN If @UserId=1 SELECT e.[EventId], e.[EventTitle], e.[Active], Registration = COUNT(r....more >>

I have the following query, SELECT DISTINCT TOP 100 PERCENT LN_NR, COUNT(LN_NR) AS orgCount, SUM(TOT_PSTN_OFFCR_NR) AS incumbent, SUM(TOT_SLRY_AMT) AS totalSalary, SUM(TOT_SLRY_AMT) / SUM(TOT_PSTN_OFFCR_NR) AS AverageSalary, MIN(MIN_PSTN_SLRY_AMT) ...more >>

Hi, I have a dynamic sql that return an integer and i want to assign this integer to a variable. How can I do this? I don't want to create a function since I will have multiple dynamic queries and variables that needed to be code in one sp. any approach? here is just one of dynamic query an...more >>

I have a stored proc (see below) that has 2 IF conditions and the syntax checks fine. However, is it best to separate command sets with GO or a semicolon in this situation? It seems like I should have something after the 1st IF section. Thanks. David CREATE PROCEDURE [ms_insManualInvo...more >>

I need to read a field from a table in which the data are saved in hex format. The data are for example: '68656C6C6F' When the data is read it must be transformed in ASCII. In this case the data is: 'hello'. In a second step I must update the data. In this case the data must be transformed...more >>

Using SQL Server 2000 and sorry my explanation is so long. I have a table called Personal with the following fields: SSN LastName Color Year I have a second table called Inventory with the following fields: SSN LastName Color Year SDate Table Personal has SSN and LastName data ...more >>

here is the query I'm working with. I am looking for distinct netbios names, but the distinct statement is returning duplicates because it looks at distinct rows from what I understand. How can I get my query to sort by distinct values in SYS.Netbios_name0 column, but no other columns? ...more >>

Hi newsgroup. I am developing a CMS (asp.net), the web pages itself will be stored in the sql server (into ntext's). At least, that was the plan. Now, is this even a good idea? Or should I store big text as a file on the hd? Are there performance considerations with ntext fields? ...more >>

SELECT A,B,C,D FROM Alhpabet ORDER BY CASE WHEN @SortExpression = 'A' THEN A,B,C,D WHEN @SortExpression = 'B' THEN B,D,C WHEN @SortExpression = 'C' THEN C,D,A END this will work if you only have 1 sort item in the list, but how do I have more than one sort item e.g. 'THEN C,D,A' ...more >>

I have a large table with multiple dollar amounts: income_1,income_2,income_3........income_49,income_50 I need to loop through the table and perform a calculation during my SELECT. Is there an easy way to concatenate a variable to the column name instead of having to type all of the name...more >>

I am getting this error message in SQL Server Error log, Could not load the DLL sqljdbc.dll, or one of the DLLs it references. Reason: 193(%1 is not a valid Win32 application.). This connectivity between WEblogic server (on UNIX) and SQL Server DATABAse Server Any one know about this ...more >>

I'm querying SQL 2005 table with FOR XML AUTO, ELEMENTS. This field has a lot of HTML code. Example: <ul> <li>bullet 1</li> <li>bullet 2</li> <li>bullet 3</li> </ul> ....comes out like... &lt;ul&gt; &lt;li&gt;Bullet 1&lt;/li&gt; &lt;li&gt;Bullet 2&lt;/li&gt; &lt;li&gt;Bullet 3&lt;/li&g...more >>

I am experimenting with variations of a complex query and would like to compare their result sets. Both queries select the same attributes. There are several attributes selected, and all the attributes should be included in the comparison. The comparison of query A to query B should work ...more >>

Does anyone know why I am getting this error? The name 'master.dbo.xp_cmdshell 'rename G:\SQLBackups \MGGZS_20070819_001632.bak.gz MGGZSPN.bak.gz'' is not a valid identifier. ...more >>

Hi I'm busy optimising a number of stored procedures, I'm not the best placed person to be doing it (but theres no one else to do it) Present case, I have a table (say) Order which holds about 300k rows. Its joined to a user table which gets filtered down to about 50 rows (users). I also...more >>

I want to give the permission to the user that he can select any view from the database, I am using SQL Server 2005 please give me the query how can I do this. Thank you...more >>

I have the following query: select accounts_new_date_entered, CONVERT(char(10), accounts_new_date_entered, 101) as ts from accounts_new It returns a hex value for the first field and a null for the second any idea what is going on? Am using sql 2005 thank you ...more >>

Hi, I wonder if there is a better way to do the following. select * from table1 where id in ( select min(id) from table1 where key1 in ( select key1 from table2 a inner join table1 h on a.id= id ) group by key1 ) Thanks in advance. ...more >>

I have these three table definitions: items (id,item_name) tags (id,tag_name) items_tags (item_id,tag_id) One or more 'tags' can be applied to one or more 'items'. So sample data would look like this: items: id item_name 1 'item aaa' 2 'item bbb' tags: id tag_name 1 'blue' 2 'r...more >>

The goal of this function is to return the best guess phone number from a phone number database. In testing a query with this where and order by statements will bubble the best guess phone number to the top (1st record) in the query table. In the function statement the order by seems to be i...more >>

I have a little problem, does anyone know how I can with Stored Procedure import a CVS (textfile) into table fields ? EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com...more >>

Could you please guide me to write a query to fine out the last Index rebuild date on a particular table? Thanks in advance. Regards Arijit ...more >>

Dear All, I have a sql script that runs finish within 2 minutes. I then move the codes to stored procedures and the execution time takes 1.5 hours. I've tried to run the both of them on the same client management studio but the same result occurs. NOLOCK is in place for all select queries ...more >>

I would like to remove all non-integer values in a string, by this I mean any combination of values between A - Z in a string e.g 1324RTY. How can I use replace to achive this? -- obelix "Whether you think you can or you think you cant you are right" .... Anon Message posted via SQLMo...more >>

Hi All, I am using a SQL Server that is hosted in the US, and I am UK based. The hosts can't change the localization of the DB (understandably), and currently, when I need to insert a date into a column, I use something similar to: BEGIN UPDATE users SET userLastLoggedOn = DATEADD(hh,5,...more >>

Hi All, I'm trying to drop an sqlserver database from c# but can't because it is claiming it is in use. As I don't have a connection to it it must be connection pooling that is causing the problem. I can test this by disabling connection pooling and the database can be dropped. Is there any...more >>

I'm trying to read an excel 2003 file in the sql server 2000 as a table. I use the following methods: 1) The linked server method: EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\Book1.xls', NULL, 'Excel 8.0' EXEC sp_addlinkedsrvlogin 'ExcelSource...more >>

SQL 2005. I have a log file that is over 5 GB. I have done a full backup on both the database and log file. I have also executed dbcc shrinkfile commands, however, dbcc sqlperf(logspace) still indicates that the log size is over 5 GB and the log space used is only 10 MB. How can I reclaim ...more >>