Hi ALL! I have 2 tables. 1) Dept = The Master Table (DeptID, DName, Loc) where DeptID is the Primary Key 2) Emp = The Detail Table(EmpID, DeptID, EName, Sal) Where EmpID is the Primary Key And DeptID is the Foreign Key to Dept(DeptID) I have a Stored Procedure to Delete the Record from DEP...more >>

i'm sure the following has been extensively benchmarked ... a stored procedure to retrieve a single row .... @PrimaryKeyFieldValue is sp input parameter method 1 : select @ForeignKeyFieldValue from Table1 where T1.PrimaryKeyFieldName = @PrimaryKeyFieldValue select * from Table2 where Pri...more >>

Hi, I'm new to T_Sql, and I try to create a function to return a unique 10 char string, in the following format: PKXXXXXXXX, where XXXXXXXX is a 8 digit number, unique in database (I work in Access ADP application) CREATE FUNCTION "fn_GetAccountNumber" () RETURNS char(10) AS BEGI...more >>

current sproc uses temp tables for searching, sorting and paging of data, passing list of ids to other sproc, etc.. would it better to use table variables instead? especially under heavy load where it seems that locking occurs, etc if so, then how do i pass the table variable to another sproc ...more >>

Can anyone solve my query question http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32388 ...more >>

Hi there, I have heard that indexing a column with data type datetime is not recommended. Does anyone know if this is true and why is that? If this is true, I don't have any problems on implementing a different approach, but I would like to know if it is true that indexing columns with d...more >>

Hi all, As above really...I have the following SQL statement (I've dumped this from my ASP page running it so it has some values, and I've moved it around to try and prevent wrapping)... SELECT WebsiteID, WebsiteName, WebsiteDesc, WebsiteURL, WebsiteMetaKeywords, WebsiteMetaDescrip...more >>

I have a database with a table with movie-titles and a table with actors who is starring in the movies So it is like this: Title_table title_id title_name Actors_starring_in_movie_table title_id2 actor_id So I would like to select the movie titles that have the same actors starring...more >>

Hi, I need to find 2 queries that do the following: Given a table name, list all of the child tables for it. Given a table name, list all of the parent tables for it. I need it to use the infomation_schema tables. I know about sp_fkeys but I want to modify the query to suit specifi...more >>

Has anyone successfully implement partitioning on multiple columns? My attempts produce the following error: Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not updatable because a partitioning column was not found. For example: CREATE TABLE [dbo].[Test_200312_101...more >>

I have a company table and I would like to write a query that will return to me any duplicate companies. However, it is a little more complicated then just matching on exact company names. I would like it to give me duplicates where x number of letters at the beginning of the company name match A...more >>

Hi, I have the SP below, it basically inserts a single row into table X and a row into table Y if applicable. X & Y are related and the SP ensures that the integrity of the relationship is maintained. As someone who has only a little experience of developing SQL Server applications the next...more >>

Hello, I'm in the process of optimizing an application I wrote and keep running into the term 'narrow index' in some articles/webcasts without detailed explanation of what exactly a narrow index is. I realize that a narrow index is an index on a column with a minimal amount of bytes, b...more >>

Anyone know the equiv M$ t-sqlk that does table locking such as below? This code snippet is from Sybase and I need to convert it to M$ SQL2k - SP3. /* Lock the tables that will be transferred TO DW. * The locks will be removed when this stored procedure * is exited. "mode wait" means wait pati...more >>

I have a database that saves Orders. Each Order has a customer related to it. This is different from the NorthWind example db because I will not have a table with all the customers saved (each order will have a new customer saved (could be unique or duplicate). I was thinking of putting the c...more >>

Hello ALL, In a stored procedure, I need to execute a insert clause in a table from other database, like this: "INSERT INTO other_database.dbo.table_name VALUES" I declare a in parameter @DATABASE_NAME VACHAR(30) an declare a variable @SQL_INSERT. The @SQL_INSERT receive 'INSERT INTO ' + @DATA...more >>

Hello, I have a VARCHAR(20) where I have some IDno, like "1,12,23" Is there a way to put this into a tsql-statement like: ... WHERE IDno IN (@IDnoVariable) I get this error: "Syntax error converting the varchar value '1,12,23' to a column of data type int." Regards, /Mikael Sörensso...more >>

I've been having trouble passing arrays with SP_OA. Has anyone tried using sp_OAMethod to pass an array to or from COM object? I've looked around for an example but have been able to find one. The documentation says that you can pass an array out of the COM object and will get converted to a...more >>

I would like to create a daily file that parses out the errors from the SQL Server errorlog file. Please help me complete this task. Thank You, Dan...more >>

I had a table which was used in several views. "Show dependencies" used to work. But, a script was run that dropped the table, then recreated it (with the same name). Now, "Show dependencies" no longer shows the views that depend on this table. Yet, the views still work and still depend on...more >>

Hi all, I am attempting to run some totals on one of our tables. I am experiencing an issue totaling three types of records stored in the same table and identified by a number (either 0, 1 or 2) to total properly. The field is listed below as stype - it represents a one of three dif...more >>

Hy Guys... The SQL Server is not installed in my computer, but I want to install here the Query Analyzer... How can I do it???? ...more >>

Without getting into details about table structure, indexes, number of rows, etc. Which query do you think would run better on SQL Server. Or do you think that SQL Server will always use the EXACT same plan, since they are (or seem to me to be) the same query: Query1 ===== SELECT Users.Use...more >>

How can I find the number if business(Mon-Fri) days between two given dates?...more >>

Hi, I have a database for my custom search engine on my site. This search engine crawls and indexes my database every night to keep the search engine updated. Obviously the index table gets fairly large in such a scenario, so proper indexing of the table is a must to be able to get reasonab...more >>

I am using Microsoft Query to extract data from a SQL Server database, and return the results to Excel. Hope this is the right newsgroup. One of the tables in my database contains a text field. Some of the records in my table have HTML tags embedded in this text field. In other words, I mig...more >>

I've got a fairly simple UDF that returns a 1 row, 2 column table thusly: CREATE FUNCTION dbo.fn_NextAwardAmounts ( @ssn char(9) ) RETURNS @SemesterAmounts TABLE ( sem1 smallint, sem2 smallint ) AS ... This is working perfectly. I wa...more >>

I am using windows xp for my development. I want to insert some japanese words into the database. In the Enterprise Manager, I choose the server then select the table and trying to enter the japanese words. I can see the japanese words during typing in the table grid. but I can't view it again ...more >>

Hello. I am just starting to brush against SQL server and need to run a report from a user table with field names and the descriptions from the 'description' field in the 'columns' tab you see in teh query analyzer. I cannot find the table name where the description is stored. I have t...more >>

Hi guys, do you have any idea as to how can I convert an nvarchar field to numeric type? Is it possible? Thanks in advance =) Joey ...more >>

I have a field called UserNotes that a user can type in words or enter a number from 1 to 15 which represent a code held in another table. Here's the trick. I use SUBSTRING to check the first 2 digits to see if they contain numbers and if so I lookup the code that matches from another table. T...more >>

I've got this procedure I've pushed to production. It works on my development and UAT servers, but not in production. It calls xp_sendmail. xp_sendmail works fine for me on all three servers, except when I add the @query clause. When the @query clause is in there, it works in Dev and U...more >>

In the following, buyr_buserid works fine for getting the correct record. But, I don't want the record to be found unless buyr_bpassword is somehow included in the WHERE. buyr_bpassword is in Textbox22.Text. strSQL = "SELECT buyr_fname, " & _ " buyr_lname, " & _ " ...more >>

Hello friends, In my stored procedure i need to validate the input parameters before inserting a record into the table. The following code segment is how I am validating the input parameters [code] DECLARE @RetMsg VARCHAR(500) DECLARE @invalidParam VARCHAR(500) SET @invalidParam = '' ...more >>

I have the follosing table: CREATE TABLE [AZGS_Sweepstakes] ( [ReaderNo] [int] IDENTITY (1, 1) NOT NULL , [CardDate] [smalldatetime] NULL , [SweepEnter] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [las...more >>

Ive been asked to produce a daily report on how much monies any given person people has spent over a 30 day period from any given date if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestCalc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestCalc]...more >>

I know how to copy a single row within the same table. But how do I copy a single row that has over 10 columns and I just want one of the columns modified and rest stay the same? ...more >>

Hi, I would like to create a unique id based on one or more columns, concatenated with a count. For example, I might want to append the count to my SysDate column as so: Sysdate | Result 20040202 | 20040202_01 20040202 | 20040202_02 20040202 | 20040202_03 2004...more >>

hi How can i Reset the identity column to 0 through stored procedure? Thanks ...more >>

Hi, Another question, Purpose: right justify data before exporting datato text file. To trim data on each field I used CONVERT(VARCHAR (fieldlenght), fieldname). It seems to work fine for me. But I am a little concern that I did not use the LTRIM (RTRIM(fieldname)). What is the pitfall ...more >>

Hi, I have 7 pairs of tables with same data structure on each pair. I need to validate that data in both tables for each pair are identical. What is the best way to do this? I thinking of macthing data field by field, but that consume a lot of time. Please help and thanks in advance, ...more >>

I hope someone can help with this trigger?. I have a tblConnotes that gets updated with data from an external source and when a new row is inserted in tblConnotes I need to insert the value from tblConnotes.connotenum in tblOrders.despatchref only if tblOrders.despatchref is empty or null AN...more >>

Hi, Im trying to create a stored procedure - to which I will pass Start Date and a Finish Date. The sp should count the number of days from start to finish an return a temp table with those many columns ex - If I specify 01/01/2004 - 01/05/2004 My table should have columns Day1, Day2, Day3...more >>

I would like to create a tsql script that would check the database transaction log file size hourly.(xp_cmdshell) If the log file size is greater than 500 MB then run the script listed below to truncate the log. Please help me with this script. Thank You, Mike USE DATABASE GO ...more >>

Hello, I'm using a MS-SQL-Server 2000-Database and a application which uses ADO to access to the database. Now I tried to make it multiuser-capable. The aim is to detect, if another user wants to edit the same recordset. (In that case the database-engine should "lock" the recordset, an all ...more >>

I am returning multiple recordsets in some procedures. These end up being Table1, Table2... in the dataset. Is there a way to name these in SQL??? Thanks, Will...more >>

I am receiving the following error when trying to insert this statement 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value The statement has been terminated. INSERT INTO customer VALUES (2,'EASTMAN & CO. INS. BRKRS.','2138492406','4001 W. ALA...more >>

I'm using a Scheduled Job on SQL2000 to send Email Reminders to users. Essentially, on a specified date, the job runs and executes a stored procedure that gets together a list of email addresses and sends the reminder. I use a cursor to loop through the email addresses as each email is a little di...more >>

I'm querying a Key/Value table (Product_Id INT, Key VARCHAR(255), Value VARCHAR(255) NULL) and I want to return the Key/Value pairs in a single row for the specified Product_Id. What's the fastest way to do this Currently to get a key, I do something like SELECT Product_Id, Key, Value FROM Produc...more >>

I'm in the need to create a cross table identity, that is I'll have an integer field that has unique values across several tables. Much like regular Identity but the value can be assigned only once across tables. I know how to do it in both Oracle and Interbase. But do i do so in SqlServer?...more >>

Hi, Is it possible to directly refer to an aliased field within a sub-query? Example: ================= SELECT C.SomeID, (CASE WHEN SomeYear BETWEEN 2003-04-01 AND 2004-03-31 THEN 1 END) AS InFiscal, (SELECT SUM(SomeAmount) FROM SUB_TABLE WHERE SUB_TABLE.SomeID=C.SomeID AND InFiscal=1...more >>

How do I run a query that returns all of the names of the tables in a specified database Hope someone knows.... jax...more >>

Hello Forum I would like to know how could i make a procedure take in count that i have a form with 4 fields and the person that want to search anything can fill, 1, 2, 3, or 4 fields in the form.. What is the way to make this, better?.. Thank greetings Chwing Tang...more >>

This works: cmd.CommandText = "DELETE FROM TBL_RegBuyer " & _ "WHERE buyr_buserid = '" & TextBox1.Text & "'" But I need it to match both TextBox1 and TextBox2 before the delete action is taken. Any help is appreciated. Thanks, Trint .Net programmer trintsmith@hotmail....more >>

I am doing a bulk insert statement in a stored Procedure e.g. Insert into tblDest select * from tblStaging. However if the insert fails I need to know exactly which record caused the failure (i.e. the field contents) for auditing purposes Have you any idea how I would do that Many Thanks Gaye...more >>

What data type should be for a fields with values (0/1)or (Y/n). ...more >>

Hi Can anybody please tell me what's wrong with this statement insert into test(name) values(select name from test1 where id=5 Thanks JP...more >>

Hi, I have two tables.. Employees Department | EmployeeID | DeptI | Name | Name ...more >>

Is it possible to execute a dynamic SQL with in a user defined function, and return the result? Thanks in advance. Peddi ...more >>

hai, declare @askey char(16) declare @asno varchar(6) declare @asalfa varchar(10) set @asno = '2569' 1. ) set @asalfa = '' 2. ) -- set @asalfa = 'a' --set @askey = replicate(' ', 6 - len(@asno)) + @asno + replicate(' ', 10 - len(@asalfa)) + @asalfa -- print len(@askey) I want to ge...more >>

I am trying to write a stored procedure to delete records from two tables linked by a ledgerref field. From help in the past i came up with this TSQL query , which does the job . How can i convert this to a Stored Procedure. Would it be a simple case of doing 2 seperate delete statements...more >>

I have a sql Server 2000 application which converts csv files to database records. The application periodically checks an upload directory for the existence of any "*.csv" files and if one is found it reads that file (using FSO), parses the information and writes a record to the database (ADO). T...more >>

Is it possible to enable and disable a job by Tsql (like starting a job with sp_stop_job)?...more >>