Why Don't This Little Query Work For Me

Jan 28, 2008

hello everybody!
I'm using ssce.
I'm trying to add the followed query in my table adapter but in the query builder (VS), when I choose execute query, I get a couple of textboxes but whatever I type in, I get an error message "The parameter is wrong"

here is the query:
SELECT EntryID, UserInfo, Kind, ActionDate, Span, SalaryAtAction
FROM Logbook
WHERE
((CASE
WHEN @Enter = 0 AND @Exit = 0 THEN 0
WHEN @Enter = 1 AND @Exit = 0 AND kind = '?????' THEN 1
WHEN @Enter = 0 AND @Exit = 1 AND kind = '?????' THEN 1
WHEN @Enter = 1 AND @exit = 1 AND kind IN ('?????', '?????') THEN 1 ELSE 0 END) = 1)

what I try to implement is, a table that one of it's columns is bit datatype. the end-user can filter out the table by these parameters whether to show only the true rows or only the false rows or both.

would you suggest to use a different various of the query?

(a help button is provided on the error message, it refers to ]ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/dv_vdt03/html/f0bf6b59-c245-4340-a869-5d7278fe0ae5.htm in case it helps anybody).

Shimi

View 1 Replies


ADVERTISEMENT

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



Maurício

View 2 Replies View Related

Is There A Way To Get This Query To Work In SQL

Aug 3, 2004

What I want to do is update 1 table based on data in another. This query works great in Access XP, but I cannot make it work in SQL 2000 sp3. Is there a different way to populate table A with direct data from table B in an update?

Any help is appreciated

UPDATE [Clients - Complete], AssociateDirectory
SET [Clients - Complete].Phone = AssociateDirectory.WorkPhoneNumber
WHERE [Clients - Complete].Name=AssociateDirectory.LastName And [Clients - Complete].FNAME=AssociateDirectory.FirstName;

View 2 Replies View Related

Can't Seem To Make This Query Work

Jul 24, 2006

Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
 
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow  where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
 
The error message i'm recieving is the following:
 
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 
Any ideas? any suggestions would be great.
 
Thanks

View 3 Replies View Related

SQL Query Analyzer Does Not Work

Apr 18, 2004

My SQL Query Analyzer just runs the clock but when the clock stops it fails to open the tool. Both my SQL Server 2000 Developer and Enterprise Edition do this. Can anyone suggest how I can get this working? It worked fine in SQL 7.

View 1 Replies View Related

Why Doesnt My Query Work

Dec 6, 2007

select distinct
a.Patients,
b.Patients,
a.pct,
b.pct

from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null

(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct

View 6 Replies View Related

Noob: Why Does This Query Work?

Jul 23, 2005

I'm wondering how/why this query works. Trying to get my head wrappedaround SQL. Basically the Query deletes from the Import table allrecords that are already in FooStrings so that when I do an insert fromthe FooStringsImport table into the FooStrings table, then I won't getprimary key violations.DELETE FROM FooStringsImportWHERE EXISTS(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)It seems to work fine, but I'm wondering about how the EXISTS keywordworks.(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)This part is going to return only records from FooStrings correct? Ordoes it do a cartesian product since I've specified more than one tablein the WHERE statement?I wonder if it only returns records in FooStrings, then I don't see howa record from FooStringsImport would "EXISTS" in the records returnedfrom FooStrings.The reason I wondered about the cartesian product is because, if onlyFooStrings is specified in the FROM part of the SELECT statement, thenI was thinking it is only going to return FooString records. Theserecords would then be returned by the select statement to the WHEREEXISTS, which would look for FooStringImport records, but would findnone because the select statement only returned FooString records.I'm guessing maybe because it has to do a cartesian product to evaluatethe WHERE Pkey's equal, then the "SELECT *" just goes ahead and getsALL the fields, and not just those in FooStrings.FooStrings and FooStringsImport are identically structured tables,where the FooKey is set as the primary key in each table:CREATE TABLE [dbo].[FooStrings] ([FooKey] [bigint] NOT NULL ,[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StartDate] [datetime] NULL ,[EndDate] [datetime] NULL ,[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks in advance. I'm so appreciative of the help I've gotten here,as I've been able to write several very useful queries on my own nowafter everyones help and plus lots of reading on my own.

View 4 Replies View Related

UNION Query Won't Work

Jul 20, 2005

I am having alot of trouble with a union query Im trying to create.The bizarre thing is I initially created this query and managed tosave it as a viewSELECT ID, DepartureDate, City, Country, Region,fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS TypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, DestCity, Country, Region, fkDepartureAirport,Price, '2' AS TypeFROM dbo.vFlightHowever when I tried to update the view to this:SELECT ID, DepartureDate, fkCity, City, fkCountry, Country,Region, fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' ASTypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, fkCity, DestCity, fkCountry, Country, Region,fkDepartureAirport, Price, '2'FROM dbo.vFlightit comes up with the error: view definition includes no output columnsor includes no items in the from clause.Any ideas??????All suggestions appreciatedAlly

View 1 Replies View Related

Cannot Work Out A Simple T-sql Query

Apr 2, 2008

Hi all,
I have a table with 2 columns (simplified for this question):
Date and Action

Date is normal datetime, Action is varchar and can be either Sent or Received. The data can look like this:

1. '2008-04-02 15:09:09.847', Sent
2. '2008-04-02 15:09:10.125', Sent
3. '2008-04-02 15:09:11.125', Received
4. '2008-04-02 15:09:12.459', Received
5. '2008-04-02 15:09:15.459', Received
6. '2008-04-02 15:09:24.121', Sent

7. '2008-04-02 15:09:28.127', Received

I want to find a pair of rows Sent-Received with the Max Date difference, where Received follows immediately after Sent.


It means in our example, valid are only lines
2. and 3. or
6. and 7.

In this example, rows 6. and 7. have bigger difference of Dates, so the result of the query should be

6. '2008-04-02 15:09:24.121', Sent, 7. '2008-04-02 15:09:28.127', Received


It is probably easy, I just cannot work it out. Suprisingly finding MIN was quite easy. Thanx for any tips!
Regards,
alvar

View 6 Replies View Related

Detecting Of Record Does Not Work In If Query

Nov 23, 2007

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!I hope it continues to work. In following query, else is never executed.CREATE PROCEDURE Put_into_basket(    @Product_code varchar(20))ASBEGIN    SET NOCOUNT ON;IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@Product_code)     BEGIN        INSERT dbo.t_shopping_basket (Product_code, Name,Price)        SELECT Product_code, Name,Price        FROM dbo.t_product        WHERE Product_code= @Product_code    ENDELSE    --this part is never executed    BEGIN            UPDATE dbo.t_shopping_basket        SET Quantity=Quantity+1        WHERE Product_code=@Product_code    ENDENDGO  The query should test if there is a record or row with Product_code=@Product_code. If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of. If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.But when I test query, it never runs the quantity+1 part. 

View 3 Replies View Related

How To Make Two Separate Query Work Together

Oct 15, 2014

I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.

CONCAT('at-',
REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','')
)
removes last character i.e. /
SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)

basically i need these 2 to work together to give me an output like this

at-09fd8903

from the this. URL...url above minus the "" as i said both work on there own, but not together.

View 1 Replies View Related

Index For Access Query Work ?

Feb 27, 2007

Not sure which forum too put this question

I have recently upgraded my Access Database too use SQL server 2005 using the upsizing wizard. I have selected too use SQL Linked tables instead off the ADP project for ease off conversion. So now the tables are on the SQL server and the queries are still local.

On off my Access forms which returns a datasheet view by accessing the a query is now running really slowly now SQL server is the backend, I was wondering if I can put an index on one of the tables too speed it up, as the query is local too Access will this work? Just wondering if anyone ever come across this.

View 1 Replies View Related

Simple Query Doesnt Work

Apr 23, 2008

I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???

would it be a problem with the sdf file? help please!!!

View 3 Replies View Related

Bcp Command Won't Work At Query Analyzer

Nov 8, 2007

Hi all,

I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully

Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.


Best regards,


Hery

View 9 Replies View Related

Oracle Query Does Not Work In SSIS

Nov 7, 2006

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju





View 3 Replies View Related

Delete Query That Doesn't Work ???

Apr 19, 2007

Hi!



Do you know why this query is ok on SQL 2005 and not on SQL ce ??






Code Snippet

DELETE ProfilesGroups

FROM ProfilesGroups pg

INNER JOIN Groups g

ON tpGroupID = g.gId

WHERE pg.tpProfileID = '7df60fae-a026-4a0b-878a-0dd7e5308b09'

AND g.gProfileID = '8a6859ce-9f99-4aaf-9ed6-1af66cd15894'



Thx for help ;-).



PlaTyPuS

View 1 Replies View Related

Cannot Get My TREE VIEW Recursive Query To Work

Mar 17, 2007

My Table Structure

Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....

MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy

My OUTPUT============

All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.

Can you help me please?

View 12 Replies View Related

How Can I Estimate Time, Which Query Needs To Complete Work?

Jan 28, 2008

As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.

View 14 Replies View Related

Valid Query Wont Work As DTS Task???

Jan 23, 2004

i have a query which works fine when i run it from Query Analyzer.

when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:

ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL

It throws and error that says:

"Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.

View 7 Replies View Related

Query Works Fine Outside Union, But Doesn't Work .. .

Mar 31, 2004

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??


select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc




i've included the union query here for completeness of the question



begin
declare @current_date datetime
set @current_date = GETDATE()


select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

end

View 1 Replies View Related

SQL 2012 :: Datediff - Query Does Not Work With Yyyyddmm Format

Mar 26, 2014

I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).

I need the difference between two dates in hh:mm:ss format.

I tried following query

declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */

This works fine but as you can see the date I have used in the query is in yyyymmdd format.

The query does not work with yyyyddmm format

The expected answer is =25:01:01.

View 5 Replies View Related

Problem: MySQL Query Doesn't Work With SQL Server

Nov 9, 2007

I made a page for a baseball league a couple of years ago using PHP and MySQL, and decided this winter to switch it to ASP.Net and SQL Server Express that comes with VWD 2005 Express Edition.

Anyways, I ran into a problem when trying to convert the query I made with MySQL into SQL Server.

My old code was something like this:

SELECT homeScore, visitorScore, ( homeScore > visitorScore ) AS homeWins, ( visitorScore > homeScore ) AS visitorWins FROM Schedule

This worked fine in MySQL, and returned the home team's score, visiting team's score, and then returned a 1 in homeWins if the home team won. Now, when I try running this query in SQL Server Express, I get an error saying "Invalid column name 'visitorScore > homeScore' ".

Can anyone please help me with what I should be doing differently so this works in SQL Server Express?! Thanks in advance!!!

View 17 Replies View Related

Linked Server Distributed Query Doesnt Work

Jun 21, 2006

 

Hi,

 The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.

 

OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

View 7 Replies View Related

Trouble Getting SQL Query To Work Using LIKE Clause With Astersik (*) As Wildcard In ADO.net

Apr 13, 2006

Please help.

Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.



For Example,

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK

Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.

Data:
ID Name Birthdate







AAA
Aaron
5/4/1975

CCC
Charlie
10/14/1948

DDD
Deloris
7/19/1998

The code I use is listed as follows:

Imports System.Data.OleDb


Imports System.Data
Public Class Form1


Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:ExcelData1.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


'Use a DataSet to Query data from the EmployeeData table.

Dim QryConn As New OleDbConnection(m_sConn1)

'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works

Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work

Dim da As New OleDbDataAdapter(strQry, QryConn)

Dim ds As DataSet = New DataSet

da.Fill(ds)

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows 'Show results in output window

Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))

'Expected output:






'AAA
Aaron
5/4/1975

Next

QryConn.Close()

End Sub

End Class

View 1 Replies View Related

Query Notification Does Not Work Due To Failure To Authenticate The User ?

Sep 1, 2006

I tried using Query Notification on my computer at home:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix

Query Notification worked fine.

Then I tried using it at work:

* Win XP Pro with all the SPs and hotfixes

* SQL 2005 with SP1 qand hotfix

and I see the following error in the SQL server log file and notification does not get to the client app:

----------------------------------------------------------
Date 9/1/2006 10:18:30 AM
Log SQL Server (Current - 9/1/2006 10:18:00 AM)

Source spid17s

Message
An
exception occurred while enqueueing a message in the target queue.
Error: 15404, State: 19. Could not obtain information about Windows NT
group/user 'domainmyuser', error code 0x6e.
----------------------------------------------------------

A similar error shows up in the machine's Event Log.

I am
sysadmin and full OS admin on both boxes. The difference is that the
computer at home is standalone while the one at work is part of a
domain.

What could be wrong?

View 7 Replies View Related

Pass-thru Query Doesn't Work With A SQL 2005 SSIS Package

Dec 14, 2007

I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:

Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".


What am I doing wrong?

View 4 Replies View Related

In Query Builder Design View, How Does The Filter Operator MDX Work?

Nov 12, 2007

Good morning all,

I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.

In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.

Does anyone have any links to documentation on how to use this operator?

Thanks,
Kathryn

View 2 Replies View Related

SQL 2005 Does Not Work With ADO Query On SYS.sysprocesses After Update Installed From Microsoft

Jul 11, 2007



str = "select nt_username, hostname, nt_domain, loginame, login_time, program_name " &
"from master.sys.sysprocesses where spid=@@SPID"



-This is the Query to display the username in the application.

-The client is MS-Access 2003, MDAC 2.8

-It was and is Ok both on SQL 2005 and SQL 2000. The qury runs under Query Analyzer 2000 or Management Studio 2005 without problem



-After Downloading the updates from Microsot Website. The embedded code returns no result set:

Data provider or other service returned an E_FAIL status



If anybody has any idea, I will be thankful.

View 8 Replies View Related

JDBC: PreparedStatement#getParameterMetaData() Doesn't Work For INSERT SQL Query

Feb 26, 2008

Hi,

I'm using MSSQL 2005 JDBC driver version 1.2 with MSSQL Server 2005. Getting parameter's metadata doesn't seem to be working properly there. PreparedStatement#getParameterMetaData() works fine if PreparedStatement is created for some SELECT but fails for any INSERT statements. The same code works fine with other DBs (I checked with HSQLdb and PostgreSQL). See sample below

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://mssql2005\mssql2005;DatabaseName=alexdtms;user=xxx;password=xxx";

// I don't care about non-closed statements, it's just sample code
Connection con = DriverManager.getConnection(connectionUrl);
con.createStatement().executeUpdate("CREATE Table Table1 (RECORD_ID varchar, TgtQty float)");
PreparedStatement ps = con.prepareStatement("INSERT INTO Table1 VALUES ( ?,? )");

ParameterMetaData pmd = ps.getParameterMetaData();// <-- !!!! Exception is thrown here
for (int i = 1, count = pmd.getParameterCount(); i <= count; i++)
{
System.err.println(">>>>AD_TEMP " + pmd.getParameterType(i) + " " + pmd.getParameterTypeName(i) + " " + pmd.getParameterMode(i));
}

Thanks,
Alexander Dolgin

View 1 Replies View Related

Link Server Doesn't Work Except Through Query Analyzer: MSSQL Freezing / Timing Out

Jan 7, 2007

Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT A.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?

View 2 Replies View Related

SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work

Apr 16, 2015

Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.

Here is my linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null

Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.

SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')

OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2

An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".

View 0 Replies View Related

Update Query In Ms-access Doesn't Workin C#, But Does Work In Ms-access

Apr 18, 2007

Hi,

I have an application that uses following code:



Code Snippet







using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;

//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}

//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;

//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);

SelectProjectID.Parameters.AddWithValue("@Project", m_Project);

try
{
//open the connection
conn.Open();

OleDbDataReader Dataset = SelectProjectID.ExecuteReader();

while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);

SelectActionID.Parameters.AddWithValue("@Action", m_Action);

try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();

while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}



//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);

Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);

try
{
Query.ExecuteNonQuery();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}

Code Snippet



The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.

What am I overseeing here?
--Pascal

View 13 Replies View Related

Why Does This Not Work

Feb 27, 2007

 My error is that 'Name tr is not declared'  tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'.
What is the correct way?        Try            conn.Open()            Dim tr As SqlTransaction            tr = conn.BeginTransaction()            cmdInsert1.Transaction = tr            cmdInsert1.ExecuteNonQuery()            cmdInsert2.Transaction = tr            cmdInsert2.ExecuteNonQuery()            cmdInsert3.Transaction = tr            cmdInsert3.ExecuteNonQuery()            tr.Commit()        Catch objException As SqlException           tr.Rollback()            Dim objError As SqlError            For Each objError In objException.Errors                Response.Write(objError.Message)            Next        Finally            conn.Close()        End Try 

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved