Understanding 'Assembly Blah Was Not Found In The SQL Catalog' And Workaround
Nov 27, 2006
This is the message I get trying to load the assembly into my database:
Create failed for SqlAssembly Microsoft.Adapter.SAP.SAPProvider
Assembly microsoft.oba.metadata.metadataaccess, version=1.0.0.0, culture=neutral, publickeytoken= was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)
I heard one opinion that said that it appears that SQL the CLR Hosted Environment tries to load the whole assembly dependency graph and doesn't find the dependent assembly in its catalog. That is consistent with the restriction of the CLR Hosted Environment not supporting managed code dynamically generated (
http://msdn2.microsoft.com/en-us/library/ms131047.aspx )
My questions are: what is microsoft.oba.metadata.metadataaccess and how can I preload it in SQL Server? Assuming the assembly lives somewhere in the server, is there a way to refer to it inside SQL Server without loading it? How is the SAP provider related to Office Business Applications Services (that's the only acronym I could find relating Microsoft with OBA)?
thanks,
Gustavo
View 1 Replies
ADVERTISEMENT
Jul 17, 2006
I'm trying to connect to VSS from my CLR procedure.
I'm getting this error..
Assembly
'microsoft.visualstudio.sourcesafe.interop, version=5.2.0.0,
culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the
SQL catalog.
I added reference to VSS dll by opening the project file in notepad, as I couldn't right-click and do an add reference.
Here's the code...
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.VisualStudio.SourceSafe.Interop;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
try
{
//' The SourceSafe INI file.
string iniVssPath = "C:/CLRVSS";
//' The SourceSafe User ID/Password.
string cVSSUserName = "Admin";
string cVSSPassword = "";
VSSDatabaseClass vssLib = null;
vssLib = new VSSDatabaseClass();
vssLib.Open(iniVssPath, cVSSUserName, cVSSPassword);
VSSItem VSS_Item = vssLib.get_VSSItem("$/Test", false);
VSS_Item.Destroy();
SqlPipe p;
p = SqlContext.Pipe;
p.Send("success");
}
catch
{
SqlPipe p;
p = SqlContext.Pipe;
p.Send("error");
}
}
};
View 3 Replies
View Related
Oct 24, 2005
Im trying to register my CLR UDF in SQL 2005 using this code CREATE FUNCTION GetSomething() RETURNS INT AS EXTERNAL NAME MyAssembly.MyFunction.MyMethod When I run it against my DB I get this error: Assembly MyAssembly was not found in the SQL catalog of database MyDB Ive successfully registered my custom assembly in the DB (I see it under Assemblies folder), and Ive set CRL Enabled to 1 in my DB. What am I doing wrong? Thanks in advance
View 3 Replies
View Related
Apr 27, 2007
Hello everyone,
I am struggling with this error and it's starting to get to me!
I have setup a simple project with just a data flow task. In this task, it retrieves a simple record "Select * From Table1 WhereId=1". The connection is an OLE Db connection using Advantage Oledb Provider (a provider for accessing dBase). It is accessing a Novell network drive. I have setup the package to both "EncryptSensitiveWithPassword" and "DontSaveSensitive". Both times i have received this error.
The funny thing is, when I set the package to "EncryptSensitiveWithPassword" and I setup a job to run the package, it never asks me for the password even though I set one up! I have a previous package (few months ago I made) that accessed the same database on the same Network drive and it works! It also asks me for a password when I try to modify or run the package (b/c I set it to "EncryptW/Password").
Can anyone give any light into this problem?
Thansk for all your support,
Adrian
View 1 Replies
View Related
Apr 9, 2007
I have two tables one is riders: riderid, firstname, lastname, addressid
The other is address: addressid, address1, city, state...and foolishly riderid
Originally, I saved the riderid as a FK in the address table, not the addressid in the ridertable. Not the worst thing, but now I want to use the address table for more than just riders. So, I should be storing the addressid in the riders table, not the riderd in the address. The address ID in the riders table is all NULLS. I thought that I could do a quick UPDATE to fix this...
update riders SET address =
( SELECT addressid FROM address WHERE address.riderID = riders.riderid )
But this returns the error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted .... and more...
I can see where the query rerturns more than 1 row, how do you tie that to individual riders?
View 2 Replies
View Related
Nov 5, 2007
What is the purpose of the .cer file found in MSSQL.1MSSQLData? Something to do with encryption, but what?
Why do I see as many as three .cer files on a server?
Thanks, Dave
View 1 Replies
View Related
Aug 7, 2015
CLR's not registered as SAFE are disallowed on our Network. It appears that the key associated with the assembly (ISSERVER) may be able to link to the Service Master Key. If the service master key is created with a (CA) Certificate Authority, is it possible to create the SSISDB that accompanies the Integration Services Catalog as a SAFE assembly?
[adding] BY CA meaning:
ALTER SYMMETRIC KEY [mykey] ADD ENCRYPTION BY CERTIFICATE [myservercertname]
View 8 Replies
View Related
Jan 20, 2008
Hello,I just lost all my data doing a system restore and now trying to rebuild my web project.However, Am getting the error below, trying to use a class to run my Stored procedure.What is wrong herethanksEhiCompiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:Line 22: //SqlConnection con = new SqlConnection("cellulant_ConnectionString");
Line 23: //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cellulant_ConnectionString"].ToString());
Line 24: SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"].ToString());
Line 25:
Line 26: Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 24 using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public class signup_data_entry
{
public signup_data_entry(DateTime dob1)
{
//SqlConnection con = new SqlConnection("cellulant_ConnectionString");
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cellulant_ConnectionString"].ToString());
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"].ToString());
SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
command.CommandType = CommandType.StoredProcedure;
con.Open();
View 2 Replies
View Related
Feb 22, 2008
I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly <Assembly name> with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
The signatures of methods in the assembly that are called from other assemblies.
The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
The FillRow method name attribute for CLR table-valued functions.
The Accumulate and Terminate method signature for user-defined aggregates.
System assemblies.
Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
Adding new public methods.
Modifying private methods in any way.
But I havent done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. Ive got the same error, if I add or change any method in assembly before rebuilding.
View 9 Replies
View Related
Oct 23, 2014
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
View 1 Replies
View Related
Nov 27, 2007
I have a multi select param that I display on the title of the report using something like so:
Parameters!Division.Label(0)
The problem is when a user selects more than one value it does not get displayed on the title. Addding Parameters!Division.Label(0) + Parameters!Division.Label(1) + Parameters!Division.Label(2) doesn't returns an error if user only selects 1 or 2 values because Parameters!Division.Label(2) is empty.
Is there a way to iterate through or do error checking on the array so that I can dynamically populate the title with the values selected by the user.
Any help would me much appreciated.
View 5 Replies
View Related
Feb 29, 2008
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
View 20 Replies
View Related
Jan 12, 2006
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
View 7 Replies
View Related
Aug 2, 2006
Hi
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
namespace inlineCLRsql{
public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());
}
}
}
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Thanks for your help
M
View 1 Replies
View Related
May 1, 2008
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but cant get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed.
[token 0x02000008] Type load failed.
View 8 Replies
View Related
May 31, 2007
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.
I import System.IO? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is on?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators].
2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to My Windows Authentication ID?.
3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON
4) In Visual Studio .NET 2005 Set the permission levels to external
5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated.
Regards Steve
View 8 Replies
View Related
Mar 8, 2006
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.
VS.NET creates some SQL that looks pretty interesting:
CREATE ASSEMBLY [AssemblyNameHere]
FROM 0x4D5A90000300000004000000FFFF000......<continue binary data>
WITH PERMISSION_SET = EXTERNAL_ACCESS
Boy howdy!
I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.
Anyone out there been able to get this to work?
-Ben
View 4 Replies
View Related
Jan 7, 2008
Im running sql server 2005 and trying to connect from dreamweaver. What is causing this problem?
View 2 Replies
View Related
Dec 14, 2004
The very simplified version of my problem is that these
Select DISTINCT Cast(KWID as NUMERIC)
FROM OV_MID
Select DISTINCT Convert(Numeric,KWID)
FROM OV_MID
should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.
I get this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.
I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:
Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.
Thanks!
View 1 Replies
View Related
Jul 20, 2005
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Mller
View 3 Replies
View Related
Nov 8, 2006
Hello everbody,
this query:
SELECT * FROM TBL_DEVICE_DRIVERS
WHERE (DD_CATEGORIES & 2147483648) > 0
bring the error message:
Invalid operator for data type.
The value 2147483648 is hex 0x80000000 for bitwise
joining defined.
I try to cast it to decimal, but it does not work.
Have anybody an idea?
Thanks for answers!
View 4 Replies
View Related
Aug 6, 2001
The function "DATEPART(wk, [valid_date])" appears to have the following bug:
DATEPART(wk, date) returns week 53 for the following dates (checked years 2000, 2001, 2002):
year 2000: 12-24-2000 through 12-30-2000 inclusive
year 2001: 12-30-2000 through 12-31-2001 inclusive
year 2002: 12-29-2002 through 12-31-2002 inclusive
DATEPART(wk, date) returns week 54 for the following dates:
year 2000: 12-31-2000
SQL2000 SP1.
Are there any known workarounds/fixes/patches for this (other than just hand-coding the function?)
Thanks!
David Schneider
Engineering Manager
iScribe, Inc.
DSchneider@iscribe.com
View 2 Replies
View Related
Apr 10, 2008
I'm converting crystal reports to SSRS reports right now and came across this function that I'm not familiar with.
It's a formula field in crystal that has this formula:
quote:whileprintingrecords;
NumberVar RTCurrent;
NumberVar RT31to60;
NumberVar RT61to90;
NumberVar RT91to120;
NumberVar RTOver120;
if {@AgedDays} < 31 then RTCurrent := RTCurrent + {@BalanceDue} else
if ({@AgedDays} > 30 and {@AgedDays} < 61) then RT31to60 := RT31to60 + {@BalanceDue} else
if ({@AgedDays} > 60 and {@AgedDays} < 91) then RT61to90 := RT61to90 + {@BalanceDue} else
if ({@AgedDays} > 90 and {@AgedDays} < 121) then RT91to120 := RT91to120 + {@BalanceDue} else
if {@AgedDays} > 120 then RTOver120 := RTOver120 + {@BalanceDue}
@Aged days is just an integer, but that shouldn't matter for this thread.
Is there just a While loop equivalent for this in SSRS?
View 2 Replies
View Related
Apr 10, 2008
At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?
Thanks
View 5 Replies
View Related
Apr 28, 2006
I have written a UDF into which I pass a table name, field name, value of the field, whether alpha characters are valid, whether numerics are valid, and a string of alphanumerics that are valid. I return back a string with all invalid characters removed. Unfortunately when I use this on names and addresses in an 12000 row table, it takes forever to run. Can anyone think of an easy way to do this which isn't so labour intensive. Please see code below.
NB CHAR(32) is space, CHAR(45) is -,CHAR(39) is '
CREATE FUNCTION dbo.UDF_RemoveInvalidCharacters
( @sTableName varchar(50),-- e.g. 'Contact'
@sFieldname varchar(50),-- e.g. 'Lastname'
@sFieldValue varchar(500),-- e.g. 'Jeremi@h O''Grady84'
@sAlphaValid char(1),-- e.g. 'Y'
@sNumericValid char(1),--e.g. 'N'
@sAlphanumericsValid varchar(500))--'CHAR(32):CHAR(45):CHAR(39)'
RETURNS varchar(500)
AS
BEGIN
DECLARE @sReturnValue varchar(500),
@nTableID int,
@nFieldLength int,
@nCurrentPos int,
@sTestChar char(1),
@sValid char(1),
@nAlphanumericPos int,
@sAlphanumericTest varchar(8),
@sTempTestChar varchar(8),
@sAlphasFound char(1),
@sNumericsFound char(1),
@sAlphanumericsFound char(1)
--Get ID of table that the field is on
SELECT @nTableID = [id]
FROM SYSOBJECTS
WHERE [name] = @sTableName
--Get the length of the field
SELECT @nFieldLength = sc.length
FROM SYSOBJECTS so, SYSCOLUMNS sc
WHERE so.id = @nTableID
AND sc.id = @nTableID
AND sc.name = @sFieldName
--Initialise values
SET @sReturnValue = ''
SET @nCurrentPos = 1
SET @sValid = 'N'
SET @sAlphasFound = 'N'
SET @sNumericsFound = 'N'
SET @sAlphanumericsFound = 'N'
--Test each character to ensure it is valid before adding it to the return string, a string consisting solely of alphanumeric characters would be wrong
WHILE @nFieldLength >= @nCurrentPos
BEGIN
SET @sTestChar = substring(@sFieldValue,@nCurrentPos,1)
IF @sAlphaValid = 'Y' --alphas are valid
BEGIN
IF UPPER(@sTestChar) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
BEGIN
SET @sValid = 'Y'
SET @sAlphasFound = 'Y'
END
END
IF @sNumericValid = 'Y' AND @sValid <> 'Y'--numerics are valid
BEGIN
IF @sTestChar in ('0','1','2','3','4','5','6','7','8','9')
BEGIN
SET @sValid = 'Y'
SET @sNumericsFound = 'Y'
END
END
SET @nAlphanumericPos = 1
WHILE LEN(@sAlphanumericsValid) > @nAlphanumericPos AND @sValid <> 'Y' --alphanumerics that are valid
BEGIN
IF CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid))) > 0
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid)))-1)
END ELSE
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,(LEN(@sAlphanumericsValid)-@nAlphanumericPos)+1)
END
SET @sTempTestChar = 'CHAR(' + RTRIM(LTRIM(STR(ASCII(@sTestChar)))) + ')'
IF @sTempTestChar = @sAlphanumericTest AND (@sAlphasFound = 'Y' OR @sNumericsFound = 'Y') --alphanumerics are only valid once we have alpha or numerics
BEGIN
SET @sValid = 'Y'
SET @sAlphanumericsFound = 'Y'
END
SET @nAlphanumericPos = @nAlphanumericPos + LEN(@sAlphanumericTest) + 1
END
IF @sValid = 'Y'
BEGIN
SELECT @sReturnValue = @sReturnValue + @sTestChar
END
SET @nCurrentPos = @nCurrentPos + 1
SELECT @sValid = 'N'
END
IF @sAlphanumericsFound = 'Y' AND @sNumericsFound = 'N' AND @sAlphasFound = 'N' --alphanumerics on their own are not valid
BEGIN
SELECT @sReturnValue = ''
END
RETURN @sReturnValue --in the example I would get Jeremih O'Grady
View 1 Replies
View Related
Apr 30, 2007
Hi,
Any insights to this issue would be great.
Following is my problem statement. I have write around 20Batch programs and in each batch i have atleast 7-8 data validations. If any of the data validation fails then i have to perform a log operation and exit.
Now I have each of my data validation as a Script Task (Control Flow) which inturn would call my SP and set the "Status" variable accordingly
I have written a "OnVariableValueChanged" Event with Raise Change Event for "Status" variable set to "True" Now in this event i check if Status = False, if it is false then i perform the log operation and throw a new DTSException to abort the control flow execution. The event gets fired but it continues to process the next control step(but i wanted it stop there). I could have acheived this by setting a precendence constraint (Status = True) for all of my control flow task but i feel the other approach to be very elegant.
Any suggestions to make this work??
Thanks & Regards
Arvind T N
View 2 Replies
View Related
Jun 21, 2007
I am trying to group on the number of distict field values. Basically:
=Ceiling(CountDistinct(Fields!ClientID.Value, Nothing)/10)
So for every 10 different clients, I get a group. I get an error with the above function saying aggregates are not allowed in a grouping expression. I tried creating a text box with the running value:
=RunningValue(Fields!ClientID.Value,CountDistinct,Nothing)
The value of the text box is correct, but I can't reference that text box in the grouping function. It says the textbox is undefined.
Any suggestions for a workaraound?
Thanks.
View 5 Replies
View Related
Feb 12, 2008
I have a portal site that has many iframes loading various pages. One of the iframes requires data from a database that has a slow connection and right now there is nothing we can do about the slow connection and is something we have to live with.
What seems to be happening though is that even though each page is loading seperatly in an iframe, when the page loads with the slow connection, it seems to hold up processing on the server for the other frames until the connection has been established with the server. It can be something like 10 seconds. I am guessing trying to establish the connection is holding up the worker process on IIS???
So I am trying to find a workaround bearing in mind there is nothing we can do about the slow connection for the time being? Does anyone have any suggestions? One I am thinking of is forcing this frame to load last so at least the other frames are not being held up. Another is maybe to use a seperate thread, but does anyone have any idea on this?
Thanks in advance
View 1 Replies
View Related
Nov 7, 2004
Me and a friend are setting up a .net project on a shared hosting server.....the thing is, they dont seem to allow the use of full text search.....when i connect to the server on Enterprise Manager, the option for "Create new catalog" is disabled.
we need to give users the ability to search by keywords........what's a good workaround for this without using FTS?
any advice?
thanks.
G.
View 3 Replies
View Related
Oct 18, 2007
After digging for some time now into the "guts" of SSRS, I am wondering if anyone out there has any ideas which might help me at this point.
I am trying to write an Invoice report.
Each report can have 1 to n invoices on it.
Each invoice can have 1 to n line items (spanning several pages for the larger ones)
Each page must have a fixed header and footer with account and payment information on it (the page header and page footer work OK for this).
And here is the problem. Each invoice must also include 1 to n images at the end of the report. 2 on a page and take up an entire 8.5 by 11 inch page. (spanning many pages when many line items exist)
Since the report already has a page header and footer with the report detail stuffed in a table in the middle of the page (report body), I am stuck.
I have read several posts which talk about having a can-grow container with a subreport in the existing footer, but I can't even come close to getting this to work. My footer would have to take up the entire page and having nothing but a subreport in it.
I can not provide a link to the images in the report, as each report must print in its entirety without user involvement (no drilling down).
I am thinking that my report is too complex for SSRS at this time. I would love to be proven wrong by someone on this forum.
Thanks for any and all replies.
John
View 2 Replies
View Related
Feb 6, 2007
Hi all,
Could you please suggest workaround for systems that have cross database queries and want to use mirroring.
Thanks,
Avi
View 2 Replies
View Related
Nov 19, 2007
it looks like anything larger than max value for an integer in dateadd's 2nd parameter creates an overflow exception. This pretty much forces us to work no more finitely than minutes in our app. Without a stored proc or ss2008, is there a workaround in sql?
View 9 Replies
View Related
Jan 11, 2006
My SQL Server is a shared account at MaximumASP.com a client just deployed my .NET application on GoDaddy.com and they have all there ports blocked and my app cannot connect to the SQL Server. Using "Network Library =dbmssocn" in the connection string did not help and GoDaddy will not help. MY QUESTION IS: how can I get my .NET app to connect to the SQL Server? web service? This is the first time I have run into this problem. There seems like there has to be some way. THANKS IN ADVANCE!
View 2 Replies
View Related
