SQL server questions: helloi

Showing posts with label helloi. Show all posts

Tuesday, March 27, 2012

Aborting CALL to stored procedure

Hello

I am calling a stored procedure in a MSDE/SQLServer DB form within my
Visual C++ 6.0 program along the lines
CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
With
DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
It all works sweet as, but it can take a while and I want to let the
user abort it.
Everything I've tried ends in tears.Hi

You can issue a KILL command on the SQL Server which will terminate the
process. To do this you are going to need a separate thread. More
information in books online.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> Hello
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.|||I have the command running in a separate thread.
I dont want to kill the server, just the CALL. I have tried killing
the thread and using .Abort(), and most other things I can think of,
but everything results in my program crashing.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> Hi
> You can issue a KILL command on the SQL Server which will terminate the
> process. To do this you are going to need a separate thread. More
> information in books online.
> John
> "Mike Brown" <browna@.beer.com> wrote in message
> news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > Hello
> > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > Visual C++ 6.0 program along the lines
> > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > With
> > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > It all works sweet as, but it can take a while and I want to let the
> > user abort it.
> > Everything I've tried ends in tears.|||Hi

I am not sure what you mean by killing the server. Look up the KILL command
in books online.
Killing your thread should not result in the program crashing, but may leave
an orphaned process on the SQL server.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.
> > John
> > "Mike Brown" <browna@.beer.com> wrote in message
> > news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > > Hello
> > > > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > > Visual C++ 6.0 program along the lines
> > > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > > With
> > > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > > It all works sweet as, but it can take a while and I want to let the
> > > user abort it.
> > > Everything I've tried ends in tears.|||John Bell (jbellnewsposts@.hotmail.com) writes:
> I am not sure what you mean by killing the server. Look up the KILL
> command in books online.

And Books Online says:

KILL permissions default to the members of the sysadmin and processadmin
fixed database roles, and are not transferable.

And Mike wants to give his users away to cancel their running commands.

And killing the entire connection would be a huge overkill anyway, when
all you want to do is to cancel the current batch.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Mike Brown (browna@.beer.com) writes:
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.

You don't say much of what you have tried. Then again, I will have to
admit that I have no experience of OLE DB Consumer templates, although
I've recently started to program against SQLOLEDB.

But I can't see but that to do this, you need to use asynchrounous
execution. The MDAC Books Online says:

Consumers that want to asynchronously open a rowset set the
DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property.
When setting this bit prior to calling ICommand::Execute,
IOpenRowset::OpenRowset, IDBSchemaRowset::GetRowset,
IRowPosition::GetRowset, IColumnsRowset::GetColumnsRowset,
IMultipleResults::GetResult, ISourcesRowset::GetSourcesRowset, or any
other method that returns a rowset, riid must be set to
IID_IDBAsynchStatus, IID_IConnectionPointContainer, or IID_IUnknown.
...
To cancel creation of the rowset, the consumer can call
IDBAsynchStatus::Abort or can simply release all interfaces on the
rowset. Once the rowset's reference count goes to zero, any
asynchronous processing is canceled and the rowset is released. Calling
IDBAsynchStatus::Abort still requires releasing the interface.

If you don't do it asynchrounously... I guess you could start to
release things from another thread, but I'm not surprised if it ends
in tears...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John was referring to the T-SQL 'KILL' command, not the unix kill command.

Tuesday, March 20, 2012

a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------

a too long query MAX(CASE WHEN

Hello

I am using an allready Full database MS SQL 2000

my 3 tables -->

Report :
ReportID (PK)
RName
RValue

Product :
PName
Category
ReportID (FK)

Infos :
IComments
IVaLue

my query (to get a new table with only columns, or a .NETcollection) -->

SELECT

Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,

Infos.Commentar AS IC,

MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50,
MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54,
MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78,
MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540,
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421

FROM

Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue

WHERE (Report.ReportID = 10)

GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.IComments

Report.ReportID = Product.ReportID --> Primary Key to Foreign Key
Report.RValue = Infos.IValue --> only on full text (100 char)

they are not indexed

in Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infos

it can be very long
how can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)

thank youHi

This is some sort of odd pivot but I don't understand what your problem is or what you would like to accomplish. Please could you elaborate?|||on 3 tables i have columns or rows , i want to get only columns
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421 makes a column from a row|||It does indeed. So what is the problem? :)

EDIT - oh hang on - do you mean you want zero rows??|||i want to find a better way if exists|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH|||you think a union will be really faster ?|||you think a union will be really faster ?I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.|||ok i try it

thank you|||I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.

Just FYI,
Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries.
The data types must be compatible|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH

UNION ALL if no dupes.|||As mentioned in my next post :)

Monday, March 19, 2012

A to Z table data

Hello

I am currently using ms sql 2000 and I want to display my table, column of names in alphabetical order. How can I achieve this?

Thanks

Laura

Did you mean to dispaly all column names of a table in alphabetical order? In T-SQL we use 'ORDER BY' clause in query to perform ordering. So let's use such a statement to achieve your request:

select * from syscolumns where id=object_id('myTable') order by name

|||

Thanks for the reply. Thanks also for the solution.

I was actually thinking about when I had previously created a database using Access. It had a nice easy to use feature that could be used on a column whilst building the database. When you click on the column within the database you can choose to display in assending or decending order. I was looking for such a feature in SQL but so far I have not found it. Does this feature exist? If so where is it?

Thanks

Laura

|||

Sure there is. In SQL we use 'ORDER BY' clause to sort result. You can also sort result returned in Enterprise Manager: Just rigth click a table->choose 'Open Table'->'Query', then in the 'Diagram Pane' add some columns, and you can choose 'Sort Type'&'Sort Order' for each column. Then click 'Run' to execute the query. You can press F1 in 'Diagram Pane' to get more help from SQL2000 Books Online.

Friday, February 24, 2012

A question about an error message

Hello!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC

GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!I don't think you can have dbo.table.* and probably that's what the
error says when it tried to expand dbo.view_results_7a.*
...not sure though...

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

Hello!
>
I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:
>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
>
ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC
>
GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>
The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

|||Hi,
I modified your query a little bit:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

|||Hi

Thanks for your answer, but the problem still exists. There is the same
error message like before.

othellomy@.yahoo.com schrieb:

Quote:

Originally Posted by

>
ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC
>

I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
from another database but on the same server, so I have to write in
this way:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

The error is still the same :-(|||(Comagmbh@.gmx.de) writes:

Quote:

Originally Posted by

I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:
>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
>
ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC
>...
The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

Didn't you get a complete error message? That should tell you what is
wrong.

But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
out of nowhere. I don't know what is supposed to be, so I can't suggest
an alternative. But I would not expect the above to work on MySQL either.

Two more things:
1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
but they can result in extra processing. In SQL 2000 it may seem that
if you run a SELECT on the view that you always get data in the order
of the ORDER BY clause, but that is mere chance, and it does not happen
that easily in SQL 2005.

2) Try to remove the COLLATE clause. Adding to the query when it is not
needed can prevent indexes from being used and hamper performance.

Quote:

Originally Posted by

>
Didn't you get a complete error message? That should tell you what is
wrong.
>
But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
out of nowhere. I don't know what is supposed to be, so I can't suggest
an alternative. But I would not expect the above to work on MySQL either.
>
Two more things:
1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
but they can result in extra processing. In SQL 2000 it may seem that
if you run a SELECT on the view that you always get data in the order
of the ORDER BY clause, but that is mere chance, and it does not happen
that easily in SQL 2005.
>
2) Try to remove the COLLATE clause. Adding to the query when it is not
needed can prevent indexes from being used and hamper performance.
>

Hi! Okay I wrote this:

ALTER VIEW view_results_7
AS
SELECT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
table.MNC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI

But there is still this error message:

Server: No 107, 16, state 3 procedure view_results_7
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."|||(Comagmbh@.gmx.de) writes:

Quote:

Originally Posted by

Hi! Okay I wrote this:
>
ALTER VIEW view_results_7
AS
SELECT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
table.MNC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI
>
>
But there is still this error message:
>
Server: No 107, 16, state 3 procedure view_results_7
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."

What ugly environment are you using that only gives the unexpanaded
error message?

In any case, that IMSI_Blacklist..tblIMSI_Stiering_Blacklist still
comes out of nowhere. You need to mention it in the FROM-JOIN clause.

Besides, you have something called "table" in the query above. Since
TABLE is a reserved keyword, I would expect that to yield a syntax error,
so I suspect that you are not even posting the query you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
As suggested by Erland, table is a reserve keyword therefore you
can't have it in your query. I thought you are using table to
reference some pseodo table in the from clause so I used that because
you did not post the actual query. You have to post the actual query
for two reasons. Reason 1: I can see if you are using the 'table'
keyword in your query. If yeas, then it is obvious that was the error.
Number 2: if you have a table name in the select clause but missing
from the from clause then it will also cause error and also if you have
more tables in the from clause but not joining them appropriately in
the join or where clasue then it will be error too. So, the best thing
is if you post the actual query exactly as is.

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

Hi
>
Thanks for your answer, but the problem still exists. There is the same
error message like before.
>
othellomy@.yahoo.com schrieb:
>

Quote:

Originally Posted by

>
>
I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
from another database but on the same server, so I have to write in
this way:
>
ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC
>
The error is still the same :-(

Tuesday, March 27, 2012

Aborting CALL to stored procedure

Tuesday, March 20, 2012

a tricky query

a too long query MAX(CASE WHEN

Monday, March 19, 2012

A to Z table data

Friday, February 24, 2012

A question about an error message

SQL server questions

Blog Archive

About Me