help! trying to find records inside text string

Hi, got a problem and can't figure this one out.

basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...

record 1 : (01)
record 2 : (02)
etc...

and i want to compare this one field to those records to see whether that text string contains anything the table contains.

i've tried ContainsTable & IN but still can't figure this out.

any help greatly appreciated
:owould you kindly give us the table names and column names involved

otherwise the most i can tell you is to use LIKE

HELP! Strange behavior using t-sql REPLACE function

I am seeing very odd behavior with the t-sql REPLACE funtion.

I am trying to wipe out the 'T' character (unicode value 222) with a space character (' ' unicode value 32) and it appears to be replacing occurrences of 'th' together as well. Here are some selects to show this behavior:

select nchar(unicode('T'))
select nchar(unicode(' '))
select unicode('T')
select unicode(' ')
select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban & Ethnic', nchar(222), nchar(32))
select replace('Urban T Ethnic', nchar(222), nchar(32))
select replace('Urban & Ethnic', 'T', ' ')
select replace('Urban T Ethnic', 'T', ' ')

NOTICE that the last two statements:

select replace('Urban & Ethnic', 'T', ' ') - RESULT 'Urban & Ethnic'
select replace('Urban T Ethnic', 'T', ' ') - RESULT 'Urban Ethnic'

...work properly, while the other ones dont:

select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban E nic'
select replace('Urban & Ethnic', nchar(222), nchar(32)) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(222), nchar(32)) - RESULT 'Urban E nic'

This function ONLY seems to work with the character representation, any attempt to convert an integer value to a character representation fails miserably...

Please advise, and thanks for your help in advance.

It seems that the collation the active database uses treats "th" and "T" as the same character. To fix this, change the database collation to something more appropriate, or use tricks like the following in your queries:

select replace(N'Urban T Ethnic' COLLATE Latin1_General_BIN, N'T', N' ')

By forcing the collation to BIN (binary), a binary comparison is done, which doesn't do fancy character-equivilancy stuff.

-Ryan

|||

Thanks!

sp_helpsort reveals the default collation = Latin1-General, blah, blah, blah, etc...

I even tried to convert EVERYTHING to binary var types within the query and it STILL came up with the wrong result.

Thanks again for your help. I didn't even consider changing the COLLATION type in the query itself...

sql

Help! Problem with DateTime.Now and SQLServer 2005

I am in the proces of upgrading from access to SQL Server 2005, and I
am having a strange problem. When I set the value a column in a data
row to DateTime.Now and save it to the server the date is two days in
the future! This happens for both typed and untyped datasets.
ex DataTable dt=new DataTable("Test")
...
DataRow row = dt.Rows[0];
row["testDate"]=DataTime.Now;
...
dbAdapter.Update(dt);
the date saved will be exactly two days in the future.
The same code worked fine with Access, also I am using the OleDb
provider for SqlServerI know this is a dumb question, but what is the date and time set to on
the SLQ Server?|||Nice thought but no dice the server date time is correct|||Hello BetaD,
A couple of quick questions:
a. You did check that the local system (where the code is running) is correc
t?
b. If you're writing .NET code, are you using SqlClient or OleDbClient? If
you're using OleDbClient, why?
c. Is the datetime off in the dataset only or does it saved incorrectly to
SQL server only or is it wrong in both places?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

HELP! Parameter default or provided value not valid???

Default value or value provided for the report parameter 'ParamaterName' is
not a valid value. (rsInvalidReportParameter)
I am getting this error when I am trying to run a deployed report. However,
this is driving me mad because in the report preview within VS.Net the report
works fine and i can choose the same parameter settings from within the dev
environment with no problems. I am populating this parameter with a dropdown
with valid values from a sproc (and this I've done a thousand times before).
I can not figure out why it is throwing this error, particularly since it
works perfectly fine in the preview pane. How could it be crappin out when I
deploy it?!?! I am going insane!
-isamuAre you sure it is exactly in the valid values list? The list is case
sensitive. Also, if you are using date / time functions you have to be
careful as they will need to be exact matches.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:D6B6D595-8748-4308-8EF2-2B7F846EB9B5@.microsoft.com...
> Default value or value provided for the report parameter 'ParamaterName'
> is
> not a valid value. (rsInvalidReportParameter)
> I am getting this error when I am trying to run a deployed report.
> However,
> this is driving me mad because in the report preview within VS.Net the
> report
> works fine and i can choose the same parameter settings from within the
> dev
> environment with no problems. I am populating this parameter with a
> dropdown
> with valid values from a sproc (and this I've done a thousand times
> before).
> I can not figure out why it is throwing this error, particularly since it
> works perfectly fine in the preview pane. How could it be crappin out
> when I
> deploy it?!?! I am going insane!
> -isamu|||Hi Brian,
I believe there must've been some buggy stuff going on. What I did to fix
it, on a whim, was change all my variable names in my stored procedure and in
my report, and voila, guess what? it worked. I don't know what could've
been causing it to act so strangely, but I'm just happy it got fixed. Wasted
a good 3 hours of time though.
"Brian Welcker [MSFT]" wrote:
> Are you sure it is exactly in the valid values list? The list is case
> sensitive. Also, if you are using date / time functions you have to be
> careful as they will need to be exact matches.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "isamu" <isamu@.discussions.microsoft.com> wrote in message
> news:D6B6D595-8748-4308-8EF2-2B7F846EB9B5@.microsoft.com...
> > Default value or value provided for the report parameter 'ParamaterName'
> > is
> > not a valid value. (rsInvalidReportParameter)
> >
> > I am getting this error when I am trying to run a deployed report.
> > However,
> > this is driving me mad because in the report preview within VS.Net the
> > report
> > works fine and i can choose the same parameter settings from within the
> > dev
> > environment with no problems. I am populating this parameter with a
> > dropdown
> > with valid values from a sproc (and this I've done a thousand times
> > before).
> > I can not figure out why it is throwing this error, particularly since it
> > works perfectly fine in the preview pane. How could it be crappin out
> > when I
> > deploy it?!?! I am going insane!
> >
> > -isamu
>
>

Help! Creating Custom Data Flow Task

I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

So provide a custom IDTSCustomProperty90 in ProvideComponentProperties() and let users type in the variable name is enough(of course, I should also follow what you said)?

I wanted to make it behave the same way as the FileNameVariable property of Raw File Destination, that is: users don't type in the variable name, instead, they select from all available variable names.
|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

How can I get the matching variable and it's value at runtime? And generally in which method should I do that? Thanks.
|||

Here's a sample from a component I developed to raise custom events from a package's control flow; the same basic technique should work for you in data flow as well.

First, create a property that stores the variable name. This will be set by the package developer at design time.

Code Snippet

private string variableName;

///

/// This property gets or sets the name of the variable in which the

/// event text is stored.

///

[Browsable(true)]

[Category("Custom")]

[Description("Gets or sets the name of the variable in which the event text is stored.")]

public string VariableName

{

get

{

return variableName;

}

set

{

if (value.IndexOf("::") == -1)

{

variableName = "User::" + value;

}

else

{

variableName = value;

}

}

}

(The set accessor for the property is simply explicitly adding the User namespace to the specified variable name unless a namespace is already specified. I've yet to add a drop-down so the package developer can pick from a list of available variables.)

Then, you can use the component's VariableDispenser object (which is passed in a parameter to many of the methods you overload when developing your component) to lock and work with the package variable identified by the property. Here's a sample from the Validate method in my component:

Code Snippet

Variables vars = null;

DTSExecResult result;

variableDispenser.LockOneForRead(variableName, ref vars);

if (vars[variableName].DataType != TypeCode.String)

{

// The variable is accessable, but is not the correct data type

componentEvents.FireError(0, SubComponent(variableDispenser),

string.Format("Variable '{0}' is not a String variable", variableName), "", 0);

result = DTSExecResult.Failure;

}

else

{

result = DTSExecResult.Success;

}

vars.Unlock();

return result;

The key thing here is that you're using the variableDispenser to lock the specific variable identified by the component's property, working with it, and then unlocking it when you're done.

(The SubComponent method which is referenced in the code sample above is just a little helper routine that returns a string in a standard format to identify where an error or event is coming from. I need this type of string in many places, so I have a function that can be reused, but it's not really relevant to your question...)

Hope this helps!

Help! Call a code block based on parameter name

I have a column heading that needs to call a function based on the value
contained in the heading (which is a parameter selected by the user).
Example: they can choose system, database, network or application. I have
functions which do different translations and the functions are named
translateSystem, translateApplication, translateDatabase, etc.
I tried to do this by adding a textbox (which I named tbResType) with the
following code in it:
IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
IIF(Parameters!ResType.value = "process",
"translateProcess",IIF(Parameters!ResType.value = "database",
"translateDatabase", IIF(Parameters!ResType.value = "application",
"translateApplication", "translateSystem"))))
Then I tried to use that value in the column heading to call the appropriate
code block (the function translates into multiple languages so its parm is
the language value the user chooses)
=code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
anyway ... the translation for the first textbox works fine and I see
translateProcess when I entered process for the ResType parm but the column
heading gives me an error ==> The value expression for the textbox â'textbox6â'
contains an error: [BC30203] Identifier expected.
Any clues?You may have to write a wrapper around you actual calls within your code
block. This wrapper will accept the bResType parameter and the sub call the
appropriate routines from there.
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>I have a column heading that needs to call a function based on the value
> contained in the heading (which is a parameter selected by the user).
> Example: they can choose system, database, network or application. I have
> functions which do different translations and the functions are named
> translateSystem, translateApplication, translateDatabase, etc.
> I tried to do this by adding a textbox (which I named tbResType) with the
> following code in it:
> IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> IIF(Parameters!ResType.value = "process",
> "translateProcess",IIF(Parameters!ResType.value = "database",
> "translateDatabase", IIF(Parameters!ResType.value = "application",
> "translateApplication", "translateSystem"))))
> Then I tried to use that value in the column heading to call the
> appropriate
> code block (the function translates into multiple languages so its parm is
> the language value the user chooses)
> =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> anyway ... the translation for the first textbox works fine and I see
> translateProcess when I entered process for the ResType parm but the
> column
> heading gives me an error ==> The value expression for the textbox
> 'textbox6'
> contains an error: [BC30203] Identifier expected.
> Any clues?
>|||Thanks Craig ... I am not sure how to do that. Is there an example somewhere
(simple example...) I am very new with vb .net
"Craig" wrote:
> You may have to write a wrapper around you actual calls within your code
> block. This wrapper will accept the bResType parameter and the sub call the
> appropriate routines from there.
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >I have a column heading that needs to call a function based on the value
> > contained in the heading (which is a parameter selected by the user).
> > Example: they can choose system, database, network or application. I have
> > functions which do different translations and the functions are named
> > translateSystem, translateApplication, translateDatabase, etc.
> >
> > I tried to do this by adding a textbox (which I named tbResType) with the
> > following code in it:
> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> > IIF(Parameters!ResType.value = "process",
> > "translateProcess",IIF(Parameters!ResType.value = "database",
> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> > "translateApplication", "translateSystem"))))
> >
> > Then I tried to use that value in the column heading to call the
> > appropriate
> > code block (the function translates into multiple languages so its parm is
> > the language value the user chooses)
> >
> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >
> > anyway ... the translation for the first textbox works fine and I see
> > translateProcess when I entered process for the ResType parm but the
> > column
> > heading gives me an error ==> The value expression for the textbox
> > 'textbox6'
> > contains an error: [BC30203] Identifier expected.
> >
> > Any clues?
> >
>
>|||Put this in your code...
Public Function GetTranslation(ByVal Application As String, ByVal Language
As String) As String
Select Case Application
Case "nw Interface"
Return translateNetwork(Language)
Case "process"
Return translateProcess(Language)
Case "database"
Return translateDatabase(Language)
Case "application"
Return translateApplication(Language)
End Select
Return ""
End Function
Then in your header just put
=code.GetTranslation(Parameters!Language.Value)
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> Thanks Craig ... I am not sure how to do that. Is there an example
> somewhere
> (simple example...) I am very new with vb .net
> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its parm
>> > is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>|||Sorry fogot one other parameter in the call...
you need...
=code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Put this in your code...
> Public Function GetTranslation(ByVal Application As String, ByVal Language
> As String) As String
> Select Case Application
> Case "nw Interface"
> Return translateNetwork(Language)
> Case "process"
> Return translateProcess(Language)
> Case "database"
> Return translateDatabase(Language)
> Case "application"
> Return translateApplication(Language)
> End Select
> Return ""
> End Function
> Then in your header just put
> =code.GetTranslation(Parameters!Language.Value)
>
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
>> Thanks Craig ... I am not sure how to do that. Is there an example
>> somewhere
>> (simple example...) I am very new with vb .net
>> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the
>> >value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its
>> > parm is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>
>|||I'm sorry ... I forgot to reply that it worked! Thank you very much and I
have rated it as such.
"Craig" wrote:
> Sorry fogot one other parameter in the call...
> you need...
> =code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
> Craig
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Put this in your code...
> >
> > Public Function GetTranslation(ByVal Application As String, ByVal Language
> > As String) As String
> >
> > Select Case Application
> >
> > Case "nw Interface"
> >
> > Return translateNetwork(Language)
> >
> > Case "process"
> >
> > Return translateProcess(Language)
> >
> > Case "database"
> >
> > Return translateDatabase(Language)
> >
> > Case "application"
> >
> > Return translateApplication(Language)
> >
> > End Select
> >
> > Return ""
> >
> > End Function
> >
> > Then in your header just put
> >
> > =code.GetTranslation(Parameters!Language.Value)
> >
> >
> > Craig
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> >> Thanks Craig ... I am not sure how to do that. Is there an example
> >> somewhere
> >> (simple example...) I am very new with vb .net
> >>
> >> "Craig" wrote:
> >>
> >> You may have to write a wrapper around you actual calls within your code
> >> block. This wrapper will accept the bResType parameter and the sub call
> >> the
> >> appropriate routines from there.
> >>
> >> Craig
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >> >I have a column heading that needs to call a function based on the
> >> >value
> >> > contained in the heading (which is a parameter selected by the user).
> >> > Example: they can choose system, database, network or application. I
> >> > have
> >> > functions which do different translations and the functions are named
> >> > translateSystem, translateApplication, translateDatabase, etc.
> >> >
> >> > I tried to do this by adding a textbox (which I named tbResType) with
> >> > the
> >> > following code in it:
> >> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> >> > IIF(Parameters!ResType.value = "process",
> >> > "translateProcess",IIF(Parameters!ResType.value = "database",
> >> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> >> > "translateApplication", "translateSystem"))))
> >> >
> >> > Then I tried to use that value in the column heading to call the
> >> > appropriate
> >> > code block (the function translates into multiple languages so its
> >> > parm is
> >> > the language value the user chooses)
> >> >
> >> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >> >
> >> > anyway ... the translation for the first textbox works fine and I see
> >> > translateProcess when I entered process for the ResType parm but the
> >> > column
> >> > heading gives me an error ==> The value expression for the textbox
> >> > 'textbox6'
> >> > contains an error: [BC30203] Identifier expected.
> >> >
> >> > Any clues?
> >> >
> >>
> >>
> >>
> >
> >
>
>

Help!

All,
My report should look like this:
WTD MTD YTD
Actual value 3 5 7
Percentage 10% 20% 50%
I have set a parameter list based on time dimension in the cube and
they are displayed in different levels representing the time dimension
hierarchy. I'm not sure how I can use the parameter that I select from
this dropdown list and use it to roll up wtd,mtd and ytd values. Any
help will be greatly appreciated.maybe this might be of use:
with
member [measures].[wtd total] as ' IIF([Time].CurrentMember IS [Time].[Week
Level], YourValue, 0)'
member [measures].[mtd total] as ' IIF([Time].CurrentMember IS [Time].[Month
Level], YourValue, 0)'
member [measures].[ytd total] as ' IIF([Time].CurrentMember IS [Time].[Year
Level], YourValue, 0)'
select { [measures].[wtd total]
, [measures].[mtd total]
, [measures].[ytd total]} on columns,
{ [Your Dimensions] } on rows
From YourCube
"Maddie" wrote:
> All,
> My report should look like this:
> WTD MTD YTD
>
> Actual value 3 5 7
> Percentage 10% 20% 50%
>
> I have set a parameter list based on time dimension in the cube and
> they are displayed in different levels representing the time dimension
> hierarchy. I'm not sure how I can use the parameter that I select from
> this dropdown list and use it to roll up wtd,mtd and ytd values. Any
> help will be greatly appreciated.
>

help writing s.proc in sql2005

hi all.

in my sql2005 i have a function that returns a value. func(x) returns j

how can use it in a select clause inside a s.proce?

select bb, func(xx) as jj , from ....

?

You should be able to include it right in your SELECT statement, since it returns a scalar.

You will, however, need to qualify the function with the schema; SELECT dbo.func(xx) or SELECT myschema.func(xx) etc.

Help with Variables in XI?

I have a calculated value on a record. Is there a way to return that value to the next record like below?
Record 1 : $300,000 $100,000 $200,000 {300,000-100,000}
Record 2 : $200,000{from above} $100,000 $100,000 {100,000-100,000}Try the function "Previous"

Help with value

Can someone please tell me the best way to handle this problem...
I have the following
SERVICES
Server Service
-- --
S1 A,B,C,D,E,F
S2 A,B,C,D,E,F
S3 A,C,D,E,F
I need to run a query that will select a Server and Services like A,B,
or C. If however, like Server S3, there is no B, then replace it or
somehow state that B is not installed.
Can SQL do this or do I need to pull the information and use something
else?
I can get it if all Services are there, however, if 1 is missing, then
I get messed up.
ex. SELECT a.Server,a.Service FROM SERVICES a
WHERE (a.Service like '%A%' or a.Service like '%B%' or a.Service
like '%C%' ) and
(a.Server = 'S1')
Thanksd4 wrote:
> Can someone please tell me the best way to handle this problem...
> I have the following
> SERVICES
> Server Service
> -- --
> S1 A,B,C,D,E,F
> S2 A,B,C,D,E,F
> S3 A,C,D,E,F
>
The best way is to fix the daft design. Like:
CREATE TABLE server_services (server CHAR(2) NOT NULL REFERENCES
servers (server), service CHAR(1) NOT NULL REFERENCES services
(service), CONSTRAINT pk_server_services PRIMARY KEY (server, service))
;
INSERT INTO server_services (server, service)
SELECT 'S1','A' UNION ALL
SELECT 'S1','B' UNION ALL
SELECT 'S1','C' ... etc
SELECT S.server, T.service,
CASE WHEN V.server IS NOT NULL
THEN 'Installed'
ELSE 'Not Installed'
END is_installed
FROM server AS S
CROSS JOIN services AS T
LEFT JOIN server_services AS V
ON V.server = S.server
AND V.service = T.service ;
as so often in SQL, your problems will disappear if you get the design
right to start with.
David Portas
SQL Server MVP
--|||I'm just pulling the data, didn't get to design it. And really the
Services are services that are running (or stopped,etc) on that server,
so they may be different for each one. Thanks for the reply, I'll try
it out.|||I'm just pulling the data, didn't get to design it. And really the
Services are services that are running (or stopped,etc) on that server,
so they may be different for each one. Thanks for the reply, I'll try
it out.

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [zero]
in the table.
--
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
--
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
--
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
--
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
--
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:
>> EXEC @.pass = dbo.usp_GeneratePassword
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
>)
>> Hi all,
>[quoted text clipped - 180 lines]
>> Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"thegekkster via SQLMonster.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [
;zero]
in the table.
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:[vbcol=seagreen]
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
> )
>
>[quoted text clipped - 180 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||I'm glad I was able to help you out.
Dan Guzman
SQL Server MVP
"thegekkster via droptable.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>