how can i pass a declared variable like @myVar to a function which is inside a package in an Oracle DB I am running the script from "SQL Query Analyzer" Part of the SQL server script is like this: set @stat = (select * from openquery(MYLINKEDSERVER, 'select MYPACKAGE.MAYFUNCTION(' + @myVa...more >>

Hello, I am having an error when inserting a record in an Access database. The field [publication_date] is of Date/Time type. This is my query and the parameters for that field: Dim queryString As String = "INSERT INTO [t_news] ([title], [text], [publication_date]) VALUES (@title, @tex...more >>

Hello, I need to add a new record to an ACCESS database. I get the error: Syntax error in INSERT INTO statement. I have no idea what am I doing wrong. This is my code: ' Set Connection Dim connectionString As String = AppSettings("connectionString") Dim dbConnection As IDbConnectio...more >>

I work for a huge multinational corporation. After many hours of mysterious downtime playing Doom and Age of Empires corporate-wide, I just got this email from the MIS Suits. [my comments in square brackets]: <email> At approximately 2:00pm this afternoon an electrical breaker in the Horsha...more >>

I get the following error when i try to run a query on a linked server. Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL statement. SQLSTATE 42000 (Error 16805) Associated statement is not prepared SQLSTATE HY007 (Error 0). The step failed. any good ideas on how to ...more >>

I have a table with the following columns: EnrollmentID,StudentID,Course_Code,Course_Session,Course_Year I'd like to get EnrollmentIDs which have more than one occurance of the combination of (StudentID + Course_Code + Course_Session + Course_Year). I know I can write two selects and in ...more >>

Hello Sql Gurus, I had a calculation 999999999999.99 * (7.00 /100 / 12) * 6 sql server gives 34999997999.99965000002 vb.net gives 34999999999.999649 (tried in debugger) I had breakdown the problem to resolve as below. could any of you throw some light to resolve this. Calcula...more >>

Hello I would like to schedule my trace QSTRC which has the following events daily from 8-5: Locks -Lock:Deadlock -Lock:Escalation Stored Procedures -SP:StmtStarting TSQL SQL:BatchStarting ...more >>

Hi, anyone knows how to verify if a linked server is active only using SQL code? This question is because I need to check if a created linked server is ok to perform actions on it. Many thanks, Luis ...more >>

I am trying to do a Select * from (all tables where the table name begins with a letter P) I need to export data using Query and do not have access to Enterprise Manager so I want to select multiple tables using Query Analyzer. Could some please guide. Thanks in advance Sam Commar ...more >>

I am trying to gather information by comparing two tables. Table A and Table B both have the field shop_person. Table A is the entire list of employees, table B contains data about attendence, designated by a code. I want to compare the two tables and select those people from table A who ar...more >>

Hi there Please take a look at this code USE Northwind GO DECLARE @Index INT SET @Index = 0 WHILE @Index <= 8 BEGIN SET @Index = @Index + 1 SELECT TOP 2 * FROM Employees WHERE EmployeeID = @Index END GO This will give me 1 record at the time 1 1 2 3 ...more >>

Hello Folks... I have a little problem. I have a table (900,000 rows). I need to select x rows based on a field value( no problem so far). I set up a cursor to get the data ... declare cursor JJJ fpr Select * from x where y = '1'. Next fpr every record I need to add 1 to XXXX new r...more >>

Hi everyone, I installed SQL 2005 and I am testing it now. I can't seem to find an easy way of debugging procedures any more. In SQL 200 query analyzer, one can right-click and there is Debug option there. Where did it go now? Please point me in the right direction. Thanks Farmer ...more >>

I am trying to write a simple sp that will depending on a value run different queries. e.g begin case when a=1 then select * from b else select * from c end end something like that been working on this for a while, any easy way ...more >>

Hi, I have been able to delete a backup just using this: exec master..xp_cmdshell 'DEL e:\databases\MYBACKUP.bak' However, If I go to EM and I click on 'Restore Database' I still can see that backup listed there. Is there anyway to update those logs? Thanks a lot...more >>

I'm trying to update my Inventory table with the Vendor ID of the vendor that has given us the lowest quote on each partnumber. I created this View to get the appropriate data from the VendorQuote table: CREATE VIEW vVendorQuoteTemp AS SELECT TOP 100 PERCENT VendorQuote.ItemID, VendorQuote.Ven...more >>

Is it possible to add a non-table field (i.e. a new field that does not belong to any table) to a view? I am trying to avoid creating a brand new table with just one field so that I can add it to the view. Any help will be very much appreciated. ...more >>

Hi, If I have the foll table table1 1 2 3 4 how can I write a select statement to display the data in one row as 1,2,3,4 Thanks...more >>

I have ORDER records that can have 0 or more associated ORDERNOTE records. A CODE in ORDERNOTE tells me if a certain condition is true. So I might have this: ORDER1 / note1-code12 / note2-code7 ORDER2 ORDER3 / note3-code7 I'm trying to obtain a derived column that is 1 if an order ha...more >>

Hi, I'm using DBCC like this: SET NOCOUNT ON DBCC ... WITH NO_INFOMSGS And I still get a row count. How to I repress the row count? Thanks, Bryan ...more >>

Hi guys, When I try to generate a table script including index and primary key, I want to apply this table script to another sql server. but I got the following script return, do I really need this code? this is statistics not index. Is there a disadvantage if I don't use it? ...more >>

Hi, This is a simple SQL: select orders_id,keycode,validpayment,moneyreceived,prospect_id from orders where Keycode='ABCDEF123' and validpayment='valid' and moneyreceived>0 and prospect_id is NULL option (maxdop 1) If I run with option (maxdop 1), it returns correct lines. But if I run ...more >>

Hi every1, I trying to get data using a UNION ( so I can have all the values, because in the main table in one column 'PARENT_PROD_ID' the value is missing for one particular record). However, it is repeating the rows for that particular record where the row is empty. I'm getting the same ro...more >>

Hi, Earlier in the week David Portas (thanks again by the way) was kind enough to provide a few pointers with regards to a problem I was having creating shifts from a date. In overview I have a calendar table with around 10 years worth of shift information (approx 10000 rows). I also hav...more >>

Hi all, I have the file MYBACKUPS.BAK that contains several backups 001-Back1 (Complete) 002-Back2 (Complete) 003-Back3 (Complete) (They are always Complete) How can I delete from Query Analyzer '002-Back2'? I haven't been able to find how to do that... Thanks!...more >>

In VB 6, using ADO, SQL Server 2000 In my VB program I use the connection object (abc) to begin a transaction. abc.BeginTrans I then, in my VB App, using ADO, execute multiple stored procedures inserting records into temp tables ##Meetings, reading them, and then finnaly executing (from ...more >>

Let me see if I have this straight; A view is like a virtual table. Once a view is created, I can update and manipulate the view and it will always maintain those changes, yet not change the tables that the views are based upon. So, why use a view instead of creating another table to do th...more >>

Hi, I am trying to replicate this statement in a SELECT: IIf(IsNull([cmZip4]),[cityst] & " " & [cmZip],[cityst] & " " & [cmZip] & "-" & [cmZip4]) AS CityStZip If cmZip4 is null, I want to format cityst + " " + cmzip, if it is not null, I want to format it as cityst + "" cmZip + "-" + cm...more >>

I am trying to find non distinct rows in a table. For e.g 1.A list of all same Social Security numbers 2.A list of all same last names Could someone please assist with what is the sql for this. Thanks S Commar ...more >>

I have a stored procedure initiated by the user that determines records to purge, then does a delete query. I want the stored procedure to give some feedback by returning the number of records that were deleted. How do I do that?...more >>

Hi all, I need to identify duplicate data (based on the non-key columns) in two tables that have a child parent relationship: Table1(ColID, col1, col2, col3, col4) Table2(CID, colID, c1, c2, c3) These are columns that could have same data: Table1(col2, col3, col4) and Table2(c2, c3) where...more >>

Hi All I have a code block which works and looks like this... SELECT i.ItemNumber , i.ItemDescription ,h.[Year] ,d.OnHandQuantity ,h.IssuedQuantity1 + h.ShippedQuantity1 as January FROM dbo.FS_Item i INNER JOIN dbo.FS_ItemData d ON i.ItemKey = d...more >>

The following queries; select count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime) from tblMasterRT where transactiondatetime>'5/1/2005' and transactiontype in ('a','r','l','o','j') and transactioncomplete=1 and flag3=1 group by year(transactiondatetime...more >>

Server = SQL Server 2000 SP3a Client = Access 2000 SP3 My sproc works fine if I run it piece by piece, the sproc fails when called by iteself. It stops at a particular point with: [Warning: Null value is eliminated by an aggregate or other SET operation] The sproc layout is simply: ...more >>

Can anyone tell me where to find an object map of the SQL DMO libraries? I am looking for something that shows the objects and all their properties kind of like the SQL Server system tables help file Microsoft provides....more >>

Hi, There is a requirement to find mismatched transactions for credit and debit columns. The table is 29 Million rows. The case statement takes the current amount in the credit column and finds the value in the debit column, multiplies it by -1 and hopefully the sum of credit and debit tog...more >>

Hi, Does anyone have any samples about what I need to capture in SQL Profile in order to look at the Lock/DeadLock situation? Thanks Ed...more >>

CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int) INSERT INTO test values('aaa','6/5/2005',0) INSERT INTO test values('aaa','6/16/2005',75) INSERT INTO test values('aaa','6/20/2005',159) INSERT INTO test values('bbb','6/8/2005',7) INSERT INTO test values('bbb','6/15/20...more >>

I need to create a stored procedure to import a comma delimited text file into a SQL table. I am new to SQL, am very compfortable with VB and Access. Anyone have a good reference for me to look at? A good source of info for me to start with? Pointers? Anything? Thanks! ...more >>

I am trying to perform a basic NOT in query using Query Analyzer in SQL 2000. I get no results, and I know they are there. select createdate,* from riccustomers where email not in (select email from ricorders) order by custno desc Why would this not work? *** Sent via Developersdex...more >>

Hi! I just bought the book "SQL for smarties" by Joe Celko. Here he shows the possibility to use the nested set model. I am in the situation where I need to model a hierarci: One company has several regions that has several districts that has several projects that has several buildings that h...more >>

Hi gurus, I am trying to create a stored proc, which accepts a database name as parameter. This is then supposed to query a table on a field (db_version.current_version) in "that database", and check if the value in that field is = '1.0.1.1' If that matches , stored proc should return '...more >>

Hi! If I have a field in a table, that has a foreign key but also allows nulls, would it affect my queries, performance or data size in some way? Specifically, if most values in the field are nulls? My other option is to create a junction table between the two tables and only insert row-pai...more >>

Hi, I am faced with an unusual SQL Server upgrade related problem.I was using the SQL Server 7.0 until about a few days ago when we upgraded to SQL Server 2000. I had the following query in one of the views. "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT WHERE UT.USER_REF_ID=@T_PATH...more >>

Hello, I have a stored procedure that returns two tables. It works fine in the query analyser. However, When I write a vb 6 application and ADO 2.5 to get the two recordsets, it always give me recordCount -1, EOF or BOF results. If I remove one table from the stored procedure (just keep one...more >>

This query allows a specific row to returned first, if found. Is it possible to modify so that results are ordered by Title (varchar). When I try this I get data type conversion error. thanks. hals_left Create procedure dbo.getResults @ID smallint AS select ID,Title FROM tblResults O...more >>

The @@cursor_rows should print 20 instead it is printing zero. Can anyone help me in getting the total no of records fetched from the select statement before opening the Claims cursor. CREATE PROCEDURE Test AS Declare @id varchar(10) Declare Claims Cursor for select top 20 [id] from <...more >>

Hi, I have a table that contact many columns that I have included in my freetext index. I want to be able to search for an item of text in any of these fields, yet it seems that the in the freetext SQL statement I have to specify which col i want to search. ie. SELECT contactname ...more >>

Hi All again I have a question on an index The query is as follows SELECT * FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To What index is more efficient CLUSTERED INDEX ON IP_Address_From ...more >>

Hi All, I need to know how to do the following I need to know how to validate an IP address i.e. if you have an ip of 10.1 (not valid) if you have ip of 10.1.1.1 (then valid) How does one do this in SQL Thanks for the help Kathryn ...more >>

Hi, I'm using the newId() function inside CREATE statements for a new unique value. - Do I need to set the "Is identity" property to "yes"? - What's happen when the newId() function creates a value that already exists? Or is this impossible? - Are the generated values unique for all ...more >>

Hi there! In my database client application (C#) I sometimes get an exception telling me, that my application (which runs a some kind ob batch job) was selected as deadlock victim and the active transaction was terminated. No I want to get mor information about, which other application cause...more >>

Hello, I need to create a new record in an Access database. The database has 3 fields: [id] (autonumber) [title] (string) [text] (memo) [publication_date] (DateTime) When I create the record how should I create the [id] value? Does the database insert it automatically? Do I need to ...more >>

Hi I need to create a view which displays all WeekDates from sunday to saturday in a certain year. The view stucture should be something like: Year, date(sunday), date(saturday) Example: 2005, 5.6.05, 11.6.05 2005 , 12.6.05, 18.6.05 ...... Please advice. thanks ...more >>