Friday, March 30, 2012
Managed Stored Procedures
VS2003 use .NET 1.1.
Good article about VB.NET and SQL/CLR: http://www.devx.com/dotnet/Article/21286
Managed Store Procedure does not deploy
I have created a managed stored procdure in a sql server project in VS. I have put in the corect server name password and login fro the connection to the database.
When I deploy however it doesn't deploy the stored proccdure to the database even though it says it has successfully deployed the stored procedure. Has anyone had this
problem and how can you make sure it is deploying to the correct database.
Did you make sure it is deploying the stored procedure into the correct database? It might be deploying it in master.mdf.
sqlManaged Services?
Does anyone know of any companies offering managed type services for SQL
Server?
Thanks in advance.
Any help would be greatly appreciated.
I may be thinking too hard or not enough, but what do you mean by managed
services?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
> Hello,
> Does anyone know of any companies offering managed type services for SQL
> Server?
> Thanks in advance.
> Any help would be greatly appreciated.
|||1. Managed Hosting
2. Managed Monitoring
"Kevin3NF" wrote:
> I may be thinking too hard or not enough, but what do you mean by managed
> services?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
>
>
|||You are wanting someone else to maintain your SQL Server install, or do you
just want hosted SQL Server access?
If you are just looking to outsource the management of your SQL Server admin
tasks...contact me offline :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:E48FC353-E46A-4223-8A0E-F958B20544CF@.microsoft.com...[vbcol=seagreen]
> 1. Managed Hosting
> 2. Managed Monitoring
> "Kevin3NF" wrote:
Managed Services?
Does anyone know of any companies offering managed type services for SQL
Server?
Thanks in advance.
Any help would be greatly appreciated.I may be thinking too hard or not enough, but what do you mean by managed
services?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
> Hello,
> Does anyone know of any companies offering managed type services for SQL
> Server?
> Thanks in advance.
> Any help would be greatly appreciated.|||1. Managed Hosting
2. Managed Monitoring
"Kevin3NF" wrote:
> I may be thinking too hard or not enough, but what do you mean by managed
> services?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
>
>|||You are wanting someone else to maintain your SQL Server install, or do you
just want hosted SQL Server access?
If you are just looking to outsource the management of your SQL Server admin
tasks...contact me offline :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:E48FC353-E46A-4223-8A0E-F958B20544CF@.microsoft.com...[vbcol=seagreen]
> 1. Managed Hosting
> 2. Managed Monitoring
> "Kevin3NF" wrote:
>
Managed Services?
Does anyone know of any companies offering managed type services for SQL
Server?
Thanks in advance.
Any help would be greatly appreciated.I may be thinking too hard or not enough, but what do you mean by managed
services?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
> Hello,
> Does anyone know of any companies offering managed type services for SQL
> Server?
> Thanks in advance.
> Any help would be greatly appreciated.|||1. Managed Hosting
2. Managed Monitoring
"Kevin3NF" wrote:
> I may be thinking too hard or not enough, but what do you mean by managed
> services?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
> > Hello,
> >
> > Does anyone know of any companies offering managed type services for SQL
> > Server?
> >
> > Thanks in advance.
> > Any help would be greatly appreciated.
>
>|||You are wanting someone else to maintain your SQL Server install, or do you
just want hosted SQL Server access?
If you are just looking to outsource the management of your SQL Server admin
tasks...contact me offline :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:E48FC353-E46A-4223-8A0E-F958B20544CF@.microsoft.com...
> 1. Managed Hosting
> 2. Managed Monitoring
> "Kevin3NF" wrote:
>> I may be thinking too hard or not enough, but what do you mean by managed
>> services?
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your
>> questions
>>
>> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> news:656FD08C-A27A-448A-AAFD-BFE4180D3610@.microsoft.com...
>> > Hello,
>> >
>> > Does anyone know of any companies offering managed type services for
>> > SQL
>> > Server?
>> >
>> > Thanks in advance.
>> > Any help would be greatly appreciated.
>>
Managed replacement for SQLDMO?
Thanks,
Rainer.
In SQL Server 2005 SMO(Sql management object) replaces DMO(data management object). I don't use DMO because DMO uses the system tables in the Master database which is Microsoft property. Microsoft make changes with service packs that can affect DMO based code. Try the link below for Microsoft provided tutorial on SMO. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_ovyukondev.asp
|||Thanks. Smo was the magic word ;). I found here a good article that solves my problem:http://www.yukonxml.com/articles/smo/Rainer.
Managed Providers, Is it possible to use one not listed?
OLEDB that it is really using managed providers. The managed provider for
ODBC, for OLEDB, for SQL Server and for Oracle. So even though it says OLEDB
provider for Oracle it is really the managed provider. And even though it
says OLEDB Provider for SQL Server it is really the dotnet managed provider
for SQL Server.
First, is what I said correct?
Second. I have the dotnet managed provider for Sybase. I would like to use
this. Is it possible to use additional managed providers with RS? If so, how
do I do this? Thanks,
Bruce L-C#1:
What matters for the ReportServer is only the contents of the RDL file. If
the RDL says:
<ConnectionProperties>
<DataProvider>ORACLE</DataProvider>
<ConnectString>data source=server</ConnectString>
</ConnectionProperties>
it will use the managed Oracle provider.
This provider is registered in the config files (designer, server) as:
<Extension Name="ORACLE"
Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
er,Microsoft.ReportingServices.DataExtensions" />
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior!
However the DATA view in Report Designer is different for the visual
designer:
* the visual query designer with 4 panes will internally always use OleDB
providers for verifying and executing queries directly in "Data" view. (Main
reason: the visual query designer does not work with managed providers).
Example: if you choose "Oracle" in the data source dialog, the Data view has
to use the OleDB provider for Oracle behind the scenes, but Preview and
Server will use the managed Oracle provider.
* the generic text-based query designer (2 panes) will _always_ use the data
provider you specified.
#2:
Yes, in general you can use managed third party data providers. I'm not
familiar with the managed Sybase provider, but here is how you would
register the Oracle ODP.NET provider in RSReportServer.config:
<Extension Name="ODP"
Type="Oracle.DataAccess.Client.OracleConnection,Oracle.DataAccess"/>
See also:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
When using a managed provider, you can either use it directly (by just
registering it correctly in the config file as shown above) or write a
custom data extension which internally uses the provider. If the managed
Sybase provider behaves similarly as the MS managed providers, then you will
be able to use it in both, Report Designer and Report Server.
However, if it is implemented similar to the Oracle ODP.NET provider, you
can only use it directly within the report server right now, but it won't
work with report designer to design queries. The reason for this is related
to the way the ODP.NET provider is implemented by Oracle (and the way it
modifies database connection properties after the connection is opened). We
will try to avoid this issue by a code change for our SP2, so it should then
be possible to use ODP.NET in Report Designer also.
Would be very interesting to know if the managed Sybase provider works in
both, designer and server. At least it will work with the server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23wAdnlQaEHA.752@.TK2MSFTNGP09.phx.gbl...
> As I understand it, when setting up the data provider, even though it says
> OLEDB that it is really using managed providers. The managed provider for
> ODBC, for OLEDB, for SQL Server and for Oracle. So even though it says
OLEDB
> provider for Oracle it is really the managed provider. And even though it
> says OLEDB Provider for SQL Server it is really the dotnet managed
provider
> for SQL Server.
> First, is what I said correct?
> Second. I have the dotnet managed provider for Sybase. I would like to use
> this. Is it possible to use additional managed providers with RS? If so,
how
> do I do this? Thanks,
> Bruce L-C
>|||Thanks for the detailed reply. I'll let you know how I fare.
Bruce L-C
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:Omp0wbRaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> #1:
> What matters for the ReportServer is only the contents of the RDL file. If
> the RDL says:
> <ConnectionProperties>
> <DataProvider>ORACLE</DataProvider>
> <ConnectString>data source=server</ConnectString>
> </ConnectionProperties>
> it will use the managed Oracle provider.
> This provider is registered in the config files (designer, server) as:
> <Extension Name="ORACLE"
>
Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
> er,Microsoft.ReportingServices.DataExtensions" />
> Note: the behavior of PREVIEW in Report Designer is identical to the
> ReportServer behavior!
> However the DATA view in Report Designer is different for the visual
> designer:
> * the visual query designer with 4 panes will internally always use OleDB
> providers for verifying and executing queries directly in "Data" view.
(Main
> reason: the visual query designer does not work with managed providers).
> Example: if you choose "Oracle" in the data source dialog, the Data view
has
> to use the OleDB provider for Oracle behind the scenes, but Preview and
> Server will use the managed Oracle provider.
> * the generic text-based query designer (2 panes) will _always_ use the
data
> provider you specified.
>
> #2:
> Yes, in general you can use managed third party data providers. I'm not
> familiar with the managed Sybase provider, but here is how you would
> register the Oracle ODP.NET provider in RSReportServer.config:
> <Extension Name="ODP"
> Type="Oracle.DataAccess.Client.OracleConnection,Oracle.DataAccess"/>
> See also:
>
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
> When using a managed provider, you can either use it directly (by just
> registering it correctly in the config file as shown above) or write a
> custom data extension which internally uses the provider. If the managed
> Sybase provider behaves similarly as the MS managed providers, then you
will
> be able to use it in both, Report Designer and Report Server.
> However, if it is implemented similar to the Oracle ODP.NET provider, you
> can only use it directly within the report server right now, but it won't
> work with report designer to design queries. The reason for this is
related
> to the way the ODP.NET provider is implemented by Oracle (and the way it
> modifies database connection properties after the connection is opened).
We
> will try to avoid this issue by a code change for our SP2, so it should
then
> be possible to use ODP.NET in Report Designer also.
> Would be very interesting to know if the managed Sybase provider works in
> both, designer and server. At least it will work with the server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23wAdnlQaEHA.752@.TK2MSFTNGP09.phx.gbl...
> > As I understand it, when setting up the data provider, even though it
says
> > OLEDB that it is really using managed providers. The managed provider
for
> > ODBC, for OLEDB, for SQL Server and for Oracle. So even though it says
> OLEDB
> > provider for Oracle it is really the managed provider. And even though
it
> > says OLEDB Provider for SQL Server it is really the dotnet managed
> provider
> > for SQL Server.
> >
> > First, is what I said correct?
> >
> > Second. I have the dotnet managed provider for Sybase. I would like to
use
> > this. Is it possible to use additional managed providers with RS? If so,
> how
> > do I do this? Thanks,
> >
> > Bruce L-C
> >
> >
>sql
Managed Procedure to automate archiving files in a database
I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?
Hi,
You might want to have a look atJobsin sql server. You are able setup jobs to run at set intervals (in your case, midnight).
With moving archived files into a different directory u can consider usingxp_cmdshell
eg. EXECxp_cmdshell 'copy c:\test.txt d:\archived\text.txt --this is equivelent to running this in command prompt.
If you dont like this idea then consider writing aWindows Service.
managed objects
I'm doing some development work with Visual Studio 2005 and SQLServer 2000. My SQL DB is running on a Windows 2000 Server box in the office, and I'm doing the development on my XPPro workstation. Now I've been trying to connect to the Win2000 box though VS and although I can see the server and the DB when I hit ok I get this error
"The SQL server specified by these connection propertise does not support managed objects"
What the heck does that mean?
any help would be great :-)Well when you are using VS2005 it assumes you are connecting to SQL Server 2005, quick fix I think is to install .NET framework 2.0 in the Win2k server and make sure IIS is running even if you are not using it. Hope this helps.
Managed index in Fuzzy Lookup Error
If we run the sam package after selecting the option for "manage index" it gives error:
Error: 0xC0202009 at Data Flow Task, Composite Lookup [15209]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInstall':
OK this becasue there is a known issue in SQL server 2005 June CTP. Its not there in Sep CTPManaged index in Fuzzy Lookup Error
If we run the sam package after selecting the option for "manage index" it gives error:
Error: 0xC0202009 at Data Flow Task, Composite Lookup [15209]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInstall':
OK this becasue there is a known issue in SQL server 2005 June CTP. Its not there in Sep CTPManaged Identity Ranges in Merge Replication
Over the weekend I was taking advantage of system down time and made some
changes to my merge replication database.
The changes were all successful until this morning I get users who are
getting duplicate key error messages. I have verified that the duplicate
key is causing the error.
The remote locations have all been assigned there identity ranges, but it
appears that some cross-over has occurred from the previous ranges and
values. I thought SQL would look in the range assigned for the next
available number and use that. However, it appears that each subscriber is
using the next incremental number within their respective range. That is
the problem and I don't know what to do next.....Suggestions?
WB
What changes did you make to your merge replication database?
"WB" <none> wrote in message news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Over the weekend I was taking advantage of system down time and made some
> changes to my merge replication database.
> The changes were all successful until this morning I get users who are
> getting duplicate key error messages. I have verified that the duplicate
> key is causing the error.
> The remote locations have all been assigned there identity ranges, but it
> appears that some cross-over has occurred from the previous ranges and
> values. I thought SQL would look in the range assigned for the next
> available number and use that. However, it appears that each subscriber
is
> using the next incremental number within their respective range. That is
> the problem and I don't know what to do next.....Suggestions?
> WB
>
|||I added a new column to one table and changed the PK on the same table.
other changes included changing the field length on a few columns and
creating a new table
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:e9YK$79MFHA.2680@.TK2MSFTNGP09.phx.gbl...
> What changes did you make to your merge replication database?
>
> "WB" <none> wrote in message
news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
some[vbcol=seagreen]
duplicate[vbcol=seagreen]
it[vbcol=seagreen]
> is
is
>
sql
Managed Disks in Windows 2003
logically partiotioned into P and Q appear as one disk group.
If i wanted to setup an active/active SQL cluster, how can i seperate into 2
groups, so that one node owns P and SQL instance 1 has data files on P and
the other node owns Q with another SQL instance owning Q. I didnt see a
managed disk dialog during the 2003 windows cluster instance.
Consult with your extenal array vendor documentation/support. It may be that your array cannot present virtual disks (like many high-end SANs can). This may mean that you need disk sets made up of different physical disks to present 2 "disks" to your cl
uster. eg physical disks 1, 2+3 are drive P, while physical disks 4 and 5 are drive Q. Don't forget that you'll also need a separate disk for the Quorum. So, in your configuration you'll need to have AT LEAST 3 separate disks to present to your cluster
. What sort of external array are you using?
|||MSCS looks at the physical layer, not the partition level. So you can have 4
partitions on a single disk, but MSCS will display this as one physical disk
resource. The only way to achieve an active/active configuration would be to
add another physical disk...can't be done with your current config.
Regards,
John.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e$W6OIPFEHA.2768@.tk2msftngp13.phx.gbl...
> While setting up windows cluster in 2003, my external array which is
> logically partiotioned into P and Q appear as one disk group.
> If i wanted to setup an active/active SQL cluster, how can i seperate into
2
> groups, so that one node owns P and SQL instance 1 has data files on P and
> the other node owns Q with another SQL instance owning Q. I didnt see a
> managed disk dialog during the 2003 windows cluster instance.
>
>
Managed code memory issues
There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"
http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx
Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?
Hi,
Assuming you are interested in SQL Server's hosting of the CLR you can find a few (not many) SQLCLR-specific counters in both Profiler & Performance Monitor....CLR is "asking" SQL Server for resources....see below BOL excerpts:
The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.
Profiler Trace Events
SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see Monitoring Events.
Assembly Load Event Class
Used to monitor assembly load requests (success and failures).
SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class
Provides information about Transact-SQL batches that have started or completed.
SPtarting Event Class, SP:Completed Event Class
Used to monitor the execution of Transact-SQL stored procedures.
SQLtmtStarting Event Class, SQL
tmtCompleted Event Class
Used to monitor the execution of CLR and Transact-SQL routines.
Performance Counters
SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using SQL Server Objects.
SQL Server, CLR Object
Total time spent in CLR execution.
Managed code memory issues
There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"
http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx
Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?
Hi,
Assuming you are interested in SQL Server's hosting of the CLR you can find a few (not many) SQLCLR-specific counters in both Profiler & Performance Monitor....CLR is "asking" SQL Server for resources....see below BOL excerpts:
The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.
Profiler Trace Events
SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see Monitoring Events.
Assembly Load Event Class
Used to monitor assembly load requests (success and failures).
SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class
Provides information about Transact-SQL batches that have started or completed.
SPtarting Event Class, SP:Completed Event Class
Used to monitor the execution of Transact-SQL stored procedures.
SQLtmtStarting Event Class, SQL
tmtCompleted Event Class
Used to monitor the execution of CLR and Transact-SQL routines.
Performance Counters
SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using SQL Server Objects.
SQL Server, CLR Object
Total time spent in CLR execution.
Managed C++ User-defined Aggregate Function
I'm attempting to write an aggregate function in C++ to compare performance with the equivalent function in C#.
However, I'm having problems getting SQL Server to see the function in the assembly. It allows me to load the assembly into the database, but I can't see the type in it.
Here's my code:
// CPPTest.h
#pragma once
using namespace System;
using namespace Microsoft::SqlServer::Server;
using namespace System::Data::SqlTypes;
using namespace System::Data::SqlClient;
namespace CPPTest {
[Serializable]
[Microsoft::SqlServer::Server::SqlUserDefinedAggregate(
Format::Native,
Name="AGG_CPP_OR")]
public ref struct AGG_CPP_OR
{
public:
void Init();
void Accumulate(SqlInt32 Value);
void Merge(AGG_CPP_OR^ Group);
SqlInt32 Terminate();
private:
SqlInt32 m_accum;
};
}
// CPPTest.cpp
#include "stdafx.h"
#include "CPPTest.h"
void CPPTest::AGG_CPP_OR::Init()
{
m_accum = 0;
}
void CPPTest::AGG_CPP_OR::Accumulate(SqlInt32 Value)
{
m_accum = m_accum | Value;
}
void CPPTest::AGG_CPP_OR::Merge(CPPTest::AGG_CPP_OR^ Group)
{
m_accum = m_accum | Group->m_accum;
}
SqlInt32 CPPTest::AGG_CPP_OR::Terminate()
{
return m_accum;
}
Compile it with /clr:safe option and it can be loaded as an assembly into SQL Server 2005 (9.0.1399), but the AGG_CPP_OR type is not seen as an aggregate function. I've also tried implementing IBinarySerialize and setting Format to Format::UserDefined (and putting in MaxByteSize) but it makes no difference.
Does anyone know what I'm missing here?
Many thanks,OK, take it out of the CPPTest namespace and it can be added as an aggregate function with
CREATE AGGREGATE BITWISE_OR(@.input int)
RETURNS int
EXTERNAL NAME [CPPTest].[AGG_CPP_OR];
GO
Now it's a dependancy issue stopping it from running, but I'll keep on it.
Thanks,
Manage with SQL Server Management Studio Express
I have read that SQL Server Compact Edition can be managed within SQL Server Management Studio Express.
Can anyone show me how to do it?
Or I can manage the Compact Edition in other way instead of in VS 2005.
Thanks a lot,
JD
SQL Server Management Studio Express SP2 will allow you to manage SQL Compact Edition (despite the statement on the download page). It can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en
Monday, March 26, 2012
Making managed code calls inside SQL Server in context of the client
Dear all,
I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.
I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.
However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.
In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.
Is this possible? If so, could someone please refer me to an example of how to do it.
If it can be done, it opens up lots of very cool possibilities!
Thanks.
It's a much cleaner design if you can explicitly pass the client context information to the server-side CLR code rather than RPC back to the client. While you can do anything you want if you register the assembly as unsafe, I don't recommend that approach. It's better for both security and performance reasons to keep server-side processing on the server itself as much as possible.
|||
Dear BonnieFe,
Thanks for your suggestion. It would be a much cleaner design, if it was possible. Unfortunately, the data I need back changes for every row in the returned data, since it is obtained by passing a value from a returned data column to a method which has to be called in the context of the calling process.
I have now solved the problem by calling a web service in the calling web site from the CLR code inside SQL Server 2005. This seems to work fine, although it is bound to be slower than it would be if it used RPC back through the SQL connection.