Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Monday, March 26, 2012

Easy newbie question

Sorry if this seems too easy to be interesting - I have yet to find an
elegant solution, as I am completely new at this so don't really know
what to look for. I have the following table:

Id Cat
1 A
2 B
3 C
4 A
5 B
6 B
7 C
...

I would like to create a new column, CatId, which has a value n, which
is the nth appearance of the record's category (ordered by Id). In
this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
appearance of the letter B so it would equal 3.

How could I do this in T-SQL?On 19 Mar 2007 11:26:36 -0700, jrpfinch wrote:

Quote:

Originally Posted by

>Sorry if this seems too easy to be interesting - I have yet to find an
>elegant solution, as I am completely new at this so don't really know
>what to look for. I have the following table:
>
>Id Cat
>1 A
>2 B
>3 C
>4 A
>5 B
>6 B
>7 C
>...
>
>I would like to create a new column, CatId, which has a value n, which
>is the nth appearance of the record's category (ordered by Id). In
>this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
>appearance of the letter B so it would equal 3.
>
>How could I do this in T-SQL?


Hi jrpfinch,

SELECT a.Id, a.Cat,
COUNT(*) AS CatId
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.Cat = a.Cat
AND b.Id <= a.Id
GROUP BY a.Id, a.Cat;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 19 Mar, 18:26, "jrpfinch" <jrpfi...@.gmail.comwrote:

Quote:

Originally Posted by

Sorry if this seems too easy to be interesting - I have yet to find an
elegant solution, as I am completely new at this so don't really know
what to look for. I have the following table:
>
Id Cat
1 A
2 B
3 C
4 A
5 B
6 B
7 C
...
>
I would like to create a new column, CatId, which has a value n, which
is the nth appearance of the record's category (ordered by Id). In
this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
appearance of the letter B so it would equal 3.
>
How could I do this in T-SQL?


To ensure you get a good answer to a question like this always state
what version you are using and include a CREATE TABLE statement that
defines the key(s) in your table.

Here's my guess based on the assumption that (Id, Cat) is unique. Note
that if CatID is always to be based on the values of the other two
columns then it might make more sense NOT to include it in the table.
Instead compute it whenever the results are displayed.

UPDATE tbl
SET CatID =
(SELECT COUNT(*)
FROM tbl AS t
WHERE Cat = tbl.Cat
AND ID <= tbl.ID);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Thursday, March 22, 2012

Easy (?) data-editing solution? Need help pls.

I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

Example:

Current data: "Bloomfield, CT"

Needs to become: Bloomfield

In other words, I need to remove the left quote, and everything after (including) the comma.

there are dozens of different cities in the DB like this.

I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

Help?Most databases have a substring and in-string (or position string) function which you can use in combination to do parsing. My example is oriented to DB2:

SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)
Result would be Bloomfield

You can use the above in an UPDATE statement:
UPDATE table SET city = SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)

Originally posted by rexnervous
I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

Example:

Current data: "Bloomfield, CT"

Needs to become: Bloomfield

In other words, I need to remove the left quote, and everything after (including) the comma.

there are dozens of different cities in the DB like this.

I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

Help?|||Also, use the 'REPLACE' and 'GRATER' functions to eliminate the quotes:

UPDATE mytable
SET city = REPLACE(SUBSTR(city, 1
,GREATER(POSSTR(city, ',') - 1,LENGTH(city)))
,'"','')
WHERE POSSTR(city, ',') > 0
OR POSSTR(city, '"') > 0

:cool:|||Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.|||For Access take a look at Instr and Mid there is even a Replace. Have fun!

Originally posted by rexnervous
Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:
> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
Systems Programmer
use a unc path \\servername\shareName
Greg Jackson
PDX, Oregon
|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>
sql

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when
I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

Friday, March 9, 2012

Dynamically create an SSIS Bulk Insert Package

I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!

Cheers~

Do you have any more specific messages other than "FAILURE"?|||

No, unfortunately not. This is a new venture for me and I am not even sure that I am going about the creation process correctly. Quite honestly, I am getting 'pieces' of code from different places off of Microsofts Books Online and sort of putting them together. I have been able to successfully load a package template and execute it successfully, but creating the package from scratch has proven to be quite the challenge. I have found a plethora of samples in C#, but I have not been able to locate a full sample in VB .NET at this point.

The reason I only get a 'FAILURE' return is because I am simply saying DTSExecResult = pkg.execute() and then displaying the return value of the result.

Any suggestions, guidance or direction will be GREATLY appreciated at this point! Thanks a million!

Cheers~

|||Be sure to enable logging: http://technet.microsoft.com/en-us/library/ms136023.aspx

Also know that I'm not going to be much help with the coding. I'm learning on my own as well, but I'm just trying to gather specific information for others who will read this thread.

Also know that at some point you may be asked to share your code.|||

I will gladly share my code! Thank you so much for the replies thus far. I am hoping that I can get some great responses and learn as much as possible in the coming days about this topic. Talk to you soon!

Cheers~

|||Try saving the package (Application.SaveToXML) after creating it. Then open and run the package in BIDS. That makes it a lot easier to see what the problem is.|||

John, I am about to do this. However, what do you mean by running it in BIDS? Thanks.

Cheers~

|||Business Intelligence Development Studio -- or Visual Studio -- which is where you develop SSIS packages via the GUI.|||

OK, what I am seeing now is that the Destination connection is not available, and there are no columns mapped from the input source - (flat file) - to the ole destination table. I am assuming that this is because I am failing to do something correctly in the code to setup the package's BULK INSERT properties.

Are there any tutorials available to understand how to set all the properties for source and destinations, respectively? Any direction you might can provide is awesome!!!!

Again, I am finding bits and pieces here and there, but nothing that says "Hey, do it this way"...

Cheers~

|||

OK, I have made some progress. I now have a Flat FIle source and an OLEDB destination. My delimma at this point is mapping the SOURCE columns from the flat file to the DESTINATION columns of the OLEDB table. The Flat File does not have a header row and it is a pipe "|" delimited file. Any idea on how to map the source / destination columns?

Cheers~

|||

Do you have Output columns in the Flat FIle Source output? Save the package in code (example http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2179817&SiteID=1) and open it to check what it looks like.

If so then you should be able to connect the output to the destination's input, and select your columns. Mapping is a simple method call, but what you map to what is your logic really. In this example I just use index position becuase I "know" that my source and destination formats match.

Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.