Cannot execute through ISQL

Hi,
I cannot execute ISQL query from dos prompt to one
specific MS SQL Server 2000 production machine, but works
fine with other server any reason
Please let me know ASAP
Thanks & Regards,
Mustaq
What 's the problem? Any error messages?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:2700c01c46271$e512d660$a301280a@.phx.gbl...
Hi,
I cannot execute ISQL query from dos prompt to one
specific MS SQL Server 2000 production machine, but works
fine with other server any reason
Please let me know ASAP
Thanks & Regards,
Mustaq
|||Hi Vyas,
It doesn't prompts me any error message. It just comes out.
Regards,
Mustaq

>--Original Message--
>What 's the problem? Any error messages?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2700c01c46271$e512d660$a301280a@.phx.gbl...
>Hi,
>I cannot execute ISQL query from dos prompt to one
>specific MS SQL Server 2000 production machine, but works
>fine with other server any reason
>Please let me know ASAP
>Thanks & Regards,
>Mustaq
>
>.
>
|||Can you show us the query and the way you are trying to run the query? Are
you running it interactively or running it from a batch, by specifying a
query or input file?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
Hi Vyas,
It doesn't prompts me any error message. It just comes out.
Regards,
Mustaq

>--Original Message--
>What 's the problem? Any error messages?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2700c01c46271$e512d660$a301280a@.phx.gbl...
>Hi,
>I cannot execute ISQL query from dos prompt to one
>specific MS SQL Server 2000 production machine, but works
>fine with other server any reason
>Please let me know ASAP
>Thanks & Regards,
>Mustaq
>
>.
>
|||Vyas,
Here is the command and query..
C:\Documents and Settings\husmu02>isql -S AH_Stage -d
AH_Baseline -U sa -q "select @.@.version"
Just to let you know the back ground
Client machine is only installed with MS SQL 7.0 Client
network utility with Mdac 2.7 and configured, Server is
SQL Server 2000 SP3, which is in WAN.
I am able to do ODBC connection and run the query but not
through isql, understand isql doesnot make use of ODBC.
Initially I was thinking to be network problem, but may
not be true, I installed MS SQL Query Analyser from 7.0
release, I can execute the query from Query Analyser
Same command and query if I specify to another MS SQL
server 2000 it works fine.
Any reason on this issue.
And can let me know how does isql work, which ports, dlls
or drivers.
Regards,
Mustaq
Regards,
Mustaq

>--Original Message--
>Can you show us the query and the way you are trying to
run the query? Are
>you running it interactively or running it from a batch,
by specifying a
>query or input file?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
>Hi Vyas,
>It doesn't prompts me any error message. It just comes
out.
>Regards,
>Mustaq
>
>
>.
>
|||Without any error messages, it is difficult to predict what's going on, but
can you try OSQL instead, and let me know how it goes?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
news:26d5001c462fa$7c5d1e80$a501280a@.phx.gbl...
Vyas,
Here is the command and query..
C:\Documents and Settings\husmu02>isql -S AH_Stage -d
AH_Baseline -U sa -q "select @.@.version"
Just to let you know the back ground
Client machine is only installed with MS SQL 7.0 Client
network utility with Mdac 2.7 and configured, Server is
SQL Server 2000 SP3, which is in WAN.
I am able to do ODBC connection and run the query but not
through isql, understand isql doesnot make use of ODBC.
Initially I was thinking to be network problem, but may
not be true, I installed MS SQL Query Analyser from 7.0
release, I can execute the query from Query Analyser
Same command and query if I specify to another MS SQL
server 2000 it works fine.
Any reason on this issue.
And can let me know how does isql work, which ports, dlls
or drivers.
Regards,
Mustaq
Regards,
Mustaq

>--Original Message--
>Can you show us the query and the way you are trying to
run the query? Are
>you running it interactively or running it from a batch,
by specifying a
>query or input file?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:2667101c4627f$d424b2c0$a401280a@.phx.gbl...
>Hi Vyas,
>It doesn't prompts me any error message. It just comes
out.
>Regards,
>Mustaq
>
>
>.
>
|||Vyas,
Thanks, got resolved by setting TCP/IP and configuring it.
Regards,
Mustaq

>--Original Message--
>Without any error messages, it is difficult to predict
what's going on, but[vbcol=seagreen]
>can you try OSQL instead, and let me know how it goes?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Mustaq" <mustaqhussain@.hotmail.com> wrote in message
>news:26d5001c462fa$7c5d1e80$a501280a@.phx.gbl...
>Vyas,
>Here is the command and query..
>C:\Documents and Settings\husmu02>isql -S AH_Stage -d
>AH_Baseline -U sa -q "select @.@.version"
>Just to let you know the back ground
>Client machine is only installed with MS SQL 7.0 Client
>network utility with Mdac 2.7 and configured, Server is
>SQL Server 2000 SP3, which is in WAN.
>I am able to do ODBC connection and run the query but not
>through isql, understand isql doesnot make use of ODBC.
>Initially I was thinking to be network problem, but may
>not be true, I installed MS SQL Query Analyser from 7.0
>release, I can execute the query from Query Analyser
>Same command and query if I specify to another MS SQL
>server 2000 it works fine.
>Any reason on this issue.
>And can let me know how does isql work, which ports, dlls
>or drivers.
>Regards,
>Mustaq
>Regards,
>Mustaq
>run the query? Are
>by specifying a
>out.
works
>
>.
>

cannot edit this cell

when I fetch recods via enterprise manager and try to insert new
record I get the following error
"cannot edit this cell" but when I use "query analyzer" using T-sql commands I can execute DML operations .

it seems that there is no problem in the permissions ,I can not understand what is the problem .

can any one please help me it is very urgent since the problem is in one
of our production databases.

note that I use sql server 2000Your description does not sound this complicated - but check out the following article:

article (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q288969&)|||Maybe the cell in the column is defined as an Identity column?|||give the sp_help 'tablename' and see the owner of the table may be it is the owners name through whch ur not entering to the table .

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>> I have a table with no dependencies. I cannot drop the table. I
>> tried from EM to delete it. I tried from Query Analyzer. I cannot
>> even delete an index that it has. It's not a big table and it only
>> has about 6,000 rows. Any ideas where to look? Thanks.
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql

Cannot display/return SQL Query Output from a Variable in DTS

I have been having a very difficult time trying to get the output of a
sql query that is in a dts global variable to return either into a
msgbox or for populating a portion of the body of a mail task.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. Four rows are returned. Basically, I wish to put these
results in the body of a mail task or right now I would settle for a
msgbox just to see it work.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
to write the recordset to a file.
I feel like I am going about this all wrong and the answer is just
staring me in the face and I dont get it.
I welcome suggestions and comments on how to achieve the goal of putting
the query results into the email body.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You may want to use call xp_sendmail directly (see Books online) rather than
the DTS Send Mail task. If not this may help:
http://www.sqldts.com/default.aspx?235
John
"SJM" <nospam@.devdex.com> wrote in message
news:O8fvDgusFHA.596@.TK2MSFTNGP12.phx.gbl...
>I have been having a very difficult time trying to get the output of a
> sql query that is in a dts global variable to return either into a
> msgbox or for populating a portion of the body of a mail task.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. Four rows are returned. Basically, I wish to put these
> results in the body of a mail task or right now I would settle for a
> msgbox just to see it work.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
> to write the recordset to a file.
> I feel like I am going about this all wrong and the answer is just
> staring me in the face and I don't get it.
> I welcome suggestions and comments on how to achieve the goal of putting
> the query results into the email body.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Indeed, I am considering sending the mail from within the script, but
right now I am concentrating on getting the data and other info into the
message body. Thanks for the suggestion.
*** Sent via Developersdex http://www.examnotes.net ***sql

Cannot display/return SQL Query Output from a Dispatch Variable

I have been having a very difficult time trying to get the output of a
sql query that is in a DTS global variable to return, presently to a
messagebox.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. I set a global variable to the result set. Basically,
I wish to display the results as a string or similar in a messagebox.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
unable to write the recordset to a file.
What is the proper way to display a variable of type dispatch? I feel
like I am going about this the wrong way.
I welcome suggestions and comments on how to achieve the goal of
displaying the query results.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You don't seem to have:
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
listed.
John
"SJM" <nospam@.devdex.com> wrote in message
news:u6hU08ItFHA.304@.TK2MSFTNGP11.phx.gbl...
>
> I have been having a very difficult time trying to get the output of a
> sql query that is in a DTS global variable to return, presently to a
> messagebox.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. I set a global variable to the result set. Basically,
> I wish to display the results as a string or similar in a messagebox.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
> unable to write the recordset to a file.
> What is the proper way to display a variable of type dispatch? I feel
> like I am going about this the wrong way.
> I welcome suggestions and comments on how to achieve the goal of
> displaying the query results.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, that MSDN is what I followed to get the data into the variable,
getting it out is the problem.
The workaround I am using is to execute the SQL statements in the
VBscript and not use the execute SQL DTS task. For example:
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL STATEMENT HERE")
if rs.eof and rs.bof then
msgbox "Error: No records"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
msgbox " " & rs.Fields(intLoop).Name
Next
set rs = rs.Next
loop
end if
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||
Everything is fine with the code listed below. I am going to use this
instead of the DTS execute sql task.
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
dim strText
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL QUERY")
If rs.BOF then
msgbox "No Records Found"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
strText = strText & rs.fields(intLoop).value & " " & vbCrLf
Next
rs.MoveNext
loop
msgbox strText
end if
set rs = nothing
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
It was the wrong link, should have sync'd
http://msdn.microsoft.com/library/d...asp?frame=true
I have just followed
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
and the above and there have been no problems.
John
"SJM" <nospam@.devdex.com> wrote in message
news:euD9JTUtFHA.2624@.TK2MSFTNGP12.phx.gbl...
>
> Everything is fine with the code listed below. I am going to use this
> instead of the DTS execute sql task.
> Option Explicit
> Function Main()
> dim cnn
> dim rs
> dim intLoop
> dim strText
> set cnn=createobject("ADODB.Connection")
> cnn.Open "Provider=sqloledb;" & _
> "Data Source=SERVER;" & _
> "Initial Catalog=msdb;" & _
> "Integrated Security=SSPI"
> set rs=cnn.execute("SQL QUERY")
> If rs.BOF then
> msgbox "No Records Found"
> else
> do until rs.EOF
> For intLoop = 0 To rs.Fields.Count - 1
> strText = strText & rs.fields(intLoop).value & " " & vbCrLf
> Next
> rs.MoveNext
> loop
> msgbox strText
> end if
> set rs = nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> *** Sent via Developersdex http://www.examnotes.net ***

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks Paulhttp://support.microsoft.com/default.aspx?scid=kb;en-us;70956
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>|||Sorry Sir this is not the answer I am expecting. Please follow my query.
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
>

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks PaulAnibran
Since you did not post DDL +samole data please look at this example removes
duplications
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Dear Uri Dimant
Please do not insert an primary key. As my table do not have any primary key
nor I am authorised to do that.
Please keep one row of the data which was present as duplicate.
Please do it in one query
<urid@.iscar.co.il> wrote in message
news:uxlNcYA5DHA.2008@.TK2MSFTNGP10.phx.gbl...
> Anibran
> Since you did not post DDL +samole data please look at this example
removes
> duplications
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||Anirban,
You simply can't do this as a single query. The only way to specify which
rows to delete is to identify those rows based on the values of their
columns. The column values of identical rows are identical, so no
expression can be true for one row and false for an identical row.
You can do this with a temporary table, and you can hide the use of a
temporary table by doing this with a trigger (which creates the temporary
inserted and deleted tables):
create table T (
CustomerID char(5)
)
insert into T
select CustomerID
from Northwind..Orders
go
create trigger T_del on T for delete as
if not exists (
select * from T
)
insert into T
select distinct * from deleted
go
delete from T
go
select * from T
order by CustomerID
go
drop table T
SK
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Thanks steve,
I was not sure some people keep telling me this is possible.
Are you sure this is not possible in sql 2000 also?
Thanks,
Paul
Steve Kass <skass@.drew.edu> wrote in message
news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
> Anirban,
> You simply can't do this as a single query. The only way to specify which
> rows to delete is to identify those rows based on the values of their
> columns. The column values of identical rows are identical, so no
> expression can be true for one row and false for an identical row.
> You can do this with a temporary table, and you can hide the use of a
> temporary table by doing this with a trigger (which creates the temporary
> inserted and deleted tables):
> create table T (
> CustomerID char(5)
> )
> insert into T
> select CustomerID
> from Northwind..Orders
> go
> create trigger T_del on T for delete as
> if not exists (
> select * from T
> )
> insert into T
> select distinct * from deleted
> go
> delete from T
> go
> select * from T
> order by CustomerID
> go
> drop table T
> SK
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||I don't think it matters whether you use 7.0 or 2000. It might be
possible in other RDBMS products, and it would be possible in SQL Server
if some sort of unique ROWID value were available on every table. Then
you could do
delete from T
where exists (
select *
from T T2
where T.column1 = T2.column1
and T.column2 = T2.column2
and -- same for all columns
and T.ROWID > T2.ROWID
)
But there is no exposed ROWID in any version of SQL Server
SK
Anirban wrote:
>Thanks steve,
>I was not sure some people keep telling me this is possible.
>Are you sure this is not possible in sql 2000 also?
>Thanks,
>Paul
>Steve Kass <skass@.drew.edu> wrote in message
>news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>>Anirban,
>>You simply can't do this as a single query. The only way to specify which
>>rows to delete is to identify those rows based on the values of their
>>columns. The column values of identical rows are identical, so no
>>expression can be true for one row and false for an identical row.
>>You can do this with a temporary table, and you can hide the use of a
>>temporary table by doing this with a trigger (which creates the temporary
>>inserted and deleted tables):
>>create table T (
>> CustomerID char(5)
>>)
>>insert into T
>>select CustomerID
>>from Northwind..Orders
>>go
>>create trigger T_del on T for delete as
>>if not exists (
>> select * from T
>>)
>>insert into T
>>select distinct * from deleted
>>go
>>delete from T
>>go
>>select * from T
>>order by CustomerID
>>go
>>drop table T
>>SK
>>"Anirban" <tulu_paul@.hotmail.com> wrote in message
>>news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
>>
>>Is there any query which will delete the dulpicate rows in all manner of
>>
>a
>
>>table.
>>But one row of each, duplicate row should remain the table after
>>
>deletion.
>
>>The table does not contain any primary key.
>>I donot want to use temporary table.
>>One query only no script or cursor.
>>Oracle uses rowid in this situation.
>>Do SQL Server have any trick to do it in one line?
>>
>>Thanks Paul
>>
>>
>>
>
>

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks PaulAnibran
Since you did not post DDL +samole data please look at this example removes
duplications
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>

---

|||Dear Uri Dimant
Please do not insert an primary key. As my table do not have any primary key
nor I am authorised to do that.
Please keep one row of the data which was present as duplicate.
Please do it in one query
<urid@.iscar.co.il> wrote in message
news:uxlNcYA5DHA.2008@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Anibran
> Since you did not post DDL +samole data please look at this example

---

removes

quote:

---

> duplications
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

---

|||Anirban,
You simply can't do this as a single query. The only way to specify which
rows to delete is to identify those rows based on the values of their
columns. The column values of identical rows are identical, so no
expression can be true for one row and false for an identical row.
You can do this with a temporary table, and you can hide the use of a
temporary table by doing this with a trigger (which creates the temporary
inserted and deleted tables):
create table T (
CustomerID char(5)
)
insert into T
select CustomerID
from Northwind..Orders
go
create trigger T_del on T for delete as
if not exists (
select * from T
)
insert into T
select distinct * from deleted
go
delete from T
go
select * from T
order by CustomerID
go
drop table T
SK
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>

---

|||Thanks steve,
I was not sure some people keep telling me this is possible.
Are you sure this is not possible in sql 2000 also?
Thanks,
Paul
Steve Kass <skass@.drew.edu> wrote in message
news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Anirban,
> You simply can't do this as a single query. The only way to specify which
> rows to delete is to identify those rows based on the values of their
> columns. The column values of identical rows are identical, so no
> expression can be true for one row and false for an identical row.
> You can do this with a temporary table, and you can hide the use of a
> temporary table by doing this with a trigger (which creates the temporary
> inserted and deleted tables):
> create table T (
> CustomerID char(5)
> )
> insert into T
> select CustomerID
> from Northwind..Orders
> go
> create trigger T_del on T for delete as
> if not exists (
> select * from T
> )
> insert into T
> select distinct * from deleted
> go
> delete from T
> go
> select * from T
> order by CustomerID
> go
> drop table T
> SK
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

---

|||I don't think it matters whether you use 7.0 or 2000. It might be
possible in other RDBMS products, and it would be possible in SQL Server
if some sort of unique ROWID value were available on every table. Then
you could do
delete from T
where exists (
select *
from T T2
where T.column1 = T2.column1
and T.column2 = T2.column2
and -- same for all columns
and T.ROWID > T2.ROWID
)
But there is no exposed ROWID in any version of SQL Server
SK
Anirban wrote:

quote:

---

>Thanks steve,
>I was not sure some people keep telling me this is possible.
>Are you sure this is not possible in sql 2000 also?
>Thanks,
>Paul
>Steve Kass <skass@.drew.edu> wrote in message
>news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>a
>
>deletion.
>
>
>

---

sql

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks Paulhttp://support.microsoft.com/defaul...=kb;en-us;70956
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>

---

|||Sorry Sir this is not the answer I am expecting. Please follow my query.
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> http://support.microsoft.com/defaul...=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>

---

cannot create connection

I created a report using Report Designer. The Dataset is working fine. In
fact, the Data tab shows me the expected query result. But...
when going to the Preview tab, I get "Cannot create a connection to data
source <name> . Login failed for user 'sa'.
Further info:
1) if I go to Tools / Connect to database and enter the info in "Datalink
properties" (server name / specific user name passw / database name) and hit
Test connection, I get "Connection succeeded"
2) During creation of data source for a SQL Server machine on the network
when connecting with winNT security I get an error while trying to access the
database list.
What am I doing wrong ' THnksHi,
Go to your "Shared Data Sources" click on the datasource you use for your
reports, open the properties by double clicking on the "credientials" tab
enter your password again and save it.
It should work.
Amarnath
"julio" wrote:
> I created a report using Report Designer. The Dataset is working fine. In
> fact, the Data tab shows me the expected query result. But...
> when going to the Preview tab, I get "Cannot create a connection to data
> source <name> . Login failed for user 'sa'.
> Further info:
> 1) if I go to Tools / Connect to database and enter the info in "Datalink
> properties" (server name / specific user name passw / database name) and hit
> Test connection, I get "Connection succeeded"
> 2) During creation of data source for a SQL Server machine on the network
> when connecting with winNT security I get an error while trying to access the
> database list.
> What am I doing wrong ' THnks
>|||Amarnath,
thanks a lot. It works like a charm.
Julio
"Amarnath" wrote:
> Hi,
> Go to your "Shared Data Sources" click on the datasource you use for your
> reports, open the properties by double clicking on the "credientials" tab
> enter your password again and save it.
> It should work.
> Amarnath
>
> "julio" wrote:
> > I created a report using Report Designer. The Dataset is working fine. In
> > fact, the Data tab shows me the expected query result. But...
> >
> > when going to the Preview tab, I get "Cannot create a connection to data
> > source <name> . Login failed for user 'sa'.
> >
> > Further info:
> >
> > 1) if I go to Tools / Connect to database and enter the info in "Datalink
> > properties" (server name / specific user name passw / database name) and hit
> > Test connection, I get "Connection succeeded"
> >
> > 2) During creation of data source for a SQL Server machine on the network
> > when connecting with winNT security I get an error while trying to access the
> > database list.
> >
> > What am I doing wrong ' THnks
> >
> >

Cannot create an instance of OLE DB Provider "MSDAC"

Hey Guys,
I'm trying to query a couple of Excel 2007 spreadsheets for some reports
using OpenDataSource in my query to pull in the data.
Everything is find with the preview inside the IDE, but when I deploy my
report I recieve the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'Regression'.
(rsErrorExecutingCommand)
Cannot create an instance of OLE DB provider "MSDASC" for linked
server "(null)".
I've installed 2007 Data Connectivity Components on the report server and
this allowed me to query the data while developing. I'm at a loss why this is
not working after I deploy.
The SQL I'm using to query the data is:
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="\\server\document.xlsm";Extended Properties="Excel 12.0
Macro;HDR=YES"')...Sheet1$
Any ideas/suggestions?My guess if this works in development and not in deployment that it has to
do with file security. The report is executed under a different user account
and that account doesn't have rights to the folder where the Excel data
resides. Just a guess because I have never done this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"shaggydog" <shaggydog@.discussions.microsoft.com> wrote in message
news:55853C99-FB58-422B-8FB7-EECD3EF37715@.microsoft.com...
> Hey Guys,
> I'm trying to query a couple of Excel 2007 spreadsheets for some reports
> using OpenDataSource in my query to pull in the data.
> Everything is find with the preview inside the IDE, but when I deploy my
> report I recieve the following error:
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'Regression'.
> (rsErrorExecutingCommand)
> Cannot create an instance of OLE DB provider "MSDASC" for linked
> server "(null)".
> I've installed 2007 Data Connectivity Components on the report server and
> this allowed me to query the data while developing. I'm at a loss why this
> is
> not working after I deploy.
> The SQL I'm using to query the data is:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
> 'Data Source="\\server\document.xlsm";Extended Properties="Excel 12.0
> Macro;HDR=YES"')...Sheet1$
>
> Any ideas/suggestions?
>|||Thanks for your response, while not directly related to file security, it was
a security problem. Here's what I did to solve my problem in case anyone
comes across this thread in the future...
I was using a shared data source which connected using windows
authentication. No matter how I tweaked and fiddled around I could not get
windows authentication to work.
So I setup a SQL account with limited rights. Then provided me with a new
error message stating:
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.
You must access this provider through a linked server.
While I have Ad hoc access enabled via Surface Configuration, it would not
allow me to perform an ad hoc query if the user account wasn't a sysadmin to
the SQL Server. Ugh!
I played around with tweaking the DisallowAdhocAccess registry setting for
the provider, but I didn't find a way to allow non-sysadmin accounts the
ability to perform ad hoc queries.
In the end using a SQL authentication with a sysadmin account allowed
OpenDataSource to pull in the external data.
For what it's worth....
"Bruce L-C [MVP]" wrote:
> My guess if this works in development and not in deployment that it has to
> do with file security. The report is executed under a different user account
> and that account doesn't have rights to the folder where the Excel data
> resides. Just a guess because I have never done this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "shaggydog" <shaggydog@.discussions.microsoft.com> wrote in message
> news:55853C99-FB58-422B-8FB7-EECD3EF37715@.microsoft.com...
> > Hey Guys,
> >
> > I'm trying to query a couple of Excel 2007 spreadsheets for some reports
> > using OpenDataSource in my query to pull in the data.
> >
> > Everything is find with the preview inside the IDE, but when I deploy my
> > report I recieve the following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'Regression'.
> > (rsErrorExecutingCommand)
> > Cannot create an instance of OLE DB provider "MSDASC" for linked
> > server "(null)".
> >
> > I've installed 2007 Data Connectivity Components on the report server and
> > this allowed me to query the data while developing. I'm at a loss why this
> > is
> > not working after I deploy.
> >
> > The SQL I'm using to query the data is:
> > SELECT *
> > FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
> > 'Data Source="\\server\document.xlsm";Extended Properties="Excel 12.0
> > Macro;HDR=YES"')...Sheet1$
> >
> >
> > Any ideas/suggestions?
> >
>
>

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
Jono
Jono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono
|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>
|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
JonoJono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono|||No, just hangs.
>--Original Message--
>Jono
>Any error message?
>|||No, just hangs.
>--Original Message--
>Jono
>Any error message?
>

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
JonoJono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>

Cannot connect using sql authentication in query analyzer

I just moved a database from one sql server to another. Both use Windows
2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
I connect to the database in the old server without a problem. I connect to
the new server fine with NT authentication, or if I log in as sa with SQL
authentication, but if I log in as a different user with SQL authentication
on the new server, I get this message:
Unable to connect to server Enterprise3:
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
Server is terminating this process.
Ooops - Just remembered one other piece that may be impartant: We were
swapping the two servers, so had to rename both. With SQL Server 7, that
means we had to do a mini-reinstall of the "corrupt" SQL Server. We've tried
to reapply the service packs and the hotfixes, but it will not allow it.
(Current installation is more recent.) This is not a problem with the SP3
server, but only with the sp4+ server.
"UWKC Admin" wrote:

> I just moved a database from one sql server to another. Both use Windows
> 2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
> service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
> I connect to the database in the old server without a problem. I connect to
> the new server fine with NT authentication, or if I log in as sa with SQL
> authentication, but if I log in as a different user with SQL authentication
> on the new server, I get this message:
> Unable to connect to server Enterprise3:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
> Server is terminating this process.
|||Hi
What do you call "mini-reinstall"? If you have problems, always do a full
install, anything else is not supported by Microsoft.
Regards
Mike
"UWKC Admin" wrote:
[vbcol=seagreen]
> Ooops - Just remembered one other piece that may be impartant: We were
> swapping the two servers, so had to rename both. With SQL Server 7, that
> means we had to do a mini-reinstall of the "corrupt" SQL Server. We've tried
> to reapply the service packs and the hotfixes, but it will not allow it.
> (Current installation is more recent.) This is not a problem with the SP3
> server, but only with the sp4+ server.
> "UWKC Admin" wrote:
|||Mike - Thanks for the question. When I say mini-reinstall, I mean the
reinstall that SQL Server demands we do after renaming a SQL Server 7.0
installation. It tells us something to the effect that that the
installstallation is bad and may have been tampered with, then tells us to
reinstall SQL Server. We do that, using the original SQL Server CD. I call
it a mini-install because it only takes a couple of minutes to complete.
"UWKC Admin" wrote:

> I just moved a database from one sql server to another. Both use Windows
> 2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
> service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
> I connect to the database in the old server without a problem. I connect to
> the new server fine with NT authentication, or if I log in as sa with SQL
> authentication, but if I log in as a different user with SQL authentication
> on the new server, I get this message:
> Unable to connect to server Enterprise3:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
> Server is terminating this process.
|||Did you run sp_dropserver and sp_addserver after you
changed the server name and updated the registry with the
SQL Server CD (the process you refer to as a mini-reinstall
just updates the server name entries in the registry)?
e.g.
sp_dropserver 'YourOldServerName'
go
sp_addserver 'YourNewServerName', local
This part of the process just updates the server name in the
sysservers table. You should run in on both servers that you
renamed.
I'm not sure this will make any difference as I don't
remember anyone getting access violations if they miss any
of the steps in renaming a server...but worth a try.
-Sue
On Thu, 9 Dec 2004 11:33:06 -0800, "UWKC Admin"
<UWKCAdmin@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mike - Thanks for the question. When I say mini-reinstall, I mean the
>reinstall that SQL Server demands we do after renaming a SQL Server 7.0
>installation. It tells us something to the effect that that the
>installstallation is bad and may have been tampered with, then tells us to
>reinstall SQL Server. We do that, using the original SQL Server CD. I call
>it a mini-install because it only takes a couple of minutes to complete.
>"UWKC Admin" wrote:

cannot connect to SQL Server via Query Analyzer

Hello, I am new to SQL and I would like to connect to a remote sql server.
I have Win 2k Professional installed at my PC and I have installation CD of
SQL Server 2000 Developer Edition.
I have chosen following options during installation:
SQL Server 2000 Components --> Install Database Server --> Local
Computer --> Create a new instance of SQL Server, or install Client
tools --> Client tools only
All installation process has looked good, no error message during it.
Now I am running SQL Query Analyzer and I am trying to connect to our remote
SQL server (which is certainly running) but without any success, the error
message "Unable to connect to server xxxxxx: SQL Server doesnot exist or
access denied." always will appear.
Maybe there is something wrong with my PC as only following ports are opened
there: 80, 135, 139, 445, 1025
I think that also the port 1434 should be opened but it is not and I cannot
open it.
Please help
MarkIf you are connecting to a default instance then, try to use the follwing
for your server name
tcp:<servername>,1433
replace <servername> with your server name.
and make sure the port 1433 is open.
This way we are forcing it to use the tcpip protocol on the specified port.
Be default the default instance listens on port 1433.
hth
--
Vikram Vamshi
Eclipsys Corporation
"Marcello" <marcello@.inetcom.cz> wrote in message
news:%23Ufg2GFIFHA.2860@.TK2MSFTNGP12.phx.gbl...
> Hello, I am new to SQL and I would like to connect to a remote sql server.
> I have Win 2k Professional installed at my PC and I have installation CD
> of
> SQL Server 2000 Developer Edition.
> I have chosen following options during installation:
> SQL Server 2000 Components --> Install Database Server --> Local
> Computer --> Create a new instance of SQL Server, or install Client
> tools --> Client tools only
> All installation process has looked good, no error message during it.
> Now I am running SQL Query Analyzer and I am trying to connect to our
> remote
> SQL server (which is certainly running) but without any success, the error
> message "Unable to connect to server xxxxxx: SQL Server doesnot exist or
> access denied." always will appear.
> Maybe there is something wrong with my PC as only following ports are
> opened
> there: 80, 135, 139, 445, 1025
> I think that also the port 1434 should be opened but it is not and I
> cannot
> open it.
> Please help
> Mark
>

cannot connect to SQL Server via Query Analyzer

Hello, I am new to SQL and I would like to connect to a remote sql server.
I have Win 2k Professional installed at my PC and I have installation CD of
SQL Server 2000 Developer Edition.
I have chosen following options during installation:
SQL Server 2000 Components --> Install Database Server --> Local
Computer --> Create a new instance of SQL Server, or install Client
tools --> Client tools only
All installation process has looked good, no error message during it.
Now I am running SQL Query Analyzer and I am trying to connect to our remote
SQL server (which is certainly running) but without any success, the error
message "Unable to connect to server xxxxxx: SQL Server doesnot exist or
access denied." always will appear.
Maybe there is something wrong with my PC as only following ports are opened
there: 80, 135, 139, 445, 1025
I think that also the port 1434 should be opened but it is not and I cannot
open it.
Please help
Mark
If you are connecting to a default instance then, try to use the follwing
for your server name
tcp:<servername>,1433
replace <servername> with your server name.
and make sure the port 1433 is open.
This way we are forcing it to use the tcpip protocol on the specified port.
Be default the default instance listens on port 1433.
hth
Vikram Vamshi
Eclipsys Corporation
"Marcello" <marcello@.inetcom.cz> wrote in message
news:%23Ufg2GFIFHA.2860@.TK2MSFTNGP12.phx.gbl...
> Hello, I am new to SQL and I would like to connect to a remote sql server.
> I have Win 2k Professional installed at my PC and I have installation CD
> of
> SQL Server 2000 Developer Edition.
> I have chosen following options during installation:
> SQL Server 2000 Components --> Install Database Server --> Local
> Computer --> Create a new instance of SQL Server, or install Client
> tools --> Client tools only
> All installation process has looked good, no error message during it.
> Now I am running SQL Query Analyzer and I am trying to connect to our
> remote
> SQL server (which is certainly running) but without any success, the error
> message "Unable to connect to server xxxxxx: SQL Server doesnot exist or
> access denied." always will appear.
> Maybe there is something wrong with my PC as only following ports are
> opened
> there: 80, 135, 139, 445, 1025
> I think that also the port 1434 should be opened but it is not and I
> cannot
> open it.
> Please help
> Mark
>

Cannot Connect to SQL Server

I have been unable to connect since Sat to a SQL 2000 Server. Get the following error when trying to connect through Query Analyzer across the network:

Unable to connect to server
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General Network Error

In my Event Log I have the following error:

17310: process_loginread: Process 2548 genertaed ftal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Sevrer is terminating the process

When I try to connect through Query Analyzer locally, it locks up when I type in the sa password

Any help would be appreciatedRE:
I have been unable to connect since Sat to a SQL 2000 Server. Get the following error when trying to connect through Query Analyzer across the network:
Unable to connect to server
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General Network Error
When I try to connect through Query Analyzer locally (at the Server Console??) , it locks up when I type in the sa password

Q1 Any help would be appreciated

A1 Some things to consider and / or check may include:

Verify that the Sql Service is running. Is security (still) configured for the connection (sa = standard login) being attempted? Verify that the correct protocol, port, etc. is being accessed.

{More detailed information should make it easier for others to provide more useful / helpful feedback.}|||SQL Server is running. SQL Server Agent Locks Up when you try to start it.|||RE:
SQL Server is running.

Q2 SQL Server Agent Locks Up when you try to start it.

Some issues are apparently now resolved?

A2 Consider trying to:

Verify that the Sql Agent Service security context is configured with valid account information, or try using a local system account. Check the logs (incl. agent logs) for errors. Again, in general the more information available, the easier it is for others to provide helpful feedback.|||Didn't you apply patch against Slammer on version prior to SP2?|||Hi I'm learning SQL on the fly but I hope I might be able to help a bit, even if it's a little bit.
1. Are the computers talking? Can you see the other computer on the network?
2. Were any programs installed recently? It might've replaced a DLL that SQL Server might've needed to logon.
3. Are you sure about the SA password and user? If not, check the user and password.
4. What's the login mode? Might be a good idea to check it out.
5. Hard disk might be dued for final rites.

Good luck.

All the best,

Ka Kwok|||Originally posted by ispaleny
Didn't you apply patch against Slammer on version prior to SP2?

What did this virus do? I have the same problem.|||Originally posted by adrian320
What did this virus do? I have the same problem.

From memory it's a DOS attack. It makes a massive amount of requests to the server.
Regards,
Ka.|||http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp take help to combat the issue.|||Thank you for all the input,

I applied the hotfix for the virus to the 2000 sql servers we have running and they seem to work fine know.

Thanks.