Substring and Patindex having an User Defined Function

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - How to Drill Down a Parent Child Hierarchy in Reporting Services 2005 ? Does nobody know how to fix my problem Other question : Is Microsoft planning an update for the Reporting Services 2005. I think it is not that difficult for Microsoft, because in the Analysis Services in the browser of the cube , the drilldown for a paret child hierarchy is already possible . It would be cool, when a parent child hierarchy could be used in the reporting services as easy as a normal hierarchy. Please Help me JF Tag: Substring and Patindex having an User Defined Function
  - 2
    - how to properly write the mdx? Hi, If this code is in the cube it will not work, what you have put in the cube code is a select statement of sorts (or a query). Code in cubes generally looks more like: Create Member CurrentCube .[Measures].[Internet Gross Profit] AS [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] , Format_String = "Currency" , Non_Empty_Behavior = { [Internet Sales Amount], [Internet Total Product Cost] } ; If on the other hand you are running itas an mdx query, delete the calculate line. WITH MEMBER [Measures].[TotalCount] AS [Measures].[Case Activity Count] SELECT {[Measures].[TotalCount]} on columns , {[Activity].[Phone]} on rows FROM [Case Activity Summary]; Good luck Matt Tag: Substring and Patindex having an User Defined Function
  - 3
    - error when running SSIS package as a job Hi Shahab, Is ACI\snasir hard-coded as the username in any of the connection managers Andy Tag: Substring and Patindex having an User Defined Function
  - 4
    - formatting in sql server Where you want to display this record In a web page or in the SQL server o/p Tag: Substring and Patindex having an User Defined Function
  - 5
    - Out of memory while querying causes wrong data to be returned First of all try installing SP4 http://www.microsoft.com/downloads/details.aspx displaylang=en&FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5 Second, if problem persists contact customer support and have them looking into your problem. Edward. -- This posting is provided "AS IS" with no warranties, and confers no rights. Tag: Substring and Patindex having an User Defined Function
  - 6
    - MDX Calculations Script updates I am not aware of anything like this that is available and there are a couple of barriers to actually building something like it. at present you would need to have admin rights to the database in order to change the MDX Script the MDX script is essentially stored as one long string, so you would need to be able to reliably parse the script and insert the new calculation in the correct spot. So I would doubt that you are likely to see anything like this developed. Some reporting tools let you define your own calculated members for a given report, which can be changed by report authors, but I take it you would like the power users to be able to update the centrally stored calculations. And just to clarify, updating the MDX script does not actually require you to reprocess anything. The script is evaluated at runtime, you only need to deploy a new script for it to be "live". And with the custom rollup formulas, I think you will find that you only need to do an Update process on the dimension for them to be updated, which as long as your dimension is not too big, should not take too long. Tag: Substring and Patindex having an User Defined Function
  - 7
    - OnInformation Event Firing Twice? Have you tried opening the package in the Visual Studio designer to see what looks right and what looks wrong (as compared to a package created in Visual Studio that does not exhibit this double-event behavior) related to event firing I don't know what would cause this behavior, but this would be the next step that makes the most sense to me. Tag: Substring and Patindex having an User Defined Function
  - 8
    - [RESOLVED]Getting only multiple records for all customers It is not easy to 'guess' what criteria you have used. Obviously, eliminating OrderID 3 is because nothing was ordered. But why have OrderIDs 6 and 14 been left out Tag: Substring and Patindex having an User Defined Function
  - 9
    - SQL Server 2000 and ASP.NET connection problem Try the combination of the IP address and the server instance name, e.g., "data source=172.16.3.11\SQL Server; Tag: Substring and Patindex having an User Defined Function
  - 10
    - sql server security problem... "Don't save sensitive" is not what you want. When saving to SQL Server (File->Save Copy As), choose "SQL Server Roles and Storage" for the protection level. That works for me. Otherwise, choose EncryptSensitiveWithPassword and specify a password in the Agent step. Tag: Substring and Patindex having an User Defined Function
  - 11
    - Combining Output PDF files Without combining the reports using subreports, I think you would use code & custom PDF components, unless you want to jump into writing a rendering extension. Softartisians Officewriter & abcPDF.NET are two possible options. You can also try automating Acrobat. http://www.planetpdf.com/forumarchive/84747.asp One alternative may be to export to a different format and then convert to pdf afterwards. cheers, Andrew Tag: Substring and Patindex having an User Defined Function
  - 12
    - Measure Weirdness have a measure that is a running total based on a formula: SUM({NULL:[Time].CurrentMember}, [Actual Work] / [Estimated Work] * [Planned Cost]) When this measure is rolled up for all time (for example 2007) the calculation is correct. For individual weeks or months, the calculation is not correct. See any issue here Tag: Substring and Patindex having an User Defined Function
  - 13
    - SQL2005 SP2 Dec CTP Sharepoint AddIn install problem In Central Admin, can you check if you can find the feature under Site Settings -> Site Collection features If present, make sure that it is activated. Thanks, Sharmila Tag: Substring and Patindex having an User Defined Function
  - 14
    - Reporting Services Instance not found it was stupid of me... got it to work! thanks! -Lawrence Tag: Substring and Patindex having an User Defined Function
  - 15
    - SSIS - Several Bugs in creating a simple task Robert F Bouillon wrote: I was creating an SSIS package, seemingly simple. One of the tasks I needed to do involved copying our production database to a seperate DB instance for staging. I wanted an SSIS package that would stage production so we could push and test changes. My first problem is that I tried to create a simple Transfer Database task. When I tried to run the task, I got an error stating that our Stored Procedure, DtsRun, could not be scripted. It's an encrypted stored procedure. That kinda makes sense does it not The whole point of encrypting it is so no-one can get at it. That's my understanding anyway. Robert F Bouillon wrote: A little annoyed, I decided that I'd do a transfer SQL objects instead. Now I don't want to hard-code my objects to xfer based on the current schema and edit this SSIS script every time we make a DB change; I just want it to copy ALL objects except for my encrypted proc. I'll just build a list with with another task. I decided that I'll write a script that generates a list of proc names, and I'll pass that to the Copy SQL Server Objects task. Well apparantly I can't assign a collection value with a script. I have to create ANOTHER package that modifies THIS package because (1) The entire package isn't exposed to the script task and (2) object variables can't be used in expressions. I've never used Copy SQL Server Objects Task but can you not loop over your objects using ForEach Loop's SMO Enumerator (or other) and move them one by one. Robert F Bouillon wrote: Am I missing something So because I use a single encrypted stored procedure, I have to write 2 fairly complex packages to copy my database That's just stupid. I can understand if SQL can't move the encrypted package while the database is online. I could deal with that. But the extremes I have to go to JUST to copy my database are truly rediculous. Have you tried the Import/Export wizard to generate a package to do this Robert F Bouillon wrote: I was really excited about SSIS, but I've been really dissapointed. The performance of the designer on my machine is really poor (I have a Core T2700 with 2GB RAM). Just about every error message I've gotten, both design-time and run-time, I've had to google because they're so obscure. And if you can only store blittable types and strings in variables, that severely limits the functionality of a package (without going to programmatic extremes). What else would you like to store I haven't yet come across anything that cannot be stored in vars. Robert F Bouillon wrote: Most of all, I'm really dissapointed in the expression system, If the CLR is loaded into the SSQL/SSIS runtime, then why am I coding it using ANOTHER proprietary coding syntax I thought .NET was meant to keep people from having to redesign the wheel Yeah alot of people don't like this but there are good justifications for it. Quite simply, the expression syntax is optimised for what it is provided for. Greasy fast speed. And faster than .Net. Hopefully someone from MSFT will reply will teh proper justification. Robert F Bouillon wrote: Microsoft is constantly pushing C#, yet I can only script in VB.NET, which means I now have code-bases in different languages. It is a problem yeah. The reason is that the scripting environment is VSA - something completely seperate to SSIS. VSA only supports VB.Net. The inclusion of scripting was late in the dev cycle for SSIS v1 and hence they didn't have time to build something that used C#. Given the choice between having VB.Net or nothing at all I know what I'd got for. In the next version VSA is expected to get replaced with VSTO and this will give full C# support. -Jamie Tag: Substring and Patindex having an User Defined Function
  - 16
    - The SELECT permission was denied on the object 'tblName', database 'dbName', schema 'dbo' I would recommend using SQL Server profiler to find out what is the execution context being used at the moment the permission check fails. It may be possible that the connection is being established under a different context than the one you expected. Thanks a lot, -Raul Garcia SDE/T SQL Server Engine Tag: Substring and Patindex having an User Defined Function
- Next
  - 1
    - Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string? Setting the value of a variable is a run-time thing. After the script runs, you won't have the last value generated by the script stored in the variable. The variable will revert back to the default set at design time. Tag: Substring and Patindex having an User Defined Function
  - 2
    - DTS Code works only with VS 2003 but not with VS 2005 ? DTS has been replaced with SSIS, so yes, plenty of things have changed. DTS questions can be asked over at the DTS news group, if that helps: http://groups.google.com/group/microsoft.public.sqlserver.dts lnk=srg Tag: Substring and Patindex having an User Defined Function
  - 3
    - i want know the difference between "SQL Server 2005 -Std Edition" and "SQL Server 2005 Std license" IF both descriptions refer to SQL Server, then there probably are no differences. The best way to know the product is to check the SKU. However, since the word 'license' is not capitalized, I wonder if it should have been SQL Server 2005 Std Single Processor License" Tag: Substring and Patindex having an User Defined Function
  - 4
    - Report Definition Not sure if this is what you mean, but you can control the # of rows displayed by limiting the # of rows fetched from the data source. There's no way AFAIK to fetch N rows but only display N-M rows. Tag: Substring and Patindex having an User Defined Function
  - 5
    - Access To Dts.Variables Causes Exception Right Click the script task, press F4, this will show the properties of the script task, there you will have properties called ReadOnlyVariables, ReadWriteVariables. You can set the variables name in this based on your requirement. These variables will be available in the script, else they dont have visibility inside the script task and an error will be thrown at run time. Thanks Tag: Substring and Patindex having an User Defined Function
  - 6
    - Property DefaultSchema is not available for Database [DatabaseName] this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then Tag: Substring and Patindex having an User Defined Function
  - 7
    - Cannot insert duplicate key Try the below query, INSERT INTO tbUnique SELECT Distinct * FROM tbDupes Tag: Substring and Patindex having an User Defined Function
  - 8
    - RS Integration with SharePoint 3.0 - Query with userid parameter Hi, actually these are two different questions: it depends how you implemented the authentication. If reports are called by Sharepoint, it is possible to use the information of the Windows Identity to authenticate against the SQL Server. As you are using a UserId approach and not the actual identity at the SQL Server level you could also use SQL Server authentication to run the query of the report. So the following options could be available: [Report (UserId) -- IIS (Windows Integrated Authentication)] -- SQL Server (SQL Server Authentication with static credentials) [Report (UserId) -- IIS (Windows Integrated Authentication)] -- SQL Server (Windows Authentication) HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Substring and Patindex having an User Defined Function
  - 9
    - SQL 2000 - All Client Tools You don't buy the client tools. If you have a SQL Server installed then, depending on the license (per proc vs server cal), everyone that connects can install the client tools. Just have them install the tools from the SQL Server CD. Tag: Substring and Patindex having an User Defined Function
  - 10
    - Printing in Reporting Services hangs I am also facing a very similar problem... Printing to a real printer or export to pdf shows "Printing Now..." and w3wp.exe service on server starts consuming 100% CPU!!! I tired to run/print the same report from VS2005 and RSReportHost.exe dies while printing. After further analysis, we found that there was a field in a subreport that was extremely long... so long that it was spilling over a page's height. It seems like a bug in Reporting Service printing routine when there is a subreport with large fields, it freaks out... Tag: Substring and Patindex having an User Defined Function
  - 11
    - SUM in Report Hello Leandro, I belive something like: Code Block =sum(iif(Fields!FlagPresenca.Value=1,1,0)) will do what you want. The expressions in SQL Server Reporting Services can use Visual Basic syntax so the IIF works as described here: http://msdn2.microsoft.com/en-us/library/27ydhh0d(VS.71).aspx Good luck! Larry Smithmier Tag: Substring and Patindex having an User Defined Function
  - 12
    - Failed to load expression host assembly. Details: The type initializer for 'CableReporting.Utilities' threw an exception HI Apurv, You need to copy your assembly file to the folder under microsoftsqlsever\mssql.2\ Report server\bin Tag: Substring and Patindex having an User Defined Function
  - 13
    - Could not create Maintenance Plan It seems like that should work. Please file a defect report for this on http://connect.microsoft.com/SQLServer . Issues reported on the connect site go directly into our internal issue tracking system. Be sure to describe how the cluster is configured, whether SSIS is installed on each node, etc. Thanks, Steve Tag: Substring and Patindex having an User Defined Function
  - 14
    - Cascade Issue This is not allowed in SQL Server. The tree of cascading referential actions must not have more than one path to any specified table. If the script succeeded, a single update/delete on table [AA] may cause cascading referential actions to table [BB] through both foreign keys [FK_bb_aa] and [FK_BB_AA1]. You need to specify ON DELETE NO ACTION or ON UPDATE NO ACTION on [FK_bb_aa] before creating the foreign key [ FK_BB_AA1]. Tag: Substring and Patindex having an User Defined Function
  - 15
    - An error occurred during local report processing. Index was outside the bounds of the array. One thing it might help you is to see if there are any diffrences of your local rdl file compared with the publised one I hope you know how to get the rdl out of the published report. Tag: Substring and Patindex having an User Defined Function
  - 16
    - Unable to connect to the remote server Can you verify that this is the correct virtual directory in IIS manager I can't remember if the $ in the name caused any problems. Anyway, in this CTP, RS is effectively the same as SQL 2005 SP2 so don't expect any big changes. Tag: Substring and Patindex having an User Defined Function

MannuBhai

Hi All,

Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters Something like this:

Code Block

Select SUBSTRING(Select * from DB.dbo.Function('Parameter'), PATINDEX('%\%', Select * from DB.dbo.Function('Parameter'), 8)

Mannu.

Top

Re: Substring and Patindex having an User Defined Function

MannuBhai

Hi All,

Yes, we can do this. I just figured this out. The fault was that I was not using braces. It was solved. Thanks.

Mannu.

Top