I need help on the procedure below. this procedure gets intput table_name and do a row count. I want to print out the result from the Dynamic query within the procedure.
Thanks in advance
create PROCEDURE [dbo].[SRC_sp_Batch_test]
@table_name as varchar(50)
AS
DECLARE @execstr2 as varchar(1000)
declare @count as varchar(10)
set @execstr2 = 'select count(*) as count1 from ' + @table_name
exec (@execstr2)
-- want to print out the records count from executing @execstr2
I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp CREATE TABLE #temp (Command varchar(8000)) INSERT INTO #temp SELECT 'INSERT INTO Test1(column1,column2)values(1,2)' UNION ALL SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
This is a simple problem. I just don't know how to fix it. I want to print out the item that I am fetching in my cursor. look at line 12 below. My value is not being printed out. Does anyone know why?
1 DECLARE @mycur1 CURSOR2. DECLARE @InMarketId INT3. SET @InMarketId=5754. DECLARE @test VARCHAR(10)5. SET @mycur1 = CURSOR 6. FOR7. SELECT SubDivisionId FROM SubDivision WHERE MarketId=@InMarketId8. OPEN @mycur19. FETCH NEXT FROM @mycur1 INTO @test10. WHILE @@FETCH_STATUS = 0 11. BEGIN -- Delete from SubDivivisionSubMarket where SubDivisionId=@test12. PRINT @test 13. FETCH NEXT FROM @mycur1 INTO @test14. END15. DEALLOCATE @mycur1
Hi everybody, When I run my query analyzer I am getting results in the bottom window. How can I print results in that situation. Any ideas? Thanks, Rudi
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.
In TSQL, is there a way to 'Set' the query option results in text as the head of my sql statement?
kind of tired of hit contol+t to get the text result.
also, when I print '@dateStart--' + cast(@dateStart as varchar(30)) I don't get seconds and I even need mis. Is there a simple way instead of a combination of datepart() to get ss, mis?
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....
Hello, I need some help, I am in school right now and I am in a SQL server class. We have been working in the query analizer making a database. Well I have to print out everything that I have typed. But I want to view it first. How do I do that?
Sorry I did a search and couldnt find anything.. Probably cause I dont really know what to search for or look under. Thanks for your guys time.
Hi, this is my first post here. I hope to be helpful trying to help and not only asking questions arround here. After I have my report ready I will share here the total experience from top to bottom!But for now, here's the issue:
I'm building a RDLC Repor on my ASP.Net VB web application. I added the .rdlc file to the application and created a table to show lines of data binded from a dataset. The thing is:
- The DataSet expects a parameter @intNumber, a identifier to get the correct data to display the correct report.
- I'm using ReportViewer to view the report, and by code I've passed a Report Parameter to the *.RDLC report with success, just like this:
Dim parms(0) As ReportParameter parms(0) = New ReportParameter("intNumber", 37) ReportViewer1.LocalReport.SetParameters(parms)
The present issue is the following: I want to use that parameter sent to the report to be sent to the query of the DataSet as parameter to the query to return the data to fill the report. I've heard that this is not possible, just with report server...
Another issue is the print button, also heard that only can appear on report server...no way to display and work on RDLC reports?Very confused right now...these issues are stupid, MS tools should allow these operations, which are not efficient if this is not possibla on RDLC...
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?
Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results: Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _ "[Table].[Name] = @Name)" But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!
I am having trouble wrapping my head around some dynamic queries. I have x number of queries stored in a table. Each row in this table has a From, Join, and Where column. So, for x=3, I can run the query from each row so that I may have Q1: (1, 2, 4, 5, 7, 8) Q2: (1, 3, 5, 7, 9) Q3: (2, 4, 6, 8, 10) I need to use these queries to generate a shared table: |-------------------| | ID | Q1 | Q2 | Q3 | | 1 | 1 | 1 | 0 | | 2 | 1 | 0 | 1 | | 3 | 0 | 1 | 0 | | 4 | 1 | 0 | 1 | | 5 | 1 | 1 | 0 | | 6 | 0 | 0 | 1 | | 7 | 1 | 1 | 0 | | 8 | 1 | 0 | 1 | | 9 | 0 | 1 | 0 | | 10 | 0 | 0 | 1 | |-------------------| I'm not sure the best way to do this. I think that doing it on the asp.net side will be easier than in t-sql, although I am exploring both possibilities. Any suggestions?
Hi, I have basic design question regarding dynamic query, When we have to build a dynamic query (which has table name also as an input parameter), ->Is it better to write a stored procedure ..? or ->directly specify the dynamic query and set the command type as text in .NET code...?
I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..
I have an array list of Branch_ID's; i need to select all records from 2 tables that have any of the branch id's in that array associated with them. How would I go about doing this?
I have two options two write queries one Static & one Dynamic. I wanted to know which one will be good.
Example
if @x = 1 Select * From Customers order by CustomerID Else IF @x = 2 Select * From Customers Order by CustomerName Else IF @X = 3 Select * From Customers Order by city else.. ...
Against this Set @Strsql = "Select * From Customers order by " + @Orderby Exec (@Strsql)
Which will have better performance ? Is SQL Stores query plans for static queries with all if statements clause or it remains as good as dynamic ?
I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z row2 x b z row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!
Hi I am new to sql. I have to run a query in a manner that I can see customer name and their photo dynamically? I already have table created where names and customers images are there.Please help.
create procedure up_CrossTab (@SelectStatement varchar(1000), @PivotColumn varchar(100), @Summary varchar(100), @GroupbyField varchar(100), @OtherColumns varchar(100) = Null) AS /* Inputs are any 1000 character or less valid SELECT sql statement, the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1 */ set nocount on set ansi_warnings off
declare @Values varchar(8000); set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'') /* An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2 */ create table #temp (Tempfield varchar(100))
insert into #temp exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' + replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' + Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield ) from #Temp order by Tempfield
drop table #Temp /* Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3 */ exec ( 'select ' + @GroupbyField + @OtherColumns + @Values + ' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off set ansi_warnings on GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Ok.I must write a SP and it gets a parameter,say @param. if @param=1 then in the select statement I will select Col1,if @param=2 then I will select Col2 and so on.
Good Day to all, I just started using SQL Server 2005 and didn't have any experience with other DB... Is it possible to make a dynamic query? I would like to make a stored procedure where the table name from where it will select/insert/update data is user inputed... if it's possible, can someone please guide me.. thanks in advance...
I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @tblNode AND tblCorp = @tblCorp AND tblService = @tblServiceThe @tblNode, @tblCorp, @tblService are all pulling from 3 separate drop down controls. Ok easy enough.However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?I guess I am looking to see if a wild card can be used so tblNode = % or something. The server is MSSQLThank you
Hii I am Varun i have a problem with the dynamic stored procedure This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1 (@TeacherID_OnLeave numeric(9), @DateFrom datetime ,@DateTo datetime , @UserID numeric(9) ) AS declare @flag as numeric(9) declare @year as varchar(4) set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0) if( @year=null or len(@year)=0) set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+') begin insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+'); end') else set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave) return @flag --exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+' --') --return @flag --exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
I created procedure to execute dynamic query. if my query is Q1='select empno from EMP;' it works fine but it does not work with query Q2='select empno into @v_empno from EMP where empno = :xemp'; I am using exec sp_excutesql @Q2, @xemp =21;
Can we not use INTO in dynamic query....? it gives me syntex errors for @v_empno ...???
Good afternoon, i'm new to Functions on the SQL server
I'm trying to create a dynamic query that would select the the column passed to the function from a certain table
my table called selected_Date, and has StartDate, and EndDate columns
when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..
I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.
If I try to run it with QA, I get the following message: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'FCST'. UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...
However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.
What could I be missing?
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst, Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst, Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst, Dec_07=Dec_fcst FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT, Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst, Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst FROM PLAN_SHIP INNER JOIN BOD_HEADER ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX WHERE (SCEN_ID = 1) AND ([Year] = 2007) ) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX WHERE OPSPLAN.SRCPLAN = 'SHIP'
