I have a SQL Server project in Visual Studio 2005 which deploys an assembly to SQL Server 2005 containing various stored procedures user defined functions. Is there any way to tell Visual Studio to drop/create the stored procedures in a schema other than dbo?
ie: User.ChangePassword instead of dbo.ChangePassword.
Hi there, Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code. How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?
Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?
Our current service throws on-demand .DTSX and now we'd like that will throw old ETL 2000 too.
I'd like to know if success or not success when calling dtspkg.dll from my managed code (vb). Is it possible or not? I only capture errors for the sake of TRY..END TRY but I don't know how the hell to know if the dts execution was successful. Execute method not returns anything.
I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed.. It did not stop at the break point within the sql sp. I did granted execute permission for sp_sdidebug. Do I need to attach any process?
Is there anything left off by the article? I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found". Reason can be Create and Insert statement gets executed as 2 different sessions. How to get the above requirement work fine? Thank you. HV
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
All -- Please help. I am using managed code in SQL Server 2005 and have a question about stored procedure name qualification. With a VS.NET 2005 Pro Database project, when I use >Build, >DeploySolution, it works great but my stored procedures are named with qualification using my domainusername.ProcName convention, something like this... XYZ_TECHKamoskiM.GetData ...when I want them to be named with the dbo.ProcName convention, something like this... dbo.GetData ...and I cannot see where this can be changed. Do you happen to know? Please advise. (BTW, I have tried going to this setting >Project, >Properties, >Database, >AssemblyOwner, and putting in the value "dbo" but that does not do what I want.) (BTW, as a workaround, I have simply scripted the procedures, so that I can drop them and then add them back with the right names-- but, that is a bit tedious and wasted effort, IMHO.) Thank you. -- Mark Kamoski
1) first i installed Sql Server 2005 Enterprise Edition, everything was working fine except i was not able to create managed objects from BI, like stored procedures, triggers, UDT, etc etc.
2) so i installed VS.net 2005 complete + Sql Server 2005 Express Edition
now i was able to create stored procedures, triggers, etc etc, i'm talking about the CLR objects, ok, i.e. the managed code, but........ i was only able to do this from Sql Server 2005 Express Edition.
whenever i try to create managed objects CLR like stored procedures and stuff from the Sql Server 2005 Enterprise edition it gives me the following error :
"The Sql Server supplied by these connection properties, does not support managed code, please choose a different server"
please help meeeee.. please please, tell me what's the problem ?
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!
I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:
"How to: Create and Run a CLR SQL Server Stored Procedure "
I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:
"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"
It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!
Here's the code as copied directly from the howto:
using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public partial class StoredProcedures { [SqlProcedure()] public static void InsertCurrency_CS( SqlString currencyCode, SqlString name) { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlCommand InsertCurrencyCommand = new SqlCommand();
==SQL Server 2005 SP2==Is it possible to set the default schema in code?I know that, for a particular DB user, I can set the default schemastatically in Mgmt Studio. However, I want to do this dynamicallyin source code.I am using JDBC if that matters.Any help appreciated.TIAaj
Hi Smart Folks, My SQL Server-based app will require generating a few tables at run-time, and, possibly, a new small database. Again, this is at run-time. Any advice on this? Should I build the functionality from the app, having my code generate all of the commands on the db to create what I need, or should I rely on SQL Server functionality (Scripts? SPs?) to do so. I am sure there is trade-off in one appraoch versus the other - I could use a tour of the trade-offs. Thanks all! DF
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Hello people :-)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 :-)
In VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?Thanks,Rainer.
Can anyone explain why when I look at table using enterprise manager, highlight a table, all tasks, maanage indexes why only 1 index appears and when I look at the same table in sysindexes is says that there are 8 indexes. This is the sql code I executed: select object_name(id), indid from sysindexes where object_name(id) = 'tbh_matter_summ'
Is it possible that there is a problem with the database?
I have an MDS entity which will be managed in excel. I do not want the user to enter the Code and Name so I have configured code to auto generate and name to default to another attribute value. I have then hidden code and name in excel.
When I use excel and enter the attribute and publish, the name column is blank in the MDS table.Am I missing something here with the defaulting?The Action is defined as 'Name Defaults to Country'. Where Country is the attribute I am populating.
I spent the last days programming a managed datamining plugin-algorithmn for SSAS 2005 and have troubles with the Prediction-Method.
Die Prediction-Methode of the "Shell-Algorithm" leads to an error-message on execution of the prediction-query (Internal Error: Unexprected Error ... dmxpredict.cpp Row 205 ...).
Can anyone provide me with a very simple implementation of this Method which just says that for every output-attribute the first (discrete) state should be predicted with a probability of, let's say, 90 % ?
SQL Server 2005 gives you a possibility to create managed triggers. In a managed trigger I can create a new thread and process trigger event in various ways. My question is that are there any reasons why I should NOT start new threads in database triggers? The following code shows how I could create new threads. Do you see that this could cause any errors or problems in SQL Server functionality? My goal is to minimize the trigger effects in a database overall performance.
'This handles database updates of AdventureWorks Person.Contact table. <Microsoft.SqlServer.Server.SqlTrigger(Name:="UpdateContact_Trigger", Target:="Person.Contact", Event:="FOR UPDATE")> _ Public Shared Sub UpdateContact_Trigger() 'Notify: SqlContext.Pipe.Send("Trigger FIRED")
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext() 'Ensure that it was update: If triggContext.TriggerAction = TriggerAction.Update Then
'Open a connection: Using conn As New SqlConnection("context connection=true") conn.Open() 'Fetch some information about the update: Using sqlComm As New SqlCommand sqlComm.Connection = conn sqlComm.CommandText = "SELECT ContactID, FirstName, LastName from INSERTED" Using rdr As SqlDataReader = sqlComm.ExecuteReader If rdr.Read Then 'Process the data in a separate thread. The thread could send a message to MSMQ about the change 'or process if differently. The purpose of using separate thread is avoid performance hit 'in the application / database operation. Dim oData As New TriggerData("update", rdr.GetInt32(0).ToString, "contact") Dim trd As New Threading.Thread(New Threading.ThreadStart(AddressOf oData.ProcessEvent)) trd.IsBackground = True trd.Start() End If End Using End Using End Using End If End Sub
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
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.
This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.
Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?
In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?