Hi all, Is there a lock that is similar to NOLOCK hint by not honouring exclusive locks, BUT reads original (committed) data (no dirty reads) instead? Pls kindly advise. Help is much appreciated. TQ....more >>

I want to know what's the differences between PL/SQL and stored procedures, the followings are my analysis, please comment and advise. 1) PL/SQL is Oracle specific, stored procedures are supported in Oracle, MS-SQL Server, or other databases. 2) PL/SQL has 2 types: procedures and functions ...more >>

Hi there, I am very sorry for my not perfect English. I've got very simple question. Is it possible to have stored procedure with 2 result tables and then from my application to refer to this tables by ado. Something likes that: Select * from aaa Select * from bbb And then in...more >>

How could I do a replace of " " to " " in a string were the string is a field and the values are variable. I would like to use Enterprise Manager or a stored proc. IE. Node.Nodename is equal to "have a nice day" I want to replace the " " with " " after nice....more >>

Friends, If I need to import a text file into a pre-existing table using T-SQL, then BULK INSERT works fine. But I cannot figure out how to import that same text file, using T-Sql in QA, if no target table currently exists. Is there a way to do this? To clarify, I can import the same ...more >>

I need to calculate a prior date. When I pass a negative dayvalue this fails. AUCTION_DATE is a smalldatetime column in a table, and I am sure you can imagine what the from and where clauses look like. Select DATEADD(day, 100, AUCTION_DATE) - works but is of course incorrect Select DAT...more >>

Hello, I am using DB-Lib to bulk copy some data into a table that has an identity column (set up to be used during replication). I don't think I want to actually bind to an identity column with bcp_bind, do I? Is there any way to skip that column? Any help would be appreciated. Ed H...more >>

hey all, goal: Automate my invoices given: My Transactions Table Customer1, Cut Lawn, $20, Invoice# Customer2, Cut Lawn, $20, Invoice# Other tables Invoice Header Invoice Detail I guess what I need to do is assign invoice numbers to each of my transactions in my transactions table. Ne...more >>

Guys, I have a table with two fields Vendor varchar(20) VendorResume text(16) I want to remove/reduce/replace multiple spaces with a single space in the VendorResume field. Does anyone have a store procedure handy for this. I came up with one procedure but it is very inefficient and t...more >>

Hello All, Let's say I have a query: SELECT TOP 1 columnA FROM .... long list of joins, where clause ORDER BY columnA DESC I am pulling data from 2 views that are 4 tables each with the same structures unionized together joined on a primary key, and also one table which is just a table...more >>

Hi everyone. It's the first time I post here so forgive me if I chose the wrong group, since this question is about a problem we found out when using SQL Express 2005. The problem is that a conditional clause behaves different when it's used into a IF THEN block than when used in a WHERE clau...more >>

We have 20 or so update scripts from visual studio that we would like to merge into a large update script. We would like error handling as well, in a way. using the following code, i was hoping to include the alter-table entries and other related updates within these blocks, and then catch...more >>

hi guys, Here is what hiring manager said, " Contractor is required to have professional and general liability insurance of at least one million dollars per occurrence in force at alltimes. Contractor must warn BROKER thirty (30) days prior to any planned lapses or changes in insurance...more >>

I want to create a stored procedure that will list all the tables for a given database. I can get the databases using the sp_database function, but the sp_tables requires that I be in database to use it. I want to create a stored procedure that will take in the database name as a variable an...more >>

thanks for your help how would i select a column if i have a string to work with: DECLARE @ColName varchar(30) SET @ColName = 'CusId' SELECT @ColName FROM Customers this doesnt work cause it returns CusId instead of the ids thanks for your help ...more >>

C# code: But database object has no UserDefinedFunctions collection. Why? The same way I used for Stored Procedures and no problem... foreach (SQLDMO.UserDefinedFunction udf in database.UserDefinedFunctions ) { Console.WriteLine(udf.Name); SQLDMO.SQLDMO_SCRIPT_TYPE st = (SQLDMO.SQLDM...more >>

Hi I have Some tables named with double Quotes in them and with underscores in them. I am trying to pass them as a variable dynamically to a reindex stored procedure and hence want to embed them within the square brackets. I tried to do that but still end up with incorrect syntax error. Wha...more >>

I'm considering porting some code that originated in MySQL. Current code is unmanaged, but I may port to .NET. I'm not that familiar with MySQL at this point, so I'd like to determine whether to stay with MySQL (presuming there is an interface to ADO or ADO.NET ...is there?) or to go with mor...more >>

thank you for your help does anyone know why this statement fails DECLARE @NewValues varchar(8000) SET @NewValues = '' DECLARE curCols INSENSITIVE CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'COCUS') ORDER BY ORDINAL_POSITION OPEN curCols DECL...more >>

Can I use an ALTEER TABLE statement in a stored procedure, I need to drop a named constraint. Getting complie error. Thanks ...more >>

SS05 : Is it possible to write INSERT or UPDATE T-SQL where the contents of an XML doc are inserted into a col of datatype XML ? ...more >>

Hi, In Query Analyzer after you have executed a query you can 'Select All' and copy. When I paste for instance to Excel I don't get the column headings. Is there a way to get the column headings? ...more >>

Can someone tell if there's a better way to write this piece of code than what I have here? Thanks create function dbo.dia_mtg_specialty_code_fn (@product_id code_code) returns varchar(100) as begin declare c1 cursor for select usr_mtg_specialty_code ...more >>

is there a way using t-sql to loop through a tables columns? i want to build a long string with this type of logic foreach (column in table.columns) { strValues += column.name + "=" + row.value } thank you for your help ...more >>

Hi, At the moment we have apx 15 images per item (apx 5000/client). Currently, these are sent to us from the client to our lan so they can be QC'd. They go through the QC process and a database pointer is allocated to the database item (in tblItemImages ( itemID INT, ImageFileName varchar...more >>

I am a new SQL user have a nice simple query which returns the sum of sales by month for 2005... SELECT TOP 100 PERCENT MONTH(TransDate) AS [Month], SUM(NetSales) AS [2005] FROM dbo.BtaData a WHERE (WrittenFlag = 1) AND (YEAR(TransDate) = 2005) GROUP BY MONTH(TransDate) OR...more >>

The further I delive into SQL Server replication, the more apparent it becomes what a complete and utter lash-up the whole "technology" is. Has anyone ever encountered this particular problem? I have transactional push replication in use, with a publication with @centralized_conflicts='fal...more >>

I have a Changelog system that requires modification. It was setup to record changes against servers (some are SQL) in the organisation. I have a changes table into which change records are saved: Create Table CL_Changes ( ChangeID Int not null, ServerID Int not null, DatabaseID Int nul...more >>

I have my data summed and groupd properly to send to Crystal for a cross tab. Unfortunatly I can't get a 12 month dispaly on a landscape paper to fit with the total. So is there a Cross Tab function that I have missed that will gen the dataset form me? TIA ...more >>

Hi everybody. I would apreciate soem hinds on the following: 1 need to save office documents (word, excel) or other type of document (drawings), in an sql data base, and to be able to open the document using it's associated application. The front end is VB I need some documentation on ho...more >>

All I'm trying to do is pass a parameter to a stored procedure with the wildcard %. Reading on the internet, it seems that while looking at the query in the querybuilder the % won't work, but it will in code. IT DOESN'T. Any suggestions? Thanks for any help you might be able to provide. ...more >>

Is there a way for locking entire database programmatically, so only the process that locked the databasecan can operate with the database ? If so , what are the steps. Can I do that from C# code ? Can I do that uisng "osql" ? Thank you. ...more >>

I have copied a DB from one server to another, but now want to retore it with a new name. Have tried the following: RESTORE DATABASE SyteLine7_SGBTest FROM DISK = 't:\backup\SGBLiveSite.bak' WITH MOVE 'SyteLine7_UK_Data' TO 'r:\program files\mssql\data\SyteLine7_SGBTest.mdf', ...more >>

Hello folks, I really need some help here figuring out how I can procede with my query. I would like to monitor my inventory that will become expired against my forecast. Basically, I have two tables: Forecast (SKU, Month, Quantity) Inventory (SKU, Inventory, ExpirationDate) Let's ...more >>

I'm passing a comma separated list into a stored procedure e.g. exec usp_returnProductsFromIdArray '5,4,1,99' The comma list is being turned into a table (see usp_IntListToTable below) Here's the proc that returns products that are in the list CREATE procedure usp_returnProductsFromIdA...more >>

Hi, I have a table with two columns CREATE TABLE [dbo].[Table1] ( [userid] [int] IDENTITY (1, 1) NOT NULL , [groupid] [int] NOT NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [userid] ) ON [PRIMARY] ) ON [PRIMARY] GO PK is identity column Sample Values in table UserId gr...more >>

Hi all, This query selects the total count based on the where search. I want it to return a Column name results and a Column after that with counts and if it contains what I mentioned in the were clause to put a yes and then how many and the next row a no with how many that it didn't have? ...more >>

Hello! I have a stored proc that checks the file existance in one location and copy them to another location. The stored proc reads the record one by one and builds the DOS COPY command. In the end, it exccutes the DOS command using xp_cmdshell. See below the code. The stored proc is worki...more >>

I have SQL Server 2000 with SP 4 running on a Windows 2003 machine. I also have a web application using .NET 1.1 from another Windows 2003 machine. In one of the pages of the application I am calling a stored procedure by creating a SqlCommand object and adding the appropriate parameters. The...more >>

Automatically Create Joints Hi, I have a "tblShop" table in my database that holds the shop name and branch locations like this: tblShop ======= shopID (smallInt) shopName (varchar) states (varchar) The branch location (ie. the "states" field) is a comma-separated string of the...more >>

I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any one of the fields is null, the whole value comes out at null. Can anyone give me a hint on how to still show fld1 and fld2 if fld3 is null? -- Chuck Foster Programmer Analyst Eclipsys Corporation - St. Vincent He...more >>

Hello, I have a simple SQL SELECT statement with an INNER JOIN. Since this particular query is likely to get hammered, I was hoping someone could tell me which way of specifying the conditions for the joined table would be more efficient. Here is the code snippet for method 1 (note the WHERE cla...more >>

Hi folks, I have a large table which has approximately 150 million records. I am running a SELECT COUNT(*) query to find out the exact number of rows present in that table. It is running for more than an hour, yet to complete. Estimated execution plan shows that it uses clusted index scan. Bu...more >>

Hello, I was wondering if someone can provide some assistance. I need to do the following: -connect to the desire servers -cycle thru the user database -if desire databases exist: --check to see if desire user is present in master..syslogins ---if the desire username is in master..sysl...more >>

hi, I use XML explicit option to get the output in the xml format. but if i have a null value i want the empty tag. example: emp_pwd = null <emp_pwd></emp_pwd> how to do this? thanks vanitha...more >>

hi friends, i want to select each and every table from the database and then check for duplicates, eliminate duplicates and transfer data to the destination database. in that process. I wrote a SP which selects each and every tables. Example declare tablename cursor for select distinct...more >>

Hi I am executing the following: xp_cmdshell 'type C:\Affiliate\mytext.txt' all the path and the file location is valid and works well at command prompt but when I issue it from the master database extended proc xp_cmdshell. it prints NULL and says The system cannot find the path specified. ...more >>

Hi guys I've got a table with a field called PMon_Total. At the moment, a lot have rows have this set to null. I want to substitute this NULL for a 0 so that I can count all the PMon_Total fields, however, I can't get coalesce to work. Here's my select... SELECT ProjectID, SessionID,...more >>

hi friends, i want to find the record updated datetime. if 1 record is updated in the table, I want to find the datetime of the updation. I can't insert a column with timestamp and then insert the data to find. is there any back process that stores this information. thanks vanitha ...more >>

Hi All, Thanks in advance! I want to generate an XML file in a Update trigger script. Only the updated record needs to be exported to the XML file. How do I know which record is being updated in the Update trigger? Thank you so much! Regards, Sean ...more >>

Hi all, Can any one help with this Case Statement error!! @mid INT = NULL @pid INT = 0 SELECT asmt_v1_area_id, name FROM asmt_v1_areas WHERE CASE WHEN @mid IS NOT NULL THEN mid = @mid //error Incorrect syntax near '='. ELSE ...more >>