Conversion query

I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks

Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

Controling Replation agent actions

Hello there
I some questions:
1. Iw'd like to run some store procedures before i'm starging the
replication every day. Dose it enouth to add the task before the replication
task?
2. If the replication faild i would like to make sure that none of the
changes will be made.
3. IF an error occur can i nevegat it to the type of error:
if the error is data error(validation of foreign key) or connection error.
So i can give it order to run 5 minutes afterword?
4. Can i output the errors to outside files in order that other programs
that use sql server (Access) can give a message to the user?
Roy,
adding another step would be fine, or another job which runs sp_start_job
after your code had finished.
Trapping errors is really well catered fro in SQL 2005 but not so easy in
SQL 2000. You'd be better testing your changes in code rather than
attempting your changes eg look for a PK value before trying the FK insert.
If the PK doesn't exist, then logging this to a table and taking appropriate
actions eg making your own error message available to Access.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||1) I would make these procs the first job step of your replication job
2) Double click on the job step, select advanced and have it run a job or
script on failure.
3) This is a little tricky - the error should be in the msrepl_errors table
in the distribution database where the agent is run. You can query it there,
but the error may not be there depending on the error message. What you
would need to do is restart the agent on failure, but this time use the
verbose agent profile. The complete error message will now be in the
msrepl_Errors table. Errors are also logged in text files and dumped in
%WindDir%\system32 and will have an err extension. You can poll for them
using FileSystemWatcher.
4) You might want to poll msrepl_errors as discussed above and write to an
access database. Or you could fire something using the Replication Alert
Agent Failure.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
> Hello there
> I some questions:
> 1. Iw'd like to run some store procedures before i'm starging the
> replication every day. Dose it enouth to add the task before the
> replication
> task?
> 2. If the replication faild i would like to make sure that none of the
> changes will be made.
> 3. IF an error occur can i nevegat it to the type of error:
> if the error is data error(validation of foreign key) or connection error.
> So i can give it order to run 5 minutes afterword?
> 4. Can i output the errors to outside files in order that other programs
> that use sql server (Access) can give a message to the user?
>
>
|||Whell Hilary
I've made an error in the replication and no MSRepl_Errors table were
created
After i got the error when i run the agent on the enterprise manager i
opened the query anlyser with the distribution database and i counldn't see
the table
When will the table appear?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYG7KczAGHA.272@.TK2MSFTNGP09.phx.gbl...
> 1) I would make these procs the first job step of your replication job
> 2) Double click on the job step, select advanced and have it run a job or
> script on failure.
> 3) This is a little tricky - the error should be in the msrepl_errors
> table in the distribution database where the agent is run. You can query
> it there, but the error may not be there depending on the error message.
> What you would need to do is restart the agent on failure, but this time
> use the verbose agent profile. The complete error message will now be in
> the msrepl_Errors table. Errors are also logged in text files and dumped
> in %WindDir%\system32 and will have an err extension. You can poll for
> them using FileSystemWatcher.
> 4) You might want to poll msrepl_errors as discussed above and write to an
> access database. Or you could fire something using the Replication Alert
> Agent Failure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
>

Continuing a SP after an error

Dear All,
Can nayone tell me how to make a Store Procedure continue
after an error, rather than just return. The reason is we
want some proper error reporting.
Thanks
PeterHi Peter,
Have a look into @.@.error and RAISERROR in Books online. This will define the
error hadling process.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a51e01c40a83$4ab13ee0$a601280a@.phx.gbl...
> Dear All,
> Can nayone tell me how to make a Store Procedure continue
> after an error, rather than just return. The reason is we
> want some proper error reporting.
> Thanks
> Peter|||Peter,
SQL Server DOES continue after most errors, unless you have SET AXACT_ABORT
ON. However, for some errors, the batch is terminated and there is nothing
you can do about this. I suggest you read Erland's articles about error
handling on www.sommarskog.se
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a51e01c40a83$4ab13ee0$a601280a@.phx.gbl...
> Dear All,
> Can nayone tell me how to make a Store Procedure continue
> after an error, rather than just return. The reason is we
> want some proper error reporting.
> Thanks
> Peter

contains keyword for integer field type

I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.

Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.
Something like a CONTAINS keyword...

If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

try something like this:

select

ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select 37 [ID])aa

It will work as long as Your ID is Int type if it is not int do this

select ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select cast(37.00 as int) [ID])aa

You can also use substring
selectsubstring(ID,1,1) first,substring(ID,2,1) second
from(selectcast(37asvarchar(20)) [ID])aa

Good luck