I have the following scenario I hope someone can help me with.. I have a database with a few tables and two major queries (stored procedures). The principal data object is a queue. Query #1 is responsible for locating a row in the queue and updating its status. Status may be Available, Pen...more >>

I use CHR(10) + CHR(13) to add CR/LF to my data, but it shows a special character like a vertical bar. any idea about this? ...more >>

I have a web application which connects to Microsoft SQL Server 2000 through JDBC-ODBC Driver. The application server is JBoss and I am using connection pooling. When the application connects to the database it provides userid and password which are 'sa' and 'password' respectively. They are con...more >>

I'm trying to set up a table that will convert default SQL error messages for check constraints into friendly front end messages. What I'm having trouble with is how to pick apart the default message so I can do this. Is the error stored anywhere that I can look at it's parts? Any suggestions on ...more >>

I'm confused by the difference between sp_createstats and sp_updatestats. I have several read-only databases. After these databases are re-built I need to make sure the statistics are up-to-date before they are made read-only. I've read that we cannot always rely on the auto update stats ...more >>

Inside of a stored proc, how would I issue a Contains using a variable and a wild card, this dosent work declare @myvar varchar(50) -- this would be an imput param set @myvar = 'hello' -- this would be an imput param select whatever from CONTAINS(fieldname, ' " ' + @myvar + ' *" ') than...more >>

I'm trying to eliminate (or at least reduce) deadlock issues. I've already ensured all stored procedures are accessing tables in the same order and now I am looking at locks and transaction levels. Are row level locks the default for stored procedures in SQL Server 2000 or do I need to issue s...more >>

create table #tab1 ( Cd varchar(10) , CdDate Datetime , id int ) create table #tab2 ( CdDate Datetime , idnum int ) INSERT INTO #tab1 values ('Fox','2005-04-25 14:38:07.000',1) INSERT INTO #tab1 values ('Fox' ,'2005-04-25 14:38:16.000',2) INSERT INTO #tab1 values ('Pen'...more >>

I have a table that has an id field and a description field... Unfortunately the id field does contain dupicate id entries... What I need to do is select each distinct id and then one of the many description fields that are assigned to the id number can anyone help with this ...more >>

CREATE TABLE [dbo].[Jobs] ( [MyLovelySurrogateKey_ID] [int] IDENTITY (1, 1) NOT NULL , [StartDate] [datetime] NULL , [StopDate] [datetime] NULL , [JobTitle] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [etc...] ) ON [PRIMARY] StartDate and StopDate are optional to users o...more >>

I have a SQL statement. SQL sever lets me to save my SQL. I got internal SQL server error while I run it. I think that my SQL is valid otherwise SQL will not let me save the SQL. My object and fileds name should be correct, otherwise SQL server should complain about this. Other than this, ...more >>

i've read lots of usenet and and microsoft support articles about how to remove duplicate rows from a table, but i am trying to modify that logic to delete "similar" rows. for example, consider the following: create table t1 ( col1 int, col2 bit, col3 bit) insert into t1 values (1, ...more >>

In my stored proc I need to check whether a empty or NULL value is sent and check for it in my sp. How can I do this? David ...more >>

What I want to do is to create a Web Interface to allow a User to click a link and execute a Stored Procedure, I think this would be straight forward. What I also want to do is to allow the user to select a check box to have that stored procedure run over night or even schedule the Stored proc...more >>

I basically need to send emails from MY DTS packages and also some of my stored procedures.There is no way of having any MAPI compliant software installed on the productions database server,so I came up with the idea of installing an SMTP server and use the extended stored procedure introduced...more >>

I noticed that there is something in master database called Extended stored procedure which can be dlls? what's this? How can we make our own sps a dll and put it somewhere like this? Thanks ...more >>

Hello, ALTER TABLE tb1 ALTER COLUMN fd1 VARCHAR(80) NOT NULL It's appended at the end, can I control fd1 it's the first field? thank... ...more >>

How can I query a database that checks for duplicate data in a combination of fields. For instance, LastName may have many duplicates but I want to find duplicates of LastName combined with FirstName. Thanks. ...more >>

I am used to storing Null when a value does not exist. I have been told to store "zero length strings" in some cases instead. Fine . . . I do not know how to generate a "zero length string" or a "zero length number" or anything else "zero length." But I suspect it is simple. Can you provid...more >>

I am struggling with developing a query to handle the following scenario. A transaction table has a foreign key field that stores the customer Id and employee Id for the particular transaction. The rules for this business are such that a retained client is someone who has three consecutive tr...more >>

Is there any way to modify this proc so that: If it recieves a non-negative centre_id the data is ordered by centre_name but with the centre whose id was passed, being first in the the list. If -1 is passed then just order by centre_name. thanks. CREATE PROCEDURE [dbo].[get_centres] @...more >>

Is there a way to show on the bottom of a table, the navigation buttons with the record number of total records like Access for example. thanks al...more >>

I've got this big giant script that drops all my default constraints, drops all indexes, then drops all clustered index constraints, then adds alot of new indexes, constraints, and clustered indexes. I've got it running without throwing any errors now. However, sometimes the database locks u...more >>

I have been working on creating templates to have for when creating databases, tables, views, etc. When I do a replace template values on this particular template all values get replaced except for the drop database line. The strange thing is if I replace the values and then do replace val...more >>

Can I perform an if..then on a parameter in my stored proc in the where clause? i.e. create procedure s_test @param int as select table1.a,table1.b,table2.f from table1 inner join table2 on table1.a = table2.a where a = 1 and -- pseudo code-- if @param = 1 then table1.b = 2 else ...more >>

I'm an asp/.net programmer and am having a problem using the like operator. I'm searching Title and Description columns in a Products table for terms like Jersey, Helmet, Hat, etc. When searching these fields I'm using the Like keyword as follows: WHERE Title Like '%' + @SearchParam + '%'...more >>

HELP!!!! I am using SQL in Access and need to pull all of the records that don't match in the key field. The key fields are the same name in both tables and I have built a relationship on a different field. Both tables have some matching records and some non matching. I want all of the record...more >>

I am calling a stored proc from a DTS package and the proc passed an output parameter into a global variable in the package. The procedure that is called, calls another procedure 4 times with different input parameters. This is the procedure that actually produces the output that I need. Th...more >>

I am trying this update query to update the table(Allfile) with a daily data feed we get. Both Table structures are identical. I get the following error: Server: Msg 8152, Level 16, State 9, Line 1 String or binary would be truncated. The statement has been terminated. Here is my updat...more >>

Hi, I'm trying to get a web page (on the company intranet) to connect to my sql server through windows authentication. When the script executes how do I ascertain what the user is going to be? Thanks Adam...more >>

how come that if I put in this value tblMyTable.currency is a decimal(8,4) insert into tblMyTable(currency) values ('0.4568') I get this in return .4568 or is this normal?...more >>

Dear gurus, We have created a couple of tasks (using vb 6 a sql2000 which create a olap cube in our side, here in Spain) That's fine and it is working properly but from now on that cube must be processed in another country. I suppose that there are two solutions for that: -Implement remot...more >>

Q1: When creating indexes, which is the best way: 1. Create one index for each column which needs to be indexed? 2. Create one index, which contains all the columns, which need to be indexed? Q2: Does indexex affect the datafile (physical file) size a lot? ...more >>

Back to our debate from two weeks ago. I left with a feeling that the sProc I was using to drop tables from a backup database if they were older than 31 days and then create the next set of backups on the fly with a suffix added to the table name in the format yyyymmdd was actually not so bad....more >>

I am attempting to make an update on a remote server table with a join on a local server table. The SQL looks like this: update [Remote-Server].RemoteDB.dbo.RemoteTable set Col1=LT.Col1 from LocalTable LT inner remote join [Remote-Server].RemoteDB.dbo.RemoteTable RT on LT.istringId=RT.istr...more >>

I receive the following error when I tried to add a "12:00" value to a SMALLDATETIME column: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error....more >>

Hi, I have a table with only one record in it looks someting like this Field Titles : Name Address PC Data : Bert A House CL4 Is it possible to reformat this so that it looks like Name Bert Address A House PC CL4 Usually I am doing this the other wa...more >>

How to debug user defined functions??? I mean i can't put a PRINT ... OR SELECT ... to check something . Thanks -- Message posted via http://www.sqlmonster.com...more >>

Hi I have a tre table setup in order to support dynamic attributs of users: user table: | *userid* | *username* | 1 | joe | 2 | james attributetype table: | *attributetypeid* | *attributename* | | 1 | height | 2 ...more >>

hi, how to output 2 table data to a text file? ...more >>

Dear Experts, I dont know how to return value from stored procedure the field type of file_size in file table should be int and I run "exec fsm_AddFile 'test.txt', 'd5124', 5, 'testing file'" suppose 0 should be return from the SP if any error encounted, but it only return Server: Msg 24...more >>

Server: Msg 1015, Level 15, State 1, Line 1 An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference. ...more >>

Hi, I am new to SQL Server, I am using SQL Server 2000, I want to store only date (not time) in my table, is it possible in sql , is there any data types except datetime and smalldatetime. Thanks in advance. Hardik Shah. ...more >>

Hi, What would be the correct syntax for creating dynamically a view from a stored procedure being given that 1) the stored procedure is in master db 2) the new view must be in @dbname passed as variable to the procedure The following code results in "CREATE VIEW must be the first stateme...more >>

Hi, I have got two table one master table and one detail table. Master will be having records with codes of different pattern depending on the user's selection. From there I need to take those codes and make a join with the detail table and fetch the matching records from the detail table...more >>

Hi, Correlated subquery is written in the following syntax in Oracle. Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from Emplyee where sal>=X.sal) How can this be written in SQL Server? Please throw some light. Thanks, Su Man ...more >>

Dear all, I'm first to using SQLExpress and CLR. The SQL is loading libraray, not execute file, Such that, where can I to create the .config file and read the appSetting. Thks for help Andy ...more >>

Hi, Some SPs have more than one result set. How can I access a particular result set? For example, I want to insert the third result set of: exec sp_help 'Customers' into a table. Thanks in advance, Leila ...more >>

User A and User B modify the same record, UserB save first. As User A save it , I will get the concurrency error. I know I can use Try ..... dsTable.udpate() catch err As DbCurrency messagebox.show("UpdateFailed ") end try However, How can I let User A know "User B save the same record ...more >>

Hi, what's COLLATION for, can someone give a sample to me to begin? SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage') ...more >>

Hi all, The problem with the below example is that it is not giving me the wanted results: <CODE> Declare @pad nvarchar(100) Drop Table MyTree Drop table MyPaths Create table MyPaths (path nvarchar(1000), pNodeId int, cNodeId int) Create table Mytree (pNodeId int, cNodeId int, proce...more >>

Hello! Is that possible to create a user-defined function, that would take value of type text, and pass it to the OLE Automation function call? The SP code I use to do OLE Aut. call, is as follows: DECLARE @object int DECLARE @hr int DECLARE @property nvarchar(255) DECLARE @src nvarcha...more >>

Hi, I know how to choose the format of datetime using CAST and CONVERT. However, I do not know how to separate them in two fields. i.e. In one column i will only display the Date part ( 2004/03/04) and in another only the Time (12:45:04). TIA -steve ...more >>

Hello everyone, One of our applications was timing out while executing a certain stored procedure that inserts information in a group of tables. When I checked the situation via Profiler, that particular SP has indeed an almost consistent duration of roughly 30 seconds (which was the timeou...more >>

Hello everyone, I have a client application (Windows app) that logs into a database via an SQL account. As part of the nature of the application, the initial login screen of the application has the following fields: database name: (text field) sql username: (text field) sql password: (tex...more >>

Hi, What is the diff. Between SP:Completed and SP:StmtCompleted Event in SQL Profiler? I can view the total time taken by SP:StmtCompleted Event. But how can I see the Break up total time taken by a SP:StmtCompleted and other events ? Thanks ...more >>