estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott

escaping single quotes

Hi,

I need to have an varchar value with single quotes. For eg: the below code throws compilation error.

Declare @.val VARCHAR(20)
SELECT @.val = ''+name+''
print @.val

Error: Invalid column 'name'

I want to print name enclosed with single quotes. Please guide me.

Regards,
SamDeclare @.val VARCHAR(20)
SELECT @.val = 'Kaiowas'
PRINT @.VAL

EDIT (added): SET @.VAL = @.VAL + '''Kaiowas'''
EDIT (added): PRINT @.VAL

SET @.VAL = @.VAL + '''' + @.VAL + ''''
PRINT @.VAL

escaping an apostrophe in MSSQL

Hi, i'm having problems executing the following

SET @.SQLAH = 'SELECT sub_id WHERE '
SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = Hotel'
EXEC(@.SQLAH)

Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:

..
SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = 'Hotel' '
..

But this escapes the string, how would i escape an apostrophe in a string?

I thought maybe:

SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = \'Hotel\' '

But no joy :confused:

ThanksAs far as I know MS SQL is using the ANSI standard for that: two single quotes:

SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = 'Hotel'''

(Don't know if it works inside a procedure though)|||hi

nope, it doesnt work - i'm using a stored procedure
:eek:|||SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = ''Hotel'' '-PatP|||thats the badger! cheerssql

Escape special characters in query?

How can I escape the character " in the query designer?
This is my MDX query:
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
len([Konsern].currentmember.Properties("Caption") > 0)) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
My problem is that the work Caption needs a " on each side. My query works
in the MDX Sample Application, but it fails if I use the " in the Report
Designer - and it fails if I don't use them...
Without the "'s:
An error has occured during report processing.
Query execution failed for data set PB_AxpCmpWMB_Project1'
Formula error - syntax error - token is not valid:
"filter([Konsern].members,
len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
With the "'s in Report Designer Data tab:
x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant must
contain exactly one character.
How can I use my query with "s?
All help appreciated!
Kaisa M. LindahlI found out. You escape " with an additional ".
And then I had to change my query again, as the Report Designer didn't like
me using (len).
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
([Konsern].currentmember.Properties(""Caption"") > """")) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
Kaisa
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:eylH2R6$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> How can I escape the character " in the query designer?
> This is my MDX query:
> ="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
> HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt
fakturert
> timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT
Salgspris]}
> on 0,
> non empty filter([Konsern].members,
> len([Konsern].currentmember.Properties("Caption") > 0)) on 1
> from [Prosjekt Komplett]
> where ([Transaksjonstype].[Alle transaksjonstyper].[" &
> Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
> My problem is that the work Caption needs a " on each side. My query works
> in the MDX Sample Application, but it fails if I use the " in the Report
> Designer - and it fails if I don't use them...
> Without the "'s:
> An error has occured during report processing.
> Query execution failed for data set PB_AxpCmpWMB_Project1'
> Formula error - syntax error - token is not valid:
> "filter([Konsern].members,
> len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
> With the "'s in Report Designer Data tab:
> x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
> 'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant
must
> contain exactly one character.
> How can I use my query with "s?
> All help appreciated!
> Kaisa M. Lindahl
>

Escape sequence

Hi,
I have a question with this query -
SELECT * FROM table1 WHERE column1 = 'T_C_%';

This query returns rows where column1 = "T_Care", "T_CRP" etc etc, whereas I was expecting only rows where column1 = "T_C_Tail", "T_C_Head"

However, when I use an escape character(/), the results are more in the lines of the expected results.

Can somebody explain this?
cheers/- PradeepIn a LIKE expression, any underscore (_) matches any one character, and any percent sign (%) matches any group of characters. You can also use brackets ([]) to define sets of characters.

See the BOL description of LIKE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp) for more details.

-PatP|||Thanks Pat, really appreciate your quick response.
-Pradeepsql

Errors while upsizing Access database to SQLS2k

I am trying to upsize an Access database to SQL Server 2000. I select
"Create new database" option.
I select "LOCAL" as the database. and check [x] use trusted connection.
SQL server is on this local machine. I supply a name for the new
database and click [Next>] and I get the following error.
================================================== ===============
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.
================================================== ===============
Why am I getting these errors?
SQL Server is installed to accept mixed mode (or Windows authentication
mode)
SQL Server is running.
VS.Net Professional is loaded on this system so an ODBC driver should
be available.
What steps, other than I am taking should I be doing?
Are you doing this on the machine that MSDE is running on? If you are trying
to do this remotely then you will not be able to connect to the database
server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter to your
installation. Also since you installed a named instance you will need to
supply the full name to the instance, which in your case will be <your
machine name>\HipMSDE. (local) will not work as the database server name, as
it will try to connect to a server with only the same name as the machine
and not the named instance.
Jim
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
>I am trying to upsize an Access database to SQL Server 2000. I select
> "Create new database" option.
> I select "LOCAL" as the database. and check [x] use trusted connection.
> SQL server is on this local machine. I supply a name for the new
> database and click [Next>] and I get the following error.
> ================================================== ===============
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access denied.
> ================================================== ===============
> Why am I getting these errors?
> SQL Server is installed to accept mixed mode (or Windows authentication
> mode)
> SQL Server is running.
> VS.Net Professional is loaded on this system so an ODBC driver should
> be available.
> What steps, other than I am taking should I be doing?
>
|||Jim,
Thank you for your reply.
When I changed the name to the name of my computer "SEDNA", it worked!
I thought "(local)" was the proper name to use because the upsizing
wizard offered it in the list. I thought it "discovered" that name, and
after all the name in Enterprize manager is "(local)" not "SEDNA". Now
that you let me in on this sectret, I know. Thank you for tell me the
other scret, that I can upsize to MSDE using "SEDNA\HipMSDE". There is
know whay I could have guessed this!
This all frustrates me very much! How am I supposed to know to use
"SEDNA" rather than "(local)" when "(local)" is offered to me in the
list? and Enterprize manager does not use "SEDNA"? How am I supposed to
know the sintax "SEDNA\HipMSDE" when that is NOT offered in the list?
How am I supposed to know the secret code "SEDNA\HipMSDE"? Nothing
anywhere tells me this sintax!!?!

> it will try to connect to a server with only the same name as the
machine
> and not the named instance
Where the #%$* am I supposed to discover this! I've been reading these
instructions for two days!!!?! I have not seen anything even hinting to
this.
Why does the system mislead me and accept "(local)" under othere
contexts such as VB.NET connection strings. It was the VB.NET
connection string context that lead me to guess "SEDNA" because that
works in a connection string even though the name of the instance is
NOT!!!! "SEDNA", its the name of the machine.
Where do I learn all these mysteries?
Doug
Jim Young wrote:
> Are you doing this on the machine that MSDE is running on? If you are
trying
> to do this remotely then you will not be able to connect to the
database
> server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter
to your
> installation. Also since you installed a named instance you will need
to
> supply the full name to the instance, which in your case will be
<your
> machine name>\HipMSDE. (local) will not work as the database server
name, as
> it will try to connect to a server with only the same name as the
machine[vbcol=seagreen]
> and not the named instance.
> Jim
> "dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
> news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
select[vbcol=seagreen]
connection.[vbcol=seagreen]
exist[vbcol=seagreen]
authentication[vbcol=seagreen]
should[vbcol=seagreen]
|||hi,
dbuchanan wrote:
> This all frustrates me very much! How am I supposed to know to use
> "SEDNA" rather than "(local)" when "(local)" is offered to me in the
> list? and Enterprize manager does not use "SEDNA"? How am I supposed
> to know the sintax "SEDNA\HipMSDE" when that is NOT offered in the
> list? How am I supposed to know the secret code "SEDNA\HipMSDE"?
> Nothing anywhere tells me this sintax!!?!
>.....
http://msdn.microsoft.com/library/de...setup_2xmb.asp
you can download BOL (BookOnLine) from
http://www.microsoft.com/sql/techinf...2000/books.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

ERRORS on Insert/Update DB2 from SQL Enterprise Manager (linked server)

I've set up an AS400/DB2 as a linked server, all standard configs (V4R5) on a SQL server 2000 machine and can select all day long. When I go to update or insert it throws an error referencing my driver. I'm using the HIS/OLEDB2 driver. Any ideas? Someone mentioned turning on Journaling on the 400?

Anything at this point would helpRE:
Q1 I'm using the HIS/OLEDB2 driver and can select all day long. When I go to update or insert it throws an error referencing my driver. Any ideas? Anything at this point would help

A1 Perhaps DB2 Connect provides a better odbc driver? Per IBM, DB2 Connect: "Provides extensive application programming tools for developing client-server and web applications using industry standard APIs such as ODBC, ADO, OLE DB, JDBC, SQLJ, DB2 CLI and Embedded SQL."

See: http://www-3.ibm.com/software/data/db2/db2connect/|||I found an answer that worked from a colleague. Thanks.

Error:Procedure or function CatalogJSummary has too many arguments specified

hello, all

I am using a Stored procedure and calling this in my code in C#.

Here is my procedure:

CatalogJSummary

(

@.SeriesId INT

)

AS

SELECT Games.GameName, Games.Id AS GameId

FROM GameCodes INNER JOIN

Games ON GameCodes.GameId = Games.Id INNER JOIN

MobileSeries ON GameCodes.SeriesId = MobileSeries.Id INNER JOIN

Mobiles ON MobileSeries.Id = Mobiles.SeriesId

GROUP BY Games.GameName, Games.Id, MobileSeries.Id

HAVING (MobileSeries.Id = @.SeriesId)

RETURN

and here is the calling C# code::

public DataSet CatalogJSummary(int sid)

{

SqlConnection cnUnique=new SqlConnection();

cnUnique.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["jconnection"];

//con.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["jconnection"];

com.CommandText="CatalogJSummary";

com.CommandType=CommandType.StoredProcedure;

com.Parameters.Add("@.SeriesId",SqlDbType.Int);

com.Parameters["@.SeriesId"].Value=sid;

com.Connection=cnUnique;

// con.Open();

cnUnique.Open();

adp=new SqlDataAdapter();

adp.SelectCommand=com;

DataSet ds1=new DataSet();

adp.Fill(ds1);

cnUnique.Close();

return ds1;

}

but when this code execute,with the parameter passed to stored procedure using this code at runtime, i get the following error::

Procedure or function CatalogJSummary has too many arguments specified.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function CatalogJSummary has too many arguments specified.

Source Error:

Line 243:adp.SelectCommand=com; Line 244:DataSet ds1=new DataSet(); Line 245:adp.Fill(ds1); Line 246:cnUnique.Close(); Line 247:return ds1;

Source File: c:\inetpub\wwwroot\mobmasti.catalog\classes\truetonescls.cs Line: 245

Anyone please help me to resolve this error

thanks in advance

There are many options you can try:

1. From SQL Server books online:
If the first three characters of the procedure name are sp_, SQL Server
searches the master database for the procedure. If no qualified procedure name is provided, SQL Server searches for the procedure as if the owner name is dbo. To resolve the stored procedure name as a user-defined stored procedure with the same name as a system stored procedure, provide the fully qualified procedure name.

2. Make sure the field names between your calls and the procedure are the same

3. Make sure you attached the database with the user it was intended to be used with. Otherwise you may not have sufficient rights and this message will show up.