Hi, What is the best way to generate a constant String from a Table (Only 1 row). I have some sql statements returning a few rows. I want to also return a row with a Constant String along with this sql statement.. e.g. Select * from orders Union Select 'End of Order Select' from ??...more >>

Hi Freinds, SQL 2000 I have to delete 8,000,000 record from a teble which has 45,000,000 records. There are index files also with that table. Is there any option to delete with no logging? I already changed my SQL DATABASE type to simple, but again it take long time to delete 8000000 recor...more >>

Hi SQLers I have the flowing scenario. I have a cursor that amongst other things returns me the name of a stored procedure that needs to run and a parameter to pass to it. Depending on whether the stored proc returns a result I then decide what to do with the other data I get from the curso...more >>

Hello folks, Shouldn't I be able to do this? I'm not able to get the syntax to work Alter table TableName add ColumnName tinyint not null default DefaultZero This works: Alter table TableName add ColumnName tinyint not null default (0) ....but I need to immediately drop the default pr...more >>

Hi my name is Abdel Lopez, I wonder if you can help me. What I want to do is create and populate a SQL CE database in a Windows application, do you know how can I do it?? I'm not so worried about transfering the file to the Pocket PC right now, my main task is to create and populate the SQL...more >>

I'm fairly new at anything ambitious in SQL. I can set up tables and create basic stored procedures, triggers and such. I know my way around ok, but there is a lot to learn. I'm just curious about something. Nothing to show you in particular becuase it's not been started but I want to know if 2 ...more >>

Is there any way to know if running dbcc with REPAIR_ALLOW_DATA_LOSS has actually resulted in data loss? Thanks, Bob Castleman DBA Poseur ...more >>

Hello, I have an interesting problem. I have to compare 2 consecutive records. I'm using Crystal Reports v.10 if that helps. I have a nchar Col4 and a date/time in Col3. I need to compare the date/time of every 2nd of 2 records. The time in the second record of each pair is always a few s...more >>

All, I have a table containing an ID and a text column. I need to dump the content of the table into a text file using the following code: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO BEGIN TRAN set textsize 62713 DECLARE @ptr varbinary(16) SELECT @ptr = textptr(note) FROM notes W...more >>

Anybody knows the pros and cons of using Views vs Stored Procedures?...more >>

Hi, I've created the SPROC below. It runs fine -- in Query Analyzer -- but I cannot seem to figure out how to store the result in the output param -- @Stoc -- so I can access it using an ADO Command. I am trying to avoid using a Recordset. Any help would be GREATLY appreciated! Thanks ...more >>

Hello! We have a bill of materials table with a classic parent/child relationships. What I need to be able to do is to take a specific part number and return the highest level parent for that part number. For instance, If part number A is used as a component in part number B, and part nu...more >>

I have created an auxiliary calendar table with the help of http://www.aspfaq.com/2519 I would like to add a column for the daynumber of year (e.g. 1-365), and populate it. I am new to sql and am not finding help with this. Any help would be greatly appreciated....more >>

I have a part software package and I would like to get a message from SQL sent to the user. I want the SQL message to be sent every time the user creates a new part. The message will say the last part number is "X", X beiing the last partnumber added in the part table, there is a field in the...more >>

I couldnt find on google a command to get the number of rows in a table. Any suggestions ? TIA -steve ...more >>

I am new at this - so please forgive me. I understand the basics of writing SQL code, but am not familiar with where the code should go or how to run it. I simply am trying to write a query to extract some data from Project Server database. I have SQL Server 2000 loaded. Where should the...more >>

I've got a stored procedure that I'm issuing a distributed query in. When I try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada, yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS ON' should do the trick. That's all well and good except that when ...more >>

I would like to test if the day is Sunday (listed below), if 'YES' then delete the files in the directory with following critieria (E:\NewOrleans\*FULL.BAK) and create a new full backup file. If 'NO' (Sunday) create check to see if a E:\NewOrleans\*FULL.BAK file exist if 'NO' create a ne...more >>

Hi Freinds, SQL 2000 How can I change the SQL acocunt password? I have to reset password evey 15 days and I have 2000 accounts. How can I chabge it by writting a script? Thanks in advance, Pat ...more >>

Dear fellows/gurus, My company is pursuing a DBA but the problem is the following: budget is limited to 30,000 euros gross per year.It's amazing, in my opinion is a poor salary for such position. So, please, how much do you reckon as a minimum for cover a job position as experienced DBA...more >>

Hi all, How can I Extract only alnumeric values within string. Thanks in advance. -- Create table and DTS into Pol table first. Use this on different server. IF OBJECT_ID('Tempdb.dbo.##Pol', 'u') IS NOT NULL DROP TABLE ##Pol go CREATE TABLE ##Pol ( PolNumbe...more >>

I am working on a reporting application that uses dynamic SQL to generate result sets. My client initially used a non-dynamic user-defined function that returned a table, but when I made the SQL dynamic, it turns out that EXEC statements cannot be used within a function. Is there a way to retu...more >>

I need to create a stored procedure that will only list a year. Current recordset (since its 2005) Year 2003 2004 However, when the new year comes around the returned recordset should be: Year 2003 2004 2005 and so forth when a new year starts. Thanks in advance ...more >>

Hello, I want to select more than one value. The following query works as is, collecting 25 records from a nchar80 field beginning with a space and then 107T: select * from comm1002 where col004 LIKE ' 107T%' I would like to also collect records beginning with space and 108--is this possib...more >>

The following proc indicates whether you have EXEC permission to a proc - however it fails for extended procs. I'd be grateful for a fix! A good test is @SPNM = 'xp_sprintf'. Note that the proc is getting a valid object id for the extended procs. PROCEDURE procHasExecutePermission ( @SPNM ...more >>

I have a situation where I need to create a cursor based on a stored procedure, so basically something like this... DECLARE @proc varchar(250) Set @proc = '[' + @DBServerName + '].thedb.dbo.spGetBCXDiags ' + Cast(@LastUpdateDate As Varchar(7)) DECLARE the_cursor CURSOR FOR @proc SQL Serv...more >>

In a sp I am populating a temp table that may have a number of null column values. I'd like to populate the columns that will not have null values by using an INSERT statement, and then separately UPDATE the other columns which may have null values. When I execute the INSERT statement to po...more >>

have a character field that represents a date in the format YYYYMM. This data is broken down in three month blocks, 12 Dec 11 Nov 10 Oct by this year and the corresponding 3 month block from last year. So the data will look like this 200512 200511 200510 200412 200411 200410 I have a...more >>

EXEC server7.msdb.sp_start_job @job_name = 'mailresults' I am on server3 and have to run a from here on server7 does anyone know the correct syntax I should use for this task thanks for any help Sammy...more >>

Below is a query written by one of the developers here. He showed in to me, I think as a joke, because he was going to run it in our hosted environment against all of our customers. Ichoked, because it was such a long query, but it really isn't terribly complex after a look at it. But I was cu...more >>

SQL2000/VB6/ADO I recently spent most of my day trying to debug a problem with an ADO disconnected recordset. Basically, I had a normal ADO Command configured, and opened a Recordset against it. I then tried to disconnect the recordset by setting its ActiveConnection to Nothing, as you do...more >>

I have a datetime variable coming from my ASP.NET application that has a time portion. I give my users the option to perform an equals, greater than, less than, or between comparison. The trouble comes in the way the application builds the criteria string. The WHERE clause passed in is in the fo...more >>

Hello all! So I have two SQL statements, SELECT * FROM tblFilePath Where username ='" + lblUser.Text + "'", lbluser.text is a label on the from. SELECT * FROM tblFilepath WHERE CONVERT(CHAR(8), uploadDate, 112) = CONVERT(CHAR(8), GETDATE(), 112) I'm trying to put theese two statmen...more >>

The following yields strange results select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where ([FieldB]>=1) sample output is FieldA FieldB FieldC 4 11 0 3 4 0 3 4 0 5 12 0 5 12 0 4 5 0 Addition, subtraction and multiplication all work properly, but division ...more >>

Hi All, Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside stored procedure, if a client has already started a transaction? The reason, I am asking this question is that, though an error occured in a stored procedure which was called within a transaction (on the clie...more >>

I am having difficulty using dynamic SQL with datetime variables. A code snippet like this gives me an error about converting a string to datetime: .... WHERE cat LIKE ' + @Product + ' AND Week IS NOT NULL AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' AND clutr_nm LIKE...more >>

I have a table storing student enrolmnts at unit level tblUnitEnrolment (studentID, CourseID, UnitID, UnitGrade) How can I select the grades, null if there is no record, to get the following query output for a courseID: Student Unit1, Unit2, Unit3 1 Pass, Fail, Null 2 N...more >>

Hi, Is there any way, that I can determine whether a table has any identity column, programmatically in SQL Server. In other words, is it stored some where like syscolumns or sysconstraints or whatever, whether a table has identity column and what column has the identity property set to on?...more >>

Hi experts, I have a table which stores data in multi-rows basis for a particular record. The structure of the table is as exhibit: CREATE TABLE [dbo].[Table_Header] ( [Rec_Id] [numeric](18, 0) NOT NULL , [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Param_...more >>

In one of the table I have a column"XMLCompressed" with Image data type. I don't know which type of file it is. I have tried with .xml, .svg, .jpeg etc. Is there any way to see what is stored inside that column...more >>

Hi "Normal" xml, I can import a file, I'm no guru but I can get it working. Relational files seems to be a bit of a tough cookie for me. I read some articles but I would like to stay as SQL as possible(most articles use 3rd party apps). I was wondering if anyone can help me out either with...more >>

Hi, I have been able to normalize my db in all other aspects except one, and ofcourse it is the most critical one (probably Murphy's Law). What I need to do is store test results, but these results are variable in size from 1 up to over 100. For example, a food manufacturer may only take 1 ...more >>

I am wondering about some capacity that, I have heard, might be installed in SqlServer 2005. I am writting an end user application that I want to have low hardware requirement (apart from running .NET). I would like to use a SqlServer database for data repository, but I don't want a server, ...more >>

Hi, I encounter an error when to create temporary table using user defined data type. Here is my code, EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL' GO CREATE TABLE #ShowSSNUsage (EmployeeID INT PRIMARY KEY, EmployeeSSN SSN ) GO here is the error msg, Serve...more >>

I have two tables InfoBlockContentDataBlock ( about 3 000 000 rows ) InfoBlockContentVID ClientInfoBlockVid 1 1 1 2 1 5 2 1 2 2 2 3 and table...more >>

Hi All, I have a small T-SQL Bloack which is taking a long time to run. How can I optimize this? I have 2 tables "Donor" and "Donations" Donor Table has "DonorID" and many columns and "SourceID" field. Donations table has "DonationID", "DonorID", "DatePaid" and "SourceID" and many other ...more >>

Hello, I currently work on Sql Serveur transactions (not distributed) and their behaviors according to the selected isolation level. I try to reproduce the default working of Oracle, and I have the following problem: With an isolation level READ COMMITED, by default Oracle "lock" no req...more >>

Hi all, Okay, we've got a product which has SQL Server as the backend database. The client has the product as well as the database. But the DBA at the clients end messes up with the data & we are held responsible (that the product is what is messing up the data & NOT their DBA). How can we p...more >>

Hi All: I am having trouble updating a TEXT column (I know that TEXT is a blob but I am dealing with very large amounts of data that VARCHAR can not hold!) What I want to happen is all rows from TableA that meet a certain criteria to be inserted into TableB but if it already exists in TableB...more >>

Hi, I have created a simple stored procedure and I am getting some errors in it. I couldn't figure out why the error is. Any help would be appreciated. Here is my sp: ------------------------------------------ CREATE PROCEDURE dbo.test ( @ID int, @NUMBERS nvarchar(2000) ) AS ...more >>

We have multiple servers running SQL Server 2000 Enterprise SP3. When this client was SP3, it had no problem debugging procedures on the remote machine. Now that the remote machine (running SQLSVR 2000 Developers Edition) has been upgraded to SP4, it can no longer debug remotely. The screen...more >>

Hi, I have a varchar field that contains values like 00:17:17, 01:04:47 which symbolize the time. How can I convert this to seconds? 00:17:17 --> 1037 seconds 01:04:47 --> 3887 seconds Thanks, Ninel -- Message posted via http://www.sqlmonster.com...more >>

Hi I have this problem. In Query Analyzer when I write dbo.spParseArray '12-13-14','-' everytihng goes OK but when i write SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-')) it returns me an error Server: message 208, level 16, state 1, row 1 Invalid o...more >>

Hi guys I have a small problem. I want to use BCP with in a transaction it's this first I execute a SQL Statement against the database and delete some data, then I run the BCP utility. my problem is how to use these two with in a single transaction! please help me I appreciate your...more >>

How to perform replication suppose on two tables but NOT for all columns?? Suppose if i have IDENTITY column in table i can use option NOT FOR REPLICATION, however in case my column doesn't identity, so ?? -- Message posted via http://www.sqlmonster.com...more >>