Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Constrained Flag In The STRTOSET Function Violated?


I get this error when, i try to set a parameter for user input, it doesnt
matter what i type in, it says this:


<<Query (1,408) The Restrictions imposed by the constrained flag in the
STRTOSET function were violated>>

what exactly does this mean? and how do i fix it?

My parameter is for dates, but i have the datetype set to "String", when i
change it to" DATETIME" , i get the same error when i select from the
calendar. So far the only date option that is working right now. Is a long
drop down list, that could go for days at a time. I just want users to be
able to input dates, such as "Jan 9, 2000" or 10/10/1999. What can i do about
this?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Constrained Flag In The STRTOSET Function Violated
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

View Replies !   View Related
Unknown Members In Report Parameter Causes CONSTRAINED Flag Error In STRTOSET Function When NullProcessing Unknown Member
Hi,



I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).



When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:

STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).



When running the report and selecting the 'Unkown' valuefrom the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.



How can I prevent the constrained flag from being used, or am I doing something wrongwith converting null values to 'Unknown'?



Thanks





View Replies !   View Related
OLAP Multi Value Parameter Error:Members Belong To Different Hierarchies In The STRTOSET Function
Hi - I am building a reoprt (SSRS, SQL Server 2005) with a multi value parameter that exposes a picklist on the report page - when ALL is selected, or certain combinations of the elements in the picklist are chosen, I get the following error, that a query failed for a dataset because:

Members Belong to Different hierarchies in the STRTOSET function.

I have built reports with multiple value pick list before, and not encountered tis error. Any ideas? I cannot find a reference to this error anywhere.

Thanks

Bob

View Replies !   View Related
Un-flag Reinitialization Flag On Trans Subscription
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.

(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)



The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.

HELP !

View Replies !   View Related
0xC020906A The Data Value Violated The Schema Constraint
Hi,

Recently I had the following error when I tried to run an update command:
0xC020906A The data value violated the schema constraint

At the end of a data flow I wanted to do an update on the data I gathered during that data flow.

The sql-command was something like:
"Update MyTable set aField=? where theID = ISNULL(?,-1)"

Apparently SSIS doesn't accept a null value to be placed in the parameter or am I totally wrong (and I hope I am)? Maybe I have to change the sql-command, if so, please tell me.
Anyway,I replaced all the null values by a -1 via a Derived Column task before entering the command task and the problem was solved.

Greets,
Tom

View Replies !   View Related
Constrained Delegation For SSRS


I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here:
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx



I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.

I have the authentication providers for the site set to "Negotiate,NTLM".

I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.


If browse to the URL while logged on to the SSRS server then I am able to access the site
My problem is when I try to access the site from anywhere but locally on the SSRS server:

I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get:"You are not authorized to view this page". Even with an account that is local admin on the SSRS Server.
If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work.
Have i overlooked something? What could be causing the login prompt?

View Replies !   View Related
Filtering Result Sets With STRTOSET


Here's part of my query:


( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED)) ON COLUMNS FROM

[Heidtman DW]))) WHERE

(IIF( STRTOSET(@DimSalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@DimSalesTypeDescription, CONSTRAINED), [Dim Sales Type].[Description].currentmember ),


This should filter the result set, returning only those items that match the parameter that is selected. I would like to run a different filter which returns everything EXCEPT the parameter selected. Is there a way to do that with STRTOSET?

Thanks.

View Replies !   View Related
Error:Column 'FieldName' Is Constrained To Be Unique. Value '0' Is Already Present
Hi,
Can anyone help me?
when I am inserting a record to a dataset I am getting  Column 'FieldID' is constrained to be unique.  Value '0' is already present.
I have 2 fields in table like FieldID,FieldName where FieldID id is Primary key and identity column
I am not inserting any value to FieldID as it is identity column.
DataRow newDR= dbDataSet.Tables[strTable].NewRow();for (int i = 0; i < e.Row.Cells.Count; i++)
{ if ( e.Row.Cells[i].Key != "FieldID" )
{if (e.Row.Cells[i].Value != null)
{
newDR[e.Row.Cells[i].Key] = e.Row.Cells[i].Value;dbDataSet.Tables[ strTable].Rows.Add(newDR);
}
}
Some times it is woking fine with out any error............
 
thanks..

View Replies !   View Related
Flag
hi all!

i have columns.
1. Flag : stores only + / - / <NULL>
2. Number : stores float

how would i select this 2 columns to generate -ve and +ve number depend on the flag? eg :
Flag : -
Number : 4 ====> -4 and +4 if flag=+

when i try to just to combine with -(Flag) for -ve, it gave converting type error even i already CAST it..

tq in advance


~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View Replies !   View Related
Triggers And Flag Bit
I have a perl script (uses sql query) that will run if there is anyupdate in the row. Currently, I am a trigger that sets the DateModifiedfield with getdate() Then my perl script looks for certain range ofmodified dates and runs the necessary query.I am trying to come up with a different mechanism where I don't have touse the date field. I am looking into flag bits. But not sure howto use it.Should I create a trigger that will set the flag bit after any update?This way the script will just look for the updated records, regardlessof what time it was updated. Maybe I am misunderstanding flag bits, thenafter another subsequent update how would the perl/sql script know whichrecords were updated? I hope this make sense.thanks.--s

View Replies !   View Related
Trace Flag Help
Hi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.

View Replies !   View Related
Version Flag
Guys,

I have following scenario where I need to populate version flag based on the partyid, count and seq columns.

Here is it how it should populate for each new count of a partyid the max(seq) should be versioned as 1

For example

PARTYCOUNT SEQ VERSION
_________________________________________________
10011 1
10111 0
10112 1
10121 0
10122 0
10123 1
10231 1

Is there any way to populate this version flag.

Any sugggestions and inputs would help

Thanks

View Replies !   View Related
Regressor Flag
I am trying to build a decision tree algorithm with multiple regressors. I am having trouble changing the properties for the input variables to regressors (the only multiple flag choice is "not null"). I created a mining model using the DMX query thinking I could change the code to include multiple regressors, however I get a syntax error when trying to execute the model. Also, I am wondering if you can view the dmx code for a model built using the mining wizard.

My query is as follows:

CREATE MINING MODEL BVRSlotq1052

(Machine Long Key,

[Banksize] LONG CONTINUOUS REGRESSOR ,

[Cabinet] Text Discrete,

[CUID] Long Continuous PREDICT_ONLY,

[Denom] Text Discrete,

[Gametype] Text Discrete,

[Max Coins] Text Discrete,

[Par] Long Continuous,

[Pos] Text Discrete,

[Progressive] Text Discrete,

[Type]Text Discrete)

Using Microsoft_Decision_Trees

View Replies !   View Related
Getting Records Where Flag='Y' Or If No Y Then N

If you have a table with columns like Category int, Cost money, EffectiveDate DateTime, SpecialFlag (char Y or N) how would you write a query to get for each category, get the most recent, with a specialFlag of Y if a Y record exists and if not, grab the N record?

I have tried using the exists clause for the flag, but as soon as I mix in the group by to get the latest effectivedate, the max is evaluated first and I am not guaranteed to use the Y records if they exist.


To illustrate:
A 1, $10, 1/1/2007 Y
B 1, $12, 1/1/2007 N
C 2, $5, 6/7/2007 N
D 2, $4, 1/1/2007 N
E 3, $1, 1/1/2007 Y
F 3, $2, 6/7/2007 Y
G 3, $3,2/1/2008 N

I want records A because it is flagged Y, C because it is the most recent and there is no Y record and F because it is flagged and most recent.

View Replies !   View Related
Backup Flag


Hi there,


I'm working on a program and I want it to check to see if the database it is pointed to has had a backup done against the database in the last 10 days. I will then throw a message box indicating that the database has not been backed up and that they should consider doing so.

Is there any attibute that I can check to see when the last time the database was backed up?

THanks for your input! - Eric -

View Replies !   View Related
Flag Random Record
I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 *  FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement.  I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?

View Replies !   View Related
Trace Flag (3605) ???.
Does any body has any ideas what the trace flag (3605). We try to test the deadlock in SQL Server 7 but we do not see the error file. The following trace flags was turn on. No error was return but we got the deadlock message. If you have encounter this problem and have solution for it, please share with me. Thank you

Please cc directly to me also:
lamnguyen@platsoft.com

This is the simulate the problem:

dbcc tracestatus(-1)

TraceFlag Status
----------- -----------
1204 1
1205 1
1208 1
1704 1
3604 1
3605 1


first process:

begin tran

update mytitle
set title_id = `abcd`
where title_id = `bu1111`


waitfor delay `00:00:20`
update mytitle
set title_id = `abcd`
where title_id = `bu1032`

commit tran

go

Error return but we cannot see in the server trace flag (3605)

Server: Msg 1205, Level 13, State 17
Your transaction (process ID #20) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.



Second processes:


begin tran

update mytitle
set title_id = `abcd`
where title_id = `bu1032`

waitfor delay `00:00:20`

update mytitle
set title_id = `abcd`
where title_id = `bu1111`

commit tran

go

View Replies !   View Related
Update Notification Flag
i want to make a stored procedure that will update the notification flag in the table.

If the email was succesfully sent the flag will be updated as SENT. And if it is un successful the flag will be NOT SENT in column of the table in SQL. The email will be send using .NET codes and updates of notification flag will use SQL stored procedure. Please help

View Replies !   View Related
Setting A Failure Flag


I created a package wide OnError event handlerwith the following script:


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

'

Dts.Variables("FailureFlag").Value = True

'

Dts.TaskResult = Dts.Results.Success

End Sub

End Class



I ran the package with an error and set a break on error condition. When I looked at the value of "FailureFlag" it was False. Any ideas why it is not being set?



I have a post execute event that is supposed to rollback from a snapshot on failure and it is not doing so on failure so I'm trying to figure out what's wrong.

View Replies !   View Related
Trace Flag (-T1222)


Hi,

Do i need to restart SQL Database Engine ServiceonceIset trace flag (-T1222).


Regards
Sufian

View Replies !   View Related
Setting Trace Flag
In order to use Microsoft Dynamics NAV with the SQL Server 2005, I need to set Trace Flag 4616, and I know it's a setting somwhere, where I can add this flag no. -but I can't remember where? Please help?

View Replies !   View Related
Zusammengesetzter Primary Key Mit Deleted-Flag
Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datenstze physikalischgelscht werden, sondern der Lschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Lschung drin.Dann funktioniert der PK nicht mehr, denn es knnen ja durchaus mehrereDatenstze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefhrlich (daFliekommafeld, und wer wei schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP

View Replies !   View Related
Waiting On Flag To Activate Jobs ?
is there a way to automate a job to wait on a flag in a directory and to start my maintenance plans ?

if so how would i code is there external functions plz help.

View Replies !   View Related
Trace Flag For Table Scan?
Does anyone know of a trace flag that you can set that will tell you when your code is doing a table scan?

Many thanks in advance!

View Replies !   View Related
Deadlocks & Trace Flag 1204
I am trying to investigate some deadlocks. I have executed dbcc tracon(1204) in query analyser, within the connection issuing the query always chosen as deadlock victim, but I do not get a deadlock report in the error log when the next deadlock occurs.

Any ideas why not ?

View Replies !   View Related
Use Trace Flag 1400 During Startup



Hi all

i am using Sql Server 2005 SP2, I am trying to configure Mirroring. The issue is ,After configuring mirroring ,Whem i am starting START MIRRORING button .I am getting the error

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)


I used BDCC TRACEON (1400) and I altered the Startup parameter in configuration manager of SQL SERVER ;-T1400


But still I am getting the above error which is indicated in red

Please help me to fix the issue


View Replies !   View Related
Setting The Auto Shrink Flag
Hi,



I recieved an alert that says 'The Auto Shrink flag for database is not set according to best practice.' Can anyone tell me where I can find this flag and what it shoul dbe set to?



Thanks



Af.

View Replies !   View Related
When Did Trace Flag 1205's Behavior Change?
Microsoft technical article 832524 states:

"The -T1205 startup parameter collects information every time that the deadlock algorithm checks for a deadlock, not when a deadlock is encountered. You do not have to use the -T1205 startup parameter. "

Anybody know WHEN this behavior began, because I know at one point 1205 only provided some extended info over and beyond what you'd get from flag 1204.

In fact, the above wording is suspicious - including the phrase "...not when a deadlock is encountered..." is clearly there to correct the impressions of those of us stunned by the new behavior. The errorlog file will grow FAST now if you turn on 1205........

I'm pretty sure this was not the case with the initial release of SQL2000, must be in a service pack, but I don't know which - haven't used 1205 in a while. All over the web are pages documenting the behavior of 1205 as used to be, but I can't find any indication of when this behavior changed, which would be useful in an root-cause-analysis I have to write.

View Replies !   View Related
Interpretation Of TRACE FLAG Output For DEADLOCK
Hi,

In our system we encountered Deadlock and We introduced trace flags (-1, 3604, 3605, 1204, 1205, 1206)to see more about the object of contention.
The output of these trace flags are given below(Not all outputs are given):-

98/06/27 18:05:29.82 spid16 *** DEADLOCK DETECTED with spid 13 ***
spid 16 requesting EX_PAGE (waittype 0x8005), blocked by:
UP_PAGE: spid 13, dbid 6, page 0x395e5, table 0x60525654, indid 0x2
(blocking at 0x37dcb58)
pcurcmd INSERT(0xc3), input buffer: dbp_msg_crt_txinface
spid 13 waiting for UP_PAGE (waittype 0x7), blocked by:
EX_PAGE: spid 16, dbid 6, page 0x398c6, table 0x60525654, indid 0
(blocking at 0x37dcb58)
pcurcmd UPDATE(0xc5), input buffer: dbp_msg_upd_tx
VICTIM: spid 16, pstat 0x0100 , cputime 48624

One process in this involved in this deadlock (dbp_msg_crt_txinface, sp_id 16) is trying to insert a new row into the table where as the other process(dbp_msg_upd_tx, sp_id 13) is trying to update one of the columns(no index on this update column) of that table for a record identified by the primary key.
We wanted to know whether the deadlog occurs in the data page of the table or at the Index page. We have a clustered index on one of the columns of the table(object_id :0x60525654) which is a datetime(using getdate()) supplied by the system(which is a incrementally varying value) and an non clustered index on the primary key of the table(which is a identity column monotonically increasing values). We have moved on to the non clustered index on this primary key as per MICROSOFT suggestion. However we were not getting deadlocks before we made this change.
We wanted to know how this deadlock can be eliminated (of course by changing the indexes - no other changes are feasible, as this table is already used in production) and where does the deadlock occurs exactly(data page or at the index page) and how it happens?

Thanking you,
babu.

View Replies !   View Related
Save Me From A CURSOR. Flag Values That Won't Convert.
Hey all:

Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.

Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the valuesthat cannot convert to smalldatetime.

I have another table that houses the primary key and the field of the record that cannot convert.

Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the dataand attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.

I guess if I can run a query that will return allrecords that can convert forthe field(or can't convert) I'd be all set. Any help here is appreciated.

--Thanks--


View Replies !   View Related
Manipulate The 'deleted Record' Flag On DBF Files
Hi,



I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.



These DBF files have some (actually a lot)records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.



It is very Ok as long as the 'deleted' rows actuallyhave no more businessvalue, but in my case, I need to do some processing on them, and even to migrate their data.



What are the options available for me to be able to query and differentiate the 'deleted' records ?



Thank you in advance,



Bertrand Larsy

View Replies !   View Related
Trace Flag 8679 -- Is It Needed Under SQL Server 2005?
Using SQL Server 2000 std. edition, I was bitten by the bug described in KBs 818671 and 289149. Query optimizer using Hash Match Team operators would sometimes fail. I added -T8679 at SQL Server startup.

Now that I'm upgrading to SQL Server 2005, is this trace flag still required?

I see that "this was fixed in SQL 2000, SP1." However, I would like a more precise confirmation that this flag is no longer needed in SQL 2005. Sometimes, no news is not necessarily good news.

The error is intermittent, and at least partially dependent on data conditions not available to me for exhaustive regression testing (or else of course I would do that).

Any info, comments, etc. ... would be welcome.

Thanks ...

Tracy Slack



View Replies !   View Related
Performance Loss When Not Using ODBC-driver Flag &&"Use Ansi Nulls, ....&&"
Hi,

I'v got 2 tables:


create table XXX (S1 varchar (30) NOT NULL PRIMARY KEY, S2 int NOT NULL)
create unique index Idx_X1 on XXX (S2)
create table YYY (S1 varchar (30), S2 int FOREIGN KEY REFERENCES XXX(S2))
create unique index Idx_Y1 on YYY (S2)

I'm connecting to the database via the native Microsoft ODBC-driver.
Applying the following statement

select Y.S1 from YYY Y left outer join XXX X on X.S2=Y.S2 where X.S1=?;

via Windows SQL-Api in c++ for each record in Table XXX (I tried it with 1000 records) takes about 0,3s alltogether, if the flag "Use Ansi Nullls .." in the driver Configuration is checked. 5s ifthe flag is not checked.5 seconds seem quite a lot of time for this task and are actually too slow for my application. I wouldset the driver-flag at once, if then the size of the database would notgrow much faster.

Has anybody had this problem before, or maybe even solved it?


View Replies !   View Related
Error In --&&> EXEC Xp_cmdshell 'bcp &&"SELECT * FROM Tbl A Where A.flag=N&&" Queryout F.txt -c -T'
some one can tell if i can make a "where" clause inside the BCP , when i say: select * FROM tbl where flag=N , usuallythe where flag='N' works with the ' ' , but it gives no error when save the store procedure withoutit ' ' savesnicely the SP,

but
like this it saves well "SELECT * FROM tbl where flag=N" and
when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP ---> -c -T , must have some more or less

View Replies !   View Related
Update Incremental Table With Flag From Other Table
Hi,

I have an incremental table in my datawarehouse and I want to update a ShippedFlgcolumn with Y or N from the existence of the same order in another table.

For instance:
Table A is my incremental table. It has columns OrderNbr and ShippedFlg. Then there is Table B with shipping info including OrderNbr column. I need to update ShippedFlg column in Table A with Y if the order exist in Table B and N if it does not exist.

I thought about the Lookup transformation but do not know how to connect it to Table B. Is there another way? I would think this would be a common practice in datawarehouse ETL but I can not find any article on it.

Thanks,

Fred

View Replies !   View Related
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !   View Related
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies !   View Related
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
andget the right table shown in the output.

Thanks in advance,
Scott Chang

View Replies !   View Related
A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View Replies !   View Related
Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !   View Related
I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View Replies !   View Related
ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View Replies !   View Related
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved