Showing posts with label moving. Show all posts
Showing posts with label moving. Show all posts

Monday, March 26, 2012

Incorrect Syntax Near MyCompany_SearchForJobs

I am moving my site to ASP.NET 2.0 from 1.1...in doing so I am moving some functionality over verbatim. One of them is a search feature for our job board. The code to call the stored proc has not changed...it basically looks for values that are provided on a search page and passes them to the database. If a field is not specified in the UI, a System.Db.Null is passed...in the past I did not even have to pass this back as my query worked as is...now I am getting the above exception and I cannot figure out what it is! If I pass all nulls back it works...as soon as I specify a value bam it fails. I have removed the parameter definitions defaulting to null...no difference...any ideas??
ALTER PROCEDURE MyCompany_SearchForJobs
(
@.state int = null,
@.city NVarChar (75) = null,
@.serviceArea int = null,
@.jobType int = null,
@.dateSearch DateTime = null
)
AS
SELECT
MyCompany_JobPostings.*, MyCompany_OrgInfo.OrgName AS OrgName, MyCompany_States.State AS DisplayState
FROM
MyCompany_JobPostings, MyCompany_OrgInfo, MyCompany_States
WHERE
MyCompany_JobPostings.State = IsNull(@.state , MyCompany_JobPostings.State)
AND
MyCompany_JobPostings.City Like IsNull(@.city, MyCompany_JobPostings.City)
AND
MyCompany_JobPostings.PositionType = IsNull(@.jobType, MyCompany_JobPostings.PositionType)
AND
MyCompany_JobPostings.OrgId IN (SELECT OrgId FROM MyCompany_OrgInfo WHERE ServiceArea = IsNull(@.serviceArea, ServiceArea) OR ServiceArea2 = IsNull(@.serviceArea, ServiceArea2))
AND
MyCompany_JobPostings.Status = 5
AND
MyCompany_JobPostings.JobPostedDate = IsNull(@.dateSearch, MyCompany_JobPostings.JobPostedDate)
AND
MyCompany_JobPostings.OrgId = MyCompany_OrgInfo.OrgId
AND
MyCompany_JobPostings.State = MyCompany_States.StateId
ORDER BY
MyCompany_JobPostings.JobPostedDate DESC

I figured it out...:) It was my lovely assignment code that was messing it up. I had code that chose which stored procedure to call...one with date ranges and one without. I moved the line of code that tells what type of Command it was above the assignment of the Command object and thus reinitializing it to a "text" proc. Goofy but it works now thankfully. :)

Monday, March 19, 2012

Incorrect Date Format

I am moving data from an Interbase DB to SQL Server 2005. The date
columns for whatever reason in Interbase are not valid dates and they
have vChar field types. This is how they are formatted
2006-03-13-00.02.04 notice the extra hyphen between the date and time.
I have created an SSIS package import and would like to correct the
date then. I have looked at but not figured out how to use the 'derived
column' function in SSIS.
Does anyone have any tips on how to do this. Or maybe an easier way.This seems to convert the string into a format that can be converted to
datetime. Hopefully you find it helpful.
DECLARE @.str varchar(50)
SET @.str = '2006-03-13-00.02.04'
SELECT SUBSTRING(@.str, 1,10) + ' ' + SUBSTRING(@.str,12,
(datalength(@.str)-11))
SELECT convert(datetime,REPLACE((SUBSTRING(@.str
, 1,10) + ' ' +
SUBSTRING(@.str,12, (datalength(@.str)-11))),'.', ':'))
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144252421.220884.54000@.j33g2000cwa.googlegroups.com...
>I am moving data from an Interbase DB to SQL Server 2005. The date
> columns for whatever reason in Interbase are not valid dates and they
> have vChar field types. This is how they are formatted
> 2006-03-13-00.02.04 notice the extra hyphen between the date and time.
> I have created an SSIS package import and would like to correct the
> date then. I have looked at but not figured out how to use the 'derived
> column' function in SSIS.
> Does anyone have any tips on how to do this. Or maybe an easier way.
>|||Thanks. I have come up with a similar conversion, but used in SSIS as a
drived column function. It converts to a valid date minus adding AM or
PM. Problem is that I add data conversion to change it into a date
field it fails? Does it actually need AM or PM added to it? Almost
there...
Here is my version
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")|||I do not think has has anything to do with AM or PM. I think the field
conversion generates the typical data conversion loss statement, and
that is what is failing the SSIS package. Any thoughts on this..|||As long as the data is in 24 hour format you should not need AM/PM. Do you
have the data that you want to convert to a datetime stored within a table?
Can you try running my T-SQL against the data to see if it converts
correctly?
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144261325.341459.91850@.i40g2000cwc.googlegroups.com...
> Thanks. I have come up with a similar conversion, but used in SSIS as a
> drived column function. It converts to a valid date minus adding AM or
> PM. Problem is that I add data conversion to change it into a date
> field it fails? Does it actually need AM or PM added to it? Almost
> there...
> Here is my version
> REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
> RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
>