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
Currently, user apple can exec sp_help_job (system stored procedure) in database msdb on Server/Instance name CALFI303 but it doesn€™t return any records. What permissions I need to give to the User apple ? If I sign on with sa, it returns the records with no problem.
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
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 RelatedDoes 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 RelatedHi.
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.
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.
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.
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.
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.
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
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?
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
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.
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
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
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 View RelatedBACKGROUND:
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
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
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 Relatedwhy 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
Hello All,
Can anyone tell me what is error is and how to resolve it?
Thanks.
Has ever built a separate server just for SSIS and how did you do it?
View 2 Replies View RelatedI've 2 tables as follow, --> Full script and data as attachment, Scripts.zip
CREATE TABLE [dbo].[myMenuCollection](
[menuCollection_idx] [int] NOT NULL,
[parentID] [int] NULL,
[code]....
You can see - User select 3 Menu, which is the Menu Id is 1, 4, 10.If the Parent Id for Menu is 0, there is 1 record only to insert. If the Parent Id for Menu != 0, we've to make sure the Insert statement will insert the Parent Menu automatically
Based on Photo Above, there's 3 Menu is selected. But, in back-end - Insert statement will insert 4 record. Please see Menu Id = 10. The Parent Id = 9. So, we need to insert Menu Id = 9 automatically into myInsertedMenu table
Hi folks.
When installing SQLServer 2005 Express, I use the following command line:
cmdline = " /qb ADDLOCAL=SQL_Engine,SQL_Data_Files SECURITYMODE=SQL INSTANCENAME=MyServer SAPWD=MyPwd DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1 requiresmsiengine=1"
I noticed that when installing on a Windows XP machine, the installation results in a SQLServer instance which is configured with Built-in account = Network Service. However, with the same command line used on Windows 2000 machines, the configuration winds up being Built-in account = Local System. My understanding is that the default configuration is supposed to be Local System.
What can I do to ensure that the instance configuration is always Local System during the silent installation? This is required otherwise, under the Network Service configuration, it creates a messy situation to attach DBs.
Thanks!
Mike
hi all,
Can any one clarify me the doubt in deploying the application build with SQL Server Express 2005 as database and Vb.Net 2005 as frontend with the same setup made for delopying application. will the server installation and database creation can be made automatic.
If this is possible then what are the steps in doing so and the files to be included while enbeding with vb.net setup.
thanks
harsh
Hi guys
I have a table which I need to analyze, what jobs this is working with, what stored procedure is calling this table etc. Is there any built-in function to show me or anybody know how to do it? This is my new job, and there are hundreds of stored procedures and jobs, do i really have to go thru one by one?
Please help.
Michelle
Greetings,
I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes? Why so many? Also, what is the "text" datatype and when is it most likely to be used? There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.
TIA,
Mark
Hi everyone,
I am a novice programmer. I want to develop a Inventory software using
C# 2005, which will run on a LAN environment. There will be three machine
which will store and retrive data from a common database. Which version
of SQL server is appropiate for this ? Is it possible to provide the
networking facilily using MS Access ? I don't know how to configure SQL
Server for this. Please help me.
Thank You.
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)
Hi,
Plz help!
I built a web site using IIS and ASP.NET 2.0.
My web site has a log on screen and I only configured it with one user without any roles. This web site uses the SQL EXPRESS.(I Have 2 databases: 1 for the security required by the application and the other for my application. for the connectionstring I used a windows authentication security with an instance = true.)
Everything went well on my PC.
When I tried to deploy the web site on another PC, there was a big problem.
When I tried to login with the same user I created, then I have a problem.
The password is incorrect(Why? I don't know).
Maybe there is a problem with the IIS or with the SQL SERVER.
pLZ HELP!!
hi i am new to mssql db stuff. I just developed my first VB.NET application and it uses a mssql database which is hosted locally on my development machine. I built the deployment msi file for my application which can be installed easily on client machine, but have no idea how to move the mssql db with it.
View 1 Replies View RelatedSimply put, I have a 'Date and Time', (06/03/2006 11:40:00), passed to the SelectCommand via the QueryString. I would like to gain data that is between the supplied time and 1 hour prior.Is there a simple way to take 1 hr off the 'Date and Time" value or is it necessary to build code that parses the string then adjusts it?
The 'Time' and 'TimeStamp' entities below are both of type 'Date and Time' and all values are gained from the same sql database. I.e. the time used as the basis for selecting the hr period is from the same database as the one where the hour period will be selected from.
Any help would be great.
SelectCommand="SELECT [Timestamp], [Volume] FROM [out8$] WHERE (([CustomerLvl1] = @CustomerLvl1) AND ([Timestamp] = @Timestamp))">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="TNT Express" Name="CustomerLvl1" QueryStringField="Cust"
Type="String" />
<asp:QueryStringParameter DefaultValue="20/02/2006 22:20:00" Name="Timestamp" QueryStringField="Time"
Type="DateTime" />