Problem in insert a datetime into SqlServer 2005

I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005

The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "

and the result in Datebase (All the time become 1/1/1900 0:00:00 )

Although i change the datetype from datetime to smalldatetime the result is same

and i try input the date 28/3/2006 0:00:00 into server but

it show the error:Incorrect syntax near '0'.

What wrong ? help me please,Thank.

They have to be passed as strings:
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test','3/28/2006','5/4/2006','5/4/2006' ) "
or as ISO values which is preferable
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',20060328,20060405,20060405 ) "
HTH, jens Suessmeyer.

|||

Your date is in the future (28th of March this year). That is not allowed:

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 296, Level 16, State 3, Line 5
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

HTH

|||

I prefer to use dates in the following format 'yyyy-mm-dd'.

Have you tried that already (as mentioned above)?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

@.Original Poster: Could you please track the status of the post ? Thanks.

-Jens

sql

Problem in doing a backup of database on SQL server through Java code using jdbc

Problem in doing a backup of database on SQL server through Java code
using jdbc
Statement callBackupDbase = con.createStatement();
String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";
if(callBackupDbase != null){
callBackupDbase.execute(dbackup);
}
I get the following error
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.
Could anyone help me with that
BhagatI suggest you ask this in a jdbc group. The problem is that your code opens a transaction and then
try to execute the backup command. See the error message. You need to make the jdbc API not open a
transaction for you. How you do that, I don't know, it would be a jdbc issue, Perhaps in the
connection string, perhaps by using some other function calls in jdbc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bhagat" <bhagats_bhagat@.yahoo.com> wrote in message
news:1128354135.243884.15270@.g49g2000cwa.googlegroups.com...
> Problem in doing a backup of database on SQL server through Java code
> using jdbc
> Statement callBackupDbase = con.createStatement();
> String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
> backup file";
> if(callBackupDbase != null){
> callBackupDbase.execute(dbackup);
> }
> I get the following error
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
> backup or restore operation within a transaction.
> Could anyone help me with that
> Bhagat
>|||Thanks Mr Tibor Karaszi,
I shall try in the JDBC group

Problem finding values with aggregate functions

Hi all!

In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?

CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */

insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)

select min(row_value), row_name from some_table group by row_nameJon wrote:
> Hi all!
> In a statement I want to find the IDENTITY-column value for a row
that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
> CREATE TABLE some_table
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> row_value integer,
> row_name varchar(30)
> )
> GO
> /* DROP TABLE some_table */
> insert into some_table (row_name, row_value) VALUES ('Alice', 0)
> insert into some_table (row_name, row_value) VALUES ('Alice', 1)
> insert into some_table (row_name, row_value) VALUES ('Alice', 2)
> insert into some_table (row_name, row_value) VALUES ('Alice', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 2)
> insert into some_table (row_name, row_value) VALUES ('Bob', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 4)
> insert into some_table (row_name, row_value) VALUES ('Celine', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 6)
> select min(row_value), row_name from some_table group by row_name

*Assuming* that row_name/row_value combinations are unique, then it
would be:

select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name

*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())|||Jon (jonsjostedt@.hotmail.com) writes:
> In a statement I want to find the IDENTITY-column value for a row that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?

Yes:

select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name

What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem executing procedure and how can I use their results in a select statement

I have this Stored Procedure that walk through a table that stores hierarchical data and reorganize the output, so the resultset will be ordered by this hierarchy.

The table structure is (fieldnames in english between parentheses for better comprehension):

CD_CATEGORIA (CD_CATEGORY)
DS_CATEGORIA (DS_CATEGORY)
CD_CATEGORIAMAE (CD_MOTHERCATEGORY)

Here is the Stored Procedure code:

CREATE PROCEDURE [dbo].[sp_RetornaCategorias]

-- Add the parameters for the stored procedure here

@.ID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @.TabelaSaida table(

cd_categoria int,

ds_categoria varchar(70),

nr_nivel int);

declare @.i int

select @.i = 0

-- keep going until no more rows added

while @.@.rowcount > 0

begin

select @.i = @.i + 1

insert @.TabelaSaida

-- Get all children of previous level

select Categorias.cd_categoria, Categorias.ds_categoria, @.i + 1

from Categorias, @.TabelaSaida AS TblSaida

where nr_nivel = @.i

and Categorias.cd_categoriamae = TblSaida.cd_categoria

end

-- Saída de dados

-- output with hierarchy formatted

select space((nr_nivel-1)*4) + ds_categoria

from @.TabelaSaida

order by nr_nivel

END

But when I try to execute this Stored Procedure, it runs but nothing is returned.

I'm using this code to execute it:

EXEC [dbo].[sp_RetornaCategorias]

@.ID = 1

Are there anything wrong with it? How can I fix this?

And how can I call a Stored Procedure and get its resultset from a SELECT statement?

Hi Juliano,

Your SP wont actually return anything unless you declare a Variable as OUTPUT.

Your SP's resultset is from the select statement.

There is great MSDN documentation on SP's here; http://msdn2.microsoft.com/en-us/netframework/aa479373.aspx

With regards to fixing it, im not entirely sure its broken yet.

|||

It looks like your insert into the tablevariable is based in a join against that same tablevariable, but when you start out, it's newly created and thus empty..
So, the insert would then yield 0 rows, and the loop will break.

Try to run the SQL statements in a query window, then you can see what happens in each step.

/Kenneth

|||

ur procedure and calling seems to be alright...just check the data in the tyables ur refering...and is there actually nething to be returned.......basically run the select query seperately and check..

this 1...does this gives ne values ?

select Categorias.cd_categoria, Categorias.ds_categoria

from Categorias, @.TabelaSaida AS TblSaida

and Categorias.cd_categoriamae = TblSaida.cd_categoria

|||

I don't see how this could possibly return any rows, since @.TableSaida that is used in the join is newly declared and created, and thus is also empty.

/Kenneth

Problem doing Update and Insert to different tables in same procedure.

We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated.

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[AddPhoto]

@.AlbumID

int,

@.Caption

nvarchar(MAX)

AS

INSERT

INTO [Photos](

[AlbumID]

,

[Caption]

,

[Location]

,

[LastModified]

)

VALUES

(

@.AlbumID

,

@.Caption

,

'tmpLocation'

,/* tmpLocation needed because app broke when Location column set to Allow NULLs */

GetDate

())

/* Retrieve generated PhotoID */

DECLARE

@.PhotoIDint

SET

@.PhotoID=SCOPE_IDENTITY()

/* Build unique location path from album and photo ID */

DECLARE

@.Locationnvarchar(MAX)

SET

@.Location='\'+CONVERT(nvarchar(10), @.AlbumID)+'\'+CONVERT(nvarchar(10),@.PhotoID)+'.jpg'

/* Update photo with new location path */

UPDATE

[Photos]

SET

[Location]

= @.Location

WHERE

[PhotoID]

= @.PhotoID

/* Update photo with new location path */

******************************************The code never executes the statement below********************************************

INSERT

INTO [PhotoDefault](

[pidm]

,

[defaultPhoto]

,

[activityDate]

)

VALUES

(

'1234'

,

'test'

,

getdate

()

)

/* Return PhotoID and Location */

SELECT

@.PhotoID, @.Location

RETURN

Thanks,

Jason

Next time, when you post your code please use the Code editor available when you post. Your code is hard to read.

The code (After formatting) looks fine to me. Throw in a couple of PRINT statements before and after the INSERT. There is no reason the INSERT should be skipped.

|||

Sorry about the code post. I didn't know about the code editor.

I have posted print statements after the insert and they are never excuted. I have also tried posting the first insert after with no luck as well. Any other suggestion?

|||

I formatted your code for you. Try this new code if you see the messages:

set ANSI_NULLSON set QUOTED_IDENTIFIERON GOALTER PROCEDURE [dbo].[AddPhoto] @.AlbumIDint, @.Captionnvarchar(MAX)ASINSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified])VALUES ( @.AlbumID, @.Caption,'tmpLocation' ,/* tmpLocation needed because app broke when Location column set to Allow NULLs */GetDate())/* Retrieve generated PhotoID */DECLARE @.PhotoIDint SET @.PhotoID = SCOPE_IDENTITY()/* Build unique location path from album and photo ID */DECLARE @.Locationnvarchar(MAX)SET @.Location ='\' +CONVERT(nvarchar(10), @.AlbumID) +'\' +CONVERT(nvarchar(10),@.PhotoID) +'.jpg'/* Update photo with new location path */UPDATE [Photos]SET [Location] = @.LocationWHERE [PhotoID] = @.PhotoID/* Update photo with new location path */******************************************The code never executes the statement below********************************************SELECT'I am here'INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate])VALUES ('1234','test',getdate() )SELECT'I am here again'/* Return PhotoID and Location */SELECT @.PhotoID, @.LocationRETURNGo

Problem creating an enumerator?

Hi,

I tried to create an enumerator using Vb.NET but it will give me an error in this statement:

<DtsForEachEnumerator(DisplayName = "LSParseEnumerator", Description = "Returns an Enumerator by separating items in a String by a certain character", UITypeName = FullyQualifiedTypeName, AssemblyName, Version = 1.00.000.00, Culture = Neutral, PublicKeyToken = "")> _

The error is in the Version = 1.00.000.00 and i have already tried to put it in between "".
Error Returned:
Error 1 Comma, ')', or a valid expression continuation expected. C:\Documents and Settings\Luis Sim?es\My Documents\Visual Studio 2005\Projects\LSParserEnumerator\LSParserEnumerator\LSParserEnumerator.vb 5 217 LSParserEnumerator

Your quoting is off a bit. UITypeName, Version, and Culture should look like this:

UITypeName="FullyQualifiedTypeName,AssemblyName", Version="1.00.000.00",Culture="Neutral"|||Jay i have already tried that to but the example i gave you is from the MSDN2 website so it should be good...

http://msdn2.microsoft.com/en-us/library/ms136120.aspx

The way you told me to use gives me the following errors:
DisplayName is not declared
Description is not declared
and so on for all variables....

The only method that works is

<DtsForEachEnumerator()> _

But this way i can't refer to UI and specify all the other options...

This should be easy i think...
Best Regards,
Luis Sim?es

|||Okay, so much for documentation. I'm still focusing on quoting. Usually those strings look like this:

“Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

Have you tried UITypeName="FullyQualifiedTypeName, AssemblyName, Version = 1.00.000.00, Culture = Neutral, PublicKeyToken =" ? Not sure what you'd do about that PublicKeyToken. Quote it? Leave it off?
|||

This is something really odd... can it be from visual studio 2005 express? bug or something?

When is insert the <DtsForEachEnumerator(...
it will tell me that i have all optional parameters like this:

New([Description As String], [DisplayName as String], [ForEachEnumeratorContact As String], [LocalizationType As Type], [UITypeName as String]) Initializes a new instance of Microsoft.SqlServer.DTS.Runtime.DtsForEachEnumeratorAttribute

But i have already tried using just types in that order like this:

<DtsForEachEnumerator("MyEnumerator", "A managed enumerator", "Name of company to contact", , "WorldVision.LuisSimoes.SQLServer2005.Enumerators.LSParseEnumeratorUI, LSParseEnumerator")>

But the following error occurs:
Error 1 Too many arguments to 'Public Sub New()'

This is odd since it gives the parameters and then it shows an empty constructor?

|||Ok problem solved :)

<DtsForEachEnumerator(DisplayName:="For Each String Token Enumerator", Description:="Enumerates string tokens split by a certain separator character", UITypeName:="WorldVision.LuisSimoes.SQLServer2005.Enumerators.LSTokenEnumeratorUI,LSTokenEnumerator,Version=1.0.0.0,Culture=Neutral")> _

Cheers