Hi, I have a BIG problem, I'm trying to run sp_OACreate from a sp and keep getting "Access Denied", this works just fine on my dev.machine but on the production server it doesn't work at all. I've read all the papers I can get my hands on and have gone through all the permissions but still mi...more >>

Hello, I have a window-less VB application running as a SQL server job. The application is very simple and it just picks up the blob file from filesystem and inserts into the database. I am using SQL Server 2000 running on Windows server 2003 standard edition. Most of the time my ...more >>

I was wondering if it is possible to have an UPDATE satement inside a SELECT statment. What I want to do is select certian rows from a table then based on the result I want to update another table. Metaphorically something like the following SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[...more >>

I'm attempting to refer to an alias in my SELECT clause (within a stored procedure). Basically I'm building a string which I will eventually execute by calling the "exec" statement on my string. Within my string, I have 3 columns for my SELECT clause. Here is an extremely watered down e...more >>

Does any one know how to print a SRS report in PDF format from th command line? I know that I have to use the rs.exe utility and that' about it. Any help would be much appericated -- bansal ----------------------------------------------------------------------- bansalh's Profile: http://www...more >>

How do I see the current value of QUOTED_IDENTIFIER in a SQL2000 database? Thank you....more >>

Given a column name what's the best/fastest way to find all instances of the column name and the table that each belongs to? TIA </joel> ...more >>

I have a stored procedure that contains a series of bcp calls to export data. The bcp calls are executed by xp_cmdshell and are all the same (other than the table name of course). I have SET NOCOUNT ON as the first line in the stored procedure and it is not returning any data. The bcp cal...more >>

Does anybody know of any good rss feeds on SQL Server? Any advice would be really appreciated. Cheers, Mike *** Sent via Developersdex http://www.developersdex.com ***...more >>

I have never had to do this before, but is it possible to pass in a parameter to a stored procedure that is concatenate? for example exec VaultResources.dbo.usp_UTIL_GrantPermission 'vw_ACAPSSOL_' + @YYYYMM Or do I need to create a separate variable and do the concatenate before calling t...more >>

I believe in avoiding triggers whenever possible. However, in the circumstance that a trigger is the "right" solution, I'd be curious how people document/diagram triggers so that a successor could actually find them aside from just having a document that says "by the way, there are some tri...more >>

I have a 2 systems that we store employee data in, one for the time clock and another for our payroll systems. We would like to update information on only our payroll system and have that update the time clock database. They are both SQL databases and on the same server (but different databa...more >>

Hi, Is there a way to get a list of all the current named transactions? Bryan ...more >>

This returns 2 random records for me. However, on occasion, the 2 records returned are identical. Does anybody have any thought for preventing this from happening? SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories C WHERE P.Image1<>'' AND C.ProductID = P.ProductID ...more >>

Hi, I'm trying to decide whether to keep some functions in VB 6.0 or develop them as SQL stored procedures; such as the "isnull" function in VB. If I developed an equivalent function as an SQL stored procedure, do you think system performance would be enhanced? Thanks! -- Christy W...more >>

I have a table already created and need to add an autonumber/ unique identifier field. If the table already contains records can I just add the new autonumber field and run a query to populate this field for all records with unique values? how do I do this? are their other ways of doing t...more >>

In SQL 2000, I know that "Display Dependencies" (and probably the Sysdepends table) are not accurate. Does the syscomments field for a view always contain the correct and current view definition? I need to find all uses of a given field across all views (there are about 150 views). Is...more >>

SELECT [Pcode], avg(convert(float,[Seq])) FROM [DB].[dbo].[PartyTime] WHERE [Seq] IN (select top 8 [PartyTime].[Seq] from [PartyTime] JOIN [PartyTime] [PT] ON [PartyTime].[Pcode] = [PT].[Pcode] AND [PartyTime].[Seq] = [PT].[Seq] ...more >>

Sorry for the lack of DDL. I'm hoping someone can help me without it but will post DDL if requested. Given three tables with identical structure (not my design): Table1, Table2, and Table3 . Each table has a compound primary key with the columns A, B, C I'm trying to build a SELECT that will...more >>

I'd like to calculate the percent of the total for column DataValue. So if the sum of column DataValue is 200 and the values for record A is 25 I'd like to return 12.5% Based on my sample data I'd like to return: A,12.5 B,25 C,12.5 D,50 CREATE TABLE TEST (ID varchar(1),DataValue int) ...more >>

Hi, I need to display only records from one table(t1) that don't have records in another table(t2). In other words these two tables... table1 table2 itemID | sectionID subItemID | itemID | sectionID | showID ------------------------ ...more >>

Guys, can anybody remind the settings responsible for the results of the search by the NULL fields? I saw this database setting several months ago but forgot this switch. The problem is the following. We're trying to run a search in a few tables using some SQL query with a multilevel combinati...more >>

Let's say I have the following table LoanId int FileName varchar(50) DateCreated datetime that's a history table of all of the files associated with a loan. It may contain multiple records for each 'filename', i.e. 1, 'FileName1', 4/24/2005 12:00:00 AM 1, 'FileName1', 4/25/2005 12:00:00...more >>

Hi all, Any suggestions on the best way to pass a set of values to a stored procedure that will use them in a query? Specifically, I want to call a stored procedure from .NET that takes a set of phone numbers and returns a resultset of records containing these numbers from a table. Is it ...more >>

I was doing a code review and found code that was similar to the code below. Bad style/practice aside, I was surprised that it ran without error and did not give "expected" results. Expected meaning five one-row ouptuts. This is using SQL Server 2000, can anyone tell me if 2005 will act the sam...more >>

if i have the following table: create table owner.mytable ( identitykey int IDENTITY (1, 1) NOT NULL, description varchar (50) NULL ) and the following stored procedure: create procedure owner.myprocedure ( @description varchar(50), @identitykey int output) as insert into o...more >>

I'm getting this error and I don't know where to begin. Our environment is SQL Server 2000 sp3a on Win Server 2003. I get the error when running an insert statement in a sql job that executes as the admin. The insert is a simple one from one table to another. Ultimately, I need to inse...more >>

I'd like to write an SP with parameters that returns specified groups rows in ordered groups of arbitrary size. Kind of a "sliding" TOP. Like, show me the first 40 items in a list, show me the 4th group of 40 items in a list, show me the Nth group of X items, etc. I see this kind of thing all the...more >>

What is the stored proc that can call an external exe, etc. from a stored procedure, function, trigger, etc.? I can not for the life of me remember what it is. I need to an action upon row inserts and wanted to have a trigger run an external program. thanks, ...more >>

Hi All, I have defined a function and want to call the same in a stored procedure. The function call is giving me syntax error! Here is the function call. function_name(@param) What is the correct syntax to call the function? kd...more >>

Hi, Not sure if I have posted this is the right place, if anyone could point me in the direction if I'm wrong I'd be grateful. Anyway, heres my query, I have a SQL query which is searching records where a date has past, and it works fine in SQL query analyser. Heres an example of the query....more >>

that takes 20 seconds may however maximally 5 last how am I to provide the indices? fill factor? SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID, dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS Massnahme, dbo.IHLEAktionenStamm.Name AS Akt...more >>

I have a stored procedure that I call from a VB6 program: DECLARE @myid UNIQUEIDENTIFIER BEGIN TRAN SET @myid = NEWID() UPDATE data WITH (ROWLOCK) SET UID=@myID, Done=1, DateTimeDone=GetDate() WHERE ID= (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=1) C...more >>

Why on earth am I to be encumber'd with putting N' in front of all sorts of stuff? You'd think that apostrophes and Ns everywhere would eventually piss everyone off. Yet, I look around me and I see it isn't so. Oh, no. What's wrong with that? I need to know. Here I go again... Who wan...more >>

Hi All, When is the 'for update' clause used in the cursor defintion statement? Is it required to provide the 'for update' clause in the cursor definition statement, if the fields in the cursor defiintion are not updated, rather another field that is not included in the cursor selection st...more >>

Hi, the table look like this: id, area, areaorder, areatext 1, group1, 1, Group1Text A 2, group1, 2, Group1Text B 3, group2, 1, Group2Text A 4, group2, 2, Group2Text B We need to be able to keep the order in each group, but also the ability to insert and update within a group eg. "INSE...more >>

I have 3 selects in my stored procedure I am using first and second select just for calculating number of rows (@@ROWCOUNT), and last one is the final select that I need. But I get all three selects Tomislaw ...more >>

Hi all, I have the following problem. I have two tables. The first table has some columns (idPerson, idCharge, reference, etc), but i only need the idPerson column. The second table has some columns (idCharge, Amount, date, bank, etc.). Example table1 idPerson, idCharge, reference 001, ...more >>

Example data : == create table t1 (a int, b varchar(20)) insert t1 values (1, 'one') insert t1 values (1, 'one-one') insert t1 values (1, 'one-one-one') insert t1 values (2, 'two') insert t1 values (3, 'three') insert t1 values (3, 'three-three') == I want to return a result set whi...more >>

Hi i have some problem that as i understand can be solved by using of CURSOR Suppose i have some stored proc it does something ... ... ... ..... .. and fills the following table DECLARE @t TABLE( [SID] [int] NOT NULL , [SName] [nvarchar] (30) NOT NULL , [STID] [int] NOT NULL ) t...more >>