Proper Sorting of Months In DropdownList

I'm having an issue getting the data how I want it from SQL to then populate my dropdownlist.

I have a table called SALES_BUDGR_TBL that contains alot of columns but the ones I'm focusing on are SRCURM (Month) and SRACYR (Year). What I need to do is populate a dropdownlist and show the current month first and then work backwards. The data in the dabase goes back 14 months. I created a reference table called MonthOrder that has 3 columns, MonthID, MonthName and MonthAbrv. I did this so I have a way of saying which month belongs where when sorted.

Here is the select statement I currently have. I'm joining the tables and then I want to Order by the MonthID, but I would also like it to show the 2006 records first, then goto 2005, starting with December and going down of course.

Is there anyway to do this? My Select and Results are below...

SELECT DISTINCT SB.SRCURM, SB.SRACYR, MO.MonthID, MO.MonthName FROM SALES_BUDGR_TBL SB
INNER JOIN MonthOrder MO ON SB.SRCURM = MO.MonthAbrv
ORDER BY MO.MonthID DESC

DEC 2005 12 December
NOV 2005 11 November
OCT 2005 10 October
SEP 2005 09 September
AUG 2005 08 August
JUL 2005 07 July
JUN 2005 06 June
MAY 2005 05 May
APR 2005 04 April
APR 2006 04 April
MAR 2006 03 March
FEB 2005 02 Febuary
FEB 2006 02 Febuary
JAN 2006 01 January

If I say only get 2006 records it works but I need a better way to do this.

Please try to modify your ORDER clause to:

ORDER BY SB.SRACYR DESC, MO.MonthID DESC

|||

SELECT DISTINCT SB.SRACYR AS Year,DATEPART(month,SB.SRCURM+' 01, 2006') AS Month,CAST(SB.SRCURM+' 01, '+SB.SRACYR) AS MyDate FROM SALES_BUDGR_TBL SB
ORDER BY SB.SRACYR DESC,DATEPART(month,SB.SRCURM+' 01, 2006') DESC

|||Thanks, I got it working.

Programmatically get ScriptTask source code

Hi am trying without luck to load a package which contains a ScriptTask and read the source code of that task.

I can load the package and get the ScriptTask no problem.
However i am not sure how to get the source code.
I know i have to use the ScriptTaskCodeProvider and i assume the GetSourceCode() method.

This is what i have so far

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;
ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();
codeProvider.LoadFromTask(scriptTask);
string sourceCode = codeProvider.GetSourceCode(scriptTask.VsaProjectName);

Any assistance greatly appreciated.

Cheers
Richard.

Application application = new Application();

Package package = application.LoadPackage(@."C:\Yukon\ISPlaying\ISPlaying\Package14.dtsx", null);

TaskHost taskHost = package.Executables[0] as TaskHost;

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;

ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();

codeProvider.LoadFromTask(scriptTask);

string script = codeProvider.GetSourceCode(String.Format("dts://Scripts/{0}/ScriptMain.vsaitem", scriptTask.VsaProjectName));

Programmatically altering object code

Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software

Programatically create and configure a FlatFileConnectionManager

I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.

Here is some of my code:

Package p = New package();

ConnectionManager sourceCM = p.Connections.Add("FLATFILE");

sourceCM.Name = "some-name";

ConnectionManagerFlatFileClass cmffc = sourceCM.InnerObject()

I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:

ConnectionManagerFlatFileClass cmffc = (ConnectionManagerFlatFileClass)sourceCM.InnerObject()

Now it will compile. But when I run it I get the error:

"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"

Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?

-Jamie

P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!

OK, it looks like I can reference the Columns collection of my flat file connection manager using

sourceCM.Properties["Columns"]

but that returns me an object of type DTSProperty which I've tried casting to IDTSOutputColumnCollection90 but it says it cannot convert a Microsoft.SqlServer.Dts.Runtime.DtsProperty to Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumnCollection90 which, again, makes complete sense!

So, if anyone can tell me how I can programatically access the columns in my flat file connection manager then I'd be very grateful.

-Jamie

|||

DtsProperty.GetValue Method ?

DtsProperty is the definition of the property, and you want the actual value, which is where the GetValue method comes in.

I think the whole idea of this is to abstract you from the real class, such that they can be changed and updated at will without breaking existing code. Not too sure on the deal on binary compatability as we used to think of it, it seems to be less of an issue, but there is probably some good reason!

|||

Thanks mate, but that won't do it. However, I've managed to find the answer elsewhere (and for once in my life I didn't rely on Darren to give me the answer ). Namely, in this sample: http://msdn2.microsoft.com/en-us/library/ms161541.aspx

Here's the code that does it, turns out I DO need the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace:

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

...

private wrap.IDTSConnectionManagerFlatFile90 ffc = (wrap.IDTSConnectionManagerFlatFile90)sourceCM.InnerObject;

wrap.IDTSConnectionManagerFlatFileColumn90 col = ffc.Columns.Add();

OK, cool. I've got it working. It kinda begs the question tho, why is there no class in the Microsoft.SqlServer.Dts.Runtime namespace that enables us to do it? Its not like configuring a FlatFile connection manager isn't core functionality. I've asked the same question elsewhere (sort of). Hopefully it'll get answered.

-Jamie

program to insert the csv file to SQL server

Dear professional
I am writing a program to insert the csv file to SQL server.
However, the data source contains different charater like that:
ABC-001
ABC-002
BDE_001
BDE_002
have there is any SQL to convert the data as below:
ABC-001-->ABC001
ABC-002-->ABC002
BDE_001-->BDE001
BDE_002-->BDE002
ThanksBasically you need to concatenate. like
left(fieldname, 3) + right(fieldname,3)
Or you can use substring also
Amarnath
"Anton" wrote:
> Dear professional
> I am writing a program to insert the csv file to SQL server.
> However, the data source contains different charater like that:
> ABC-001
> ABC-002
> BDE_001
> BDE_002
> have there is any SQL to convert the data as below:
> ABC-001-->ABC001
> ABC-002-->ABC002
> BDE_001-->BDE001
> BDE_002-->BDE002
> Thanks
>
>|||"Amarnath" wrote:
> Basically you need to concatenate. like
> left(fieldname, 3) + right(fieldname,3)
> Or you can use substring also
> Amarnath
> "Anton" wrote:
> > Dear professional
> >
> > I am writing a program to insert the csv file to SQL server.
> > However, the data source contains different charater like that:
> >
> > ABC-001
> > ABC-002
> > BDE_001
> > BDE_002
> >
> > have there is any SQL to convert the data as below:
> >
> > ABC-001-->ABC001
> > ABC-002-->ABC002
> > BDE_001-->BDE001
> > BDE_002-->BDE002
> >
> > Thanks
> >
> >
> >
> >|||YOu can use Replace function. Replace '-' with ''