Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So I
datepart each portion of the date and then add zeros when needed and string
it back together.
When trying to do some of the checks on the dates
set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
if(@.@.ERROR=0)
begin
--set @.Day
--set @.Year
end
else
begin
set @.NewDate=@.myDate
end
the SQL will blow up on the first line SOMETIMES
Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
the error it keeps on chugging along
Whats the deal?
JP
.NET Software DevelperIn case that @.myDate is type of varchar.
Datepart will raise an error only when you pass a value which cannot be
recognized as valid datetime format. Datepart of null returns null and,
consequently, Conversion of null returns null => no error.
MSDN Quote:
SQL Server recognizes date and time data enclosed in single quotation marks
(') in these formats:
-Alphabetic date formats (for example, 'April 15, 1998')
-Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
-Unseparated string formats (for example, '19981207', 'December 12, 1998')
In your specific case Convert function cannot raise an error.
Regards,
Marko Simic
"JP" wrote:
> Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So
I
> datepart each portion of the date and then add zeros when needed and strin
g
> it back together.
> When trying to do some of the checks on the dates
> set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
> if(@.@.ERROR=0)
> begin
> --set @.Day
> --set @.Year
> end
> else
> begin
> set @.NewDate=@.myDate
> end
> the SQL will blow up on the first line SOMETIMES
> Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
> the error it keeps on chugging along
> Whats the deal?
> --
> JP
> .NET Software Develper
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Tuesday, March 27, 2012
Saturday, February 25, 2012
.mdf to .mdb
My web host only supports .mdb database files, so is there a way to convert .mdf to .mdb with a program? Or would I have to do it by hand?
Thank you.
I am not aware that such a program exists.
Maybe you can use SQL Server DTS or SQL Server Transformation Services to download your database into a .mdb format.
|||Try to use this.http://www.data-conversions.net/products.php?prod_num=1&&dest=MENU&&ID=100|||
I was having the same issue and followed these steps...
1. In your ODBC connections add a SQL server connection to your current SQL server and database.
2. Create a new Access Database
3. Import from ODBC source and select all tables.
The tables will import with a dbo_ prefix, but all of the structure and information will be there. It does not carry over the table relationships either.
Nick
.mdf to .mdb
My web host only supports .mdb database files, so is there a way to convert .mdf to .mdb with a program? Or would I have to do it by hand?
Thank you.
I am not aware that such a program exists.
Maybe you can use SQL Server DTS or SQL Server Transformation Services to download your database into a .mdb format.
|||Try to use this.http://www.data-conversions.net/products.php?prod_num=1&&dest=MENU&&ID=100|||
I was having the same issue and followed these steps...
1. In your ODBC connections add a SQL server connection to your current SQL server and database.
2. Create a new Access Database
3. Import from ODBC source and select all tables.
The tables will import with a dbo_ prefix, but all of the structure and information will be there. It does not carry over the table relationships either.
Nick
Sunday, February 19, 2012
*Help* Function to convert a column to a scalar value - how?
Hi,
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>
Thursday, February 16, 2012
*.mdf to *.sdf
Hi!
Is there any way to convert *.mdf database to *.sdf ?
Is there any way to convert *.mdf database to *.sdf ?
Thank YouI've found special tool for that
It's called Data Port Wizard. It copies data from SQL Server / Access to / from SQL
CE 2.0 or SQL Mobile. You can get it here: www.primeworks.pt|||Hi Andrew is there any freeware to convert mdf to sdf. coz the primeworks trial period is only for 30 days.|||It would only take a couple of hours to write you own version
For each table in .mdf
Get .mdf schema
Write same schema in .sdf
For each table
read .mdf until eof
INSERT into .sdf
|||
hi Brian,
Thanks a lot for your help. I am a student in singapore, and i am now developing a soft ware in vb.net CE . Is it possible for you to give me a sample coding for this conversion if it is available with you.
Thank you
*.mdf to *.sdf
Hi!
Is there any way to convert *.mdf database to *.sdf ?
Is there any way to convert *.mdf database to *.sdf ?
Thank YouI've found special tool for that
It's called Data Port Wizard. It copies data from SQL Server / Access to / from SQL
CE 2.0 or SQL Mobile. You can get it here: www.primeworks.pt|||Hi Andrew is there any freeware to convert mdf to sdf. coz the primeworks trial period is only for 30 days.|||It would only take a couple of hours to write you own version
For each table in .mdf
Get .mdf schema
Write same schema in .sdf
For each table
read .mdf until eof
INSERT into .sdf
|||
hi Brian,
Thanks a lot for your help. I am a student in singapore, and i am now developing a soft ware in vb.net CE . Is it possible for you to give me a sample coding for this conversion if it is available with you.
Thank you
*.mdb to *.sdf
Is there any way to convert *.mdb database to *.sdf ?
You can use Access Database Synchronizer (ADS) from Microsoft. http://www.microsoft.com/downloads/details.aspx?FamilyId=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en
There are many things which you can do with this, for e.g. in addition to migrating data from a .mdb file to a .sdf file, you can also use ADS to keep them both in sync! You can also program against ADS using SqlCeRemoteDataAccess object of SQL Server 2005 Compact Edition. For details, please refer to the readme and the sample code which accompanies ADS.
HTH,
Deepesh
Subscribe to:
Posts (Atom)