Creating An All Option For Parameter Value In GUID Format
Jan 31, 2007
I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.
select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname
The issue I am running into is implementing logic in another dataset referencing my parameter.
All is fine in the where clause if it is structured "where ownerid in (@Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@Users) or @Users = '00000000-0000-0000-0000-000000000000') it errors out.
How do you impement "All" when you're dealing with a GUID type field? Thanks.
View 3 Replies
ADVERTISEMENT
Apr 11, 2002
I am working revising a number of stored procs on a system which has suffered some schema changes.
Sometimes I can test my SP code passing in a guid without a problem. Example below:
Test Command:
EXEC usp_Unit_INSERT
'{74A1BABA-0B76-4436-B6AA-01716B686044}', --unitguid
'36', --91', --UnitNumber (varchar)
10, -- xxHospitalNumber
'testUnknown' --UnitName
Above works fine.
I am testing another similar stored proc and am getting this error:
Server: Msg 8152, Level 16, State 9, Procedure usp_Patient_Info_INSERT, Line 24
String or binary data would be truncated.
The statement has been terminated.
(Line 24 performs an insert to a GUID)
Pertient code portions below.
Can anybody shed any light. I am essentially doing nearly identical things to another Stored Proc which works just fine.
Code below fails with above error, but is virtually identical in how it treats all GUID fields to another which does work fine.
-------------------------------------------------
CREATE PROCEDURE [usp_Patient_Info_INSERT]
@PatientGUID varchar(40),--uniqueidentifier,
@PersonGUIDvarchar(40),--uniqueidentifier ,
@CaseNumberdecimal(10,0),
<< and so forth >>
AS
IF @PatientGUID Is Null
SET @PatientGUID =cast( (newid()) as varchar(40))
INSERT INTO [Patient_Info] (
PatientGUID,
PersonGUID,
CaseNumber,
<< and so forth >>
Values (
cast( @PatientGUID as uniqueidentifier),
cast( @PersonGUID as uniqueidentifier),
@CaseNumber,
<< and so forth >>
View 1 Replies
View Related
Jul 3, 2007
Hi all,I have setup my database that have 3 columns:1. Primary Key2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.3. Another Foreign key with just an int. I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:SELECT aspnet_Users.UserId, t_music.MUSIC_TITLEFROM t_user_viewedJOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FKWHERE aspnet_Users.UserId = @UserId Any help would be greatly appreciated,Kenny.
View 9 Replies
View Related
Jul 5, 2007
I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side.
Thanks! in advance.
View 2 Replies
View Related
Aug 1, 2007
Hi All,
I can run the report if I write following in the query and run it as text in report designer's data tab it works fine.
exec abc '9B95363B-F82D-4E55-AD89-2AD928AC981F',NULL,NULL,'07/03/2006',07/08/2006'
But when I am trying to run it as stored procedure as follow
abc
and I assign the same value for the parameter in Define Query Parameter dialog box. it gives following error.
"
an error occured while executing the query.
Failed to convert parameter value from string to guid.
Additiona Information :
failed to convert parameter value from a string to a guid.(system.data)
"
Thanks for help.
View 8 Replies
View Related
Feb 10, 2008
Hi, i have some problems passing a guid parameter to a stored procedure; the code is below and the error i get is;
Failed to convert parameter value from a String to a Guid
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
cmd = new SqlCommand("spInsKeyswap_history", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
MembershipUser mu = Membership.GetUser(User.Identity.Name);
guid gdUserID = mu.ProviderUserKey;
cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@status_code", SqlDbType.Int);
cmd.Parameters.Add("@support_id", SqlDbType.Int);
cmd.Parameters.Add("@return_value", SqlDbType.Int);
cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
cmd.Parameters["@birthday"].Value = txtBirthday.Text;
cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
//get logged in users user_id from membership
cmd.Parameters["@dealer_id"].Value = gdUserID;
cmd.Parameters["@status_code"].Value = 1;
cmd.Parameters["@support_id"].Value = 0;
cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int returnValue = (int)cmd.Parameters["@return_value"].Value;
View 1 Replies
View Related
Apr 28, 2008
Im making a records management type deal where the ID field for a record is what the user is going to be using to identify it.
My question is when the user goes to create a new record what would be the best way to handle generating the ID and showing them the ID, while still being able to scrap the record if they cancel.
Would generating the ID field, creating a new record in the database, giving the user the ID, and then update the record when they finish creating the rest of the fields be the best option. Then I can detect page redirects and stuff so that I can cancel the record if they havent saved. Otherwise Im assuming that I would have to have it so that they can find records with no information and delete them.
Or is there a way that a procedure or SQL server could reserve an ID number for a specific amount of time and release it if the user doesnt finish creating the record?
View 3 Replies
View Related
Jan 29, 2008
I have cascading parameters in my report, and the vast majority of the fields on which the parameters are
based are varchar fields. In each of the drop-down menus on the report, I would like to have an option
at the top of the list called <All> which allows the user to select all of the possible values.
I have done this successfully for all the varchar fields, but there is one parameter which is based on an
integer. This obviously means it won't accept the value <All>. So, as a temporary workaround I have
created an option called -1, which when selected by the user, selects all the possible values in the
list. However, this is likely to be confusing for users if they don't realise that the -1 option means
"all". Is there any way I can make the -1 actually appear in the list as <All>? How would I go about
this?
The available values for the parameters come from an underlying dataset query.
View 1 Replies
View Related
Aug 29, 2014
I know ran across this some time ago when researching some other topic, but I would like to add an option to our stored procedures (which have multiple parameters) so a user could do something like:
EXEC dbo.usp_MyStoredProcedure - EXEC dbo.usp_MyStoredProcedure -?
The result would just list the parameters in the stored procedure and the options available for each parameter/what the parameter was use for.
Here is an example of one stored procedure in use:
CREATE PROCEDURE [dbo].[usp_MyStoredProcedure]
( @BCP INT = 0---- 0 [Default]: No BCP Import, 1: Insert TempBCP Data
, @Debug INT = 0---- 0 [Default]: Run process, 1: Create temp tables, run queries
, @StartDate smalldatetime = '1/1/1900' ---- Enter StartDate; [Default]: otherwise use Max of Invoice date
, @Date INT = 0 ---- 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is
)
WITH EXECUTE AS 'ADBAccount'
AS
BEGIN
....
END
View 2 Replies
View Related
Feb 21, 2008
Is it possible in the ssrs tool to create a multivalue parameter with the option to ignore the parameter.
There would be three choices then: Select ALL, Select these Specific Values, or Value doesn't matter.
Thanks
ssrs user
View 3 Replies
View Related
Jan 22, 2008
I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!
View 4 Replies
View Related
Sep 10, 2007
Hi,
I tried to pass parameter from one report to another report. I can send the parameter using following option:
I used jump to url option and write the following expression:
="javascript:void(window.open('http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=%2fNextGen+Reports%2fMAUA%2fSales+Order+Detail&rs:Command=Render&SalesOrderNumber="+Fields!SalesOrderNumber.Value+"'))"
and it shows me the following in browser url
http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=/NextGen+Reports/MAUA/Sales+Order+Detail&rs:Command=Render&SalesOrderNumber=SO43667
now the problem is how to get this ordernumber in my report any option ???pls urgent...any javascript function to take this no into my another report
View 37 Replies
View Related
May 6, 2015
i am parameterize my DB connection in sql sever 2012,it showing in agent job as parameter.but when i run job it giving me error:
Argument "" for option "parameter" is not valid. The command line parameters are invalid. The step failed.
what i need to do is pass different environment here while deploying to different env.
View 2 Replies
View Related
Oct 3, 2007
I have AdventureWorks installed. I am using SQL Server 2005.
I need to import some large tab delimited text files into SQL. From the research i did I thought that using BCO would be the best solution becausr I will have to import these files and export in the same format.
I am attempting to follow the example at:
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
B. Creating a non-XML format file for character data
In my command window at C: I enter:
bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T
I was expecting to be prompted with questions about the source file and have a format file created. When i press enter nothing hallende. No errors. Nothing.
Can come one give a beginner the step by step on how to creat the format file and use it to import (and export would be nice too).
View 2 Replies
View Related
Apr 23, 2007
I have created a database with several tables. I want to create a database diagram to show the relationshipbetween the tables. Below are the steps from the SQL Server 2005 documentation on how to create a database diagram. The problem is that when I right click on the Database Diagrams folder I am only given two options. They are:"Working with SQL Server diagrams" and "Refresh"There is no menu choice to create a new database diagram. Can anyone tell me what the problem here is?
To create a new database diagramIn Object Explorer, right-click the Database Diagrams folder or any diagram in that folder.
Choose New Database Diagram on the shortcut menu.
The Add Table dialog box appears.
Select the required tables in the Tables list and click Add.
The tables are displayed graphically in the new database diagram.
View 1 Replies
View Related
Jul 6, 2006
sql2k sp4
Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.
declare @exec varchar(1026)
set @exec = 'bcp faa_ivr.dbo.primary_informant format -SboxNameinstanceName -c -T -f\destinationFAAIVRprimary_informant_format.txt '
exec master..xp_cmdshell @exec
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
(5 row(s) affected)
I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.
View 4 Replies
View Related
Sep 23, 2005
Hi all,
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
Thanks!
View 6 Replies
View Related
Dec 13, 2006
Is there an elegant way to fill the empty space leading a textbox in a table with periods, similar to a table of contents effect?
When I do not allow the textbox to grow and just append a long string of periods it looks fine in my report preview, but after I deploy everything appended after the primary field in that text box is missing?
My report has a lot of data to the right but it is collapsible so the fields to the left are a good distance away. This is why I am trying to include light visual aids that assist lining up data values. I am not stuck on the leading period idea but it seems the least cluttered.
Aaron
View 2 Replies
View Related
Feb 1, 2007
Hi,
How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.
How to solve it?
Thank you.
View 1 Replies
View Related
Dec 26, 2006
Hi all, i am a newbie to reporting services. Hope someone can help me with this date problem.
I have a parameter date which i declared as type string in the report manager. when i entered the date as eg) "31/12/2003" and clicked on the 'preview' tab in the report manager, it gives me error saying that 'Cast from string "31/12/2003" to type 'Date' is not valid.'
So i use the cultureinfo function to set the date to 'en-US' format.... something like the one below:
CStr( Format(DateTime.Parse( Parameters!strDateTo.Value , New System.Globalization.CultureInfo("en-US")), "dd-MMM-yyyy"))
This works perfectly in my preview tab. But when i deployed to the server and when viewed on IE, it gives me an error msg -> "#Error" instead. Why is that so??
I have also checked my pc regional settings . It's set to United States and the date format is also "dd/MM/yyyy". Have also checked the settings in the server and it's also set to the same format. But i am still getting this error.
By the way... i am using windows 2003 with SRS 2000 SP2.
Any help will be appreciated. Thks !
View 1 Replies
View Related
Mar 27, 2008
I have datetime parameter but SSRS used mm/dd/yyyy format.
How can I change to dd/mm/yyyy format ?
in dataset I used convert(datetime,@dateparam,103) but I get error if I select for example 14/03/2008
"The value provided for the report parameter 'dateparam' is not valid for its type
View 9 Replies
View Related
Apr 27, 2008
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies
View Related
May 31, 2007
I am trying to create an "All" parameter. I created a stored procedure that says:
Code Snippet
CREATE PROCEDURE dbo.Testing123
AS
SELECT distinct ID AS ID, ID AS Label
FROM TPFDD
UNION
SELECT NULL AS ID, 'ALL' AS Label
FROM TPFDD
Order by ID
GO
Then I createded a report parameter and set the default to All
I also created a filter that sets the textbox vaule to the report parameter.
In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?
View 5 Replies
View Related
May 17, 2006
Hi
I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox
this is the parameter definition:<asp:ControlParameter ControlID="tbComment" Name="Comment" PropertyName="Text" Type="String" />
Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.
I use a SqlDataSource with automatic generated script.
look forwart to a solution
walter
View 1 Replies
View Related
Nov 26, 2006
Hi All:
I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,
any helps are appreciated.
thanks
Nick
View 5 Replies
View Related
Jun 12, 2007
Hi,
I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.
The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?
Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.
Thanks in advance
View 6 Replies
View Related
Jan 24, 2007
Hi,
I have some difficulties to format a Date value in my reporting service.
As you know you can use this kind of expression:
=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")
But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?
Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?
Many thanks!
View 8 Replies
View Related
Sep 28, 2007
Hello,
Basically my situation is the server environment it seems to be all US region. Everything runs well. When local users attempt reports they get the format parameter issue because their machinese are all CDN region.
I've read through this forum and found quite a bit of people with the same issue of formatting the parameter date. And there are a bunch of recommendations for each scenario.
What i'm wondering is.... from all the experience of the users out here who have had that issue...what would be the most recommended way of fixing this issue? Would I change the server environment to CDN region and then change all the reports default language to English(Canada)? Or is there anything else someone might recommend.
I also read about this hotfix: FIX: The calendar control in a SQL Server 2005 Reporting Services report may display incorrect values http://support.microsoft.com/kb/940382/en-US but not sure if this would fix the problem.
Thanks for everyone's help.
Cheers!
View 1 Replies
View Related
Aug 14, 2007
Is there a way to find out if the user entered the date in correct format for the report parameter? For example, I am using string data type and requring the user to enter date in yyyy/mm/dd format. Can I capture and prompt the user accordingly if the date string is not provided in the above format?
View 2 Replies
View Related
Jun 25, 2007
Hi All,
I'm using report with a date parameter (user enters a date) and all transactions before that date are displayed. I don't know how to set parameter date format on the displayed report to dd/mm/yyyy.
If it's not a parameter I usually use sql " convert(varchar(10),datefield,103)" but don't know how to use this with parameter.
Thanks
Sonny
View 3 Replies
View Related
Oct 22, 2005
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
View 1 Replies
View Related
Dec 5, 2005
Hi
Being a rookie to stored procedures the following cry for help will be posted.
The stored procedure below is supposed to create a dynamic string as an output parameter
create procedure spGetFieldNameList
@TableName varchar(256),@String varchar(8000) output
As
select @String = @String + sc.name + ','
from syscolumns as sc(nolock) join sysobjects as so(nolock)
on sc.id = so.id
and so.name = @TableName
order by colid asc
Creating the stored procedure does not trigger an error.
Calling the stored procedure as: spGetFieldNameList 'OfferCondition'.
Causes the following message:
Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0
Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied.
Calling it this way:
declare @String as varchar(8000)
set @String = ''
set @String = spGetFieldNameList 'OfferCondition'.
Causes:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'OfferCondition'.
Any help is highly appreciated.
Thanks
View 6 Replies
View Related
