I want to get output of a stored procedure in a select statement with other columns. how to do that....more >>

I have a SP that removes the foreign keys, then truncates some files and then adds back the keys. Why do this? For example: ********************************************************************************** ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesCustomer ALTER TABLE dbo.Sales...more >>

I have sql statement in a column like thi select 'select * from table is there any possible way to run all statements like this (in a column) to exec at run time and return me the single value in the respective column ...more >>

Performance speaking - Is there any difference in opening up a recordset using a Connection object opposed to a CommandText object? Thanks, Chris ...more >>

I am trying to execute an update command in Enterprise Manager. I am trying to update a varchar(2000) column in a SQL Server table with a column from an Access database table that is set up as a linked server. This is the SQL statement: UPDATE MDSAssessment SET ExportString = CAST(MDS...more >>

Here's how install my mdf and ldf database files from the command line: osql sp_attach_db @dbname='myDatabase', @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDatabase.mdf', @filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDatabase.ldf' Any suggestion on how ...more >>

Is it possible to conditional select fields in SQL Server? I've built a Stored Procedure that returns data from a table based on a passed parameter. For example, I have built something similar to the following ... IF @parameter = 0 SELECT au_id, au_lname, au_fname FROM pubs..authors Els...more >>

Hi there, I have an application that INSERT new records to a database at a rate of 5 items per second (for a period of 10 minutes each hour). The new data arriving to the database should be verified and cross-checked with a different table (a 2-3 seconds process for each new record). Is i...more >>

Hi, I'm a bit of a rookie at this I've created many stored procedures for my new database. Now I would like to list all of the names and parameters in a file. Is there an easy way to do this?...more >>

Hello All, I have just read "E Som\mars\kog" text on error handling and found it very useful. Can anyone recommend a URL explaining the use of error handling within Job Steps, especially to force the step to Fail, to allow flow control of the steps. I have used DTS but find it unsatisfactory...more >>

Hi all Is Set RowCount effect by per connection or entire database If I use Set RowCount to 200 in my long procedures, will that cause any problem for other users in the same database ...more >>

How do I "capture" the output of a stored procedure if it is run by SQL agent. Basically I want to redirect the result set returned into a text file to ftp it. E.g. declare @header as varchar (256) set @header = '*DATABASE:MS_DATA.MDB' + char(13)+char(10) + '*TABLE:MS_Customers'++ ch...more >>

Hello everybody, I use ORACLE with PROC When I execute this request: EXEC SQL CREATE TABLE SC_PAY_TMP1 AS SELECT * FROM SC_PAYMENTS WHERE auth_date <= to_date(:sqltcDATEFIN,'DD/MM/YYYY hh24:mi:ss') AND auth_date >= to_date(:sqltcDATEDEBUT,'DD/MM/YYYY hh24:mi:ss')...more >>

SQL 2000 I need to know if a tmp table ( #MYTMPTABLE ) ecist in the same session I am working or no ! I tried to use information_schema.tables and sysobjects, but it is not there ...... How can I check it out? Thanks in advance, Patrick ...more >>

Hi We have a table with a compound primary key and are trying to use the following commad to insert some values in to it insert into bigtable (field_a,field_b,field_c) select field_a,field_b,field_c from smallertabl (The PK is made up of field_a and field_B The command give a "Msg 2627 Viola...more >>

Hi All, After renaming my system, Sql Server name also changed. Profiler Title bar displays the old name. When I checked master..sysservers, the new system name is not updated. I am unable to update that table. It throws an error "Ad hoc updates are to system catalogs are not enabled" How do...more >>

I was wondering if there is a direct way to take the result set returned by a SP call, and have it insert into a table. If you know the table schema of the SP result set, and create a temp table, in advance, it seems reasonable and desirable. I can get the same thing done with a cursor, but I w...more >>

can anyone comment style param produced by this: IF OBJECT_ID('MyTable') IS NOT NULL DROP TABLE MyTable GO CREATE TABLE MyTable ( CD DATETIME NOT NULL DEFAULT { fn CURRENT_DATE() } ) GO SELECT COLUMN_NAME , COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WH...more >>

Greetings: Here's the TSQL I am attempting to execute: ALTER DATABASE EBroadcastTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE EBroadcastTest FROM DISK = 'E:\BackupDump\' WITH MEDIANAME = 'EbroadSANDmpWed.bak' , REPL...more >>

I'm planning to use SMTP mail in SQL Server as outlined in: Microsoft Knowledge Base Article - 312839 I can make the mail work, but I have two issues that I cannot figure out (sorry, I'm new in this environment). 1. How can I include a 40K file in the HTMLBody (the output of a sp_makewebtas...more >>

How can I use the output from a stored procedure in a cursor? I need to do something like this... Declare myCur Cursor for sp_myprocedure param1, param2 open myCur detch next from MyCur into .... Can anyone help me? Thanks, -Adam...more >>

How can I write this stored procedure so that I'm not repeating the entire SELECT statement for both IF conditions: IF @arg1 = 'Y' THEN SELECT * FROM mytable WHERE a = b AND c = d AND f = g ELSE SELECT * FROM mytable WHERE a = b AND c = d AND f = g AND h = i Thanks....more >>

Hi, Can anyone help me out here, i get: "Syntax error converting datetime from character string." from the following: alter PROCEDURE spCMS_DomainsList @intDays int, @intUserID varchar(20), @strName varchar(256) AS Declare @Date Datetime, @sql varchar(1500) Set @Date = getdate() S...more >>

Hi, I have a field in my table of type INT. I want a query to return the average of that field based on a criteria though it's not returning the decimal. Any ideas how to get this to return the decimal. thanks rafael ...more >>

Hi, I'd normally keep playing with it until I got it, but the deadline is fast approaching. How would you convert any date to a form of MMYY in a 4 character period field. I'm trying to stuff a '0', but it replaces the 4 on the month side. I got the year just fine. Thanks! ...more >>

Hi, I pass a string from the htm page multi-select form like 'California,San Francisco,New York,New York City,Texas, 0,Georgia,Atlanta' The string has at least a pair of state and city. Users can select up to max six state and city. If city's value is 0, select all cities. My question is how t...more >>

Hi - I have a table with a composite index of id and datetime. How can I select the last row of this group? In other words the one with the most recent date with a given ID? Also if I want to update this row how would the sql be? Thanks in advance....more >>

I have two SPs A and B, with A calling B. What I want is for A to stop processing if the call to B fails so I have code in A like EXEC dbo.B @P1, @P2 IF @@ERROR<> 0 BEGIN SET @Msg = 'B Failed' RAISERROR @Msg, 16, 1 END The problem is that B is raising it's error (same as a...more >>

Hi I wanted to know that how Transaction work DDL statements Regards Mr.Bug ...more >>

(sorry if this comes up as a double post but I forgot to put a subject line teh first time) I have a diary table, which is used for inputting a schedule of intended works. When an item is inserted into the table it is given a status of 'scheduled', a version number (starting at 1) and a unique c...more >>

I got a problem with Bulk Insert lately in one of our clients environment. Basically, I have a stored procedure that creates a temporary table, and then use the BULK INSERT INTO.. statement to populate the temp. table with data records from a text file. This simple stored procedure running e...more >>

I would like to create a TSQL script, if a table indexes is less than 80% organized they will be reorganized. Please help me create this script. Thanks, Dan ...more >>

I have an SP that calls another SP. Is there a way to suppress the output of the secondary SP in the primary window through code Thanks in advance Offey...more >>

This up date query is failing on the "inner join" syntax. Does anyone know what syntax I should be using? UPDATE proddta.F4101 INNER JOIN proddta.F4106 ON proddta.F4101.IMITM = proddta.F4106.BPITM SET proddta.F4106.BPEFTJ = 104136 WHERE proddta.F4106.BPEFTJ =104121 AND proddta.F4101.IMSRP1)<>...more >>

I have a stored procedure which returns rows of data, I'm trying to present the data using Crystal reports. When I point crystal to the stored procedure I get an error message from crystal, when I've investigated futher what Crystal sends to the database is a SET FMTONLY ON, it then runs the s...more >>

Well, no one in "server" had anything to suggest, maybe you guys have seen this: We've got an installation of MS SQL 2k on a dual-CPU box. System has about 100G free on the drive hosting the database, 50G free on the log drive, and 30G free on the OS host drive. Of the 2G memory, task manage...more >>

How do I bcp data and a header row out to a delimited flat file when the header has more columns than the data without adding extra blank columns to the data? thanks ...more >>

I have the following 3 tables Table A Account ID AccountDescr 1 Acme Account 2 Promoz Account 3 Test Account 4 BNZ Account 5 Another Account Table B Account ID MonthlyVal 1 ...more >>

Hello, I want to store some information for a certain period of time (around 20 min.) and I am very interested in performance. I would like to know the advantages and disadvantages of using temporary tables instead of persistent tables. Thanks, Ivan....more >>

Hi - I know this has been posted before, but I can't find a solution to my problem. I have two tables: one called Graph with data for a graph in it. Each row has a location name against it, some (but not all) of which I want to replace with a new location name. I am trying the following: ...more >>

Which is the best way to run one store procedure, in an interval of time, example every 20 min??? Can be made with a job??? Thanks in advance Alejandro Carnero. ...more >>

I need to join a query to another table where there is not going to be a one to one relationship to make the join. The other table has a date column and a customer number. The date column needs to be used in a where clause in my query. I have used select top 1 subqueries in my select statement...more >>

Hello, Is there a quick and easy way of returning the server name ? Thanks for your time Peter...more >>

Hi, I have two tables one for colors and another one for sizes and I need to write a query that would give me all the combinations of sizes and colors for a specific product. Here is how my tables look. SIZES COLORS ________________________ _________...more >>

Hi, another question to the NG (even if the first one was left unanswered). I have to run a stored procedure which gets 40 parameters (needed for encapsulating some business logic without giving access to the related tables). Based on some parameters, different actions have to be processed...more >>

I would appreciate if any one can provide me the syntax of calling a DB2 Stored Procedure with an input parameter here is my code Declare @VAR1 as char(8 SET @VAR1='anything Exec [RECSTDB1LINK]..ES1USP.SAMPLE1 @VAR getting error.. Please help!!! ...more >>

Hi all: Hope you can shed some light on this. we have a Vb6 app connecting to a slq server database using ado 2.6. when each client is started they initialize a connection object which is kept open for the life cycle of the client (i know this not recommended but it is an inherited problem)...more >>

I am sure I've seen a way to do this using a query but I cannot recall how to do it and my attempts so far are not working. I have 3 tables, Teachers, Students, Evaluations. A teacher logs in, selects a student to evaluate and when they save that data, a record is added to the Evaluations tabl...more >>

Can any body provide me with some help on @errorlevel in Microsoft SQL Procedures.Please mail the infomation to my id. mailgoks@rediffmail.com gokuldas@assyst-international.com Regards, Gokul ...more >>

I am trying to disable all FK constraints in my database (so that I can truncate all tables and do a fresh data load). For some reason, the constraints are not disabled after I run this /***Disable all constraints*** SET NOCOUNT O SET ROWCOUNT DECLARE @Count in DECLARE @String nvarchar (1000...more >>

Hi anyone with the time to help! Any guidance / help would be appreciated with this stored procedure I have been away for so long and my T-Sql is so bad now! I have 5 Tables Table A is one side Table B is many side to table A by TableA.PartNumber Table C is a table to update with PartNum...more >>

Greetings I have created this stored procedure where i am getting a value passed to it from an ASP page CREATE PROCEDURE [AddPurchase_SP] @VehicleId int @Item varchar(255) @Recipient varchar(50) @PurchasePrice mone A Insert Into Purchases (VehicleId, Item, Recipient, Purc...more >>

I have 3 text columns (send_to, send_cc, send_bcc) in a table = dbo.newemail. Each of those tables is a text data type but is limited = to 8000 characters via the application. The data in those columns can = look like this... joe smith; bob; jimbob@site.com; bob smith <bob.smith@site2.com>; ...more >>

how would I go about replicateing a table across two databases? just a single table on the same local server. Also, are there any disavantages to replication? besides possible delays between changes and when they show up in the replicated database ...more >>