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.
View 2 RepliesI 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?
PRINT 'before'
WAITFOR DELAY '000:00:10'
PRINT 'after'
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?
Is it possible within SQL Express stored procedures to redirect Print statements, along with other output messages to file?
In the following example I would like to redirect the output to a file (text or XML):
PRINT 'Droping Class Type Data Object...'
DROP TABLE ClassTypeRef
Output to Messages which I would like to redirect to a file:
Droping Class Type Data Object...
DROP_TABLE - dbo.ClassTypeRef
Thanks in advance,
Mark
Hi
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.
Hi,
I am working on report where I am using a table to disply my data.There is a column called "Control Description" , data for this column can be of max 300 characters. And my report is pretty big it has 22 coulmns so each column's size is very less. Right now height of the cell is increasing to display the whole 300 char text it looks very odd.
I want to know if this is possible? when the report is displayed to user I can only show portion of the text in the tabel's cell and if user wants to see the whole thing he/she can click on it(or roll over mouse on it) and it would display entire text?
Thanks
Ashwini
exec sp_SearchProductAdvanced "(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice between 3 and 10) ",null
after executing i got this error:
The identifier that starts with '(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice ' is too long. Maximum length is 128.
how can i solve this problem. so that i can use more values .
my lenght exceeds 128. is there any way to get rid of this.
in my SP i have declared the parameter variable as
@criteria nvarchar(1000)
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3
Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40)
SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"';
SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource]
FROM JobWanted AS j INNER JOIN
Company AS c ON c.CompanyID = j.CompanyID
WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
Thanks in advance.
Ricky.
Does anyone know how to print to a file or printer using T-SQL or stored procedure?
Thanks
I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have todeal with more sophisticated queries (may I call them so?) like storedprocedures. I have a form with a combobox for selections and a textbox toenter a certain value. Let us say I call the combobox @select and thetextbox @find. The combobox always shows the first of the items to select.Now I want to return a message if nothing is found, or if nothing has beenentered at all. The stored procedure reads for instance:ALTER PROCEDURE OPC@select nvarchar(20), @find nvarchar (100)ASSET NOCOUNT ONIF@find IS NULLBEGINPRINT 'You didn't enter any value'RETURNENDIF@select = 'Author'BEGINSELECT *FROM BooksWHERE Author LIKE '%' + @find + '%'ORDER BY Author, Title, Publisher, YearEND[And so on]RETURNThis works correct when I enter something, but when I don't enter any value,a message box pops up saying that the stored procedure has been executed,but no records were found. I want to see an error message like above. It'sas simple asALTER PROCEDURE HelloASBEGINPRINT 'Hello'ENDand nothing is seen. Does anybody know? Thank you.Wim
View 6 Replies View RelatedHi,
How do I print the SELECT statement in my Stored Procedure??
I have a set of variables and I want to see how they output in the select statement.
Thanks.
I am trying to print a stored procedure in color to read it on paper.
But it prints in BW even on a color printer with color options.
I have printed the code in color using Rapid SQL. All I have now I query analyzer (SQL SERVER 2000)
any way to do this in SQL SERVER 2000
Ashley Rhodes
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.
Is this possible? I would think it would be, but not sure of the commands and I can't find them in my books.
I'm looking for something like.
EXEC usp_studentassign SPOOL C:
esults.txt
Is there such a beast?
Thanks : )
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
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)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'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.
Quick question. Is it possible to Flush the tables in SQL Server, I know you can do it in MySQL, but not sure on SQL Server?
Thanks
I got the following error. What is it?
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Canada DBA
Is there a clean way to flush the query cache so I can simulate the first execution of an SQL statement any time I want to?
View 3 Replies View RelatedI want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)
After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.
After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.
How can I flush the buffer to trc file frequently without need of stopping trace process?
Thanks for helps in advance.
Leon
Could anybody help me on how to do buffer flush before program exit?
I am using OLE DB to insert record to SQL CE database, but cannot control data commit, each time if I exit too quick, then all of inserted record cannot stored into database .sdf file. Thanks a lot!
Hi all,I have 2 independent stored procedures(I cannot join them because ofthe group condition are different however they have the same cust id)but one for reading the summary and other showing the details of thesummary. Now I want to run these two stored procedures in order toget the both return results for generating a .Net crytral report. CanI get the results form both stored procedures and how can I do that.Please help me. Thanks a lot.Hello
View 3 Replies View RelatedI have detached a SQL Server 2005 database from one server and attached it to another SQL Server 2005 and I now get the following in the error Log and in the event viewer evry 10 - 20 minutes or so.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Starting up database 'DBName'
It appears under different SPID's 18, 20, 24 ...... and consistently has the 4 errors in a row
Full Text indexing is running for both servers but I dont know if this is the cause of the error.
I would greatly appreciate any help to get rid of this as I have trawled the net and not found anything of use.
Thank You.
I am looking for an API to flush all data in memory held by SQL Serverto disk. Also, is there a tool for SQL Server like eseutil forExchange that lets you correct a SQL database?
View 5 Replies View RelatedHello all,
I am having a hard time getting this to work.
I am trying to build the where statement dynamically but it is a string and needs quotes. But the problem is the quotes are the problem. Are there any escape characters? or is there a way to make this happen??? Integers are easy keys because they do not need quotes.
SET @wheretmp = 'WHERE SourceIPID = BB901625-5E89-45D4-BD20-25730365A9DA'
Select * from tbl_All_Source @wheretmp
Hi
I have a small problem writing a stored procedure in a SQL Server 2000 database.
I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don�t generate a resulting SQL-statement.
Example:
CREATE PROCEDURE spDynStatement
AS
DECLARE @sPartOfSQLStatement NVARCHAR(100)
-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .
-- As substitute I insert the string expression
SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''
-- SELECT @sPartOfSQLStatement results in: '1' , '1.5'
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO
Thankfull for ideas on how to solve my problem,
Peter
I believe we can you multiple statements in stored procedures?
Suppose I have a stored procedure and I pass parameters to this SP.
What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:
CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,
SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position
@temp_order_id = temp_order_id
UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id
UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id
Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!
View 1 Replies View RelatedHi,
I am working on my own data flow source component. Here is a fragment of this component code:
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers) { PipelineBuffer selectedBuffer = buffers[0]; string message; while ((message = GetMessage()) != null) { selectedBuffer.AddRow(); selectedBuffer.SetString(0, message); // how to flush data here? } selectedBuffer.SetEndOfRowset(); } private string GetMessage() { // we are retrieving some message here, this is a long-term process }
When a new row is added by this component to the buffer then this row is not immediately available to the next component in data flow. It is possible to configure SSIS in that way that each row is immediately sent to the next data flow component? If no also please inform me about that.
Thanks,
Rafal
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 have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. "
The procedure takes in three parameters and retrieves 23 values from the DB to display on my form.
Any ideas?
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
Thanks.
I'm profiling a specific procedure on a database, I have the start and end times captured but within the same trace I would like to capture all the individual sql statements.
So, given this simple procedure
ALTER PROCEDURE [dbo].[usp_b]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@VERSION;
SELECT GETDATE();
END
In the trace I would like to see the two SELECTs, however all I get is this
I have the following events selected in the trace.
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
View 3 Replies View Related