Hi, Can we use Loops in Stored Procedure of SQL Server 7.0. If we can then How to Excess the results of a Query Which returns more than one record? Reply Soon Thanks...more >>

I am having trouble displaying unicode characters in a stored procedure. Is there anything I need to set to output unicode output? I am trying to display chinese text, but when I output it, I get question marks in the place of chinese text (?). Thanks. -- Lucas Tam (REMOVEnntp@roge...more >>

I have a set of database which is imported/duplicated from production box. That means all objects are inherited from production box including the data. This is the problem situation. We have a client application that keep receiving data from server application and instantly update the data int...more >>

Has anyone noticed that when running a trace in profiler, the TransactionID is never filled in. Is this dependant on the events that are captured and does it only show up for certain events?...more >>

Because my only real design experience is with Access, I don't know how powerful SQL Server (2k) can be and the best way to design for it. Moving from Access because table of historical data is simply getting too large. I will have to keep history on an individual potentially for years (for...more >>

I need to convert a time of 0020 to 12:20AM and "attach" that to a date in a separate field. Does anyone have a quick and easy way to do that? Thanks Scott ...more >>

Hi, I have a question about the performance of 2 queries (used in VB6 [DataEnvironment] application / CTI integration) When a call arrives I look up the phonenumber in an sql server 2000 SP3 database table with about 1500-3000 records (3 fields: PrimKey,CompanyName,Tel) This has to happ...more >>

Hi, Considering the fact that sp_monitor returns more than one result sets, How can I have the output of sp_monitor in a table(s) or memory variables? Thanks, Alan ...more >>

Having some trouble picking out some bad data caused by bug in 3rd party app. Row 1 is Ok Row 2-3 should have their Allocated = Y Row 4-5 should not even be here location Serial DateCreate DateModified All InvId 100109 205-02-12 2004-10-20 2004-10...more >>

Hi, I am looking to design a trigger on a table so when inserts, updates and deletes happen it goes out to another database and checks a table to see if these records (update, insert, and delete results) exists and updates accordingly. Basically, I have a business database that I have extende...more >>

Hello, I need to truncate each entry in a column of strings to leave the characters which are to the left of the first instance of a particular character. Can anyone advise? Thanks Soc. ...more >>

Hi; My company is finally making the move to program in ASP.NET for our web application stuff. We need to purchase a suite of components. Can anyne recommend a very good component library. We are looking for things like GRID, CHARTS, VALIDATION TOOLS, etc. Thanks in advance for your help...more >>

Hi and thk for your help ;-) I'm writing a stored procedure for bulk inserts.The sp have 2 parameters: @xmlOrders nText, @var_id int I have this xml (@xmlOrders ) : <ORDER> <ORDER> <art_desc>blablablabla.</art_desc> <art_code>1</art_code> <art_units>50</art_units> ...more >>

I just try to use Visual Studio 2003 with Walkthought book. I tried add new connection on server explorer. But I can not get list of Pubs and NothrWind. I already installed the MSDE. Why the sample database will not show on the list? Do I need to install a SQL server? Or MSDE is good enough. ...more >>

The log file grew to the point that it filled up the drive. I detached the database and deleted the log file. While reattaching via Enterprise Manager the Error 1813- Could not open new database "db name" then the path. Anu ideas on howto attach the db without the log file. ...more >>

I have a table containing: AccountNumber, Created, Email1, Email2 What is the best way to get the: Max created date, and the associated email1, and email2 per AccountNumber? Thanks Justin ...more >>

Hi, I have the following SP, the parameter is a string of comma delimited product ids. the [product id] filed is numeric in DDL. when I run it I got error: Error converting data type varchar to numeric. how can I use it in the SP? Thanks CREATE procedure GetProductByID @ProductByID var...more >>

I'm trying to add a couple HTML tags and text verbiage to a TEXT field. I have the below UPDATE statement that's not working. I get this error... --------------------- Server: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals add, type equals text. ----------...more >>

Here is my problem. I have two tables Docs (DocID, DateCreated) and Details(ID, DocID, ItemID). Each document has crtain number of items. I need result set with DocID as input parameter, that retuns list of all documents that depends by items in document. Here is DDL: --BEGIN DDL CREATE TAB...more >>

I have sp ALTER proc spInsertApplfromXML @fieldn varchar(50), @value varchar(8000) as SET ANSI_NULLS ON declare @datatype char(25), @sql char(4000), @into char(4000), @row_id int, @where char(20) --@split int, @value2 varchar(3800) -- get new row to update select ...more >>

Hi, I have a simple query that is in a stored procedure but I want to check if that query is valid. i.e. some queries may be written such taht the table doesn't exist or columns that don't exist. How can this be achieved? thanks for the help, Shahid ...more >>

I know text fields aren't available in the INSERTED table, but here's something I did to workaround and it seems to work. Please let me know if you see something grossly wrong or dangerous about this: Basically, what I'm doing is JOINing the INSERTED table to the real table via the primary ke...more >>

Hi! I am trying to write a query that will me the results, if the value of column "a" contains 2 dahes in it. Please see the DDL below: create table #test (a varchar(20)) insert into #test values ('th_ree_dashe_s') insert into #test values ('two_dashe_s') insert into #test values ('one...more >>

Hello I m using a stored procedure and hoping there is a better way to do temp files than what i am doing...any suggestions would greatly be appreciated SELECT * INTO tempTable FROM MainTable WHERE (CustomerID = @localCustomerID) I do this so that in joins I use later on I could...more >>

Hi, I'm getting strange deadlock situation here: I have simple stored procedure which updates one table. Using sql server profiler, I noticed that this stored procedure gets into deadlock with itself, when being executed from diferrent connections. I identified table and index (always the s...more >>

Hi, I am looking for information regarding data transformation from text file to related multiple table in sql server. As i have done text file to sql server for row to row or column to column tranformation but donot know how can i do this in case of related table (parent - chile) with multip...more >>

I have come across an interesting situation. I have a form that allows a user to search records based on a value range (i.e 100-150) The values from the ASP form are passed as input parameters to the stored procedure BETWEEN clause. However, if the user entered the range backwards (i.e 150-...more >>

I have a stored proc that accepts a comma delimited list of values. These values are based on a status: 1,2,3,4,5 etc. One value can be passed or up to 5 can be passed in and I need to propogate changes to a table. Right now I am looping and breaking out the value and doing the update. ...more >>

Hi, First of all, can dynamic sql be used for a cursor on SQL Server? If so, can you please give me the syntax? I need to use a select statement for the cursor in a stored procedure in which the database and table names for From clause are sent in as parameters. Thanks....more >>

I'm very new to this, so please bear with me. I need to count but only where a field is equal to something. For example: Count(*) as '#_of_Charges' Where [chind]='CH' Should I be using Case?...more >>

I'm very new to SQL Server. I'm trying to Count, but want to do so where the field is equal to something. i.e. Count (*) as '#_of_Charges' But I want to say where [chind]=CH Do I need to use case?...more >>

I'm given two tables containing data. I didn't write the tables, nor do I have the option to modify them. (In other words, Celko, no thank you please) I've been asked to create some sql code to link the two tables (easy enough) and then to update the 'container' table's FactoryID column to m...more >>

SQL2000 i have a table containing a datetime field and im trying to get a count of entries for each month and year i can get a count on just the months by using Select count(*) from Table1 group by Month(field1) how can i expand this to show the format below ...more >>

Hello, I have a SQL Server stored proc that does a BEGIN DISTRIBUTED TRANS. It uses a cursor to cycle through a SQL table and for each row in the table, perform some combination of INSERT, UPDATE or DELETE commands against an Oracle database. The procedure works very well...tables in both ...more >>

Hello, I have a process that's encapsulated in a stored procedure that itself calls 4 others. What I would like is for nothing to commit until every stored procedure is successful. if any of the nested procs fail, I would like to roll back all the previously succeeded actions. scroll down fo...more >>

I need a field that will hold a bunch of text. I tried TEXT and also NTEXT without success. I'm using MS Access as a front-end. Currently, I'm trying to paste 2000 rows and 140,000 characters. I know I will have more though. My references say I should be able to hold 2GB+. I'm getting an erro...more >>

#1 When I run my Stored Procedure it takes about 4 hours to run, when I run the exact same script via TSQL it takes about 45 minutes. Any ideas of why this is happening? #2 On the same not my script is about 1200 lines long, is this to long for a stored proceure? Do I need to break it u...more >>

Hi, I need to extract data filters depending on the results of a query. Example: declare @tmp table (v int) insert into @tmp (v) values (1) insert into @tmp (v) values (2) insert into @tmp (v) values (2) insert into @tmp (v) values (2) insert into @tmp (v) values (5) insert into @tmp (v...more >>

I have a very simple stored procedure select * from table1 join table2 on table2.uid = table1.uid join table 3 on tabe3.uid = table1.uid where table1.poc = @pocInfo order by table1.name This procedure works from SQL Query Analyzer, however, when called from an ASP page, I get the...more >>

I am trying to set up Transactional Replication and here is the configuration. Server1 - Publisher Server2 - Dist Server3 - Subs Since the database was huge so I ended up making a backup from Publisher and restored it on Subsriber. When I was setting up Publication using EM, I Set up Arti...more >>

I've created a linked server named ACCESS in my local instance of SQL2000. This works: select * from openquery(ACCESS, 'select * from accounts') Now, I would like to insert all records from the SQL view 'Accounts_View' into the corresponding Access table 'Accounts' What would the approp...more >>

Hi, Here's my problem. When I do a left outer join, and the result is a bit field 0/1 (TRUE/FALSE), and the right side of the join isn't found, it will return a null, not a zero/false. How do I create the join to default to 0/FALSE if the right side of the join isn't found? SELECT t.some...more >>

Is there a way to do a select statement of a time that is GMT and adjust for your time zone. We are Central so I believe we are -6. The field type is datetime. This is an example of the data. DatTim: 2004-10-20 22:16:42.883 Select DatTim from Table -- but time adjusted for my time zone ...more >>

hi, I have a field datetime type, when I query it, I got "2004-01-29 00:00:00.000" big string, I only care the date. how can I get "2004-1-29"? Thanks...more >>

I'd like to create a more generic version of a procedure that updates data into similarly named columns in which the names differ by a number that represents the day of the week. Is there a way in t-sql to pass in that number and have the variables pick it up? (The day number is calculated on ...more >>

Hello everyone, In general I can do basic SQL query, but this time I need help to accomplish what I want. I am trying to combine these 2 queries into one. I am having problem since the WebSets.DefaultURS is an optional field, so could be NULL and no name will be available.... But I need to ...more >>

I have this statement to retrieve unique records with a maximum date value for each 'vinNumber'. It seem to work fine, but... If you noticed there are some records with same 'vinNumber' and same 'dateCreated'. How do I make it to show only one record for each vinNumber? even though dates and 'v...more >>

I have 2 tables, emp and temp_emp. emp has following fields : id,name,info tmp_emp has : id,info I want to update emp.info using tmp_emp.info where emp.id = temp_emp.id I tried - update emp S,temp_emp T SET S.info= T.info where S.id=T.id , but it doesnt work, can someone advise...more >>

I have data in the format below. I would like to create an Stored procedure or view to create the data in the format below. Please see examples. Does anyone know how I could do this. The example below is simple, but shows what I am trying to accomplish. I can do this when I import to Excel a...more >>

Hi, I want to know how you can write a sql query or something that takes the content from every record in a table to a sql script that you can distribute and let the enduser run that then populates the database. Any code available? TIA Kenneth P ...more >>

Hi...does SQL do something like this automatcially? If I have a record...and I want to track the version of the record each time there is a change to it can sql do that instead of having to do what I do below?...which is creating a new record on each save i.e. primaryid = 1001 DetailReco...more >>

I've just heard something, the gist of which being that using 'CREATE STATISTICS' and 'UPDATE STATISTICS' can cause SQL server to generate some sort of B-Tree on my tables and can greatly improve performance. Can it be used in addition to indexes / clustered primary keys on the tables, and if...more >>

I am bulding dynamic SQL statements and am running into syntax issues when trying to use a literal string in a statement. In the following code snippet, the word 'Unknown' is supposed to appear as a literal string with single quotes around it it: SET @SOURCECOLUMNS = @SOURCECOLUMNS + ',' ...more >>

There are multiple processes running at the same time and in rare cases when load is very hight i am getting following error: Error opening [DATASOURCES]; Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the ...more >>

If I do select a, b, sum(c) as c into mytable from table1 create /*nonunique*/ clustered index idx1 on mytable(a,b,c) I understand that what the clustered index will do is physically order the rows according to their key in the clustered index. Therefore, is it a performance benefit to d...more >>

Currently I am using sp_helpsrvrolemember to retrieve all of the users and their appropriate roles. Then I use a filter to grab an individual user. I am wondering if there is something I am missing. I am looking for (preferably) a sproc that takes a Windows/Sql Login and returns all of the ...more >>

Hi all How can i compare two rows with eachother? Is there a simple way or do i have to compare each field in a row with the field of the other row? thanks for the help m.ahrens ...more >>

Is there any way to cause SQL server to show the execution plan for a query, without actually executing it? ...more >>