How To Accomplish What I Am Trying To Do?
Mar 20, 2007
Hello,
I need to write a package the does the following, and not sure the best approach:
The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.
That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.
Also, each time I create the new output file, I need to delete the old named file.
I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.
Any help appreciated!!
Thank you
View 3 Replies
ADVERTISEMENT
Nov 29, 2006
I have a table and one of the fields is a datetime and named "Finish." What I would like to do is when the value in Finish has expired ( FINISH < NOW ) to call a procedure and change the status to of this record. I was thinking to set a MS SQL timer/delay on each row - but that does not seem pratical or is it?
View 5 Replies
View Related
Jan 28, 2007
I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.
I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:
Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed
In the end I want to create two tables like so:
Table 1:
Event A / Closed
Event B / Modified
(Event C is not here because it was voided, so any prior records of C are gone).
Table 2:
Event A / Property A / Closed
Event A / Property B / Created
(Only events with properties would be in this table).
In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.
Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.
"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).
I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).
I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.
So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?
Thanks for your help.
View 2 Replies
View Related
Aug 17, 2007
I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :
I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table.
Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).
The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes --->Customer on the CustomerID field.
So basically here are the logical steps:
1.- Read Policy records from foxpro table
2.- insert record to SQL Server PolicyAttributes table
3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table.
4.- Get the unique number from the new Customer record ( Primary Key of Customer record)
and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ).
This way I can keep my PolicyAttributes---->Customer relationship.
Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers.
So if there are 4 customers then I will have to insert 4 records in the customer table.
CustomerName_1
CustomerName_2
CustomerName_3
CustomerName_4
Any help is greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!.
Thanks,
Sergio
View 2 Replies
View Related
Aug 5, 2007
Hi folks,This is my first post on the SQL forum and maybe it's a dummy question but... i didn't figure it out.Also, i'm not sure if it's the right place for this type of questions, excuse me if i'm on the wrong spot.
Here we go: I'd like to have on my web sites some articles and bellow each article a list of "Related Articles".I started with something like this:
After that, i noticed that i have an "One to Many" relationship between the 2 tables (fields ArticleID) but, if i try to create another relationship like ArticleRelated.ArtigleRelatedID -> Articles.ArticleID it will looks like a circle between the 2 tables and i'm not sure if it's the best way (even if it will work).
Before somebody ask "Wy do i need this third relationship", besides other good reasons, i'm using "LINQ To SQL" and i just drag and drop the tables to the designer window and it automatically create classes and colections based on the existent relationship.I appreciate any help.
View 2 Replies
View Related
Dec 18, 2007
I'm looking to build a menu / sitemap out of my 2005 SQL Server database, an example of my table structure is:
ID PageTitle ParentID
=======================================
1 Home 0
2 About Us 1
3 Contact Us 1
4 About Us / Sub 1 2
5 About Us / Sub 2 2
6 About Us / LVL 3 5
So essentially this menu should display as follows:
Home
About Us
About Us / Sub 1
About Us / Sub 2
About Us / LVL 3
Contact Us
Is it possible to write a query that would output menu in that specific order?
View 6 Replies
View Related
Mar 17, 2004
I have tried and tried but I get all types of errors.Someone I hope has done this and perhaps can explain in more detail how to get this done.
I have my Store Procedure....
I added output to the data I will be needing. at the bottom I added my code....I need to some how run this procedure...Get all the info in the select statement and display it in a form.I want to use a variable for each field...Ihave no clue why my code does not work...
CREATE procedure dbo.App_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15)
)
as
select
UserName OUTPUT,
UserPassword OUTPUT,
UserClinic OUTPUT,
UserTester OUTPUT
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password
GO
When I type in a 'correct Username/Password' it gives me this error
Procedure or function Appt_Login_NET has too many arguments specified. I just want to contain the values in separte variables for later use...Also how do you use rowcount????
my Code
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New String("Appt_Login_NET")
Dim mycommand As New SqlCommand(cmd, con)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Parameters.Add("@LoginName", SqlDbType.NVarChar, 12)
mycommand.Parameters("@LoginName").Direction = ParameterDirection.Input
mycommand.Parameters("@LoginName").Value = txtUser.Value
mycommand.Parameters.Add("@Password", SqlDbType.NVarChar, 12)
mycommand.Parameters("@Password").Direction = ParameterDirection.Input
mycommand.Parameters("@Password").Value = txtPass.Value
mycommand.Parameters.Add("UserClinic", SqlDbType.Int)
mycommand.Parameters("UserClinic").Direction = ParameterDirection.Output
con.Open()
mycommand.ExecuteNonQuery()
'str = cmd.Parameters("UserClinic").Value
Label1.Text = mycommand.Parameters("UserClinic").Value
con.Close()
End Sub
View 2 Replies
View Related
Apr 20, 2007
I'm trying to find if there is a combination of dataflow transformations that will produce the following result
SELECT
period,
project,
task,
employee = CASE
when empid in (SELECT DISTINCT empid FROM EmpTable) then empid
else 'Deleted Employee'
end
FROM ProjectTable
I know I can create a dataflow task with this query as a data source and then send it to a destination, but I was wondering if that is the best way to do it or if there was a better way to do this using the data transformations available in SSIS.
Any insight would be most appreciated.
Regards,
Bill Webster
View 4 Replies
View Related
Aug 20, 2007
I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.
I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.
I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.
Thank you,
Steve
View 10 Replies
View Related
Jan 28, 2008
Subject line pretty much sums up the question.
Any information and/or links would be appriciated greatly!
View 1 Replies
View Related
