Can it be done?

I've been trying all day to retrieve a value from a table. This value will determine some outcomes of what my stored procedure does. But for some reason I'm constantly returned a 'false' value whenever I run the following even though I know that the value is true in the corresponding table. Is my syntax wrong or is this even possible:

DECLARE @.IsDuplicate NVARCHAR(5);
SET @.IsDuplicate = (SELECT IsDuplicate FROM User_Notes WHERE FK_UN_UserID = @.FK_UserID AND FK_UN_NoteID = @.NoteID) -- Is it an admin note

the

@.FK_UserID = 3
@.NoteID = 5

the table

FK_UN_UserID : FK_UN_NoteID : IsDuplicate

1 3 False
2 4 True
3 5 True

I need to get the value of IsDuplicate where FK_UN_UserID = 3
and FK_UN_NoteID = 5, the result would give 'true', but its not??

I'm baffledlet me get this straight. you're storing True or False in an NVarChar? Why not a bit?

Anyways... Try this.

IF (SELECT Count(*) FROM User_Notes WHERE FK_UN_UserID = 5 AND FK_UN_NoteID = 3) > 1
BEGIN

Print 'You got dupes buddy'

END

--

IF (SELECT Count(*) FROM User_Notes WHERE FK_UN_UserID = 3 AND FK_UN_NoteID = 5) > 0
BEGIN

Print 'You got your params flipped buddy'

END|||Syntax:

SELECT @.IsDuplicate = IsDuplicate FROM User_Notes WHERE FK_UN_UserID = @.FK_UserID AND FK_UN_NoteID = @.NoteID|||whats wrong with using nvarchar, how would I use a bit, forgive my ignorance!!|||A bit field is meant for true-false values. It can hold only a 1 or 0. It's just a more efficient way of storing a boolean value.|||But it is also more difficult to make use of data stored that way, plus if you have only 1 bit column it will still take a whole byte to store the data.

We typically use tinyint for this sort of data. This also prepares us for when the client suddenly decides that they need to store "Maybe" in addition to "Yes" and "No".

Just my 2 cents.

Terri|||I though tinyInt would have only allowed integers as opposed to "Yes", "No" "Maybe". Am I wrong?|||You are definitely correct. I use a separate lookup table to convert the integer value to its friendly name.

Terri|||Does that approach have significant advantages? In other words does it really make a considerable differance to speed etc using lookup tables like you have done or is that just a design preference?|||To be honest, we've used a mix of lookup tables and hardcoding in the front end. It has to be faster not to use lookup tables, but it's cleaner and takes up less space to just use a tinyint.

Also, you have prompted me to review the reasons we decided to use tinyints instead of bits. In case anyone is interested:

We found that different front ends would treat the bit data differently. VB6 in particular, I think, had some issues trying to convert a Boolean variable into a bit type. ADO did not translate bit into boolean -- at least not correctly or intuitively.

Issues I have seen with SQL Server:
-- MAX() will not accept a bit value, need to CAST the bit field as int or tinyint

And, as I mentioned, if the database design is changed from "yes/no" to "yes/no/maybe" the datatype would need to change and there would be related code changes.

Terri|||Also, lookup tables provide a consistent place to find your answers...you don't have to go tracking through your code to try and remember where you documented what your #'s represented. It pays of when you come back to your code several months later.

I agree with terri 100% on this one.|||Well...

There is an advantage to using the BIT column. For example, if you know that there must be a value, and you know that it is a true/false response, then why not use a bit? You can always allow a NULL value for 'maybe' cases, if necessary.

Personally, I only use bit when there is real true/false data to be stored, but I certainly wouldn't use a bit for such circumstances. For example, an IsActive field on a company record; why would you want a maybe as your response? If we send out a mailing to all active customers, maybe isn't going to help us at all. Either they are active, or they are not active. If we're not sure, we need to decide whtether we treat them as active or inactive (i.e., assign a default value).|||What if you decide to impliment more business logic...what if you decide that you want subscribers to go into a holding tank where they must be approved...before they can be on the mailing list. Then you would need a value to indicate that the are pending approval.

or maybe you have a delay in the system. Where you mark someone to be taken off the list..but they have to wait till the end of the month to be taken off the list. (like when you call people who are sending you junk mail and they tell you it will take 2 months till you stop getting mail...because they love sending you crap)

The point is...you never know when something will come up...why cut your legs off and use a bit, when an int functions just as good...and leaves you with more options in the future?|||Don't get me wrong here. There are many times when I will use a lookup table. However, if your analysis is solid, you should be able to discern these from actual true/false circumstances that arise in any database structures. And I think that such circumstances are best served using a bit value.

Can MDX or latch a value based on a signal?

Dear All,

Persume that I have a cube with the following measures and dimension:

DimTime, measures.LatchSignal, measures.Voltage

0,1,4

1,0,5

2,0,6

3,0,5

4,1,3

5,0,7

6,0,5

7,1,6

8,0,7

Is there anyway I can repeat the value of voltage on and after the time when latch signal = 1, so that the result is rendered as follows:

DimTime, measures.LatchSignal, measures.Voltage, measure.latchedVoltage

0,1,4,4

1,0,5,4

2,0,6,4

3,0,5,4

4,1,3,3

5,0,7,3

6,0,5,3

7,1,6,6

8,0,7,6

Thanks and regards,

Tony Chun Tung Siu

Hi Tony!

Why do you try to make it in MDX. What the problem do you solve?

I would make it in DSV in oder to get

0,1,4,4

1,0,5,NULL

2,0,6,NULL

3,0,5,NULL

4,1,3,3

5,0,7,NULL

6,0,5,NULL

7,1,6,6

8,0,7,NULL

then apply lastnonempty aggregation function for the measure latchedVoltage

|||

Hi

I just wanna plot a graph for such signal implementation. This works!.

Thanks

Tony

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Can I use Strings in iif?

Hi everyone.

Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:

iif(somevalue is nothing, 'Other', somevalue)

Thank you in advance.

yes, but VB.net uses double quotes for strings

can I use sql profiler to debug stored procedure

can I use sql profiler to debug stored procedure, for example, when application runing, can I get infomation in profiler, such as value of variable in stored procedure, especially input parameter. Because I need to know what is value of input parameter I
get from client side. Thanks
Asked and answered in .programming group... Please do not multi-post.
"iter" <anonymous@.discussions.microsoft.com> wrote in message
news:17B8A330-6E4E-487A-9229-6493EF9DBBA5@.microsoft.com...
> can I use sql profiler to debug stored procedure, for example, when
application runing, can I get infomation in profiler, such as value of
variable in stored procedure, especially input parameter. Because I need to
know what is value of input parameter I get from client side. Thanks
>

Can I use a UDF in an IF statement?

Is it possible to use a user defined function in an IF statement? The UDF returns a scalar value. Like:
IF dbo.DoSomething (@.x)

The only way I can see to call a UDF is with
SELECT @.y = dbo.DoSomething (@.x)

Thank you!

YES but you have to compare its result with something or your function should return bit or integer value IF dbo.DoSomething (@.x)=1

can i use a store procedure as default value for a column?

Is it possible to use a stored procedure to fill the default value of a column when i'm building the db?

i mean if i can use a stored procedure for the "colum property": "default value or bnding"

if yes how can i do it?

No, you won't be able to specify a stored procedure in your table definition as a default value for a column. Your best bet for this is to set up a trigger (either AFTER or INSTEAD OF) to enforce the default value.

|||

You can, however, use a scalar user-defined function as the default value. So if you can convert the sproc to a UDF or call it from one, you're good to go.

Don

Can I Turn off Auto Execution of Report when all parameters have default values?

My situation....

I deploy a report in Report Server. Ther report has 5-10 parameters. When I design the report, I supply a default value for each parameter, similiar to the "Add a select all option" tutorials.

So I end up with defaults like "Select a Project Manager", "Select a Fiscal Year", etc... I use dynamic queries to read the parameters and insert the appropriate logic into the where string of my main dataset. If the user selects a value from my parameter lists, I append a clause to the SQL string, if they choose the default option, I ignore the parameter, and that effectively returns all of the records (like Select * from ....)

My Issue...

When I provide a default paramater for ALL of the parameters available, the report auto-executes when the user picks it from the folder, and isn;t given the opportunity to refine the default criteria I have provided.

My Question....

Is there any way to turn the autoexecution off?

Thanks for any help...

There is no method to turn off the auto execution of the report if all parameters have default values.

As you have probable already found out, you can populate all but one of the parameters to get the result you desire. But that makes the user have to supply a value of the parameter.

Can I set Excel connection manager's data source as a variable?

I do not know the Excel file name to load in design time.

Would like to pass the value to a variable in the package in run time?

How to do this?

Thanks,

Guangming

Property Expressions is the feature you want. You can assign and expression to most properties, including the DataSource property of a connection. The expression would just be the name of your variable, e.g.

@.[User:MyVar]

Some UIs expose expressions as a separate tab, or just expand expressions from the normal properties grid.

|||I set ExcelFilePath to a variable:

varExcelFileFullPath.

I set an initial value ( \\test\testShare\aaab.xls ) to it when designing the package. It works

Later I run it inside SQL server as below. It failed.

exec xp_cmdshell 'dtexec /SQL "\NE_LoadExcel_Test_direct" /SERVER ** /USER ***** /PASSWORD ****

/CONNECTION "DataSource-Excel";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\test\testshare\aaab2.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"

/CONNECTION "Test.NGAEarth";"Data Source=Test;Initial Catalog=NGAEarth;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.NGAEarth"

/set "\Package.Variables[User::varPreSQLAction].Properties[Value]";"delete from Features where type = ''aaab''"

/set "\Package.Variables[User::varExcelWorkbookName].Properties[Value]";"Sheet1$"

/set "\Package.Variables[User::varDestinationTableName].Properties[Value]";"Features"

/set "\Package.Variables[User::varExcelFileFullPath].Properties[Value]";"\\test\testShare\aaab2.xls"

'

Actually, I tried to change other variables. E.g., I changed the file name from aaab2.xls to aaab.xls, Excel worksheet name from Sheet1$ to Sheet2$.

It seems the package never get the parameters I set as above. It always gets the values I set in design time. The errors I got are:

Error: 2006-02-01 15:54:23.58

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

|||

Finally I found the problem:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run

Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?Yes - you can use the variables property of the Package object: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.variables.aspx

Can I read the VALUE of a formula FROM a report in VB.NET?

My report does a lot of complex calculations and at the end there is a 'Total' formula.

I want to be able to read that value FROM the report in VB.NET after the report has been printed and use it for another function.

Is this possible?

The closest I've got is this:

rpt.DataDefinition.FormulaFields("Total").Text

but this gives me the text from the formula and not its calculated value...

Any ideas?

Thanks in advanceIt is not possible
You need to do the same calculation in VB.NET
If your Database is SQL Server then create function or stored procedure and put that code as part of it and retrieve the value

can i pass array of value to crystal report

Hi,friends...i need your help..
can we pass array of value into crystal report?
I retrieve a recordset from table..but the content is encrypted.
and i have a function to decrypt it in my visual basic app.
The problem is, when i want to pass the content of the table to crystal report, i meet a problem...i just can pass the recordset directly...but the data is encrypted..
So i think, if it's possible for me to decrypt the data and put them into an array list. After that, i want to pass it into the CR.But i don know, how to pass the array to crystal report...
Anybody can help me pliz....or any better suggestion for me?
I'm using CR 8.5 and VB 6.0

Thanks for your help...After decrypting the data, store them in EXCEL or XML file and design the report using those files. Or if you use SQL Server 2000, create a procedure doing that decrption and mover the result to temporary table and design the report a=using that sp