padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value â'123â' and I want to pad it with â'00123â'.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:
> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value â'123â' and I want to pad it with â'00123â'.
>

PAD function in Data Tranformation Editor

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.

David

I don't think we have your full statement... I'm not seeing where you convert to a date...

Never-the-less, why not substring the "date" field and then cast it to DT_DBDATETIME?|||Question, how is Jan 1st, 2006, represented?

112006? Or 1012006?|||1012006|||

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

|||

davidg12 wrote:

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

Okay, so then this should be easy... If you want to do this in one derived column (are you guaranteed that the value is a correct and verified date?), then you can do something like:

(DT_DBTIMESTAMP)(SUBSTRING(RIGHT("0" + datefield,8),5,4) + "-" + SUBSTRING(RIGHT("0" + datefield,8),1,2) + "-" + SUBSTRING(RIGHT("0" + datefield,8),3,2) + " 00:00:00")

In an upstream derived column, you could do the "RIGHT("0" + datefield,8)" calculation, and then just use that field in the above expression. It would make it easier to read.
|||

Thank you Phil. This works great.

David

Packages in SQL Server

Hi: Can any one please give me an example of Package in SQL server which i can test.
Thanks.What sort of package? Maybe you should ask at UPS?|||I am guessing you are coming from Oracle. MSSQL does not have packages like Oracle. However in 2005, there is something sort of similar, which is a CLR assembly. You can implement procs, functions, types, triggers, etc in an assembly and then expose them in sql.

Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms131046.aspx

Package Synchronization?

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Assert.True wrote:

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Control access to the package so that it DOESN'T get fired simultaneously.

-Jamie

|||You can create Agent Job with your package. The Agent ensures only one instance of a particular Job is executed at the same time. Of course, it will be your responsibility to make sure the package is only executed via Agent (from ASP.NET - use sp_start_job SQL stored proc).

Package configuration - registry entry problems

I want to access a registry entry in a package configuration but am having problems. I am looking for some example values for the "Registry Entry" field in the Package Configuration Wizard.

The test is a simple one, fill a variable with a registry value and present it in message box. I have tried a number of different values but can't seem to get the right one. Here are somethat I tried:

Software\\MyCompany\\ImportExport\\ServerName\\Value
Software\\MyCompany\\ImportExport\\ServerName

HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport\\ServerName
HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport\\ServerName\\Value

Software\MyCompany\ImportExport\ServerName\Value
Software\MyCompany\ImportExport\ServerName

HKEY_LOCAL_MACHINE\Software\MyCompany\ImportExport\ServerName
HKEY_LOCAL_MACHINE\Software\MyCompany\ImportExport\ServerName\Value

HKEY_CURRENT_USER\Software\MyCompany\ImportExport\ServerName\Value
HKEY_CURRENT_USER\Software\MyCompany\ImportExport\ServerName

HKEY_CURRENT_USER\Software\MyCompany\ImportExport
Software\MyCompany\ImportExport

HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport
Software\\MyCompany\\ImportExport

jcl,

I just realized you have double posted this question. I have already replied in the other thread. Here is what I replied:

--

cj See if you can find the answer here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=187656&SiteID=1

|||

Sorry. I still cannot read the registry entry.

In the registry:

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration

then I have a string value called:

MyParameter1

and the value data is set to:

MyTestValue

I want to see MyTestValue. What do I put in the Package Configuration Wizard text box? These did not work:

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

HKCU\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

Software\MyCompany\MyPackageConfiguration\MyParameter1

\Software\MyCompany\MyPackageConfiguration\MyParameter1

HKCU\Software\MyCompany\MyPackageConfiguration\MyParameter1

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration\MyParameter1

Own forum - number of posts and stars/ranges - how to make it ??

So I have simple forum. At main page I have gridView which display topics. When I go to topic #5 (for example) I use repeater to display posts and authors. Here is a code:

SELECT aspnet_Users.UserName, forum_posts.post_id,
forum_posts.post_content, forum_posts.topic_id, forum_posts.post_date
FROM aspnet_Users
INNER JOIN forum_posts
ON aspnet_Users.uID = forum_posts.user_id
WHERE (forum_posts.topic_id = @.topic_id)

Now I have a problem - how can I display with each user his number of posts - eventually how can I display for example if he has 25 posts - one star, 50 posts - two stars, or maybe display "Starter" range, etc. How to make this, cause I don't have any idea.

This is complex stuff but the creator of the London SQL Server user group have very nice existing code which also include a function so go to the link below and in the left search pane write in Full text index and you will see his presentation PPT slides you will get all you need in it. Hope this helps.

http://www.sqlserverfaq.com

|||

Thx for reply, but I don't know what I have to look for:

in the left search pane write in Full text index and you will see his presentation PPT

My english is not good, so forgive me, what I have to look for ? :)

|||

If what you are looking for is like what is in this thread then send me an emailCaddre@.hotmail.com and I will send you the whole presentation. Hope this helps.

http://forums.asp.net/thread/908572.aspx

|||

I sent an email to you :-)

You see - on the left from the post message here you have your name, photo, date of join and number of posts. I want this number and also make some stars/ranks depends on the number of posts. How to do it ? :-)

Little explaination about what I have:

I have Asp.net tables (by Membership).

I have also table "forum_topics":

topic_id; topic_title; user_id; topic_catId; topic_date

And table "forum_posts":

post_id; topic_id; user_id; post_content; post_date

I use stored procedures to add topic/post.

I use repeater to display posts from topic, to display topics I use gridView.

I hope you will help me :-)

|||I have sent you the file you need a function to get that info from a T-SQL tree code and it also include FTP address to get the .NET code if you need it. The person in that thread went through a lot but the file helped the person because it includes your create table statement.|||

FTP doesn't work :-(

But as you said I have to look for function in T-SQL to take out this numbers, etc, yes ?

|||All the T-SQL code is in the PPT slides and you can send Tony an email I have sent him an email 6years ago. You have to take time and create your version because the person in that thread got all they needed.|||

Ok, I've made to display a UserName with posts count :

SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost
FROM aspnet_Users INNER JOIN
forum_posts ON aspnet_Users.uID = forum_posts.user_id
GROUP BY aspnet_Users.UserName

Now simply I have to make something to calculating stars depend on posts.

But I think that I could do this by the code-behind in asp - simply comparing:

x - number of posts:

0<x<20 - display one star
21<x<50 - display two stars
etc.

Do you think that this is good way to do that what I want ?

|||

I have told you it is not that simple and you don't need a Group By, here is the baseline code, now go do some copying of the code in the file I sent you.

CREATE TABLE mb_mesage(
id int IDENTITY (1, 1) NOT NULL
parent_mb_message_id int NULL

entry_date datetime NOT NULL DEFAULT (GETDATE()),
parent_entry_date datetime NOT NULL DEFAULT (GETDATE()),
flattened_tree Varchar (900) NOT NULL
DEFAULT(CONVERT(varchar(20), GETDATE(),121) + REPLICATE('0',50)),
message_depth tinyint NOT NULL DEFAULT (0),
last_posting AS (dbo.fn_message_cal_last_posting(id)),
top_parent_mb_message_id
AS (LEFT(flattened_tree(CHARINDEX( ',', flattened_tree)-1)))
)

CREATE TABLE mb_message_detail(
mb_message_id int NOT NULL,
author_id int NOT NULL,
title varchar (500) NOT NULL,
body text NOT NULL,
timestamp timestamp NOT NULL,
)

NORMAl QUERY RECENT POSTING

CREATE FUNCTION fn_message_calc_last_posting (mb_message_id int)
RETURNS datetime
AS
BEGIN
DECLARE @.dt datetime=
(SELECT MAX (entry_date)
FROM mb_message
WHERE flatened_tree LIKE CAST(@. mb_message_id AS varchar (20)) + ',%'
)
RETURN @.dt
END

----
SELECT*
FROM mb_message
WHERE parent_mb_message_id IS NULL
ORDER BY Last_post DESC

To get all children posting simply use LIKE using the root message ID as the source

DECLARE @.root_mb_message_id int
SET @.root_mb_message_id =100895

SELECT spacers =REPLICATE ('+-' message_depth ),
mb_flattened_tree,
entry_date = mb_entry_date,
reads = CAST (mb_message_reads AS varchar (20)),
mb_parent_mb_message_id,
mb_message_id = mb_id,
mb_message_depth,
FROM mb_message mb,
INNER JOIN mb_message_detail md On md mb_message_id = mb_id
WHERE flattened_tree LIKE CAST (@. root_mb_message_id AS vachar (20) ) + ',%'
ORDER BY flattened_tree