Error installing SQL Server 2000 trial

I understand that to install the Enterprise Manager, I need to download the entire trial version and then select only those components I want to install. After downloading the SQLEVAL.EXE file, attempting to run it generates the following error message:
"There is not enough space on drive c:\ to extract this package
Please free up 439.51 MB and click Retry"
But my hard drive shows over 40 GB of free space. Running XP Home Edition. Machine is 2 weeks old.
You are hitting the classical 4GB free space boundary problem. Create a dummy file of a few hundred MB and you
should be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ramking" <anonymous@.discussions.microsoft.com> wrote in message
news:200CA639-00E3-4169-AE97-21F2D8F3B2CA@.microsoft.com...
> I understand that to install the Enterprise Manager, I need to download the entire trial version and then
select only those components I want to install. After downloading the SQLEVAL.EXE file, attempting to run it
generates the following error message:
> "There is not enough space on drive c:\ to extract this package
> Please free up 439.51 MB and click Retry"
> But my hard drive shows over 40 GB of free space. Running XP Home Edition. Machine is 2 weeks old.
|||a few hundred MB? I have over 40 GB free. I will have to create a 36 GB file?
|||No, it is just when you are on a 4GB boundary (evenly divisable with 4GB). An old bug in the setup program
where some internal variable wraps around.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ramking" <anonymous@.discussions.microsoft.com> wrote in message
news:32FED02A-0E85-44D1-89B9-76DAE2938465@.microsoft.com...
> a few hundred MB? I have over 40 GB free. I will have to create a 36 GB file?

Error installing SQL Server 2000 trial

I understand that to install the Enterprise Manager, I need to download the
entire trial version and then select only those components I want to install
. After downloading the SQLEVAL.EXE file, attempting to run it generates th
e following error message:
"There is not enough space on drive c:\ to extract this package
Please free up 439.51 MB and click Retry"
But my hard drive shows over 40 GB of free space. Running XP Home Edition.
Machine is 2 weeks old.You are hitting the classical 4GB free space boundary problem. Create a dumm
y file of a few hundred MB and you
should be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ramking" <anonymous@.discussions.microsoft.com> wrote in message
news:200CA639-00E3-4169-AE97-21F2D8F3B2CA@.microsoft.com...
> I understand that to install the Enterprise Manager, I need to download the entire
trial version and then
select only those components I want to install. After downloading the SQLEV
AL.EXE file, attempting to run it
generates the following error message:
> "There is not enough space on drive c:\ to extract this package
> Please free up 439.51 MB and click Retry"
> But my hard drive shows over 40 GB of free space. Running XP Home Edition. Machin
e is 2 weeks old.|||a few hundred MB? I have over 40 GB free. I will have to create a 36 GB fi
le?|||No, it is just when you are on a 4GB boundary (evenly divisable with 4GB). A
n old bug in the setup program
where some internal variable wraps around.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ramking" <anonymous@.discussions.microsoft.com> wrote in message
news:32FED02A-0E85-44D1-89B9-76DAE2938465@.microsoft.com...
> a few hundred MB? I have over 40 GB free. I will have to create a 36 GB file?[/c
olor]

Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

error in sql statement using if statement

can anyone one help me with this statement.

select if tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

im quite new to sql and cant figure out the problem

can anyone help
colin

Colin:

See if this works better:

if ( select tdy.classyear
from tei_degree td
left join tei_degreeyears tdy
on td.degree_id = tdy.degree_id
) = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

|||I'm still getting an error with that statement.

the error says incorrect syntax near then and else statement.

i've check all the tables names and field names and there fine.

anyo other suggestions
|||

Remove the word 'then' and you should be fine.

Chris

|||cheers that worked
|||

hi!

You cannot use if inside a select statement.... Use CASE and it will work for sure....

select CASE tdy.classyear

WHEN tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end

ELSE
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end
END
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

try this and let me know ur comments...

Thanks

sql

Error in Sql Server 2000 sp4

I′ve got a problem with SQL server. The server stops responding and gives me
out an error below.
Anyone can help me. When it runs this select in the database the server
keeps with 80% 100% processor up and stops responding all client requests.
Memory
MemoryLoad = 94%
Total Physical = 2047 MB
Available Physical = 116 MB
Total Page File = 8041 MB
Available Page File = 6246 MB
Total Virtual = 2047 MB
Available Virtual = 281 MB
*Stack Dump being sent to E:\Microsoft SQL Server\MSSQL\log\SQLDump0009.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 02/23/07 10:17:59 spid 89
*
* Exception Address = 00402836
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000004
* Input Buffer 82 bytes -
* SELECT * from ProdutosParaSiteWeb_ANALISE
Luis,
Is ProdutosParaSiteWeb_ANALISE a view? If so, examine the code for the view
for complexity. Maybe you have uncovered a SQL Server bug, but recrafting
the code could work around the problem.
If ProdutosParaSiteWeb_ANALISE is a table, or the view is very simple, then
you might be concerned with database integrity. Run as DBCC CHECKDB and see
if it reveals any errors.
RLF
"Luis" <Luis@.discussions.microsoft.com> wrote in message
news:D1CA43FA-34B2-4A2A-8D83-529913BB66AB@.microsoft.com...
> Ive got a problem with SQL server. The server stops responding and gives
> me
> out an error below.
> Anyone can help me. When it runs this select in the database the server
> keeps with 80% 100% processor up and stops responding all client requests.
> Memory
> MemoryLoad = 94%
> Total Physical = 2047 MB
> Available Physical = 116 MB
> Total Page File = 8041 MB
> Available Page File = 6246 MB
> Total Virtual = 2047 MB
> Available Virtual = 281 MB
> *Stack Dump being sent to E:\Microsoft SQL
> Server\MSSQL\log\SQLDump0009.txt
> *
> ************************************************** ***************************
> **
> *
> * BEGIN STACK DUMP:
> * 02/23/07 10:17:59 spid 89
> *
> * Exception Address = 00402836
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000004
> * Input Buffer 82 bytes -
> * SELECT * from ProdutosParaSiteWeb_ANALISE
>

Error in Sql Server 2000 sp4

I′ve got a problem with SQL server. The server stops responding and gives m
e
out an error below.
Anyone can help me. When it runs this select in the database the server
keeps with 80% 100% processor up and stops responding all client requests.
Memory
MemoryLoad = 94%
Total Physical = 2047 MB
Available Physical = 116 MB
Total Page File = 8041 MB
Available Page File = 6246 MB
Total Virtual = 2047 MB
Available Virtual = 281 MB
*Stack Dump being sent to E:\Microsoft SQL Server\MSSQL\log\SQLDump0009.txt
*
****************************************
************************************
*
**
*
* BEGIN STACK DUMP:
* 02/23/07 10:17:59 spid 89
*
* Exception Address = 00402836
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000004
* Input Buffer 82 bytes -
* SELECT * from ProdutosParaSiteWeb_ANALISELuis,
Is ProdutosParaSiteWeb_ANALISE a view? If so, examine the code for the view
for complexity. Maybe you have uncovered a SQL Server bug, but recrafting
the code could work around the problem.
If ProdutosParaSiteWeb_ANALISE is a table, or the view is very simple, then
you might be concerned with database integrity. Run as DBCC CHECKDB and see
if it reveals any errors.
RLF
"Luis" <Luis@.discussions.microsoft.com> wrote in message
news:D1CA43FA-34B2-4A2A-8D83-529913BB66AB@.microsoft.com...
> Ive got a problem with SQL server. The server stops responding and gives
> me
> out an error below.
> Anyone can help me. When it runs this select in the database the server
> keeps with 80% 100% processor up and stops responding all client requests.
> Memory
> MemoryLoad = 94%
> Total Physical = 2047 MB
> Available Physical = 116 MB
> Total Page File = 8041 MB
> Available Page File = 6246 MB
> Total Virtual = 2047 MB
> Available Virtual = 281 MB
> *Stack Dump being sent to E:\Microsoft SQL
> Server\MSSQL\log\SQLDump0009.txt
> *
> ****************************************
**********************************
***
> **
> *
> * BEGIN STACK DUMP:
> * 02/23/07 10:17:59 spid 89
> *
> * Exception Address = 00402836
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000004
> * Input Buffer 82 bytes -
> * SELECT * from ProdutosParaSiteWeb_ANALISE
>

Error in SQL Query

Hi,

I am trying this query
rsOFF.Open "SELECT SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0)) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

but is is giving me this error:
Wrong number of argument used with function in query expression SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0))

I could not find error.

can anyone help me.Not sure what is causing the error, but I doubt that you need the ISNULL functions anyway. If either value is Null then the product will be null and won't roll up into the SUM anyway. Aggregate functions ignore NULLs, so they are mathematically equivalent to zeros for summation.

Try simplifying your query statement to this, and see if it does not solve the problem:

rsOFF.Open "SELECT SUM(OFF_QUANT*OFF_PRICE) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

error in sp when using order by

CREATE PROCEDURE getC

AS
(
SELECT top 3 c FROM table1
order by c Desc

)
GO


and it gives me error "Incorrect syntax near keyword order", and secondly how will i get the result in a var like...

CREATE PROCEDURE getC

@.d char(6)
AS
(
SELECT @.d=top 1 c FROM table1
order by c Desc

)
GOYou can't use order by clause on an SP.

Paulo|||-- SQL Code Begins Here
-- exec test23
create proc test23
as

declare @.top as varchar(50)
declare @.top1 as varchar(50)
declare @.top2 as varchar(50)
declare @.top3 as varchar(50)
declare @.row_count as int

set @.row_count = 1

DECLARE top3_cursor CURSOR FOR

select top 3 author_code from lauthors
order by author_code desc

OPEN top3_cursor
FETCH NEXT FROM top3_cursor into @.top
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
if @.row_count = 1
begin
set @.top1 = @.top
set @.top = ''
end

if @.row_count = 2
begin
set @.top2 = @.top

set @.top = ''
end

if @.row_count = 3
begin
set @.top3 = @.top

set @.top = ''
end

set @.row_count = @.row_count + 1

FETCH NEXT FROM top3_cursor into @.top
END
CLOSE top3_cursor
DEALLOCATE top3_cursor

select @.top1 as top1, @.top2 as top2, @.top3 as top3

go

-- SQL Code Ends Here

Hope this is what you are looking for

Roshmi Choudhury|||I'd just use something like:CREATE PROCEDURE getC
@.d CHAR(6) OUTPUT
AS

SELECT @.d = Max(c)
FROM table1

RETURN
GO-PatP|||the problem is DESC field..
you can't use this word.. because is a reserved word!!!
rename field or use order by [DESC]

DESC is a reserved word for DESCENDING in order by clause..
ex. select * fro mauthors order by aut_id desc
orders in descending mode..

it's ok??|||this message it was not for this thread..
sorry =)))

Error in SELECT clause: expression near '='.

When I use a construction like this in the sql designer of visual
studio 2005. i get the error that it doesnt understand this. However
the sql is just fine is there a way to make this work in visual studio?
i did set the prefix for the parameters so it does understands the @. in
other types of query.
DECLARE @.TEMP t_timestamp
this block in the designer:
SELECT @.TEMP=rprl_timestamp
FROM rap_rol
WHERE rol_id = 10Jeroen a crit :
> When I use a construction like this in the sql designer of visual
> studio 2005. i get the error that it doesnt understand this. However
> the sql is just fine is there a way to make this work in visual studio?
> i did set the prefix for the parameters so it does understands the @. in
> other types of query.
> DECLARE @.TEMP t_timestamp
^^^^^^^^^^^
what is this type '? t_timestamp ?
Do you use a CLR UDT ?

> this block in the designer:
> SELECT @.TEMP=rprl_timestamp
> FROM rap_rol
> WHERE rol_id = 10
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||its just a user defined datatype
it does not matter what type for my question. but in this case its a
datetime.
the problem is in the line SELECT @.TEMP=rprl_timestamp
where the @.temp is filled with the value of rprl_timestamp. its perfect
sql but the designer doesnt understand it.

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>
|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas

"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

Error in cross tab statement

Hi,

I have a coding which state as below:

CREATE PROCEDURE [dbo].[crossTab]
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100)
AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE ' + @.pivot + ' Is Not Null')

SELECT @.sql='' , @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN ' + @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I just woud like why it come out error stated that

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

when i key in

exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

Please help me

ThanxYet another attempt at a universal cross-tab function...

Does your code error out if you comment out the "EXEC (@.select)" line? If not, then try replacing it with "SELECT @.select" or "PRINT @.select" to see exactly what code is being executed that is causing the error.

Error in convert function

I use convert to work with Hijri functions
when i write
select convert (datetime,'29-10-1426',131)
the result is correct
2005-12-01 00:00:00.000

when I increase the date by one
select convert (datetime,'30-10-1426',131)
I get
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Why and how i solve itAs far as I know, the month 10 in Hijri calendar only has 29 days, so the second statement is supposed to fail. The next day to convert (datetime,'30-10-1426',131) is

select convert (datetime,'1-11-1426',131)

Regards,
Jun|||

The 10 month in Hijri calender is 30 days so
In Hijri calender any month can be 29 or 30 days
thanks

Error in an SQL Script

USE master
GO
IF EXISTS(SELECT * FROM sysdatabases
WHERE name = 'Products')
DROP DATABASE Products
GO
CREATE DATABASE Products
ON (NAME=Product,
FILENAME = ‘C:\APPS\Products.mdf’,
SIZE=10 )
GO
USE Products
GO
CREATE TABLE Categories (
catid VARCHAR(10) NOT NULL,
name VARCHAR(50) NOT NULL,
[desc] VARCHAR(MAX) NOT NULL,
PRIMARY KEY(catid) )
GO
CREATE TABLE Products (
productid VARCHAR(10) NOT NULL,
catid VARCHAR(10) NOT NULL,
name VARCHAR(50) NOT NULL,
shorttext VARCHAR(MAX) NOT NULL,
longtext VARCHAR(MAX) NOT NULL,
price MONEY NOT NULL,
thumbnail VARCHAR(40) NOT NULL,
image VARCHAR(40) NOT NULL,
PRIMARY KEY(productid),
FOREIGN KEY(catid) REFERENCES Categories(catid) )
GO
CREATE TABLE FeaturedProducts (
productid VARCHAR(10) NOT NULL,
featuretext VARCHAR(MAX) NOT NULL,
saleprice MONEY NOT NULL,
PRIMARY KEY(productid),
FOREIGN KEY(productid) REFERENCES Products(productid) )
GO

When I run the SQL script above from the command prompt, I get this :

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\HP_Administrator>cd My Documents

C:\Documents and Settings\HP_Administrator\My Documents>sqlcmd -S localhost\SQLE
xpress -i CreateProducts.sql
Changed database context to 'master'.
Msg 102, Level 15, State 1, Server YOUR-C6B5E4EABC\SQLEXPRESS, Line 3
Incorrect syntax near '`'.
Msg 911, Level 16, State 1, Server YOUR-C6B5E4EABC\SQLEXPRESS, Line 1
Could not locate entry in sysdatabases for database 'Products'. No entry found with that name. Make sure that the name is entered correctly.

I am unable to get to the cause of this problem. Thanks for your assistance in advance!

Hello,

The only thing i can see is that your create statement is using "smart quotes" around the data file:

‘C:\APPS\Products.mdf’

I'm not sure if the site just formatted it that way when you copy and pasted, but use single quotes.

Also, just as an aside, it's good practice to explicitly specify the log file in your create statement:

CREATE DATABASE Products

ON (NAME=Product,

FILENAME = 'C:\Products.mdf',

SIZE=10 )

LOG ON (NAME=ProductLOG,

FILENAME = 'C:\Products.ldf',

SIZE=10)

GO

Cheers

Rob

|||

But it is using single quotes right? Or did you mean double quotes?

Thanks for your assistance anyway.

|||No, you are not using single quotes, but smart quotes. You should be using single quotes...

smart quote: `
single quote: '

So instead of writing: `C:\Products.mdf′
write: 'C:\Products.mdf'

error in --> EXEC xp_cmdshell ''bcp "SELECT * FROM tbl a where a.flag=N" queryout

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but

like this it saves well "SELECT * FROM tbl where flag=N" and

when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP > -c -T , must have some more or less

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

|||

Manivannan.D.Sekaran wrote:

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

yes i tryed both ways with 'N' and "N" and it says the same error message

with this

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt="N"" queryout "'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'N'.

with this says

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt= 'N' " queryout "'

Msg 102, Level 15, State 1, Procedure spDELTA_P1_RANGEL_STOCKS, Line 59

Incorrect syntax near ' " queryout "'.

|||

Please copy and paste the following code,

Instead of 2 single quotes you are trying with 1 double quote.

Code Snippet

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''" queryout F.txt -c -T'

|||

THANK YOU A LOT

and it was only a little thing like a '

error help

I am trying to select data from an excel spreadsheet.. so i use

select *
FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:dt.xls', 'SELECT * FROM [Sheet1$] ' )

and the error i got is

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

any suggestions?Not too sure about the syntax here, so I could be blowing smoke. Apologies, if I am.

If the spreadsheet is named dt.xls, should it be DBQ=C:\dt.xls?

If that is not it, then I would check to see that Sheet1 is in the spreadsheet, and make sure no one has gone and renamed it.

Hope this helps.|||Originally posted by MCrowley
Not too sure about the syntax here, so I could be blowing smoke. Apologies, if I am.

If the spreadsheet is named dt.xls, should it be DBQ=C:\dt.xls?

If that is not it, then I would check to see that Sheet1 is in the spreadsheet, and make sure no one has gone and renamed it.

Hope this helps.

Fixed the syntax, and checked sheet1 was there,but still no good, any other suggestions?

Error handling...

I am needing to check for an error code after a SQL statement has run, but
the error is being trapped internal to the Select statement. How do I make
this work?
I am trying to set up some code that will try the SQL statement and if it
gets an error, it will wait 30 seconds and then try again. And if it trys 3
times (or so) with out success THEN return the error message (in the error
dialog box that halts everything till you click "OK") or better yet, just
log that the error occured and allow the process to continue and take
appropriate action based on the error number.
The file pointed to by the OPENROWSET, was just copied from a local PC to
the server. The problem is that the file still has write processes occuring
in the background after the scripted copy thinks it is finished and passes
control to the next line.
I am trying to make this work with out resorting to DTS or SSIS. The
product I am using has it's own proprietary scripting set up that can call i
n
and run Transact-SQL statements.
*** SQL Code ***
Declare @.trys int
Set @.trys = 1
PRINT 'At top'
While @.trys < 3
BEGIN
Print 'Attempting Try # ' + Cast(@.Trys as char(4))
SELECT SN.CustomerID, C.CustomerID, C.FirstName, C.LastName, C.Status
FROM Customers AS C
LEFT OUTER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\StudentList.mdb';'admin';'', StudentNumbers) AS SN
ON (C.CustomerID = SN.CustomerID)
WHERE SN.CustomerID Is not null
If @.@.ERROR = 0
BREAK
Else
BEGIN
WAITFOR DELAY '000:00:30'
Set @.Trys = @.Trys + 1
CONTINUE
END
END
Print 'At bottom'
Regards,
JohnJohn Keith (JohnKeith@.discussions.microsoft.com) writes:
> I am needing to check for an error code after a SQL statement has run,
> but the error is being trapped internal to the Select statement. How do
> I make this work?
It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
are not trappable in SQL, because they abort the batch. There is a trick
with setting up a linked server, but it's fairly kludgy.
On SQL 2005 you can use TRY CATCH.
For lots of details in error handlin in SQL Server, see this
article: http://www.sommarskog.se/error-handling-I.html. The linked-
server trick is described somewhere in the middle.
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|||Thanks for that link.
Your page was very informative on the hows-and-whys. I can tell from your
sarcasm on the page that we are in the same boat on this. The remark about
"Now, taste that concept: an informational error." made me laugh.
I'm checking into SQL Server 2005 to see if my package-application (not DTS,
just an externaly written bought app) will support the newer SQL Server 2005
.
Regards,
John
"Erland Sommarskog" wrote:

> John Keith (JohnKeith@.discussions.microsoft.com) writes:
> It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
> are not trappable in SQL, because they abort the batch. There is a trick
> with setting up a linked server, but it's fairly kludgy.
> On SQL 2005 you can use TRY CATCH.
> For lots of details in error handlin in SQL Server, see this
> article: http://www.sommarskog.se/error-handling-I.html. The linked-
> server trick is described somewhere in the middle.
>
> --
> 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
>

error handling OPENROWSET

If, for example, i have inside a procedure the next statement:
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + '
'')')

in some ocasions it will generate errors
- if no file or path,
- if no column, ...
how can i handle those errors and continue the procedure without generating an error.Error for "no column" when does that occur ? Only way i cna think of is if the file doe not exist.

To check if the file or path exists

declare @.Path varchar(128)
declare @.FileName varchar(10)

--i used 10 chars because foxpro2.6 allows only length of 10 for a filename and i am not sure what visual fp uses (also fp2.6 doesn't like spaces in path if that matters in your situation)

select @.Path = 'C:\', @.FileName = 'myfile.dbf'
declare @.i int
declare @.File varchar(1000)

select @.File = @.Path + @.FileName
exec master..xp_fileexist @.File, @.i out
if @.i = 1
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + ''')')
else
print 'no file'