Hi, I need to iterate over millions rows in a table and I need call an extended stored procedure (written in C++ and not possible be written in TSQL) using the columns of each row as parameters and write the return values to an new table. The current script open a cursor. What's the bes...more >>

I am using RecordBinding from VC++ successfully against a SQL server 2000 database. My issues revolve around getting datetimes from SQL into anything readable in VC++ as a date/double/ULARGE??? variable. Right now I can put this data into a double but the data is not able to be parsed. There does...more >>

I'm accessing a SQL Server 2000 stored procedure with ADO and I get the error message: "Operation not allowed when the object is closed" I know to add "SET NOCOUNT ON" to the procedure (that usually solves the problem) but in this case it didn't. Any suggestions as to other things to lo...more >>

Can some please help with this SQL problem: I have tables in select query and i want to show all dbo.TblTerritory.Description even is there is no data in the other columns, ie there are no records of type 'lost' in ProjectStatus SELECT TOP 100 PERCENT dbo.TblTerritory.Description, d...more >>

I'm trying to use sField as a variable for a field in the [Order Details] table in Northwind, but having trouble with syntax. Can anyone shed light on a solution? CODE: declare @orderID int declare @sField Char(40) set @orderID = '10248' set @sField='UnitPrice' "SELECT SUM(" + @sF...more >>

Hi, I have problem with SqlDependency and could not use any sample in VB.NET 2005. Some samples I found in internet, use Start mehod that I don't have in my SqlDependency instance. And some of them use Id and Servie properties of SqlNotificationRequest that VS IDE suggests using UserData instead...more >>

Hi, After following the Books Online instructions to setup a remote procedure call: "How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)" The 2 boxes are in same WORKGROUP and sa passwords are the same. I can execute SQLServer2.SP1 from SQLServer1 no...more >>

Stored procedures on our SQL Server often refer to tables in other databases. eg. create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1. We want to change things so that when a table in another database is referred to, it only happens in views and then the stored procedures refer to ...more >>

I am creating a stored procedure. I want to merge data from 3 tables. 1. schedule_rohan = contains hours available in the schedule according to day 2. appointments_rohan = contains appointments 3. patients_rohan = contains patients For example, I have the following as my select statement: ...more >>

I just heard about Reporting Services and it sounds like something I could use, but I can't find it anywhere. Is it a free download? If so where can I download it? Otherwise, where to I get it? http://www.microsoft.com/sql/technologies/reporting/default.mspx Thank you, -- Sean...more >>

Hi, I am desperately seeking for a query that I may issue against SQL Server2000- that will tell me whether a column is identiy or not!! Please help!! -- Thanks in advance, Juan Dent, M.Sc....more >>

In a script or stored procedure, you can change your database context with the USE statement. For example, USE pubs. How do I do that dynamically? For example, a stored procedure is executing in the pubs database. Based on a variable (either passed in or determined through logic), I need...more >>

Is there a way I can prevent a specific user from updating a specific row to a Table...? TIA ...more >>

I have a stored procedure disable and enable triggers on several tables. What would be a workaround to using the alter table with disable and enable triggers listed below. Thanks, CREATE PROCEDURE dbo.K_ManualMoves as BEGIN alter table T_Moves disable trigger iu_t_Moves alter table...more >>

Hi guys, Does anyone know how to extract the currency symbol and measurement unitsusing an ISQL query? For instance, in .NET I can very easily do the following -Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol and System.Globalization.RegionInfo.CurrentRegion.IsMetric Is...more >>

Given the following record set, how can I "Flag" the first payment record (Typecode = 'P') so that it has satisfied the "Fire_Fee_Recvd" In other words when a payment comes in, the billed fire fee amount is to be applied first (first in,first out) and when subsequent payments come in, I do ...more >>

Hello, I've a problem with inserting a record through a form in Access. The recordsource is a view from sql server. The view has a computed column. The problem occurs when there is an insert/update on that view. The errors indicates that arithabort needs to be set on, etc. In my ado-c...more >>

Hi, I have problem with joining. DDL: CREATE TABLE Items ( item_code INTEGER NOT NULL, item_description VARCHAR(50) NOT NULL, PRIMARY KEY (item_code) ); CREATE TABLE VAT_Groups ( vat_group CHAR(1) NOT NULL, vat_description CHAR(20) NOT NULL, PRIMARY KEY (vat_group) ); ...more >>

Hi All, I have an store procedure with a 100 and some lines, and I want to analize the result from another sp. How can I get the values to do a select statement? create procedure myPro As select * from orders bla... go create procedure analizemyPro As declare @temp varchar(100) ...more >>

I use OLE DB to call a series of stored procedures to add records to database tables within one transaction. I got the following errors: Description: Cannot create new connection because in manual or distributed transaction mode. Source Description: Microsoft OLE DB Provider for SQL Server ...more >>

When I run below code, I get Jun 29 1905 12:00AM. How can I get 2005 as the current year? CODE: SET @dtYear = DATEPART(yy,GetDate()) PRINT @dtYear ...more >>

Hello! I want to create an update trigger on table "A". What I want is to udpate the "status" and "submit_date" flds in table "A" if it meets the criteria. See below the udpate statment which works. I dont know how to change this code to work for Trigger?.How do I do that? I guess what I am ...more >>

I've searched books online and SQL manuals for this but didn't find anything, so it probably isn't possible. Is there a way to indirectly specify a column name in a query (put the column name in a variable, and use the variable in the select clause)? A simplified example: a table has columns...more >>

Hi, Once we create job job in SQL Server & if it fails then we can view the job history for the failure message. In what Database & table does this message get stored. Is it stored in MSDB or Master database? & in which table. Please help me with above. Thanks a lot Steve -- IT D...more >>

I have a table that may have one to many rows. I need to alert th users if one of the columns of this table does not contain the sam value for all rows. Say the column is 10 for the first row, 5 fo the second, and 10 the the remaining 3 rows. I just need to tel them that there is a difference...more >>

hi there, I am trying to do this, it would simply my sql so much, but the syntax won't parse. DECLARE @Tmp varchar --@Tmp will either be cow or pig after something done here, lets say pig for simplification.. SET @Tmp = 'pig' SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THE...more >>

Just looking for general feedback --- I've been asked to create a unique key for a table. The problem is, it's based on three rather wide attributes...2 varchar(50)'s and a smalldatetime. apparently this is the only combination wich comprises a unique entry. what do you guys think about thi...more >>

SQL 2000 Today few of our SQL jobs failed with 'Performing Completion Actions' and couldn't re-start . I tried stop/start SQL SERVER AGENT , but the agent is in Starting status and never came back. Finally I was forced to reboot the server. Any suggestions/thoughts will be highly appreci...more >>

Hello, The below code is intended to create a sp, then loop through a set of values, each time running the sp with the new value. The CREATE PROC executes fine, but I get an error when attempting the loop: "Invalid column name 'tblIR_LB_2'." Your help appreciated! CREATE PROC sp_Crea...more >>

I am writing a sql statement similiar to: select * from testtable where id in (select id from anothertable). The ID field in anothertable is int whereas the ID field in testtable is varchar(originally created by someone else). 99% of the data in the ID field of testtable is an INT, so I usua...more >>

I am trying to select for an insert. I have pasted the attempted code below that I have used for testing purposes. Query Analyzer will not accept the 'top' and a variable. I have tries Casting and converting even though i have declared the variable an integer. Declare @RowCount int Set @Ro...more >>

In a stored proc, I have a table that may have one to many rows. I need to alert the users is one of the column of this table does not contain the same value for all rows. Say the column is 10 for the first row, 5 for the second, and 10 the the remaining 3 rows. I just need to tell them tha...more >>

I need to copy some rows from a table in one server to a table in another server. The amount of data is not very large and nothing fancy happens to the data on the way. Does anyone have any advice on the reletive merits of DTS as opposed to just doing it in a stored procedure? regards ...more >>

Greetings, I'm working to combine rows based on a time window and I am hoping to be able to write a stored procedure to do this for me, rather than have parse through all this data in my program. I'm not very well versed with T-SQL syntax.. just enough to get by selecting using inner joins, up...more >>

Hi, I have a stored procedure which returns values from a table. I want to be able to pass it a parameter which it should ignore if it is 0 but include in the where clause if it is 1. ALTER PROCEDURE [dbo].[spGet_ExternalOrgs] @type [tinyint], @PartText [char](20), @ActiveOnly ...more >>

Thank you in advance I am trying to retrieve the number of row that BCP process upon completion,also how to find out what errors have occured when using the BCP command. thank you tomas...more >>

Hi! I'm facing a big problem. About 10 persons are working on a ssingle database that serve an application. The Search screen for "Reservation Module" (only), works fine and fast almost of the time, but sometimes, they get a Timeout Error. Indexes are there and with the same parameters, som...more >>

This is a realy basic sql outer join and I have no idea why it's not working. I have a table [FEATURE] that has 29 rows. [FEATURE] { idFeature int IDENTITY FeatureName varchar } I have another table [VEHICLEFEATURE] that lists which features are available to a specific vehicle (say ...more >>

I am a somewhat newbie to UDFs so don't laugh too hard! I have 2 tables: Tbl_Processes (first table) ID Process_Name Included_In_Test Tbl_Sub_Processes (second table) ID Sub_Process_Name Lookup_To_Tbl_Processes_For_Process (points to the ID field of the ...more >>

This is baffling me, I can use QA to run the Update statement below but if I run it in a sProc, it doesn't affect any records! Any ideas? UPDATE BK_vht_lu_vehicle SET vehicle_desc = substring(c.VEHICLE_desc,1,6) + veh_group + substring(c.VEHICLE_desc,9,31) -- select distinct Po_risk_desc...more >>

Hello, I've created a sp... how can i put the results into a varible or sql statement? Is it possble? Thanks, Brian...more >>

In myTable i've got a trigger for INSERT, UPDATE, DELETE I Update myField1 of myTable in the trigger but not in the query that starts the trigger. Then i check if UPDATE(myField1). This is true or false? CREATE TRIGGER tr_MyTable ON dbo.MyTable FOR INSERT, UPDATE, DELETE AS .... UPD...more >>

hi..I am running DBLIB (sample application of SQL Server) between 2k and 2k3. Well seems working perfect..but want to know whats going on in the Database..I think some DB configuration (like DB connection or Table creation) is required to see the effect of executed/commited transactions..need s...more >>

Hi When using sybase EA server I can utilise the following code to inform me of what a particular Spid is executing select s.stmtnum, s.linenum, o.name from sysobjects o, master..sysprocesses s where s.id = o.id and s.spid = 77 is there anything I can use in sqlserver to give me the s...more >>

Hi all, I am trying to format some dates using datapart, my date format looks like 2005-06-13 00:00:00.000, I want the year and the month together to look like 200506, but the datepart's and conversts I have tried keep giving me 20056, is there a way to add the '0' where it is needed. Th...more >>

I am attempting to write a stored procedure to allow a user to search on any field or combination of fields. I am passing the user input values as parameters into a stored procedure, if no value is entered then the stored procedures defaults the values to NULL. My current effort uses case st...more >>

I'm using the following query: SELECT B.name AS BranchName, (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND a.name = "homecover") AS AddOn1, (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND a.name = "policyfee") AS AddOn2, (SELECT a.cost from data_Bra...more >>

On our SQL Server, the databases reference each other quite often. eg. UPDATE SalesDatabase..DailySales SET Flag = 1. We are planning to change that so that all references to other databases are done through views, because we want to abstract and centralise all the cross-database references. ...more >>

I need some help to do the following from a stock system. I have an SQL view that extract my stock items and outstanding quantities. I then need to compare these to the quantities on hand... the problem being that multiple lines (on the orders) contain the same item no, so it extracts all thi...more >>

Hi, how can I convert a pivot table in Access to SQL Server. Access SQL example: TRANSFORM Sum(AM.TOTAL) AS SommaOfTOTAL SELECT AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA FROM AM GROUP BY AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA PIVOT AM.YEAR; Thanks!!!...more >>

Hi, I created a database with an additional file group (FG2). Then created two tables: one on primary and the other on FG2. FG2 is located on my flash memory(USB) and when I disconnect the memory, the table is still queried and I can insert new records, while DBCC CHECKDB reports error. How ...more >>

Hi there, I was trying to retrieve all LineItems from the following query. However, if I execute the following query, it only returns the First item. How could I modify the OPENXML synatax so it will also return the Second item and Third item in one select statement like this: VINET 19...more >>

Hi all, I have three tables: Table 1: Amount InvoiceNumber Table 2: TransactionID InvoiceNumber Table 3: Amount TransactionID InvoiceNumber Client I need to compare the amounts from table 1 and table 3. However I need to do this by client. This is where the problem arises....more >>