First, I will apologise in advance for my ignorance, but I am new to the subject of SSIS.
My objective is tbe able to execute a stored procedure from SSIS and have a log of what the stored procedure did - preferably a log file
At the moment the stored procedure diagnostics etc are via T-SQL Print statement .
When I execute the stored procedure from SSIS I don't get any of the print diagnostics.
I've experimented with raiseerror - 1 severity < 11 gives no output, but a severity of 11 results in the print diagnostics being displayed in the output window - but the task goes red.
i would like to enquire about the Print statement. If I do not want to use the Raiseerror to raise an error as its not an error, but want to just print out in a dialog box, how do i do it?I tried using the Print statement but nothing happened. I am using this inside my trigger, can someone help?Thank You
I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.
I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.
I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.
Hey i have written a constraint trigger that will return an error message, but i want to include values from a count in the error msg. i know how it would be doing in oracle
RaisError('You cannot add more then' ||Bedroom_Count|| 'student tenants into this house', 10, 1)
I have a stored procedure contains a few PRINT statement that return the status to the client. On the client side, I am using vb.net to add handler for it, as follow:
'-------------------------------------------------------------------------------- con = New SqlConnection("Data Source=.SQLEXPRESS;Initial Catalog=IntranetMaster;Integrated Security=True;Persist Security Info=False;Connect Timeout=0")
AddHandler con.InfoMessage, New SqlInfoMessageEventHandler(AddressOf ShowSQLMessage)
Sub ShowSQLMessage(ByVal o As Object, ByVal e As SqlInfoMessageEventArgs) Dim err As SqlError For Each err In e.Errors Console.WriteLine(Now() & " - SQL Message: " & err.Message) Next err End Sub
The result is that the message from several PRINT statements are coming out only at the end of the stored procedure and all in once, so I cannot time the individual processing time.
Is there anything I have done wrong? or anyway to make the PRINT result comes out immediately?
We have some SQL CLR code that was written to go get information from a web service...all works great and the CLR code does what it is supposed to. The problem is, it returns its success or failure status as a string text value (equivalent of a print statement I imagine) instead of as a result set or output parameter that i can check. In the proc I wrote to call the CLR stored proc I do some cleanup that i would like to rollback if the call to the web service that the CLR proc makes fails. Problem is, how can i get there. I have tried every way that I know and I can't seem to get that text into an object (temp table, variable whatever) that i can evaluate. Anyone have any suggestions?
I want to create store procedure which will print out something like this:
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) But first one print out good, as I expected. I either got nothing or the error message on second piece.
Msg 245, Level 16, State 1, Line xxx Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int. Any Help will be greatly appreciated.
on SQL 2005 SP1. Here is my example code (not the real one): Create Procedure [dbo].[makeNewString] @myBookID As Declare bookID int Declare newRowID int Declare insertBookInfoString nvarchar(150) select 'Declare @newBookID int' Set insertBookInforString = (Select 'Insert into [dbname].dbo.[gameBooks] value (' + @gameBookID + ',' + gameBookTitle +',' + ganeVolumn +')' from [dbname].dbo.[gameBooks]) where @gameBookID=@myBookID Print insertBookInforString select 'SET @newRowID = Scope_Identity()' print @newRowID select 'Declare @newBookID int' Set insertBookInforString = (Select 'Insert into [dbname].dbo.[cookBooks] value (' + @cookBookID +',' + cookBookTitle +','+cookVolumn+')' from [dbname].dbo.[cookBooks] where @cookBookID=@myBookID) Print insertBookInforString first insertBookInfoString prints out fine. But I kept this error on second part:
------------------------------ DECLARE Declare @newBookID int (1 row(s) affected) Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) (this is what I want) ----------------------------------------- SET @newBookID = Scope_Identity() (1 row(s) affected)
Msg 245, Level 16, State 1, Line xxx Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
I'm trying to cath content of PRINT statements through InfoMessage event. I have subscribed on InfoMessage event through ConnectionEventsVt interface. The problem that event fired only once for statement. For example i have following T-SQL script:
Code Snippet
PRINT 'Message1' PRINT 'Message2' GO
PRINT 'Message3' PRINT 'Message4' GO
When i execute this script InfoMessage event fired twice. First time Error object Description property contains "Message1" text. Second time it contains "Message3" text.
What i'm doing wrong?
Server: MS SQL Sever 2005 OS: Windows XP SP2 IDE: Delphi 7
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
We are having problems printing Reports (when printing by clicking on the AcitiveX print control), where the font for the fields are set to "C39HrP24DhTt" (barcode).
While viewing the report it displays as Barcodes but while printing, the Barcode does not get printed, but the string gets printed.
Environment: SSRS 2005
Using the ReportViewer Control in a .Net 2.0 Web App to render the reports.
BarCodes print fine in the following situations:
1. When the Report is exported to Excel and when printed from there
2. When you click on the print button on Internet Explorer
3. When saving as html (from view source) and opening that html document and printing.
BarCodes do NOT get printed in the following situation:
1. When printing by clicking on the "Print" (Active X Control) icon. Even the "Print Preview" does not show the Barcode.
UPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
I need to get value from one table and if it is (for example) string - TodayDate, I need to change it to "Today Date" with a space in it and use it later in my Data Flow.
name of variable: myVar Scope: Data Flow Task Data Type: String Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
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.
I have some "Execute T-SQL Statement Tasks" in a package. I would like to run this same package on another SQL Server without having to change it on the other server. Since the server name can be given when setting up the connection, I think if I leave the server name out then the package could run on any server? Is my assumption correct?
I am using a OLE DB source task and i want to setup a parameter in the select statement. Is this possible and how would this be done. I know you can pass a parameter in the where clause like:
select id, lname, fname, startDate, endDate from Employee where id = ?
How would i set startDate and endDate as parameters as i would like to be able to change these parameters when running the package. I would like to set dates in the select statement as parameters like how you would in the where clause.
Select id, lname, fname, ? startDate, ? endDate, from employee where id = ?
I would like to be able to change these dates without having to go in to the package and hard code it back in there and then deploy.
The package uses a OLE DB source to Flatfile Destination.
CASE WHEN [MAIN_GAME] IS NULL AND combo.[CAT_PRODUCT] ='25' THEN 'Standalone' WHEN [MAIN_GAME] IS NULL AND combo.[CAT_PRODUCT] <> '25' THEN 'No main game' ELSE LOWER(prod.[TX_PRODUCT_NAME]) END AS [TX_MAIN_GAME]
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
This is so stupid of a question but is there a simple way to print (to a printer) the results of SELECT * FROM SkillGoals --- To show all data(including column headings?)
I have a long running (48 hour+) stored proc. I've added PRINT statements that print "1%", "2%", etc to provide progress so I can get a ballpark idea of how far along the process is. I ran the stored proc and realized that this won't work. I don't see the output of the print statements until the stored proc is completed. I'm running this from Query Analyzer (SQL Server 2000 SP3a).
For example, the following will wait ten seconds and then print both statements; it doesn't print one then wait, then print the other. Is there any flush command to make the print statements take effect immediately?
I'm trying to use the PRINT statement to trace what an SP is doing, while running in QA. However, some PRINTs seem not to print anything. I can see that the code part is being executed. Any ideas? (It's just simple prints like print 'executing an insert' .)
Is there an easy way to print off a sql table ? I wanted something along the lines of an Access query that you can print. Or is it easiest to link to the SQL table and use Access to display it ?
just wanted to get sql to stamp the date/time to the screen when i'm running a query.
i tried:
print 'Formatting Table1' print datetime go
got the following error: Msg 128, Level 15, State 1, Line 2 The name "datetime" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Hi,In the query analyser if I type the following:DECLARE @SortOrder integerexec pr_Admin_SetFieldSortOrder @SortOrderprint @SortOrder@SortOrder is never printed. I just get the message saying that theQuery Batch Completed.Why ?Regards
