|
hi all
im trying to export table loan to excel file but i get this error in sql 2008 r2
Msg 7308, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
my query is
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [Bankmellat$]') select * from BML.Loan
anyone can help me for fix this problem
thanks regards
|
|
|
|
|
|
i do your solution but i get error and my problem is stable and i get error again.
|
|
|
|
|
What error? The same error? Or another?
Are you on a 64-bit machine?
|
|
|
|
|
yes i get the same error .
yes my machine is 64 bit
|
|
|
|
|
The JET-provider needs to be 64-bit too. Try downloading[^] the newer version.
|
|
|
|
|
Dear seniors,
How I can use case statement in where clause…..
Below query raised syntax error.
@MyeffectiveDate
Select * from Mytable where ((EffectiveDate <= @ MyeffectiveDate AND
CASE WHEN BillingTerminationDate = NULL THEN
(terminationDate is null or BillingTerminationDate > =@ MyeffectiveDate)
ELSE BillingTerminationDate > =@ MyeffectiveDate)
OR IsAdjustment=1)
WHAT EXACLY I WANT
If(BillingTerminationDate is null) then where clause should used ” (terminationDate is null or BillingTerminationDate > =@ MyeffectiveDate)” statement.
Other wise used
BillingTerminationDate > =@ MyeffectiveDate statement.
Waiting for your need full reply.
Thanks in advance.
|
|
|
|
|
digvijay dobhal wrote: BillingTerminationDate = NULL
You can't use = to compare something to NULL ; use Is Null instead.
digvijay dobhal wrote: If BillingTerminationDate is null then ... BillingTerminationDate >= @MyeffectiveDate
If BillingTerminationDate is NULL , then it's not going to be greater than, less than, or equal to anything else.
digvijay dobhal wrote: AND
CASE WHEN BillingTerminationDate = NULL THEN
(terminationDate is null or BillingTerminationDate >= @MyeffectiveDate)
ELSE BillingTerminationDate >= @MyeffectiveDate)
You're missing an END on your CASE block. You also can't use a CASE block like this.
What you want is probably something like this:
WHERE
(
(
EffectiveDate <= @MyeffectiveDate
And
(
(BillingTerminationDate Is Null And TerminationDate Is Null)
Or
BillingTerminationDate >= @MyeffectiveDate
)
)
Or
IsAdjustment = 1
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks Richard Deeming.
I try above query bur not succeed.
Finally I do increase number of query lines and doing below…..
select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is not null
and (BillingTerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1)
select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is null
and (TerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1) Right now its working as my expactation. But having doubt that after reviewing, may or may not it will give me pain.
|
|
|
|
|
OK, try:
WHERE
(
(
EffectiveDate <= @MyEffectiveDate
And
(
(BillingTerminationDate Is Not Null And BillingTerminationDate >= @MyEffectiveDate)
Or
(BillingTerminationDate Is Null And TerminationDate >= @MyEffectiveDate)
)
)
Or
IsAdjustment = 1
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanx.. its working
|
|
|
|
|
CASE is an expression and not an executable statement.
The general form of the predicate of a WHERE clause is expression operator expression where operator is = or <> or > or < etc.
You can use a case as one of the expressions in the predicate.
But you shouldn't need the case. Try this instead:
Select *
FROM Mytable
WHERE (
BillingTerminationDate is null
AND
terminationDate is null)
OR BillingTerminationDate > =@ MyeffectiveDate and we'll see if I understood you correctly.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
digvijay dobhal wrote: Dear seniors,
Thats about the most offensive way ive seen to start a question.
|
|
|
|
|
I have a table including 3 fields which are Machine Name, Project Name, Date Time where Date Time indicates the exact time when machine was assigned to a project.
Now I want to find machines which are now in a specific project (in fact it may be that one machine in different times be allocated to different projects).
I want lasted specific project be latest project that machine assigned to it.
|
|
|
|
|
Sounds like a simple query to me. Did I forget something or have I misread the question?
SELECT TOP 1 MachineName
FROM MyTable
WHERE ProjectName = 'Web 8.0'
ORDER BY [DateTime] DESC
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I want all machines that now be in specific project , may machines "a", 2 days ago in project "1" and now be in project "2" and machines "b" now in project "1", when I wand machines in project "1" ,only "b" should be in result and Should not machine "a" be in result
|
|
|
|
|
Member 8407653 wrote: I want all machines that now
Change the Sql accordingly. I want coffee.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Something like this should work:
WITH cteOrderdAssignments As
(
SELECT
MachineName,
ProjectName,
ROW_NUMBER() OVER ( PARTITION BY MachineName ORDER BY [DateTime] DESC ) As RowNumber
FROM
MyTable
)
SELECT
MachineName
FROM
cteOrderedAssignments
WHERE
RowNumber = 1
And
ProjectName = 'Web 8.0'
ORDER BY
MachineName
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Row_number() function in combination with common table expression is the best way indeed
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hi everyone! I need to get a list of databases mapped to a login in Sql Server 2008. I have two databases which I have mapped both databases to a login.
What is the required script to return the login mapped databases? I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.
I'm however writing a client application in Windows Forms and I would like to know the script that can return the login mapped databases. Please help.
|
|
|
|
|
Dan_K wrote: What is the required script to return the login mapped databases?
No idea, but hang on, and we'll introduce a way to figure it out.
Dan_K wrote: I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.
The SMS uses Sql-commands, and one can intercept[^] those. Meaning, you can "look" at what command the application is executing to get those results.
Enjoy
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy. But provided links on the specified page were not found.
|
|
|
|
|
Aaaah, the open source profiler has become a commercial product. There's also a profiler included with Sql Server, but it would not be installed for a Sql Express version - that's why I linked an open-source version. You might want to try their trial-version[^].
Alternatively, there's an implementation of a TSql profiler here[^]. Guess I'll be using that one from now on, as I do not like to recommend trial-versions.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I just hit this problem today when recompiling an ADO-enabled project in VS2005 after installing Windows 7 SP1. After an hour of frantic Googling I found this[^] and this[^] and managed to get it to compile again, with testing on XP machines to follow soon.
As MS is apparently no longer recommending we use ADO, what should I be changing my C++ programs to use to talk to SQL Server?
|
|
|
|
|
viaducting wrote: As MS is apparently no longer recommending we use ADO
I did not read that in the links you provided. Can you point it out to me? It'd be a small game-changer if all older software would suddenly stop to work
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|