Float to Datetime Conversion

I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Thanks in advance,
sajmeraconvert(datetime,cast(cast(foo as integer) as char),112)|||Thanks for the quick reply. I also tried the following and got the result.

convert(datetime,convert(varchar(20),convert(int,c onvert(float,<field name>))))

Thanks again!|||SELECT CAST(CONVERT(VARCHAR, Col1) AS DATETIME) AS NewValue
FROM Table1

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

Flat File With Fixed Length Header and No Delimeter

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

Do you need the data in the first row?

You can just ignore it by setting the "Header rows to skip" setting to 1.

K

|||

Yes. Essentially the file is just one row..Which would include the header details (number fo records) then all of the fixed length records follow (on the same line)

Scott

|||

Scott,

Given the unstructured nature of this file I think you will have to parse it out yourself in a script task. This isn't as daunting as it sounds. First clue I can give you is that it will have to be an asynchronous script task.

You can still import it into the pipeline using a Flat File Connection Manager though. It'll be a 1-column, 1-row file that's all.

-Jamie

|||

Using a script component of type source should be easiest. A little example follows.

Here is my sample file, representing an 8 byte header, followed by three rows of two columns, 10 and 20 bytes respectively.

00000003A234567890B234567890C234567890D234567890E234567890F234567890G234567890H234567890I234567890

The results table will look a bit like this-

Name Address
A234567890 B234567890C234567890
D234567890 E234567890F234567890
G234567890 H234567890I234567890

You will need to create the two columns in the script component , Name and Address as DT_WSTR 10 and 20 in length.

Now the code-

Public Class ScriptMain

Inherits UserComponent

Private stream As StreamReader

Public Overrides Sub CreateNewOutputRows()

Dim headerRecordCount As Integer

Dim recordCount As Integer = 0

'// Get filename from connection, using full acquire method

Dim filename As String = CType(Me.Connections.Connection.AcquireConnection(Nothing), String)

'// Open source file

stream = New StreamReader(filename)

'// Reader header block, 8 characters

Dim headerBuffer(7) As Char

If stream.ReadBlock(headerBuffer, 0, 8) = 8 Then

'// Store record count for later use in validation

headerRecordCount = CType(New String(headerBuffer), Integer)

Else

Throw New Exception("Invalid file format, header not valid.")

End If

With Output0Buffer

While stream.Peek > 0

'// Add data rows

.AddRow()

.Name = ReadColumn(10)

.Address = ReadColumn(20)

recordCount = recordCount + 1

End While

'// Close down buffer

.SetEndOfRowset()

'// Check record count

If recordCount = headerRecordCount Then

Me.Log(String.Format("Header row count ({0}) matched toital rows found.", headerRecordCount), 1, Nothing)

Else

Throw New Exception(String.Format("Invalid file format, header row count ({0}) not equal to rows found ({1}).", headerRecordCount, recordCount))

End If

End With

End Sub

Private Function ReadColumn(ByVal length As Integer) As String

Dim buffer(length - 1) As Char

If stream.Read(buffer, 0, length) = length Then

Return New String(buffer)

Else

Throw New Exception("Invalid file format, full column length not found.")

End If

End Function

End Class

|||Thanks for the answers guys..

Have gone with using a script component of type source, as per code above. With one change...Just ensured that the stream is closed after processing to ensure the resources are released...

I also added an extra Output for the script which holds the header details - my real data file has extra (useful) details in the header.

Thanks again..

Scott

Flat file to QUASI-Relational ?

I have a flat file table that describes crash data in SQL Server.
It contains vehicle information.

I would like to know if anyone knows a SQL statement that could go from
this
table= events

CRASHID | VEH1_TYPE | VEH2_TYPE | VEH_3TYPE
--------------
555555 | CAR | TRUCK | VAN

TO

CRASHID | VEH_TYPE | VEH_NUMBER
----------
555555 CAR 1
555555 Truck 2
555555 VAN 3

Any Ideas? I am relitively new at this and can only see how it could be
done by creating multiple tables and appending them.
Any help that could create the end selection in one query would be
great.

Thanks,
ChuckINSERT INTO NewTable (crashid, veh_type, veh_number)
SELECT crashid, veh1_type, 1
FROM OldTable
WHERE veh1_type IS NOT NULL
UNION ALL
SELECT crashid, veh2_type, 2
FROM OldTable
WHERE veh2_type IS NOT NULL
UNION ALL
SELECT crashid, veh3_type, 3
FROM OldTable
WHERE veh3_type IS NOT NULL ;

--
David Portas
SQL Server MVP
--|||Great.. Just what I was lookin for... Thanks for the help it is really
nice to have ppl who can/will help out..sql

Flat File Insert

Hi! I'm a newbie to SSIS and I'm trying to insert a flat file into a table.

The flat file contains some bad data so I want to insert the data into a text column.

I'm getting the following error below... It seems like SSIS cannot find the row delimiter, but I just want to load the file into a text column as a single row. I am able to insert the data to a text column in SQL 2000 DTS. Is there a way to do this in SQL 2005?

Please help... and Thanks in advance!!

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.

The component returned a failure code when the pipeline engine called PrimeOutput().

The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[Flat File Source [1]] Error: An error occurred while processing file \\sss.sss on data row 1.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.

How do you know it is a problem with the delimiter?

Could you provide more information about the structure of the file and how you are configuring the flat file source component?

|||

PLog

It sounds like you probably need to set up the flat file parsing to expect a single column and use a delimiter for that column that is not in your data (so it doesn't match anything in the data). You set the flat file parsing in the flat file source adapter UI. Let us know if that answers your question.

thanks,

denise

Flat file connection is skipping columns

The data file contains column names in the first row. Excel imports the file correctly. I can see the tabs in UltraEdit32. But...the flat file connection just skips over a column.
In the preview window, it appears to skip the column entirely.

However, when the data is imported, data from the non recognized column goes into a column that is mapped to receive data from another column shifting the data in to the next column.

I am using the CopyColumn and the SQL Destination controls.

Any ideas on how to make this work?

Thanks,
IanOCan you copy 'n paste some of the data here for us to look at?

Fixed position of table at bottom of report problem...

I have a table that just contains text at the bottom, but depending on
the query, it might land anywhere. it always needs to be one inch from
the bottom.
Any help is appreciated.
trinttrint wrote:
> I have a table that just contains text at the bottom, but depending on
> the query, it might land anywhere. it always needs to be one inch
> from the bottom.
> Any help is appreciated.
You can set the location for the absolute position of the table relative to
the position of its container!
You know your page-size...so fill the top- and left-coordinates with the
right values
regards
Frank|||Ok,
That works...but I can't get the top to go higher than one inch from
the top
Frank Matthiesen wrote:
> trint wrote:
> > I have a table that just contains text at the bottom, but depending
on
> > the query, it might land anywhere. it always needs to be one inch
> > from the bottom.
> > Any help is appreciated.
> You can set the location for the absolute position of the table
relative to
> the position of its container!
> You know your page-size...so fill the top- and left-coordinates with
the
> right values
> regards
> Frank|||trint wrote:
> Ok,
> That works...but I can't get the top to go higher than one inch from
> the top
You can set the top margin / lower margin in report-settings for the whole
report to 0
regards
Frank|||I have a similar issue with absolute positioning. I have a table and a text
box in a report. The textbox should be at the bottom always. The table can
span any number of pages. In this case, fixing the left and top locations is
not working. Can you please suggest me a solution.

Fixed Length File Read

I have a fixed-length flat file that contains about 30 columns. I have got it pretty well figured out using the flat file connection tool, but I am having trouble with the end of the line.

I know when I look at the file it is a CrLf that separates the rows, and SSIS only seems to understand this to a certain extent. It knows to go to the next line, but it also adds two rectangles to the lines, like this:

Col1 Col2 Col3 Col4 Col5
aaaaaa bbbbb ccccc ddddd eeeee

[][]aaaaa bbbbb ccccc ddddd eee
ee[][]aaaaa bbbbb ccccc ddddd e
eeee[][]aaaaa bbbbb ccccc ddddd

While this does create a cool pattern, it is a pain in the butt. The only solution I have found is adding two more spaces to the last column in the table, but the ?s just get appended there.

If anybody has any clue how to get rid of them, that would be great.

Thanks in advance

You have to use the Ragged Right format for this file. The Fixed Width format does not process row delimiters - it treats them as any other characters, and so you are seeing squares in your parsed text.

If you search through this forum you will find several more detailed posts on this topic.

Thanks.

First Time Installation of SQL Server 2005 Developer Edition

Just placed the SQL Server 2005 - Developer Edition into my Drive...

I get prompted with the following options..



This DVD contains 32-bit (x86) and 64-bit versions of this edition of SQL Server 2005. Click the link that matches your environment to begin



x86-based operating systems

x64-based operating systems

Itanuim-based operating syystems



I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...



I will be installing it on my PC... i



Microsoft Windows XP - Home Edition - Version 2 - Service Pack 2



My local Hard Drive remaining capacity is 12.6GB

External Hard Drive remaining capacity is 11.0GB



Tried looking everywhere for the answer... lol (even looked at YouTube for a simple installation video...





Thanks for your helpWindowx XP Home Edition is a 32 bit operating system. You want the X86 code for use on XP Home Edition.

-PatP|||I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...

As I only need to install the basic feature...

which one should i click?

SQL Server Database Services
Create a SQL Server fallover cluster
Analysus Services
Create an Analysis Server fallover cluster
Notification Services
Integration Services
Workstation components, Books Online development tools

Please help...|||On the Service Account section...

What should I click / Enter?

Username
Password
Domain

Under the... "Start Services at the end of setup" section, what should I tick?

SQL Server (tick already placed)
SQL Server Agent
SQL Brower

http://img.photobucket.com/albums/v675/akhlaq768/ServiceAccount.jpg

Many thanks

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
Neil
Seems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil
|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||It seems I spoke too soon! The following code reproduces
the problem:
CREATE PROCEDURE MyUDTTest AS
BEGIN
declare @.stmt as nvarchar(255)
set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
sp_addtype ''udtBuySell'' , ''char(1)'''
exec sp_executesql @.stmt
CREATE TABLE #MyTable (MyColumn udtBuySell)
END
trying to run this SP after a stop/start gives the
following error:
Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
Line 10
Column or parameter #1: Cannot find data type udtBuySell.
in tempdb create the udtBuySell data type. Then drop it.
Now the sp will run.
Any thoughts?
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...[vbcol=seagreen]
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> existing in tempdb. Hard to ell without a
> model database so you know it will exist in
> message
> fails
> I'm
> then
> this
|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil

>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
MyUDTTest,[vbcol=seagreen]
udtBuySell.[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
one
>
>.
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>

First Date of Month

I need subquery that would return table with a column that contains first
date of month.
I would be prefer avoid using temp tables if possible.
I have a status table So I can do following
SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
FROM Status
WHERE Status.Date>='7/1/2005'
GROUP BY YEAR(Date), MONTH(Date)
I am not sure if this is best way to do this. Also I still need to get rid
of time part.
Thank you.Shimon,
You can use the following expression to calculate the first date of the
month based on a given datetime column dt_col:
CAST(CONVERT(CHAR(6), dt_col, 112) + '01' AS DATETIME)
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Shimon Sim" <shimonsim048@.community.nospam> wrote in message
news:%23rFHyo9hGHA.4144@.TK2MSFTNGP02.phx.gbl...
>I need subquery that would return table with a column that contains first
>date of month.
> I would be prefer avoid using temp tables if possible.
> I have a status table So I can do following
> SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
> DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
> FROM Status
> WHERE Status.Date>='7/1/2005'
> GROUP BY YEAR(Date), MONTH(Date)
> I am not sure if this is best way to do this. Also I still need to get rid
> of time part.
> Thank you.
>
>|||You can avoid string conversions doing it this way:
dateadd(month,datediff(month,0,dt_col),0
)
Steve Kass
Drew University
Shimon Sim wrote:

>I need subquery that would return table with a column that contains first
>date of month.
>I would be prefer avoid using temp tables if possible.
>I have a status table So I can do following
>SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
>DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
>FROM Status
>WHERE Status.Date>='7/1/2005'
>GROUP BY YEAR(Date), MONTH(Date)
>I am not sure if this is best way to do this. Also I still need to get rid
>of time part.
>Thank you.
>
>
>