Change filename for flat file connection manager at runtime

I need to add the current date to the end of the filename of a flat file at runtime.

This was previously done with ActiveX script in SQL Server 2000.

oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing

I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!

Regards,
Sara

Basically, use property expressions: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

|||

Hi Sara
I am working with SSIS packages and am having the same problem that you faced some time back
Am getting an error message in SSIS package in the following lines:

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename

Set oConn = Nothing

Could you tell me what you did to fix your code. Would really appreciate your time and response.

Thanks

Rishi...

|||

Hi Rishi,

I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file.

I hope this gives you an idea of how to solve your problem!

Regards,
Sara
--

Script Task
Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt
File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt

Imports System

Imports System.IO

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim theDate As String

Dim i As Integer

strPath = "L:\vpkba\laddning\EDBkjerne\"

theDate = Now.Year & IIf(CInt(Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf(CInt(Now.Day) < 10, "0" & Now.Day, Now.Day).ToString

For i = 0 To Dts.Connections.Count - 1

If Dts.Connections(i).Name.Contains("YYYYMMDD") Then

Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace("YYYYMMDD", theDate)

End If

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Change filename for flat file connection manager at runtime

I need to add the current date to the end of the filename of a flat file at runtime.

This was previously done with ActiveX script in SQL Server 2000.

oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing

I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!

Regards,
Sara

Basically, use property expressions: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

|||

Hi Sara
I am working with SSIS packages and am having the same problem that you faced some time back
Am getting an error message in SSIS package in the following lines:

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename

Set oConn = Nothing

Could you tell me what you did to fix your code. Would really appreciate your time and response.

Thanks

Rishi...

|||

Hi Rishi,

I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file.

I hope this gives you an idea of how to solve your problem!

Regards,
Sara
--

Script Task
Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt
File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt

Imports System

Imports System.IO

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim theDate As String

Dim i As Integer

strPath = "L:\vpkba\laddning\EDBkjerne\"

theDate = Now.Year & IIf(CInt(Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf(CInt(Now.Day) < 10, "0" & Now.Day, Now.Day).ToString

For i = 0 To Dts.Connections.Count - 1

If Dts.Connections(i).Name.Contains("YYYYMMDD") Then

Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace("YYYYMMDD", theDate)

End If

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

change datetime to date

I have a datetime field (example: 08/06/2007 9:05:06AM) that I want to change so only the date is in a select statement (so I only get 08/06/2007). How do I write the expression?
Help is appreciated.
Thanksthere is no good way that i know of to do this in ANSI SQL

which database are you using?|||Thanks for posting to my thread. I found a workaround (even though it ain't pretty) but it's working so thanks again for posting.|||what was your workaround?

change date&time default formate to mm/dd/yyyy

dear

i've an old sql server 2000, with default date format mm/dd/yyyy

i've installed a new one SQL server 2005 , my application did't work with its date format

i wan't to change SQL 2005 default date format to mm/dd/yyyy instead of dd/mm/yyyy

regards

Dates are displayed as configured for the user (its a session setting). But formatting of the dates should be made in the front end / presentation layer. Don′t do that on the server, try to pass the date in a more generic format like ISO Date. 20061114

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Change date with trigger

I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Quote:

Originally posted by strellita
I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Needs:
INNER JOIN i.[id column] = preuba.[id column]
after your FROM statement
then it will work correctly.
Full example:
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE p SET datepm = getdate()
FROM inserted i
INNER JOIN preueba p ON i.[ID] = p.[ID]
END
GO
Peace,
tree

Change Date Format of Field value

What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.

I only want to remove Time from date and I am using Sql Server 2000.

Thanks

Arvind

Look at the cast and convert functions

I use cast(convert(varchar(10), getdate(), 101) as datetime)

sql

change date format from MMDDYYYY to DDMMYYY ?

Hi,
My report dates are coming out in the following format MM/DD/YYYY. How can I
change this to DD/MM/YYYY.
i.e is this to do with IIS, reporting services or the report itself ?
(machine is DD/MM/YYY)
Thanks
Scottscott wrote:
> Hi,
> My report dates are coming out in the following format MM/DD/YYYY.
How can I
> change this to DD/MM/YYYY.
> i.e is this to do with IIS, reporting services or the report itself ?
> (machine is DD/MM/YYY)
> Thanks
> Scott
In tools/options set International Settings to be same as Microsoft
Office.
Find Report object on the Properties window dropdown and set the
Language property to English (UK)
This should now allow you to set the format of the textbox to
dd/mm/yyyy or similar. (Go to property pages for some default options -
right click text box and go to properties.)|||thanks
scott

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Hi,
I have a query to retrieve data from a table for the give two dates(from and
to) in a stored procedure.
select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
now I have give the dates like this to get the results
@.FromDate =01/01/2005(mm/dd/yyyy)
@.ToDate=6/2/2005(mm/dd/yyyy)
but I would like to give the date in this format (dd/mm/yyyy) like
@.FromDate =01/01/2005(dd/mm/yyyy)
@.ToDate=2/6/2005(dd/mm/yyyy)
can someone help me with this(I know there is a way in which you can specify
in select query itself, but forgot :-) )
Thanks
KiranWhy do you need to express the date in a special format? Why not let the end
user use the format the
end user want (based on regional settings) and let the application send a fo
rmat to SQL server which
is language neutral?
See http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:Ot61f56ZFHA.3840@.tk2msftngp13.phx.gb
l...
> Hi,
> I have a query to retrieve data from a table for the give two dates(from a
nd to) in a stored
> procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can speci
fy in select query
> itself, but forgot :-) )
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> I have a query to retrieve data from a table for the give two
> dates(from and to) in a stored procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND
> @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can
> specify in select query itself, but forgot :-) )
> Thanks
> Kiran
Always use a portable date format in SQL... or else.
YYYYMMDD
or
YYYY-MM-DDThh:mm:ss.mmm
David Gugick
Quest Software
www.imceda.com
www.quest.com

Change date format

Hello.
How can I change (global) date format from yyyy-MM-dd to my custom format?
Thank you.
For input or output?
I suggest you read below article. You find your answers in it:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I change (global) date format from yyyy-MM-dd to my custom format?
> Thank you.
>
|||I want change it globally for whole SQL Server?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
> For input or output?
> I suggest you read below article. You find your answers in it:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
>
|||That didn't answer my question. There no such thing as a global setting for SQL Server datetime
format. You have to handle input in one way and output in another way. See my article, the first few
sections. It has all the answers and options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23v4GnkBcFHA.3400@.tk2msftngp13.phx.gbl...
>I want change it globally for whole SQL Server?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
>

Change date format

Hello.
How can I change (global) date format from yyyy-MM-dd to my custom format?
Thank you.For input or output?
I suggest you read below article. You find your answers in it:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I change (global) date format from yyyy-MM-dd to my custom format?
> Thank you.
>|||I want change it globally for whole SQL Server?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
> For input or output?
> I suggest you read below article. You find your answers in it:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> Hello.
>> How can I change (global) date format from yyyy-MM-dd to my custom
>> format?
>> Thank you.
>|||That didn't answer my question. There no such thing as a global setting for SQL Server datetime
format. You have to handle input in one way and output in another way. See my article, the first few
sections. It has all the answers and options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23v4GnkBcFHA.3400@.tk2msftngp13.phx.gbl...
>I want change it globally for whole SQL Server?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> For input or output?
>> I suggest you read below article. You find your answers in it:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "David Dvali" <david_dvali@.hotmail.com> wrote in message
>> news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> Hello.
>> How can I change (global) date format from yyyy-MM-dd to my custom format?
>> Thank you.
>>
>

Change date format

HI,

I have noticed that SQL SERVER 2005 uses American date format but i want to change it to UK/European time format i.e. dd-mm-yyyy

can any on tell me how to do it in sql server settings. And is it possible that I can change it only the during the course of my STORED PROCEDURE.

I know it is quite and any one will answer that soon

hoping quick reply

regards,

Anas

I haven't used 2005 yet but assuming it has the same 'problem', make sure the language is set to 'british english' instead of 'english'|||

thanx for ur reply...

other query is that how will I able to change that setting for the course of my stored procedure? is it possible

waiting for your reply

regards,

Anas

|||That shouldn't be necessary once the database has been set to the correct language. But IIRC, you can set the language on a connection basis, but you should be able to locate this info in the SQLServer help file, I can't recall what the setting might be at the moment, probably SET LANGUAGE <language>

sql

change date format

I have a table with a date field. I need to convert the date so that it
returns in a text format of DDMMYY - no separators.
How do I do this?
I have done it like as follows: Is there a better way of doing it?
Here is what I have:
right(RTrim(convert(char(8),TrnDate,12)),2) +
substring(convert(char(8),TrnDate,12),3,2) +
left(convert(char(8),TrnDate,12),2) as DteVal
"Newbie" <noidea@.nospam.com> wrote in message
news:%23gfQUJ0zEHA.1192@.tk2msftngp13.phx.gbl...
> I have a table with a date field. I need to convert the date so that it
> returns in a text format of DDMMYY - no separators.
> How do I do this?
>
|||On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:

>I have done it like as follows: Is there a better way of doing it?
Hi Newbie,
Have you tried
REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the reply. I gave it a go but for the date 17/04/2000 it
returned
170420 instead of
170400
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com...
> On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:
>
> Hi Newbie,
> Have you tried
> REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's solution needs format 3, not 103:
This should work:
REPLACE (CONVERT(char(8), TrnDate, 3), '/', '')
Steve Kass
Drew University
Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
>
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com.. .
>
>
>
|||On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
Hi Newbie,
My bad. Halfway through my post, I forgot that you didn't want all four
digits of the year and I autopiloted back to a format that uses the
complete year.
Steve is correct: 103 should have been 3.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks - works a treat!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:i342q0dcnf10r39nn5m35q4u38cblgtiib@.4ax.com...
> On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:
>
> Hi Newbie,
> My bad. Halfway through my post, I forgot that you didn't want all four
> digits of the year and I autopiloted back to a format that uses the
> complete year.
> Steve is correct: 103 should have been 3.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Change date format

Hello.
How can I change (global) date format from yyyy-MM-dd to my custom format?
Thank you.For input or output?
I suggest you read below article. You find your answers in it:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I change (global) date format from yyyy-MM-dd to my custom format?
> Thank you.
>|||I want change it globally for whole SQL Server?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
> For input or output?
> I suggest you read below article. You find your answers in it:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:u1gf6dBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
>|||That didn't answer my question. There no such thing as a global setting for
SQL Server datetime
format. You have to handle input in one way and output in another way. See m
y article, the first few
sections. It has all the answers and options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23v4GnkBcFHA.3400@.tk2msftngp13.phx.gbl...
>I want change it globally for whole SQL Server?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OtEYGgBcFHA.3280@.TK2MSFTNGP09.phx.gbl...
>

change a named instance to default instance

| From: "Brian" <deeshubby76@.nospam.nospam>
| Subject: change a named instance to default instance
| Date: Wed, 22 Dec 2004 17:10:30 -0500
| Lines: 16
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <uoZWQMH6EHA.260@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: ip68-107-195-73.cl.ri.cox.net 68.107.195.73
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:44043
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Somehow, on my XP box, when I installed SQL Server 2000, I have only a
named
| instance running (and no default instance).
|
| So, I have these ASP.NET applications that have "web.config" files that
are
| trying to connect to "localhost".
| My apps don't work until I change the connection string to
| "computername\instancename".. even though there is only one SQL Server
| running on my local machine.
|
| Is there anyway I can make my named instance behave like the default?
|
| Thanks,
|
| BW
|
|
|
Hello Brian,
From your post, my understanding on this issue is: You'd like to know how
to change a named instance to the default instance. If I'm off base, please
feel free to let me know.
I truly understand this inconvenience this has brought you. However, there
is no documented, supported way to change a named instance of SQL Server
2000 to the default instance. Indeed, a re-installation is the usual
approach.
You could however, if it is feasible, just go to the client computer, and
use the Client Network Configuration program on that machine to create the
alias name and map that to your named instance of SQL Server 2000.
The following article is for your reference:
INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
Version's Client Tools (265808)
http://support.microsoft.com/default...B;EN-US;265808
The following steps are for your reference:
Configure an Alias by Using TCP/IP Sockets
NOTE: To configure an alias using TCP/IP, you must provide the server name
and TCP/IP port number.
1.
Determine the SQL Server instance port number.
To find the SQL Server instance port number:
a.
On the Microsoft SQL Server 2000 server, start the SQL Server Network
Utility.
b.
Click the General tab, and then select the instance you want from the
Instances drop-down menu.
c.
Highlight TCP/IP, and then click Properties. The port number for this
instance is shown. Write down this port number for use later.
2.
Configure the server alias on the client computer.
a.
Start the Client Network Utility.
b.
In the General tab of the Server Alias Configuration dialog box, click
Add, and then click TCP/IP.
c.
Enter an alias name in the Server Alias text box. The alias can contain
any name.
d.
Enter the SQL Server 2000 computer name or IP address in the Computer Name
text box. Do not add an instance name.
e.
In the Port Number text box, enter the port number you recorded in step 1.
Click OK twice.
Configure an Alias Using Named Pipes
NOTE: In order to configure an alias by using Named Pipes, you must provide
a Server name and a pipe name.
1.
Determine the server instance pipe.
a.
On the Microsoft SQL Server 2000 server, start the Server Network Utility.
b.
In the General tab, select the instance you want from the Instances
drop-down menu.
c.
Highlight Named Pipes, and then click Properties. Write down the pipe name
for use later.
2.
Configure the server alias on the client computer.
a.
Start the Client Network Utility.
b.
In the General tab of the Server Alias Configuration dialog box, click
Add, and then click Named Pipes.
c.
Enter an alias name in the Server Alias text box. The alias can contain
any name.
d.
Enter the pipe name you recorded from step 1.
e.
Make sure that you change the "." (period) to the actual SQL Server Name.
For example:
\\ComputerA\pipe\MSSQL$s2000\sql\query
Please do not add the instance name. Click OK twice.
I hope this help.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Sophie,
Thanks for the tips, since I'm developing locally, with a local SQL Server,
I was able to use the steps below to get things working perfectly.
There was one step that was not mentioned in what you shared:
After I configured the Alias and port number for TCP/IP in the Server
Network Utility, it did not initially work until I had disabled the PIPES
protocol and restarted my SQL service. (MSSQL$instancename)
Thanks for you help!
Brian
"Sophie Guo" wrote:

> Hello Brian,
> From your post, my understanding on this issue is: You'd like to know how
> to change a named instance to the default instance. If I'm off base, please
> feel free to let me know.
> I truly understand this inconvenience this has brought you. However, there
> is no documented, supported way to change a named instance of SQL Server
> 2000 to the default instance. Indeed, a re-installation is the usual
> approach.
> You could however, if it is feasible, just go to the client computer, and
> use the Client Network Configuration program on that machine to create the
> alias name and map that to your named instance of SQL Server 2000.
> The following article is for your reference:
> INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
> Version's Client Tools (265808)
> http://support.microsoft.com/default...B;EN-US;265808
> The following steps are for your reference:
> Configure an Alias by Using TCP/IP Sockets
> NOTE: To configure an alias using TCP/IP, you must provide the server name
> and TCP/IP port number.
> 1. Determine the SQL Server instance port number.
> To find the SQL Server instance port number:
> a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
> Utility.
> b. Click the General tab, and then select the instance you want from the
> Instances drop-down menu.
> c. Highlight TCP/IP, and then click Properties. The port number for this
> instance is shown. Write down this port number for use later.
> 2. Configure the server alias on the client computer.
> a. Start the Client Network Utility.
> b. In the General tab of the Server Alias Configuration dialog box, click
> Add, and then click TCP/IP.
> c. Enter an alias name in the Server Alias text box. The alias can contain
> any name.
> d. Enter the SQL Server 2000 computer name or IP address in the Computer Name text box. Do not add an instance name.
> e. In the Port Number text box, enter the port number you recorded in step 1.
> Click OK twice.
>
>
> Configure an Alias Using Named Pipes
> NOTE: In order to configure an alias by using Named Pipes, you must provide
> a Server name and a pipe name.
> 1. Determine the server instance pipe.
> a. On the Microsoft SQL Server 2000 server, start the Server Network Utility.
> b. In the General tab, select the instance you want from the Instances
> drop-down menu.
> c. Highlight Named Pipes, and then click Properties. Write down the pipe name
> for use later.
> 2. Configure the server alias on the client computer.
> a. Start the Client Network Utility.
> b. In the General tab of the Server Alias Configuration dialog box, click
> Add, and then click Named Pipes.
> c. Enter an alias name in the Server Alias text box. The alias can contain
> any name.
> d. Enter the pipe name you recorded from step 1.
> e. Make sure that you change the "." (period) to the actual SQL Server Name.
> For example:
> \\ComputerA\pipe\MSSQL$s2000\sql\query
> Please do not add the instance name. Click OK twice.
> I hope this help.
> Sophie Guo
> Microsoft Online Partner Support

Certificates expiration Date

Hi There

This may seem like a stupid question but i am trying to get the hang of the new security model.

I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.

We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?

Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?

In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?

Thanx

The certificate expiration date is not enforced by SQL Server directly. These certificates can still be used for data encrypting, decrypting, signing, verifying signatures or object creation/usage in SQL Server.

Service Broker will honor the expiration date and after a certificate has expired it cannot be used (See “Certificates for Dialog Security” http://msdn2.microsoft.com/en-us/library/ms166117.aspx ).For more information on Service broker usage of certificates I would recommend to visit Service Broker Conversations webpage at http://blogs.msdn.com/remusrusanu/.

While SQL Server itself will not prevent you from using an expired certificate, it is up to the application developers to enforce or suggest the expiration, and use the available usage of expired certificates as a disaster recovery tool rather than as a rule. I strongly recommend generating new certificates to substitute expired ones.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

certificate start date is tomorrow - how to make it today?

Hi,

I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).

Regards,

Simon.

If you are importing this certificate into SQL Server for use with encryption and signing, start and expiration are actually ignored by the server so you can use it as soon as you import it.

Otherwise, I would consult the documentation that came with Microsoft Security Server? I will try to do some research and post anything I find.

Thanks,

Sung

|||

Thanks Sung,

It's not actually for SQL server so maybe I missed the mark for the correct forum for this one.

I know lots of apps ignore the cert's validity dates which is maybe why this problem doesn't get reported more often. Also, I am in Australia so maybe their is some UTC effect going on.

Any help would be appreciated.

Simon.

|||

I think you are referring to the following known issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125262. The issue is that the certificate validity dates are stored based on the local time instead of using GMT. The workaround is to explicitly set the start_date to the desired GMT time.

Thanks
Laurentiu

|||

Sorry, this is not a SQL server problem but a CertSrv problem and I dont know how to specify these validity dates for it.

Question reposted on "Security for Applications in Windows Vista "

Thanks,

Simon.

|||

Btw, for anyone looking,

My problem was just the timezone on the server was GMT-8 (USA), not GMT+10 (Australia) so the certs were out by 24 hours.

Cheers.

Century date conversions

Hi,
I'm trying to load date fields into SQLServer using DTS, but the
format of the raw data is the number of days since 1 Jan 1900. How do
I convert this to a useful format, is there a standard conversion
routine?

Thanks
Timtim.philbrook@.cazenove.com (Helsop) wrote in message news:<1609a822.0408180233.9fbed33@.posting.google.com>...
> Hi,
> I'm trying to load date fields into SQLServer using DTS, but the
> format of the raw data is the number of days since 1 Jan 1900. How do
> I convert this to a useful format, is there a standard conversion
> routine?
> Thanks
> Tim

There are (at least) two possible solutions. First, load the data into
a staging table and clean it up with TSQL, before an INSERT into the
final table:

insert into dbo.Destination
(col1, datetime_column, ...)
select col1, dateadd(dd, numdays_column, '19000101'), ...
from dbo.Staging

Alternatively, if you want to do the transformation in DTS, then you
could use the VBScript DateAdd() function in an ActiveX column
transformation to achieve the same thing.

Simon|||--> use DATEADD(dd,@.OffsetDays,'1 Jan 1900')

DECLARE @.OffsetDays BIGINT
SET @.OffSetDays = 38217
select DATEADD(dd,@.OffsetDays,'1 Jan 1900')

Tx

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECT NON EMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ON COLUMNS, NON EMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ON COLUMNS FROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ON COLUMNS FROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ON COLUMNS FROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECTNONEMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ONCOLUMNS, NONEMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ONCOLUMNSFROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ONCOLUMNSFROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ONCOLUMNSFROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ONCOLUMNSFROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

Catch Date Gaps in Different Records

I need to write a procedure to check if I have any gaps in my dates...

Start Date.........End Date
10/6/2004.........10/6/2005
10/6/2003.........10/6/2004
7/10/2003........10/6/2003
7/10/2002..........7/10/2003

What I need to do is test the End Date to the next row under the Start Date Column. (in bold to clarify) (moving upward from bottom)

sSQL011$ = "SELECT * FROM TableName " & _
"WHERE ID = '" & ID & "'"
Set rs011 = DB.OpenRecordset(sSQL011$, dbOpenDynaset)
Set Data1.Recordset = rs011
rs011.FindFirst "ID = " & ID

With rs011
do while .eof = False

if .eof then
exit sub
end if

if rs011("StartDate") 'from one record, if it is not equal to the Start
Date in the next record then
MsgBox "Gap in Dates for: " + ID

loop

end with



pseudo...
if the end date in one consecutive row is not the same as the start date in the next row then there is a date gap

thanks...moving to sql server forum|||What database engine are you using (MS-SQL, Jet, Oracle, other)?

Are multiple active rows possible? For example, is it possible to have:Start End
1900-01-01 1925-01-01
1910-02-25 1930-02-16
1925-01-01 1950-01-01
1930-06-15 1980-07-04
1960-03-17 2000-01-01If you could have that, would there be gaps or no gaps in the coverage?

-PatP|||Records in SQL Server have no assumed order unless you specify it.

There is no such thing as "consecutive row" in SQL Server.

There is no such thing as "next row" in SQL Server.

How are you sorting your records? Please do not say: "I am sorting them by the date and time they were entered, though this is not stored anywhere in the table."