When building user ID's that get loaded into a Table I sometimes run across names like O'Learah. What is the sql statement that will take field called Userid that contains: O'Learah and create OLearah removing the (') Thanks. ...more >>

I have the following SQL statement: SELECT field1 FROM table1 WHERE field2=25 ORDER BY field3 This statement works fine, but the results contain duplicates, because field1 contains the same value in multiple records. I would have thought that modifying the statement with the keyword DISTIN...more >>

Hello, I have a fast enough stored procedure SP1, which has input parameters A and B. This parameters get the values from the following data set (sql query): select distinct A,B, from tblT1 order by B. Then I need to execute SP1 for the hole above data set. For this purpose I created SP2 with ...more >>

Hello, Is there a way to create a query that will surivive a ROLLBACK? I would like to do a little bit 'o logging, but obviously this is problematic when the logging statement gets rolled back as well =) I had an article that described how to do this (for logging specifically), but that...more >>

From this sql call... I get this output I get the below output SELECT MONTH(SCRAP_DATE) AS MNTH ,RESP_DEPT ,SUM(MAT_COST + FREIGHT + LABOR + BURDEN + MISC_COST) AS COST FROM LABEL_AUDIT..SCRAP GROUP BY MONTH(SCRAP_DATE) ,RESP_DEPT, ORDER BY 1 The Question is I need to get the...more >>

Hi there, I am calling a CLR storedproc which uses a COM object for encryption. My c# code declares a stored proc that returns the output parameter as a SqlString. The problem is that SQL server creates a stub associated with my dll that turns the SqlString into NCHAR and hence has whitespa...more >>

I am running these queries in SQL Server 2005 Select * from [IPAddress].Databasename.dbo.tablename Msg 7202, Level 11, State 2, Line 1 Could not find server '192.168.62.40' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_add...more >>

Hi, On one of my process, I've to remove some constraints make some changes and re-create the constraint. As it takes somes times, I used the SQL Server profil manager to see what's happen. As I'm using some PS, I'm listing the Stored Procedure > SP:SmtpCompleted event. Before every ALTE...more >>

OK. I would like to iterate a WHERE clause in a dynamic SQL (I cant really come up with another idea) based upon a delimited input string. For example, the input variable @searchstring = "red,ball,rubber" (the delimiter could vary, so could the elements) I would like to create a stored ...more >>

Is there a way to setup a relationship within a table with a hierarchical structure to get cascading deletes? Fields: ItemID, ParentID, ItemName The table has records that refer to other records within the same table. If I delete a record with a certain ItemID I want to cascade delete all reco...more >>

In Query Analyzer we're running a curosr query whose purpose is to prune an OLTP database (SS 2000 SP4) and often receive these errors after the query runs awhile. Also, we are pruning a local DB so the networking inferences do not make sense. I've read a few links on this but they don't ...more >>

I am building a custom search application to dig through all of the messages in the database. To do this, I am performing a TSQL query using a few LIKE clauses. I rank the quality of the returned message based on the number of LIKE matches the record contains. So, my SQL looks something like...more >>

Back on 9/21/2004, Dan Ganiere asked about executing T-SQL asynchronously. T-SQL itself doesn't support that, but Gregory A. Larsen had a work around. Unfortunately, he used sp_OACreate, sp_OAGetErrorInfo, sp_OAMethod, sp_OADestroy, which require my server to be reconfigured, and it's lookin...more >>

I'm in need of a code that will give me a list of all the saturdays in a year... does anyone know how i could build this. Any suggestions will be greatly appreciated. -- CipherTeKST MCSE: Security 2003, CCNA, Security+...more >>

I have a =C3=BF in several fields in my datbase. When I do a BCP out it comes out as a =CB=9C . Any idea on how to get the to come out as a =C3=BF? ...more >>

I got the following situation in a store procedure for a report to retrieve data: 1. First I delete data from some tables 2. I insert new data to this tables that I just modified 3. I make a select statement to retrive data I get my store procedure to work until step #2. But when I make the...more >>

i use sql 2005. i need to transpose parts of my table.. the table looks like this: report_id textbox text ----------------------------------------------------------- 1 salutation1 dear sirs, 1 salutation2 sincerely yours 2 salutation1 dear sirs, 2 offer enclosed you find our offer 2...more >>

I could've sworn there was an sp out there that can refresh an object's dependencies. My scenario is if I drop a function that an sp references, the sp loses its dependency on the function, understandable. But when I re- add the function I the sp still doesn't have an object dependency on th...more >>

I need to create several scripts to run that will copy data from one table in a db to a table in another db. I am using SQL 2005 and i am qualifying the statement somthing like this: (note, this is not inserting but if I can get this to work than I can insert too) Select * from ServerName....more >>

Hi, I am using SQL 2000 and would like to retrive the values from multiple rows into one row The sample data is like ID value 1 blue 1 green 2 green 3 yellow 3 blue 3 green I am looking for out put like ID Value 1 blue, green 2 green 3 yellow, blue, green ...more >>

Hi, I an using the following bcp utility to export a sql2000 table to an excel sheet, there is only one filed data like this 00004563254666, 0000786653636..... The datatype is char(16), After I exported to excel it looks like this now 2.75E+11 Any ideas? Thanks ...more >>

Hi, /****** Object: Table [dbo].[SchoolAthleticRoster] Script Date: 07/26/2007 10:29:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SchoolAthleticRoster]( [SchoolAthleticRosterID] [int] IDENTITY(1,1) NOT NULL, [SchoolID] ...more >>

I have a 8 GB database on a Server with 32 GB RAM. I have to pin couple of tables. Does on restart of SQL Server services should the tables be pinned back. Are there good document on PINTABLE. ...more >>

What I want to do is prevent a duplicate in unique_label_type_values. Not prevent duplicates from being output, but prevent them from being input into the database. The database is inherited and I can't modify the field structure. DDL and DML: -- Begin SQL use master go create databas...more >>

Many thanks!...more >>

What's the alternative to using temp tables inside of a UDF? I don't want to change it to a stored procedure and I don't want to use table variable. Any other suggestions would be appreciated. Thanks! ...more >>

Hi all, An ordered MS-SQL2000 _VIEW_ of some loosely inner joined tables gives me something like this: Date Slot 1/1/2006 1 1/1/2006 2 3/1/2006 1 4/1/2006 1 5/1/2006 2 6/1/2006 3 7/1/2006 1 As you can see, there are different slots, on d...more >>

Hello, I have tried using the query below to delete records that exist in the SHIPPINGFORECAST table, but they do not exist in the neword01 table. That select query gives me the correct result set, but when I run it with the delete it removes all records from the SHIPPINGFORECAST table. Any...more >>

Hello everyone, I have a problem with the term lookup component. I made a package on my computer and it works perfectly, but when i try to run it on a server i get this message : "The component is missing, not registered ...". When I try to use it on a new package directly in that server I ...more >>

Hello there I got many documentation about it. but I didn't understand between these two options. Which one of them is chipper, better ect... where can i find documentation about it or explain ...more >>

Am I correct in understanding that BCP won't allow for the specification of text enclosures? I am using SQL Server Express 2005 so don't beliive can use DTS as it doesn't have it! Thanks Andrew ...more >>

Hi, If I for example have the name "Myr=E9n" stored in a SQL Server 2005 and I like to hit that both on a select search via a SP on just "Myren" (withou the =E9) and "Myr=E9n" how do I do that? In short: how do I ignore special charters in a search, and what else does that include (what other...more >>

Hello, How can I write a stored procedure or a trigger that will remove the time part of the value of a datetime field or a smalldatetime field, so that only the date is stored ? Thank you for your help. Gilbert ...more >>

Any suggestions on a good book writing stored procedure for SQL 2005? I am a application developer using C# and Delphi. ...more >>

Hi, Use Northwind, how to query out: Employee.EmployeeID, Employee.LastName, Employee.FirstName, Max(Orders.OrderDate), Orders.ShipName -> Correspond to Max(OrderDate) entry Thanks... ...more >>

Hi, Use Northwind, how to query out: Employee.EmployeeID, Employee.LastName, Employee.FirstName, Max(Orders.OrderDate), Orders.ShipName -> Correspond to Max(OrderDate) entry Thanks... ...more >>