Assembly Name For SSIS Built-in UITypeEditors?
Dec 22, 2006
I have a custom PipelineComponent that accepts a string of SQL. I don't have a custom UI, all I need is the Advanced Editor. Currently the SQL property is just the standard line of text that can be entered on the Advanced Editor. I would like to use the popup multi-line editor that the built-in components use for editing SQL. I was hoping it was the System.ComponentModel.Design.MultilineStringEditor but that is definitely not it (and that one is insufficient for entering more than a few lines of SQL). I'm assuming it must be a UITypeEditor that was shipped as part of SQL 2005, but I haven't been able to track down the qualified assembly name for it anywhere. I tried debugging in VS to get down to a IDTSCustomProperty90 that I could look at the UITypeEditor on, but no such like. I also tried using Reflector to see if I could dig up a string, but no luck there either as the pipeline components don't seem to have managed assemblies (I could've just missed them) and the control tasks (which happen to use the UITypeEditor I'm looking for too) seem to only be thin wrappers around COM interfaces. I scoured BOL and the WWW in general for a list of these assembly names, but looks like they're not out there either. Has anyone tried to find these before? Am I barking up the wrong tree, should I not be able to use these editors for copyright reasons?
View 3 Replies
ADVERTISEMENT
Mar 18, 2015
Has ever built a separate server just for SSIS and how did you do it?
View 2 Replies
View Related
Apr 21, 2006
Hi
I'vo got some trouble with the built-in logging feature and a self defined logging table.
Scenario :
A have a SSIS-package with enabled built-in logging to SQL-Server Data-Provider.
At the start of the package, i have an SQL Task which logs
the System Variable system::ExecutionInstanceGUID into the self defined logging table.
But the execution_id which the built-in logging stores to sysdtslog90 isn't the same
value like the value of the system::ExecutionInstanceGUID,
so i can't link my own table with the sysdtslog90 - table.
Any Ideas what is going wrong?
Thanks and sorry about my english
Ivo Becker (Switzerland)
View 3 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 haven€™t 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. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
View 9 Replies
View Related
Jan 25, 2007
I have a .NET assembly that I need to reference and use within my SSIS script task.
The only way I can acheive this is to strongly name my assembly and put it in the GAC.
This is not possible as my assembly is already in production and uses other 3rd party assemblies that would also need to be registered in the GAC.
As a workaround, I have created a .Net console application that references my assembly, that call from a SSIS Process Task.
Does anyone know of another way I could use my .NET assembly within my SSIS package?
Any help appreciated.
Regards,
Paul.
View 3 Replies
View Related
Jan 10, 2006
How to call a dot net assembly from ssis.
I have used a script component and went in the design script editor by clicking the "Design Script" button.From their using a object browser trying to load a dll file in references to be used in the ssis application.
But it gives error " Cannot browse the file"
Can any body help me in the same.
View 1 Replies
View Related
Mar 21, 2007
What is the proper method of using the Excel.Interop assemblies with SSIS? I am using SQL Server 2005 Integration Services (VS 2005) against the .NET Framework v2.0.50727 with the Microsoft.Office.Interop.Excel.dll assembly.
I find that the referenced instance of Excel in my SSIS package is not always released from memory as expected. I'm not sure that I have a problem per se, but I am interested in knowing the Microsoft reccommended method of using the Office Interop assemblies with SSIS.
For this thread, "working" code is defined as observing the appearance and disappearance of the Excel.exe image in the Task Manager as it is created and destroyed in the SSIS package.
The following Sript Task code (between stars) works as expected in that the image of Excel can be seen created and removed from the Task Manager list of processes:
************************************************
Imports Microsoft.Office.Interop.Excel
Public Sub Main()
Dim mobjExcelApp As Microsoft.Office.Interop.Excel.Application
Dim mobjWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim objSheet as Worksheet
Dim strExcelFilename as string = "C:TempMyExcelfile.xls"
Dim strSheetname_ValidSamples as String = "Sheet1"
mobjExcelApp = New Microsoft.Office.Interop.Excel.Application
mobjExcelApp.Visible = False
mobjWorkbook = OpenWorkbook(strExcelFilename)
objSheet = CType(mobjWorkbook.Worksheets(strSheetname_ValidSamples), Worksheet)
strWell = CType(objSheet.Range("a1").Offset(intRow, 0).Value, String)
objSheet = Nothing
mobjWorkbook.close
mobjWorkbook = Nothing
mobjExcelApp.Quit
mobjExcelApp = nothing
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
End Sub
***********************
On occasion, when the line of code that sets the variable strWell (in BLUE above) is followed with the following line of code:
strSampleName = CType(objSheet.Range("a1").Offset(intRow, 1).Value, String)
the code will work fine with no errors, however, the Excel image is not removed from the task list. I have found that the instance will disappear upon waiting (several minutes or more).
Thanks,
Rob
View 5 Replies
View Related
Jan 16, 2006
I tried to access a dot net assembly (.dll) file in ssis script component using following steps.
Create new Script Task in Data Flow Task
Edit Design Script button-> Loads script Project in MS VSA
Locate Object Browser
Select Custom Component Set from the dropdown and hit browse button
Browse and place the custom component dll (This Custom component dll has to be in GAC - Global assembly cache before browsing)
Select the namespace from the Component list of the object browser and click on €œAdd to references to Selected project in the solution explorer€? button
Write Imports <namespace> in the script code to invoke class methods from the .NET custom component
The following steps worked properly with June CTP version of yukon.In september CTP version of Yukon in SSIS when i browse the dot net assembly with the same above steps i get a error stating " The file could not be browsed ".Can anybody help me in the same.
Prashant Utekar
View 1 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 can€™t 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
Jul 26, 2001
I recently came across this built-in role in MSDB. It is not documented anywhere except sp_create role stuff. Does anyone know what this role is used for? Is this what gives users that are not dbo or sa access to see jobs?
View 1 Replies
View Related
Aug 15, 2007
Hi,
Is there some kind of built in function in SQl to look for the presence of certain characters in a table collumn.I have to replace the text in those collumns with different text or just use substr, instr functions.
Thanks in advance
View 4 Replies
View Related
Jun 13, 2006
I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?
View 2 Replies
View Related
Aug 21, 2000
Does anyone know if SQL has a built in function for returning increments of rows. For instance; I have a table with 100,000 rows, but would like to return 512 rows at a time. I've looked at the documentation and haven't came across anything that will do this. Any help is appreciated.
View 4 Replies
View Related
Jan 28, 2004
Hi.
I am a beginner of SQL server.
I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
Column Name-Customer Name
Data Type- varchar
Length- 50.
May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
other characters such as "&, !,#....".
Please help. Thank you.
View 6 Replies
View Related
Jun 20, 2008
Hi,
I have created one login with windows authentication.
after that i have deleted the built-in-administrator login.
now i am not able to login thru windows authentication mode.
how to rectify it..
i have tried to create one new login with windows authentication mode..still i am not able to connect thru windows authentication.
View 5 Replies
View Related
Dec 28, 2006
Hi,
where can i find the file SQLServer2005_load.sql? I would like to copy the whole script from that file.I am using MS SQL server 2005. please help me.
View 1 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 11 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 1 Replies
View Related
Nov 27, 2007
I have read all of the messages related to the above problem and none have solved the issue.
We have migrated a SQL 2000 database to SQL 2005. Detached from one and attached to the other and then based on some of the conversation I also used the SQL Server 2005's copy option and copied the database. Detached the old one and renamed the new database which was created in the copy process to the old name. Basically getting back to where I started. By the way there are no triggers generated during the process (as was mentioned), nothing to delete as some of the discussion was suggesting.
Big databse, lots of tables and stored procedures. All screens seem to work except when we try to update some data using a stored procedure which takes the data and updates the table. End up getting the above error during the execution. All used to work fine with SQL Server 2000. I have taken the SQL out of the stored procedure and executed manually and it worked but leaving the stored procedure alone and calling it with the updated data we get the INITCOLVS problem. The database is also set to be compatible to 90 so that suggestion also has not fixed the problem. Looking for additional suggestions and solutions. Some one was talking about making a dummy INITCOLVS function, have not done this yet but don't particularly like this suggestion, like to know the cause and the proper solution to the problem.
Again in summary:
Migrated SQL 2000 database to 2005
Have done the copy and attach process to make sure the whole conversion to SQL Server 2005 has taken place.
Have set the compatibility of the database to 90
Updates cause the above error.
Thanks in advance.
Don
View 1 Replies
View Related
Mar 21, 2013
how to format the last part of this query which comes from Access.:
SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID
FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID
WHERE TITLES_WARNING_SENT_qry.TitleID Is Null
GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.TitleID
HAVING (TITLES.RecDT<Date()-31)
I am getting:
'Date' is not a recognized built-in function name.
It is probably something simple but how would I go about converting this part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy with?
View 4 Replies
View Related
Nov 10, 2014
I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4 'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me?
SELECT
TT.PartNumber
,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]
,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])
FROM dbo.vt_TroubleTicket TT
WHERE TT.Closed = '-1'
and TT.DateClosed between '1/1/2013' and '1/1/2014'
and (TT.PartNumber = '12345')
GROUP BY TT.PartNumber
View 3 Replies
View Related
Dec 4, 2014
I just started at a new job and they have DQS set up on our BI server. Some genius decided to delete [##MS_dqs_service_login##] login, the user dqs_service still exists in the DQS_Main db. The error log is full of errors similar to this one...
The activated proc '[internal_core].[ParallelExecutionActivator]' running on queue 'DQS_MAIN.dbo.ParallelExecutionRequestQueue' output the following:
'Cannot execute as the database principal because the principal "dqs_service" does not exist, this type of principal cannot be impersonated, or you do not have permission.'I have tried the standard exec sp_change_users_login 'Auto_Fix' with no success. At this point I don't think anyone is utilizing DQS.
View 1 Replies
View Related
Jan 18, 2007
Hi,
A bit of a long-shot posting this here, as this area doesn't seem to see too much action, but Google is failing me!
I've run through all the necessary steps to create a deployment utility (as per http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx), but am not getting the DTSInstall exe when I build the project. No errors or warnings are generated, and all the other files (including the SSISDeploymentManifest) are created as expected. Anyone have any good ideas?
Mark
View 3 Replies
View Related
May 20, 2008
Hello Pros,
I have a .NET 1.1 component that I want to use as an extended stored procedure in SQL Server 2000. Is this possible? FYI, I read in many sources (e.g. MSDN blogs) that creating extended stored procedures can only be done using a low level language such as C/C++.
I am wondering if it's possible to expose this component as a COM component using the .NET interop library, then register it with SQL Server 2000, and finally be able to consume it from TSQL?
Help please,
Yousef
View 1 Replies
View Related
Feb 21, 2008
BACKGROUND:
When SQL Server 2005 w/ SSRS2005 was installed, a database named "ReportServer" was created by the system. I use the tables in this database to generate audit/statistical reports primarily hitting the dbo.ExecutionLog & dbo.Subscriptions tables for analyzing server activity.
QUESTION:
Can I modify the tables in the system built "ReportServer" database? Or, will RS stop working? Also, if i modify the tables will it prevent an update?
DETAILS:
As for the Subscriptions table, I want to add the Field "LastRunTime" as a PK so all the history will be storeded. Curently, this table only stores the last subscription that was sent and gets over written when a that subscription is sent out again. Or, is there another way to store all history?
As for the ExecutionLog table, it only stores data for 60 days. I want to store all history. Is this possible?
All comments & suggestions are welcome & I look forward to all your respoonces!
-Jeff
View 4 Replies
View Related
Oct 4, 2006
Hi
I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.
I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.
Is there a way to get these programmatically? I would like to avoid typing them out by hand.
Thanks
Chris
View 6 Replies
View Related
Apr 17, 2015
I am using MS SQL Server Management Studio version 10.50. Some built-in functions are missing, such as LAST_VALUE and IIF. Where can I get those?
View 4 Replies
View Related
Jan 28, 2008
why I am getting this error on the SQl Server 2005 but not getting it on the SQL Server Express? and how to ifx?
Code Snippet
Msg 195, Level 15, State 10, Procedure sp_check_sp, Line 14
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.
this is my code:
Code Snippet
USE shefa
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_check_sp]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT DB_NAME(st.dbid) DBName
, OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName
, OBJECT_NAME(st.objectid, dbid) StoredProcedure
, MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = 'proc'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid, st.dbid),
OBJECT_NAME(objectid, st.dbid)
ORDER BY MAX(cp.usecounts)
END
View 4 Replies
View Related
