Monday, March 26, 2012
How to use the same stored procedure to display two different char
that accepts a single parameter. I do not wish to be promted for the
parameter. Instead, I would like to pass two different values to each chart
within the report.
Please advise.
Thanks,
KonstantinHow do you know what value to pass it?
Anyway, something to get you going in the right direction. First thing to
realize is that query parameters and report parameters are two different
things. RS automatically creates a report parameter for you for each stored
procedure query parameter it sees. But, you don't have to use it. You can
map it to an expression instead. Click on the ..., parameters tab. On the
right pick change it from mapping to a report parameter and map it to an
expression instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
>I need to display two charts on a report based on the same stored procedure
> that accepts a single parameter. I do not wish to be promted for the
> parameter. Instead, I would like to pass two different values to each
> chart
> within the report.
> Please advise.
> Thanks,
> Konstantin|||Thanks for the tip.
I created two datasets based on the same stored procedure for each graph and
set my parameter for each dataset to literals.
Everything worked as expected.
Regards,
Konstantin
"Bruce L-C [MVP]" wrote:
> How do you know what value to pass it?
> Anyway, something to get you going in the right direction. First thing to
> realize is that query parameters and report parameters are two different
> things. RS automatically creates a report parameter for you for each stored
> procedure query parameter it sees. But, you don't have to use it. You can
> map it to an expression instead. Click on the ..., parameters tab. On the
> right pick change it from mapping to a report parameter and map it to an
> expression instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
> in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
> >I need to display two charts on a report based on the same stored procedure
> > that accepts a single parameter. I do not wish to be promted for the
> > parameter. Instead, I would like to pass two different values to each
> > chart
> > within the report.
> >
> > Please advise.
> >
> > Thanks,
> > Konstantin
>
>sql
Monday, March 19, 2012
How to use same connection manager in different packages
We want to use a single connection manager in different packages. IS there a way to do it? We tried copying the same connection manager in different package, but it makes a copy of the same in different package, so any change in the connection manager doesnt gets reflected in other package.
IS there a way to make all the different packages use the same connection manager?
Connection manager belong to every package hence any change need to be aplied to a every package. But is there when package configuration come to the rescue. There are a lot of threads addressing that issue. This is an articule that may help you understanding it:
http://www.sqlis.com/?26
Monday, March 12, 2012
How to use LIKE in Stored procedure with single quotes
I'm trying to build a dynamic sql statement in a stored procedurre and can't get the like statement to work. My problem is with the single quote. for example LIKE '%@.searchvalue%'. This works fine: select * from view_searchprinters where serialnumber like '%012%' But I can't figure out how to create this in a stored procedure. What syntax should I use for the line in bold?
SET NOCOUNT ON;
DECLARE @.sn varchar(50)
SET @.sn = N'012'
DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'select top 10 * from view_searchprinters'
+ ' WHERE 1 = 1 '
SELECT @.sql = @.sql + ' '
IF @.sn IS NOT NULL
SELECT @.sql = @.sql + 'and serialnumber LIKE ''' + '%' + '@.sn' + '%' + ''' '
EXEC sp_executesql @.sql, N'@.sn varchar(50)', @.sn
Just replace each single quote with two single quotes. But because it's dynamic sql, you'll need to double those as well, I think. You want the dynamic sql to have@.snreplaced withReplace(@.sn, '''', '''). So I think you'll need to replace'@.n'withReplace(@.sn, ''', '')in your SELECT statement.
You may want to consider multiple statements in your dynamic sql so that it produces: DECLARE @.value varchar(50); SELECT @.v = Replace(@.sn, '''', '''); SELECT @.sql = @.sql + 'and serialnumber LIKE ''' + '%' +'@.v' + '%' + ''' '. That might or might not make it easier to visualize.
HTH.
I've tried replacing each single quote with two single quotes and it will not work. I always get a empty result set which means it isn't getting interpreted correctly.
This will not work:
IF
@.snISNOTNULLSELECT @.sql= @.sql+'and serialnumber LIKE '''+'%'+'@.sn'+'%'+''' '
This will work for putting the wild card at the end:
IF
@.snISNOTNULLSELECT @.sql= @.sql+'and serialnumber LIKE @.sn + ''%'''I'm not sure why it works as it doesn't have a single quote to end th text before the @.sn. And I can't find an example anywhere with wild card on both ends in a find.
Not sure what you were trying to accomplish with the replace. I tried adding it an it didn't help.
|||Is your problem that you can't get your dynamic sql statement to execute for a string such as 012 or is it only when the serial number string contains single apostrophes? The replace was to handle the latter issue. If the problem is the former, the easiest thing to do is not to execute the dynamic sql. Just do a select on @.sql instead of execute and look at what is being generated. If you can't spot the syntax problem, paste it into a query window and try executing it to see what error you get.
If that still doesn't solve it, reply with the generated @.sql string so I can look at that.
|||I came up with an easy solution. If you modify the parameter value prior to using it by adding '%' to the front and end of the parameter, then you can use it as is without trying to insert the wild cards into the text. For example:
DECLARE @.snvarchar(50)SET @.sn = N'012'DECLARE @.sqlnvarchar(4000)SELECT @.sql ='select * from view_searchprinters'+' WHERE 1 = 1 'IF @.snISNOT NULLSET @.sn = N'%' + @.sn + N'%'SELECT @.sql = @.sql +'and serialnumber LIKE @.sn 'EXECsp_executesql @.sql, N'@.sn varchar(50)', @.sn|||
Actually, wouldn't it be easier to just do the following:
DECLARE @.snvarchar(50)SET @.sn = N'012'SELECT *FROM view_searchprintersWHERE serialnumberLIKE'%' +IsNull(@.sn,'') +'%'
If you're worried about performance when @.sn is null [WHERE serialnumber is like '%%'], then do the following:
DECLARE @.snvarchar(50)SET @.sn = N'012'IF @.snISNULLSELECT *FROM view_searchprintersELSE SELECT *FROM view_searchprintersWHERE serialnumberLIKE'%' +IsNull(@.sn,'') +'%'
Is there a particular need for the dynamic sql?
|||I'm working on a search app where the user can enter part of a value in any of text boxes or drop down list and the where statement is created based on those values. I had this working in regular ADO.NET in a class but I'm converting my code so that the class uses stored procedures with passed parameters rather than passing the select statement. This means I have to create the where statement in the stored procedure rather than in my vb.net class. I need to build a long select statement so I keep appending the addition to the where statement with @.sql = @.sql + ... This is where I ran into problems. AddingLIKE'%' + to the text wouldn't work due to the single quote issue. My solution avoids it rather than solves it. I'll post my entire code as I've found no good examples anywhere on how to do something like this.
ALTER PROCEDURE [dbo].[SearchPrinters]-- Add the parameters for the stored procedure here@.SerialNumbervarchar(50), @.PrinterNamevarchar(50),@.Statusvarchar(50),@.PONumbervarchar(50),@.OrderNumbervarchar(50),@.ManufacturerIDvarchar(50),@.Modelvarchar(50),@.MaintProvvarchar(50),@.IPAddressvarchar(50),@.InvoiceNovarchar(50),@.Locationvarchar(50),@.Departmentvarchar(50),@.Regionvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure here -- If All Nulls passed return everythingIF (@.SerialNumberISNULL)AND (@.PrinterNameISNULL)AND (@.StatusISNULL)AND (@.PONumberISNULL)AND (@.OrderNumberISNULL)AND (@.ManufacturerIDISNULL)AND (@.ModelISNULL)AND (@.MaintProvISNULL)AND (@.IPAddressISNULL)AND (@.InvoiceNoISNULL)AND (@.LocationISNULL)AND (@.DepartmentISNULL)AND (@.RegionISNULL)BEGINSELECT [SerialNumber] ,[Model] ,[PrinterManufact] ,[ManufacturerID] ,[Status] ,[PrinterName] ,[Department] ,[Location] ,[Region] ,[StatusCode] ,[MaintProvID] ,[IPAddress] ,[NetworkDropNo] ,[OrderNumber] ,[InvoiceNumber] ,[PONumber]FROM [HWinvDB].[dbo].[View_SearchPrinters]ENDELSEBEGINDECLARE @.sqlnvarchar(4000)SELECT @.sql ='SELECT [SerialNumber] ,[Model] ,[PrinterManufact] ,[ManufacturerID] ,[Status] ,[PrinterName] ,[Department] ,[Location] ,[Region] ,[StatusCode] ,[MaintProvID] ,[IPAddress] ,[NetworkDropNo] ,[OrderNumber] ,[InvoiceNumber] ,[PONumber]FROM [HWinvDB].[dbo].[View_SearchPrinters]'+'WHERE 1 = 1'--Start building where statement based on passed parametersIF @.SerialNumberISNOT NULLBEGINSET @.SerialNumber = N'%' + @.SerialNumber + N'%'SELECT @.sql = @.sql +'AND [SerialNumber] LIKE @.SerialNumber 'ENDIF @.PrinterNameISNOT NULLBEGINSET @.PrinterName = N'%' + @.PrinterName + N'%'SELECT @.sql = @.sql +'AND [PrinterName] LIKE @.PrinterName 'ENDIF @.StatusISNOT NULLBEGINSET @.Status = N'%' + @.Status + N'%'SELECT @.sql = @.sql +'AND [StatusCode] LIKE @.Status 'ENDIF @.PONumberISNOT NULLBEGINSET @.PONumber = N'%' + @.PONumber + N'%'SELECT @.sql = @.sql +'AND [PONumber] LIKE @.PONumber 'ENDIF @.OrderNumberISNOT NULLBEGINSET @.OrderNumber = N'%' + @.OrderNumber + N'%'SELECT @.sql = @.sql +'AND [OrderNumber] LIKE @.OrderNumber 'ENDIF @.ManufacturerIDISNOT NULLBEGINSET @.ManufacturerID = N'%' + @.ManufacturerID + N'%'SELECT @.sql = @.sql +'AND [ManufacturerID] LIKE @.ManufacturerID 'ENDIF @.ModelISNOT NULLBEGINSET @.Model = N'%' + @.Model + N'%'SELECT @.sql = @.sql +'AND [Model] LIKE @.Model 'ENDIF @.MaintProvISNOT NULLBEGINSET @.MaintProv = N'%' + @.MaintProv + N'%'SELECT @.sql = @.sql +'AND [MaintProvID] LIKE @.MaintProv 'ENDIF @.IPAddressISNOT NULLBEGINSET @.IPAddress = N'%' + @.IPAddress + N'%'SELECT @.sql = @.sql +'AND [IPAddress] LIKE @.IPAddress 'ENDIF @.InvoiceNoISNOT NULLBEGINSET @.InvoiceNo = N'%' + @.InvoiceNo + N'%'SELECT @.sql = @.sql +'AND [InvoiceNumber] LIKE @.InvoiceNo 'ENDIF @.LocationISNOT NULLBEGINSET @.Location = N'%' + @.Location + N'%'SELECT @.sql = @.sql +'AND [Location] LIKE @.Location 'ENDIF @.DepartmentISNOT NULLBEGINSET @.Department = N'%' + @.Department + N'%'SELECT @.sql = @.sql +'AND [Department] LIKE @.Department 'ENDIF @.RegionISNOT NULLBEGINSET @.Region = N'%' + @.Region + N'%'SELECT @.sql = @.sql +'AND [Region] LIKE @.Region 'ENDEXECsp_executesql @.sql, N'@.SerialNumber varchar(50),@.PrinterName varchar(50),@.Status varchar(50),@.PONumber varchar(50),@.OrderNumber varchar(50), @.ManufacturerID varchar(50), @.Model varchar(50),@.MaintProv varchar(50), @.IPAddress varchar(50), @.InvoiceNo varchar(50), @.Location varchar(50),@.Department varchar(50), @.Region varchar(50)', @.SerialNumber, @.PrinterName, @.Status, @.PONumber, @.OrderNumber, @.ManufacturerID, @.Model,@.MaintProv, @.IPAddress, @.InvoiceNo, @.Location, @.Department, @.RegionENDEND
NOTE: I made sure that I passed DBNull.Value to the stored procedure rather than a string.empty:
Public Function searchPrinters(ByVal serialNoAs String, ByVal printerNameAs String, ByVal statusAs String, ByVal poNumberAs String, ByVal orderNumberAs String, ByVal manufacturerIDAs String, ByVal modelAs String, ByVal maintProvAs String, ByVal ipAddressAs String, ByVal invoiceNoAs String, ByVal locationAs String, ByVal departmentAs String, ByVal regionAs String)As DataSet Dim dsAs New DataSet Dim objConAs New SqlClient.SqlConnection(strSQLCon) Dim objSQLCmdAs New SqlClient.SqlCommand("SearchPrinters", objCon)If region = "All"Then region = String.EmptyEnd If Try objSQLCmd.CommandType = CommandType.StoredProcedure objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.SerialNumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.PrinterName", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Status", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.PONumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.OrderNumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.ManufacturerID", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Model", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.MaintProv", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.IPAddress", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.InvoiceNo", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Location", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Department", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Region", SqlDbType.VarChar, 50)) objSQLCmd.Parameters("@.SerialNumber").Value = serialNoIf serialNo = String.EmptyThen objSQLCmd.Parameters("@.SerialNumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.SerialNumber").Value = serialNoEnd If If printerName = String.EmptyThen objSQLCmd.Parameters("@.PrinterName").Value = DBNull.ValueElse objSQLCmd.Parameters("@.PrinterName").Value = printerNameEnd If If status = String.EmptyThen objSQLCmd.Parameters("@.Status").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Status").Value = statusEnd If If poNumber = String.EmptyThen objSQLCmd.Parameters("@.PONumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.PONumber").Value = poNumberEnd If If orderNumber = String.EmptyThen objSQLCmd.Parameters("@.OrderNumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.OrderNumber").Value = orderNumberEnd If If manufacturerID = String.EmptyThen objSQLCmd.Parameters("@.ManufacturerID").Value = DBNull.ValueElse objSQLCmd.Parameters("@.ManufacturerID").Value = manufacturerIDEnd If If model = String.EmptyThen objSQLCmd.Parameters("@.Model").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Model").Value = modelEnd If If maintProv = String.EmptyThen objSQLCmd.Parameters("@.MaintProv").Value = DBNull.ValueElse objSQLCmd.Parameters("@.MaintProv").Value = maintProvEnd If If ipAddress = String.EmptyThen objSQLCmd.Parameters("@.IPAddress").Value = DBNull.ValueElse objSQLCmd.Parameters("@.IPAddress").Value = ipAddressEnd If If invoiceNo = String.EmptyThen objSQLCmd.Parameters("@.InvoiceNo").Value = DBNull.ValueElse objSQLCmd.Parameters("@.InvoiceNo").Value = invoiceNoEnd If If location = String.EmptyThen objSQLCmd.Parameters("@.Location").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Location").Value = locationEnd If If department = String.EmptyThen objSQLCmd.Parameters("@.Department").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Department").Value = departmentEnd If If region = String.EmptyThen objSQLCmd.Parameters("@.Region").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Region").Value = regionEnd If Dim objSQLAdptAs New SqlClient.SqlDataAdapter(objSQLCmd) objCon.Open() objSQLAdpt.Fill(ds) objCon.Close()Return ds Catch exAs Exception objCon.Close()Return dsEnd TryEnd FunctionAnd last but not least the code to call the function:
Dim dsAs New DataSetds = objDataAccess.searchPrinters(Me.tbSerialNumber.Text,Me.tbPrinterName.Text,CStr(Me.ddlStatus.SelectedValue),Me.tbPONumber.Text,Me.tbOrderNumber.Text,CStr(Me.ddlManufacturer.SelectedValue),Me.ddlModel.SelectedValue,CStr(Me.ddlMaintProv.SelectedValue),Me.tbIPAddress.Text,Me.tbInvoiceNo.Text,Me.ddlLocations.SelectedValue,Me.ddlDepartment.SelectedValue,Me.ddlRegion.SelectedValue)
|||
I would recommend to set all your parameters in stored procedure to have default value of null and modfiy your coller code to just not create parameter for command if value is not used in search criteria.
And what if someone would like to use OR instead of AND?
JPazgier
|||I would recommend to set all your parameters in stored procedure to have default value of null and modify your caller code to just not create parameter for command if value is not used in search criteria.
And what if someone would like to use OR instead of AND?
JPazgier
Friday, March 9, 2012
How to use IF..THEN to select SQL Server/Database?
I have a single form that the users can do lookups for items. We have two locations each with its own SQL Server and database. Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database. I haven't a clue on how to do this. If someone could point me to the documentation to do this I would really appreciate it.
<%@. Page Language="VB" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.OleDB" %><%@. ImportNamespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Sub get_Dies(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Dim dbConnectionAs New SqlConnectionIf ddlDatabase.SelectedValue ="db2"Then ? dbConnection ="server=server2;Database=db2;UID=user;PWD=pass" dbConnection.Open()Else ? dbConnection ="server=server1;Database=db1;UID=user;PWD=pass" dbConnection.Open()End If Dim sqlStringAs String =" sql statement"Dim dbCommandAs New SqlCommand(sqlString, dbConnection)Dim dbDataReaderAs SqlDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection) gvDies.DataSource = dbDataReader gvDies.DataBind() dbConnection.Close()End Sub </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Die Usage Lookup</title><link rel="stylesheet" href="/intranet.css" type="text/css" /></head><body> <form id="form1" runat="server"> <div> Find dies matching: <asp:TextBox ID="tbDies" runat="server" /><br />Select database: <asp:DropDownList id="ddlDatabase" runat="server" AutoPostBack="true" OnSelectedIndexChanged="get_Dies"> <asp:ListItem Text="" Selected="true" Value="" /> <asp:ListItem Text="DB1" Value="db1" /> <asp:ListItem Text="DB2" Value="db2" /> </asp:DropDownList> <asp:gridview ID="gvDies" runat="server" GridLines="none" > </asp:gridview> </div> </form></body></html>
in your if statement you can assign the connectionstring to the connection object such as:
Dim connectionStringAsString
If ddlDatabase.SelectedValue ="db2"Then
connectionString ="server=server2;Database=db2;UID=user;PWD=pass"
Else
connectionString ="server=server1;Database=db1;UID=user;PWD=pass"
End If
Dim dbConnectionAs New SqlConnection(connectionString)
dbConnection.Open()That is brilliant!! Did exactly what I was looking for. Thank you very much.
Wednesday, March 7, 2012
How to use deployment utility using a single package configuration file
Hi,
We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists.
Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file.
Any suggestion would be really appreciated.
Thanks in Advance,
Manish Singh
This is a recognised problem with the deployment wizard and will, I'm sure, be fixed at some point.
In the meantime, you may want to look at alternative methods such as XCopy. Another alternative may be the WiX toolset, I have had a lot of success with that.
-Jamie
|||Thanks for the post Jamie , but could you let me know how XCopy or WIX Toolset works
Thanks in Advance
Shailesh|||
XCopy basically means using the command-line tool xcopy.exe to manually copy the files.
Using WiX is far more involved and is a much igger subject than can be answered in a single post I'm afraid.
-Jamie
|||Thanks again Jamie , but i found a better option to use the shared configuration file . U can actually create an environment variable and set the path of the Configuration file . Which you could indirectly reference in each and every package .
There is another workaround , where you can manually create the manifest file . This approach allows you to call one configuration file in another :: havent checked this part though .
Thanks for your response
keep up the good work
Regards
Shailesh|||
Shailesh Chaudhary wrote:
Thanks again Jamie , but i found a better option to use the shared configuration file . U can actually create an environment variable and set the path of the Configuration file . Which you could indirectly reference in each and every package . Shailesh
This is called an Indirect Configuration. I am a big fan.
Indirect configurations ROCK!
http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx
-Jamie