Hi guys, I know that the PRINT statements only print after a certain process is done, not when the actual PRINT statment is executed. Can someone explain when exactly does it print them and is there a way to make the print statment show right away?
I'm working on a stored procedure, and I'm nearing completion. The procedure takes a while to run (it's a very large dataset), but that's not my issue. The problem is that I include several print statements, but I don't get to see any of them until the entire procedure has finished. Is there anything I can do to be able to see them as they are printed? Using sql server 2000 with management studio for 2005.
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.
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?
I redirected errors the some err table I defined. SSIS package gives two columns for the error information. One is the ErrorCode, the other is ErrorColumn. But the values for those two are some numbers which I have no ideas of what it means. How to translate to some meaningful information?
I have old editions of SQL Server such as 6.5, 7.0, 2000, and 2005. The biggest hold up we have in retiring these servers is the connection string changes that would have to be made. Any way that I could redirect these calls to a newer SQL Server 2012?
Is there any way to redirect results to file directly while executing query? Like in management studio we can redirect result to Text or File.(Option Result to File)
I'm getting some strange error handling behaviour when I tried to redirect rows when an error is encountered at the OLE DB Destination. I enabled the fastload option and set the maximum insert commit size to 10,000. My package is trying to insert 1,000,000 rows. When the row containing bad data is encountered, the entire batch is redirected to a separate table with no table constraints. So I have approx 990,000 rows of good data inserted, and approx 10,000 rows in an error table. However, only 2 of the 10,000 rows are actually bad data.
I changed the filter in the data source to reload 5 rows from the 10,000 rows of bad data. The 5 rows include the 2 rows of bad data. The fastload option and max insert commit size at the OLE DB Destination are not changed. When the package completed, 3 rows were inserted to the destination and the 2 bad rows are redirected again. Even though this is the outcome I wanted (only the real bad data is redirected), shouldn't all 5 rows be redirected since they should all be treated as a single batch?
My second question is, is there a way to get the best of both worlds - be able to load lots of data as fast as possible and only redirect the rows that are actually bad?
I am developing a package which takes data from SQL Server 2005 and hits the JDE files.
In this package I need to send error mail when anyrow returns failure on OLE DB Command component which calls JDE Stored Procedure. So I created a script component which takes error output (red arrow) from OLE DB Command component. But whenever error is triggered, this is not redirecting the rows to script component even though I configured Error output as "Redirect Row". Can anyone please tell me how to redirect error to a script component?
Is there a method in SQL Server 2k to re-locate the physical databasefiles at the time the server reboots. Currently, the only way I know todo this is by scripting some OS-level commands.Thanks in advance for your help.Amy BoydDatabase AdministratorNetwork Associates*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
For certain reason, I set nocount off in the proc procABC. I need to collect all the counters(Ins/Upd/Del) and print statment on the isql/w screen to a .txt file.
How could I capture all the running info as a daily log file?
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
I have recently started using sql server express edition in place of my oracle db instance and have a need to enable trace on this to view some of the selects that are being fired by the application. I am unable to find out how exactly this is done. I was taken to a couple of sites that said that I needed SSEUtil and have installed that also.
Could one of you gurus out there, please help me out as this is pretty urgent for me to find.
Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net? I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time. Thanks for your help in advance!
I have written stored procedures to create a database with db name as input parameter
I need to create a batch file to run the stored procedure with input value given in the command prompt along with the batch file will be my dbname, which will be used by the stored procedure to create the databse.
and also few sql statements to insert data into those tables after creating
Plz could any one help me out to create code to create a batch file
Having trouble getting this to work. Been using a bunch of code from sites (including this one), but just can't get what I want. It seems so simple, but just can't get it.
So, instead of showing what I have, I'd rather see what someone usually uses.
I'm using Windows Auth, am going to use a 'SELECT * FROM DB.dbo.Table', and I want overwrite the outputed file each time it runs. And if the file doesn't exist - create it. I would also like it to be TAB delimited, and use quoted identifiers (""). And nulls will output as null (will be imported to another SQL Server by means I don't know of - and can't find out). They'll need to come back in as Null on the other side.
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.
I am trying to take an entire MS SQL database and put it in an sql file. I have succesfully copied the tables into an sql file by highlighting the tables in enterprise manager and choosing 'generate sql script'.
That gives me the structure, but now I would like the data (in insert statements). I have looked in enterprise manager's export wizard and sql analyzer to no avail. There seem to be a lot of options for exporting data except this one! Please point me in the right direction.
At the end of the day, I would like to be able to put everything in a text file. Then, should I have problems, I can just copy my text into query analyzer and have a brand new database.
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?
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code: ///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure ....................................... etc. /////////////////////////////////////////////////////// If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString: ......................... ........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure ........................ etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Hi, I was sent a .bak file which I need to open and place in sql server express. I googled this but did not come up with any clear instructions. Can some please give me a good link that explains this. Thanks
Hi all, I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files: (1) ListColumnValues (MS-DOS Batch File) sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName = "Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o c:prosseappschapter02ColumnValuesOut.rpt (2) ListColumnValues (Microsoft SQL Server Query File) USE $(Northwind) GO SELECT $(CompanyName) FROM $(Shippers) GO When I ran the following SQLcmd: C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near '$'. 'CompanyName' scripting variable not defined. 'Shippers' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them. Please help and tell me how to correct these errors.
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
How do I move a tab serperated file into a table in SQL express using sql express? I need simple fast way to to this.
I can export all my access file into tab or comma files and then build the new tables and import them. I tried upsizing the table in access but sql express will not accept them at all and I cant find a way around it.
I am at the moment participating in a project and we have run into a few difficulties which I'm hoping you guys may be able to help with.
In short, we are attempting to construct an online manager game related to cycling. Although the database has been created already in SQL Server Express and some of the website has been made, we also would like to create an executable file (written in Visual Basic) which can be placed on the server and scheduled to execute once a day. The file should read data from the database and perform its defined calculations, before writing back to the database again for further use on the website.
The problem at the moment is that I can't figure out how to make the file recognize the database in such a way that I am able to use read/write statements with SQL directly. I have tried looking numerous places online but all sources show examples of creating web forms and using buttons and such to call functions whereas I simply want one compiled executable.
If you have any advice or recommendations on how I may tackle the problem I would appreciate the help greatly.
Essentially it a question of what is needed (perhaps in terms of code) for me to make the VB file in such a way that I can do SQL queries on it independent of any user interaction. We want the code to handle the database updating on its own each day.
