Hi experts, I have a table field, having the comma separated values as shown below. Table : Movie MovieId actors[varchar] 1 12,23,44,56 2 26,45,22,32 3 45,22,34,23 I need to query to find a string for Ex: 23 from the actors field. So the expected result is with row 1 & 3 W...more >>

Hi, Is there a way we can insert rows to more than one table having identity columns, in a single session? The documentation on SET IDENTITY_INSERT says we can use this on only one table per session. I need to update multiple tables with identity columns in a single session. Thanks for any ...more >>

Hi Experts, somebody in my company wrote this script, which suppose to combine the data from three tables and upload into another table. let me tell you little bit about data in these tables each table have allmost 7 million records with possiblitiy of duplicates ( 100,000 copies of same ...more >>

Hi All, Can I use a UDF as column default value? If so, is there any special syntax to get this to work? thx -BB ...more >>

Does the Standard Edition come with Enterprise Manger. I use Enteprise and can't afford another one unless someone knows where I can get a retail version on the cheap (around $5000). 207-347-7360 ...more >>

Hello, I need a help on selecting data from two seperate table..... I have two tables: TableProduct and TableOrders Columns of TableProduct ItemNo ItemName ItemManufacturer Similarly, Columns of TableOrders OrderNo ItemNo_001 ItemNo_002 ItemNo_003 Price_001 Price_002 Price_003 ...more >>

Hello, I have full text in a MS SQL database table. I also have a string "Search" which contains all the keywords passed in the URL to the page.aspx. I want to use the keywords. What I have now is this: SELECT * FROM dbo.documents WHERE CONTAINS (*, '"ASP*" or "BOOK*"') Of course this...more >>

Hi all, I'm trying to EXEC xp_sprint in a FUNCTION. But when executing with Query Analyser I get the following error: Server: Msg 2812, Level 16, State 62, Line 10 Could not find stored procedure 'xp_sprintf'. Anyone have any thoughts? I can see this Extended SP in the Extended Stored ...more >>

Hi I have not tried it, but is it possible to have FK Constraints between different sized data types for SQL Server 7 ? SQL Server 2000 ? For example if one table has a datatype of integer and the other has a datatype of smallint. We are changing the datatypes of a field to a smaller dat...more >>

Hi, I'd like to be able to call some stored procedures while I'm importing data into a table with a sample statement like: INSERT INTO MainDataTable (Received, FromSupplier) SELECT Staging.ReceiveDate, sp_CheckData(Staging.SupplierName) FROM Staging Is this type of thing possible? Or is...more >>

Hello, I wrote once this ask, without reply... So, if I cancel ADO's asynchron query, it doesnt stop immediately... the delay depends on the connection speed for sql, for example over slow WAN the request totally freezes down the client PC !!! maybe a temporary solution is to set rs=nothing a...more >>

Hello, this become a common question... is any common answer ? I want to use VB6 application to connect SQL2000 via ADSL 512/128.., and the half of the code have done already, I'm using ADO 2.8. All bussiness logic on the thick client side.., and I choosed ADO becouse I used these events to a...more >>

Hello, I am trying to alter a function I am working on because it is faulty. My function compares 2 datetimes and returns the time difference in minutes, minus non-workdays. However, since the start and end dates can also be a non-workday I have to reset the start or end dates to the next/or prev...more >>

Hi Can anybody tell me how to programmatically(SQL query) find if the given table has an identity column or not? Arun ...more >>

Is there an alternative to use a left or right join? Can you do something that is equivalent in the WHERE statement? Example: Employee_Table EmpID Name 1 Derek 2 Sam 3 Mike EmployeeNickNames_Table EmpID NickName 1 D-Rock 1 D-Train 3 Mike...more >>

I am working with a report generator that is based on SQL Server 2000 and uses ASP as the UI. Basically we have a set of reports that end users can execute through a web browser. In general the model works fine, but we are running into some scaling issues. What I'm trying to determine is, what...more >>

I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED Secondly, what is the optimum way to determine which action fired a...more >>

Is there any way to concatenate ntext fields? BOL says it's not supported, but is there any way around this in T-SQL, or am I forced to do it in exteral code?...more >>

Here's what I am trying to do: I have a large SQL script that I need to run often. I want to be able to run it and get a summary of the errors that occurred (possible info - where the error occurred, etc.). If at all possible I would like to stop execution of the script at the point of er...more >>

Built a cursor to iterate through 138 tables to get row counts using sp_spaceused. Row count for 2 of the 138 tables do not agree with row count from using select count(*) on the same 138 tables. I am looking for an expalanation. Pls note that I had already looked at the code defining sp_spa...more >>

Hi, I like to know if ON syntax is specific to MSSQL, T-SQL SELECT tb1.id, tb2.id FROM tb1 INNER JOIN tb2 ON tb1.di = tb2.id Thank you -- Mitra...more >>

I have a partitioned view setup as follows... CREATE VIEW [pixel_stats] ( [column list] ) AS SELECT [column list] FROM table_200402 UNION ALL SELECT [column list] FROM table_200403 UNION ALL SELECT [column list] FROM table_200403 UNION ALL SELECT [column list] FROM ta...more >>

Hi: I have the following two tables: Transaction (tran_id, decription) Charge(tran_id,code) I want to delete first the rows in the table Charge, and then delete the rows that correspond to the Transaction table. I tried the following but it doesn't work: delete from charge where tran_id...more >>

we are doing some customer service reports for that we want to pull/retrive some data from another sql server placed in some other location not with our webhosting provider. How to do this, can any one explain how to connect thirdparty server....more >>

Is there a way to set the SQLServerAgent service to start up under a user account (not Local System), AND have the service interact with the desktop? I can't seem to find any info on this, nor can I find a registry setting for it. ...more >>

Hi Freinds,=20 Can I have SQL 2005 and SQL 2000 installed on the same machine? I have = installed 2005 and want to install 200 too Thanks in advance,=20 Pat...more >>

Hello, I am working on an ASP.NET web site. I have created a string keywords() with the keywords passed in the URL. I have something like this: SELECT * FROM mytable WHERE title LIKE '%asp%' OR title LIKE '%book%' OR description LIKE '%asp%' OR description LIKE '%book%' 1. I need to ...more >>

HI, I am trying to return the first 4 rows in the table on each product. The table is set up as follows: item date a 1 a 2 a ...n b 1 b 2 b 3 b ..n .... the result should be the first 4 rows for product a, the first 4 rows for for product b and so on... Than...more >>

I am trying to create a table variable, with a primary key constraint. This will be a composite primary key that uses two fields in this table. Here is the declaration: DECLARE @myTable TABLE( CPK1 int, CPK2 int CONSTRAINT pk_myKey PRIMARY KEY (CPK1, CPK2) ) The problem ...more >>

Right now I'm storing my .NET enums in sql server as strings. num Colors { Red = 1, Green = 2, Blue = 4, Yellow = 8 }; in sql server, they're stored as "Red", "Green" varchars I would like to store them as ints in sql server so that in my code I would not have to parse them like myColo...more >>

I'm wondering if there is a way to parse a variable w/o going through the process of a WHILE loop, etc.? For example, I know I can do the following: declare @vchCode varchar(40) create table #test(chProdNum char(4)) insert #test select chProductNumber from ProductMaster where chProductN...more >>

Hello, can any one please tell me what is the syntax to get only two digits after the decimal? I have a column that contains numbers like: 23.432 543.6256 2998.927 I only want the first two digits after the decimal. any help would be appreciated ...more >>

Hi NG, I have to backup several different databases, where the user can choose the names of the backup image (bck). In order to restore the correct database I have to distinguish between these database images. I cannot assume that the backup image name says anything about the database conte...more >>

how do you set locks to tables?...more >>

Hi I am using the PRINT command in a script. My problem is that the messages are only being displayed when the entire script is complete. Is there a way to get it to display immediately when the command has been processed? This seems to work with a lot of the system stored procedures (e.g. ...more >>

Hi All, I am trying to create a query that does the following: I have a table of provinces and a series of years listed for each one. So the data looks like this: AbbrevProv InventoryYear ---------- ------------- AB 1994 AB 1995 AB 1996 AB 1997 BC ...more >>

I've got a database that has a number of tables, but there are only two that I'm concerned about. For this posting, I've simplified things, and I'm just including the columns I'm discussing here: CREATE TABLE [dbo].[Table1] ( [Table1_identity] [int] IDENTITY (1, 1) NOT NULL , [Data1] [varch...more >>

BOL: 'The table cannot be referenced by the process which called the stored procedure that created the table.' This is in reference to using local temp tables. In a nutshell, this is what I'm trying to accomplish. A global temp table won't work because it will be available for all sessio...more >>

Let's say I have a stored proc, sproc X, which makes a call to sproc Y. Sproc Y creates a temp table and generates a dataset. I'd like to use the results in the temp table back in sproc X or query on the temp table from sproc X. I tried using a local temp table, #table, in sproc Y but since the...more >>

Hi, I have a default instance of MSDE 1 (equivelant to SQL 7.0) install on my server, I have just installed a new instance of MSDE RELEASE A (equivalent of SQL 2000) Now I have MSDE / SQL 7.0 as my default instance and MSDE / SQL 2000 as my new instance. my question is : How to I m...more >>

Hello, I'm trying to get an error code back from a sqlserver stored procedure; I'm using an Output param to do it. I set up the call to the sp like this: SqlParameter retCodeParam = new SqlParameter("@ReturnCode", SqlDbType.Int); retCodeParam.Direction = ParameterDirection.Output...more >>

I have a table with data that looks like this: ParentID CategoryID Description NULL 1 Auto 1 2 Sedan 2 3 4 door 3 4 Manual What I need is a proc with the Category...more >>

I know that cursors are very expensive/inefficient and can be used if you really need to do row by row processing. However I am trying to create some sample tables and scripts that prove that the cursor version is much slower than the set/join based one is. I do not mean in the execution pla...more >>

Isn't this supposed to force a table scan? I definitely get a clustered index scan. create table idx_scan (first_column int not null constraint idx_scan_primary_key primary key (first_column)) go set showplan_all on go select * from idx_scan with (index = 0) go set showplan_all off go...more >>

I posted a question a few days back about query performance and Ben suggested a few changes to be made. The first change I made was to create a 'covering index' which chopped execute time from ~15 seconds down to 2 seconds. Outstanding! I am looking at the other changes he suggested, but I rea...more >>

from sql 2k I have a SP that has nested IF statements e.g. IF @myParam1 = abc BEGIN IF @myParam2 = def BEGIN do something END IF @myParam3 = ghi BEGIN do something END END I need the IF statements for @m...more >>

I am trying to learn more about indexing and would like to get some help on this query: I have a table with the following columns: ID Int Primary key, Tagname Varchar(100), Value Real, DateTime DateTime This table is filled with data, approx. 100000 records a day. Then there will b...more >>

No matter what I try the datepart function never returns weeknumber 1 for the date 20050109, shouln't it? It doesn't matter what "set datefirst" I use. If I check the date in my outlook its in Week 1. It seems it's a problem when the previous year was a leapyear. Week 53 (according to outlo...more >>

I used Update statement: update t1 set t1 = t2.name from t2 where t1.id = t2.id Is correct??? Or I must use update t1 set t1 = t2.name from t1, t2 where t1.id = t2.id -- Tomas Chuy-Kan...more >>

Hi, I have a table in SQL Server with the following layout: Country Date YTDValue ---------------------------------------------- Spain 2004-1-1 30 spain 2004-2-1 45 spain 2004-3-1 50 spain 2004-...more >>

I am using VB6 with ADO accessing a SQL Server 2000 database that has about 10 million records. The database has 1 table, and the table has 4 columns. CREATE TABLE [dbo].[Packet] ( [PACKET_ID] [int] IDENTITY (1, 1) NOT NULL , [PACKET_TIME] [datetime] NOT NULL , [PACKET_CONTRACT] [varchar] (8...more >>

Two days ago I posted a question here about named instances of SQL Server. No one responded. Am I to take it that no one actually implements named instances? Still curious... ...more >>

How can I change connection of SQL Server Agent to SQL Server another way than from EM? I have found stored procedure msdb.dbo.sp_set_sqlagent_properties that has parameters @regular_connections, @host_login_name, @host_login_password. But parameter @host_login_password expects encrypted passw...more >>

Hi, how can I get distinct number and smallest date from this:? number date 3744830 20041129 3744830 20041130 I need: number date 3744830 20041129 I've tried select DISTINCT(T1.number), T1.date from "dbo"."table" T1 LEFT OUTER JOIN "dbo"."table" T2 ON T1.date= (select min(T2...more >>

I just want to generate numbers by adding 1 to i variable, but the following procedure just printing initial value 2 on every line, where am I wrong ? create procedure mt as begin declare @i int set @i=2 while @i<=10 print @i SET @i=@i+1 end Please advise ? Best Regards, Luqma...more >>

I am trying to get an average throughput for all of our live clients over the last 3 months ( sept / oct / nov ) so as to try and forcast the comming year As the tables concerned are so large, i have generated test tables with the relevent data fields in if exists (select * from dbo.sysobj...more >>

Helow, I´m using 2 process that run currently. The first process have a cursor from a table A, only for read. Inside this cursor, There are an insert to a table B. The second process have a update to a field to table A. This process only update this field, don´t change the table format..... ...more >>

Can email be sent with SQL? I need to send an .ASP page as an email template using VBScript. I'd like to do so using CDONTS or CDOSYS. this is the dynamic email template http://www.womentowomen.info/emailtemplates/grid23.asp?id=122 the script needs to loop through a recordset and send em...more >>

I need to send an .ASP page as an email template using VBScript. I'd like to do so using CDONTS or CDOSYS. this is the dynamic email template http://www.womentowomen.info/emailtemplates/grid23.asp?id=122 the script needs to loop through a recordset and send emails using data from SQL 2000....more >>

I am looking for a procedure to calculate depreciation for the given no. of years. Say, I purchased a machinery for Rs. 500,000/= in the year 2000, now I need to calculate yearwise depreciation using decling balance method, @10% p.a. The output should be something like this. Ope...more >>