can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atx
Tempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>
|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

Can not created Named Calculation...

I can not seem to create a Named Calculation on a table that I have already created a Named Query on in my data source view. Any idea why this is and if there is a work around for it?

TIA

Wellman

If you have already a Named Query you can add an expression-based column to the SELECT statement using the syntax of the underlying data source.|||

Thank you for this information. Any idea why this is so? Doesn't make much sense to me.

Also, can I reference other tables as well?

TIA

Wellman

|||Which part doesn't make sense? You can view a named query as a SQL view. Just like a SQL view, your SELECT statement can include any valid syntax, including other tables, calling UDF, etc.

Can not connect to SQL Server from DMZ

We just moved to a new office. After setting up the servers, we found
that the apllication (including SQL Query Analyzer) in DMZ can not
access SQL server that is behind the firewall. We can ping the SQL
server from the server in DMZ and we also checked and confirmed port
1433 is open from the firewall. Could anyone help me out?
Thanks,Run the following test:
ISQL -Stcp:sqlnamehere -Uuserid -Ppassword -Q"select @.@.version"
Post the Error message.
Winsock Error messages should be returned if it fails.
Example:
11001 == Host Not Found
10060 == Connection Timeout
10061 == Connection Refused
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Can not browse sql 2000 tables from SSMS

Hello,

I get an error message( see below) when I try to browse tables of a SQL2k server from SSMS, but I can browse those tables in sql query analyzer with no problems.

Do I need to change any settings in SSMS to work around this error?

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

BUTTONS:

OK

thanks,
Saptagiri

Please file a defect report for this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

Reports filed on the feedback center are used to prioritize work in future versions and service packs. You can even vote for other the bugs reports filed by other people. The more votes a defect gets, the higher its priority is for being fixed.

Thanks,
Steve

Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

sql

Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA
|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas

"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can Iif( do a substring query on a storage field?

Ok,
This is what I don't understand. Hidden rows with downline dealers
only has '5678'. But the visible row that needs a drill down might
have in the dealerlineageid field (which is a field not to be
displayed)
'5678' and '2754' and 10 other dealer numbers (multiple dealer
numbers).
How would I know if '5678' is in there as a string value (which is just
one of the many numbers in that field)? If it has multiple
dealer numbers separated by commas? Wouldn't the expression for the
"hidden" value need to be a substring query? How would I do that?
Thanks,
TrintI always prefer preparing the data from base on. Where do you get the data
you want to render in the report, is there a chance to normalize it before
passing it to the report ?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"trint" <trinity.smith@.gmail.com> schrieb im Newsbeitrag
news:1113916563.747603.202200@.l41g2000cwc.googlegroups.com...
> Ok,
> This is what I don't understand. Hidden rows with downline dealers
> only has '5678'. But the visible row that needs a drill down might
> have in the dealerlineageid field (which is a field not to be
> displayed)
> '5678' and '2754' and 10 other dealer numbers (multiple dealer
> numbers).
> How would I know if '5678' is in there as a string value (which is just
> one of the many numbers in that field)? If it has multiple
> dealer numbers separated by commas? Wouldn't the expression for the
> "hidden" value need to be a substring query? How would I do that?
> Thanks,
> Trint
>|||No, it comes from a group within our company that is dedicated only
toward sql server and I am only in c#.net and reporting services.
Trint

Can Iif be used to change query result?

I'm converting a set of queries from Access to work as stored procedures on SQL server, and one of them that uses IIF gives me a syntax error. Here's the WHERE clause of the SELECT:

WHERE IIF(@.myExtNum > 0,D.ExtentionNumber=@.myExtNum,'') AND ...

I get the error message "syntax error near '>'."

It would seem this query wants to do two things: 1) if @.myExtNum is >0, return only the rows for which D.ExtentionNumber equals a user specified value, or 2) if @.myExtNum is 0 ignore this part of the condition.

I can rewrite this using multiple ANDs and ORs, but I wodered if there was a way I could get IIF working.

Something in my gut tells me this is not going to work, that IIF is being used to modify the query; that is, change which rows are returned; it is not being used to change how a given data value is displayed, which I think was the purpose for which IIF was originally intended.

Does anyone have an insight on this?

Hello,

Sorry, but IIF isn't a valid T-SQL function. You could do this instead (if D.ExtentionNumber is not nullable):

where D.ExtentionNumber =

case

when @.myExtNum > 0 then @.myExtNum

else D.ExtentionNumber

end

If it is nullable, do this instead (the -1 is an arbitrary value that is not available to the field):

where coalesce(D.ExtentionNumber, -1) =

case

when @.myExtNum > 0 then @.myExtNum

else coalesce(D.ExtentionNumber, -1)

end

Hope this helps.

Jarret

Can I write query like this?

hi, guys

Can I write query like this:
I need to get the amount of stores that have @.PID inventory less than 2
select count(*) from
(
select StoreID, sum(Inventory) as Inv from products where productID = @.PID group by storeID
) StoreInv where StoreInv.Inv <= 2

thanks.

Yes, you can. Your query is valid so long as you use an alias name for the resultset generated by a nested query. Following is a very simple example to get the point across quickly:

Select Count(*) From (Select State from Authors) A

Can I write query like this

hi, guys
Can I write query like this:
I need to get the amount of stores that have @.PID inventory less than 2
select count(*) from
(
select StoreID, sum(Inventory) as Inv from products where
productID = @.PID group by storeID
) StoreInv where StoreInv.Inv <= 2
thanks.select StoreID, sum(Inventory) as Inv from products
where productID = @.PID
group by storeID
HAVING sum(Inventory) < 2
--
"Nick" wrote:

> hi, guys
> Can I write query like this:
> I need to get the amount of stores that have @.PID inventory less than 2
> select count(*) from
> (
> select StoreID, sum(Inventory) as Inv from products where
> productID = @.PID group by storeID
> ) StoreInv where StoreInv.Inv <= 2
> thanks.
>|||Select count(*) from
(
select StoreID, sum(Inventory) as Inv from products
where productID = @.PID
group by storeID
HAVING sum(Inventory) < 2
)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:711CF005-0EEF-4A2A-895C-5DFBAA3DC031@.microsoft.com...
> select StoreID, sum(Inventory) as Inv from products
> where productID = @.PID
> group by storeID
> HAVING sum(Inventory) < 2
> --
>
> "Nick" wrote:
>|||Oops! Misread the original posting. Thanks for the correction.
--
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Jim Underwood" wrote:

> Select count(*) from
> (
> select StoreID, sum(Inventory) as Inv from products
> where productID = @.PID
> group by storeID
> HAVING sum(Inventory) < 2
> )
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:711CF005-0EEF-4A2A-895C-5DFBAA3DC031@.microsoft.com...
>
>

can i using linked Server in mssql server 64 bit version?

hi,
i always set the error when i used linked server to query data from other
server(32 bit) to my main server (64bit)
"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete
this operation could not be found on the server (they were supplied with the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005: ]."
do i miss something or i can using linked server in 64 bit edition?
thanks,
Suwito Kurniawan
Hi
Make sure that you have MDAC 2.8 on the 32 bit machine.
You can also run a Profiler trace to see what is being sent though.
SQL Server 64 and 32 can be linked to each other, in both directions,
without any problems.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Suwito Kurniawan" <suwito@.adira.co.id> wrote in message
news:#neP7OiLFHA.2764@.tk2msftngp13.phx.gbl...
> hi,
> i always set the error when i used linked server to query data from other
> server(32 bit) to my main server (64bit)
> "Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: The stored procedure required to
complete
> this operation could not be found on the server (they were supplied with
the
> ODBC setup disk for the SQL Server driver). Please contact your system
> administrator.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
> returned 0x80004005: ]."
> do i miss something or i can using linked server in 64 bit edition?
>
> thanks,
> Suwito Kurniawan
>

can i using linked Server in mssql server 64 bit version?

hi,
i always set the error when i used linked server to query data from other
server(32 bit) to my main server (64bit)
"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to comp
lete
this operation could not be found on the server (they were supplied with the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowse
t
returned 0x80004005: ]."
do i miss something or i can using linked server in 64 bit edition?
thanks,
Suwito KurniawanHi
Make sure that you have MDAC 2.8 on the 32 bit machine.
You can also run a Profiler trace to see what is being sent though.
SQL Server 64 and 32 can be linked to each other, in both directions,
without any problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Suwito Kurniawan" <suwito@.adira.co.id> wrote in message
news:#neP7OiLFHA.2764@.tk2msftngp13.phx.gbl...
> hi,
> i always set the error when i used linked server to query data from other
> server(32 bit) to my main server (64bit)
> "Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: The stored procedure required to
complete
> this operation could not be found on the server (they were supplied with
the
> ODBC setup disk for the SQL Server driver). Please contact your system
> administrator.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRow
set
> returned 0x80004005: ]."
> do i miss something or i can using linked server in 64 bit edition?
>
> thanks,
> Suwito Kurniawan
>

can i using linked Server in mssql server 64 bit version?

hi,
i always set the error when i used linked server to query data from other
server(32 bit) to my main server (64bit)
"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete
this operation could not be found on the server (they were supplied with the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005: ]."
do i miss something or i can using linked server in 64 bit edition?
thanks,
Suwito KurniawanHi
Make sure that you have MDAC 2.8 on the 32 bit machine.
You can also run a Profiler trace to see what is being sent though.
SQL Server 64 and 32 can be linked to each other, in both directions,
without any problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Suwito Kurniawan" <suwito@.adira.co.id> wrote in message
news:#neP7OiLFHA.2764@.tk2msftngp13.phx.gbl...
> hi,
> i always set the error when i used linked server to query data from other
> server(32 bit) to my main server (64bit)
> "Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: The stored procedure required to
complete
> this operation could not be found on the server (they were supplied with
the
> ODBC setup disk for the SQL Server driver). Please contact your system
> administrator.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
> returned 0x80004005: ]."
> do i miss something or i can using linked server in 64 bit edition?
>
> thanks,
> Suwito Kurniawan
>

can i use single function for a big project ??...

Hi,

I wnat to write a single function in code behind file for all DML operation, and i just want to pass query string in its parameter. but when ever my whole project will use this function to insert, update and delete. what will be the performance.

bool DMLoperation(string str){obj.conn =new SqlConnection(obj.connstring);obj.cmd =new SqlCommand(str,obj.conn);obj.conn.Open();try{if(obj.cmd.ExecuteNonQuery() > 0){obj.conn.Close();return true;}else{obj.conn.Close();return false;}}catch{obj.conn.Close();return false;}}

I am totally confused about performance, is it good idea or worst ?.

Are you going to create a SQL manager for your project?
seems it's better for you to overload SQL object other then just pass-in the sql string and execute (just my opinion)

What i can only share is my company did the same things, ie. build a customized SQL manager for SQL execution,
although performace is a key issue, you should also consider other benfit that come from this design; such as you
can run extra SQL within each SQL call, such as SQL logging;

if you build the Manager in clean and well design, i believe performance is not a problem.

Hope this help

|||

My dear, can you send some snippet of code, to use by overloading SQL object, It may be help me