Showing posts with label code. Show all posts

Tuesday, March 20, 2012

Cluster and User Defined Functions

Has anyone had difficulty with UDF's in a clustered environment? We have
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Monday, March 19, 2012

Clue - Who dun it? The Data Adapter, DataSet, SqlSelect etc...

Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:

"SQL Server does not exist or access denied. "

I'm using the 1.1 framework and my hosting company provides MSSql 2005.

Thanks for your time,

Charlie

Here's the code in my cond behind file.

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace blog

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Data.SqlClient.SqlConnection sqlConnect;

protected System.Web.UI.WebControls.DataList dList;

protected System.Data.SqlClient.SqlDataAdapter DA;

protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;

protected System.Web.UI.WebControls.LinkButton linkAdmin;

protected System.Web.UI.WebControls.Image header;

protected blog.DS ds1;

private void Page_Load(object sender, System.EventArgs e)

{

DA.Fill(ds1, "blogEntry");

dList.DataBind();

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.sqlConnect = new System.Data.SqlClient.SqlConnection();

this.DA = new System.Data.SqlClient.SqlDataAdapter();

this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();

this.ds1 = new blog.DS();

((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit();

this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click);

// sqlConnection String

//////////////////webserver////////////////

this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";

//////////////////local///////////////////

//this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" +

//"ist security info=False;initial catalog=blog";

// DA

this.DA.SelectCommand = this.sqlSelectCommand1;

this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {

new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {

new System.Data.Common.DataColumnMapping("bodyID", "bodyID"),

new System.Data.Common.DataColumnMapping("linkID", "linkID")})});

// sqlSelectCommand1

this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC";

this.sqlSelectCommand1.Connection = this.sqlConnect;

// ds1

this.ds1.DataSetName = "DS";

this.ds1.Locale = new System.Globalization.CultureInfo("en-US");

this.Load += new System.EventHandler(this.Page_Load);

((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();

}

#endregion

private void linkAdmin_Click(object sender, System.EventArgs e)

{

Server.Transfer("logOn.aspx",true);

}

fatthippo:
Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:
"SQL Server does not exist or access denied. "

If you are receiving that error, it generally means that your connection string is NOT good. Check out this KB article:Potential causes of the "SQL Server does not exist or access denied" error message.

|||

That string was pasted in from another page on the same host. It works fine there. Why should I not assume that it's good?

|||

Hello,

Something I just noticed in the stack trace.

  blog.WebForm1.Page_Load(Object sender, EventArgs e)in c:\inetpub\wwwroot\blog\index.aspx.cs:31

 this is still referencing my local machine. How can I change this?

|||

Hi,

From your code, I can see your connection string is:

Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass

Please check if your server address is correct. Is the SQL server you're connecting to a default instance or a named instance. If a named instance, please try to use Server=xxx.xxx.xxx.xxx\InstanceName

also, please check the login name and password. You can try to create a .udl file for testing. Here are the steps:

1. Create a Text file.
2. Rename it to .udl
3. Double click on the file, and test in the Data Link Properties window.

HTH.

|||

I believe I found the issue. This being my first attempt to host a .net web app on a ISP, it didn't occur to me that modifying the connection string in the provided online text editor wouldn't be sufficient. Simply put, I needed to recompile my app.

Thank-you all for your support.

Fatthippo

Sunday, March 11, 2012

CLR Trigger Error: Could not find Type xxxx in Assembly

I've got the following code (C# code) but when I execute the Create Trigger
statement I get the error
"Could not find Type 'InheritanceTriggers' in assembly 'CLRTriggers'
I believe I'm satisfying all the rules: the class is public, the method is
public, and the method is static.
Anythoughts?
Create Trigger EnforceInheritanceTrigger on [Person]
For INSERT as External Name CLRTriggers.InheritanceTriggers.EnforceBaseObjec
t
GO
CLRTriggers.dll code below
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace My.SQLServer
{
public class InheritanceTriggers
{
[SqlTrigger(Name = @."EnforceInheritanceTrigger", Target =
"[dbo].[Person]", Event = "FOR INSERT")]
public static void EnforceBaseObject()
{
SqlTriggerContext context = SqlContext.TriggerContext;
String SQLForInserted = "SELECT * FROM INSERTED;";
string ATableName = "dbo.Person";
if (context.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection connection
= new SqlConnection(@."context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(SQLForInserted,
connection);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
System.Guid id = (System.Guid)reader[0];
string s = <sql statement is here>;
SqlCommand command1 = new SqlCommand(s, connection);
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(s);
}
reader.Close();
}
}
}
}
}
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.InheritanceTriggers.EnforceBaseObject
You need to include your namespace:
Create Trigger EnforceInheritanceTrigger on [Person]
For INSERT as External Name
CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
Steven|||Unfortunately, that still didn't help:
Could not find Type 'My.SQLServer.InheritanceTriggers' in assembly
'CLRTriggers'.
but thanks for the suggestion...at least it gave me something new to try. :
)
"Steven Hemingray [MSFT]" wrote:

>
> You need to include your namespace:
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
> Steven
>
>|||I beg your pardon! I got my namespaces mixed up and you are absolutely
correct! Thank you Steve H! (I'll bet you were saying to yourself, "this
guy's nuts", I know that's the problem) :)
"Steven Hemingray [MSFT]" wrote:

>
> You need to include your namespace:
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
> Steven
>
>

CLR Stored Procedure Not Visible

I am trying to do some time series analysis on data that I have. I've read that the best way to do this is to use CLR to write the manipulation code in .Net and then load the assembly into SQL rather than extracting the data, manipulating it and putting it back again.

I'm using crystal reports with visual studio 2003 to do my reporting. The problem I am facing is that the CLR stored procedures I create using SQLExpress are not visible from my SQL explorer built into Visual Studio 2003. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.

I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?

Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?

Any help would be greatly appreciated.

Try posting this question on the CLR forums: .NET Framework inside SQL Server.

Thanks
Laurentiu

|||

Mic,

Did you find an answer to the padlock icon next to the CLR stored procedures.

I am using sql server 2005, and trying to access them in reporting services.

I must be missing something.

I tried setting permissions to no avail.

Thanks

Tim

CLR Stored Procedure Not Visible

I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?

Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?

Any help would be greatly appreciated.

Try posting this question on the CLR forums: .NET Framework inside SQL Server.

Thanks
Laurentiu

|||

Mic,

Did you find an answer to the padlock icon next to the CLR stored procedures.

I am using sql server 2005, and trying to access them in reporting services.

I must be missing something.

I tried setting permissions to no avail.

Thanks

Tim

CLR stored procedure crashing SQL Server

I had the following erroneous code in a SQL Server stored C# procedure:

class P

{

private DateTime? e;

public P(

DateTime? e)

{

this.e= e;

}

public DateTime? E

{

get

{

return E; // correction return e;

}

set

{

E= value; // correction e = value;

}

Calling the getter of E of the class P creates an infinite number of method calls. This causes the .NET stack overflow. This sometimes caused our SQL crash. Here's the log:

29.3.2007 9:46:08 A fatal error occurred in .NET Framework runtime. The server is shutting down.

29.3.2007 9:46:10 Microsoft SQL Server 2005 - 9.00.2153.00 (X64)

May 9 2006 13:58:37

In my opinion an error in SQL server CLR stored procedure must not be able to crash the whole SQL Server, as it seems to do. Could someone verify this?

What makes finding problems like this problematic is that the only error is like ".NET stack overflow". No pointer to where in the code the error occured. It took hours for me to find the problem

Hi JM_F,

This type of coding error certainly should not cause a shutdown in SQL Server, as SQL should be resiliant to stack overflow issues within the CLR. When I run your repro on my machine (x86) I get the expected behavior:

Msg 6538, Level 16, State 49, Procedure sp, Line 0

.NET Framework execution was aborted because of stack overflow.

It's likely that you are hitting a bug in the CLR. Can you describe your specific repro scenario that causes SQL to crash? You mention that SQL only crashes sometimes when you call this SP, do you know of anything that is different between when it causes SQL to crash, and when it does not?

Can you file this as a bug on connect.microsoft.com with any extra information you have? This will make it easier to investigate. If there is a dump file located in your MSSQL\LOG directory, please include that in the bug or email them to me directly at stevehem at microsoft dot com.

Thanks,

Steven

|||

Thank you for your quick reply.

We have two SQL servers installed on two physical servers.

A: a 64-bit Windows server. SQL Server 2005 SP1. The production server.

B: a 64-bit Windows server. SQL Server 2005 SP1. The test server.

The problem in the C# code causes the error message you sent when I run it on B. I've never managed to reproduce the crash on B. Because A is our production server, I can't try to reproduce the problem there anymore. Yesterday I run the erroneuos code maybe ten of times in A and it crashed two times (that I can see from the server log).

I'll send next week a message to connect.microsoft.com with more detailed information that I don't want to put here in the discussion forum.

I don't know whether this is related to the CLR problem, but yesterday evening and today morning the SQL server A got somehow jammed. For example, we couldn't open Management Studio and open Programmability/Stored procedures. It just didn't open. Also the CLR stored procedures just didn't work, they never seemed to finish. We had to restart the SQL server A (I guess this was done by starting and stopping the windows service, don't know for sure, because I didn't do this). Now we have a new problem with registering CLR stored procedures - both of the following commands produce an error:

CREATE PROCEDURE [dbo].prA AS EXTERNAL NAME a.xxxxxx;

drop ASSEMBLY a;

Msg 6512, Level 16, State 27, Procedure prLoadOptionContracts, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.

I guess we have to restart the server again?

|||

This is interesting - this error means that the SQL failed to bind to the CLR host on your server. This is usually due to a bad installation of either the CLR or SqlServer, but in very rare cases it can be a result of memory pressure on the machine when the CLR tries to load. So, if after the server restarts the CLR is able to load, that means it was a memory pressure problem, but if it persists then it is an installation one. The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in. Is your server running under WOW?

I'm really not sure if these problems are related. You're the only customer I'm aware of that has hit either of them (a search engine check for both of your error messages returns this thread only). So either you are extremely unlucky or there is some underlying cause for the failures.

Steven

|||

Steven Hemingray - MSFT wrote:
The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in.

dm_clr_properties actually gives information:

select * from sys.dm_clr_properties

name value

CLR stored procedure crashing SQL Server

I had the following erroneous code in a SQL Server stored C# procedure:

class P

{

private DateTime? e;

public P(

DateTime? e)

{

this.e= e;

}

public DateTime? E

{

get

{

return E; // correction return e;

}

set

{

E= value; // correction e = value;

}

Calling the getter of E of the class P creates an infinite number of method calls. This causes the .NET stack overflow. This sometimes caused our SQL crash. Here's the log:

29.3.2007 9:46:08 A fatal error occurred in .NET Framework runtime. The server is shutting down.

29.3.2007 9:46:10 Microsoft SQL Server 2005 - 9.00.2153.00 (X64)

May 9 2006 13:58:37

In my opinion an error in SQL server CLR stored procedure must not be able to crash the whole SQL Server, as it seems to do. Could someone verify this?

What makes finding problems like this problematic is that the only error is like ".NET stack overflow". No pointer to where in the code the error occured. It took hours for me to find the problem

Hi JM_F,

Msg 6538, Level 16, State 49, Procedure sp, Line 0

.NET Framework execution was aborted because of stack overflow.

Thanks,

Steven

|||

Thank you for your quick reply.

We have two SQL servers installed on two physical servers.

A: a 64-bit Windows server. SQL Server 2005 SP1. The production server.

B: a 64-bit Windows server. SQL Server 2005 SP1. The test server.

I'll send next week a message to connect.microsoft.com with more detailed information that I don't want to put here in the discussion forum.

CREATE PROCEDURE [dbo].prA AS EXTERNAL NAME a.xxxxxx;

drop ASSEMBLY a;

Msg 6512, Level 16, State 27, Procedure prLoadOptionContracts, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.

I guess we have to restart the server again?

|||

Steven

|||

Steven Hemingray - MSFT wrote:
The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in.

dm_clr_properties actually gives information:

select * from sys.dm_clr_properties

name value

CLR Stored procedure

Can I create a CLR stored procedure in VB or C# to print a file. Please give the code sample

All you would need to do it create a SQL Server project, add a stored procedure class, write your printing code inside there, and then deploy it to the database.
You probably shouldn't be doing this though. Any CLR stored procedures or functions should abide by the same principles as the database, which is to not rely on any resources which may not be there. Communicating with a printer is prone to this type of problem as the network may be down, or the printer may be off or out of paper etc. Therefore it shouldn't really be a stored procedure.

Thursday, March 8, 2012

CLR Procedure error with third party .dll

I have a CLR procedure with the below code calling a third party .dll in the system32 directory of the SQL server:

using System;

using System.Collections;

using System.Data;

using System.Data.SqlTypes;

using System.Runtime.InteropServices;

using System.Text;

using Microsoft.SqlServer.Server;

public class CorrectAddressProcedures

{

[DllImport(@."C:\WINDOWS\system32\CorrectA.dll", EntryPoint = "FindCityCounty")]

public static extern int FindCityCounty(StringBuilder zip, StringBuilder cityname, StringBuilder state, StringBuilder countyname, StringBuilder countynum);

public static String Space(int len)

{

StringBuilder str = new StringBuilder("");

str.Append(' ', len);

return str.ToString();

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spFindCityCounty(SqlString _zip, out SqlString _cityname, out SqlString _state, out SqlString _countyname, out SqlString _countynum)

{

int rc;

StringBuilder zip = new StringBuilder(5);

StringBuilder cityname = new StringBuilder(28);

StringBuilder state = new StringBuilder(2);

StringBuilder countyname = new StringBuilder(25);

StringBuilder countynum = new StringBuilder(3);

try

{

cityname.Append(Space(28));

state.Append(Space(2));

countyname.Append(Space(25));

countynum.Append(Space(3));

zip.Append(_zip);

rc = FindCityCounty(zip, cityname, state, countyname, countynum);

_cityname = cityname.ToString();

_state = state.ToString();

_countyname = countyname.ToString();

_countynum = countynum.ToString();

}

catch (Exception ex)

{

throw (ex);

}

I am getting the error

Msg 6522, Level 16, State 1, Procedure spFindCityCounty, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spFindCityCounty':

System.DllNotFoundException: Unable to load DLL 'C:\WINDOWS\system32\CorrectA.dll': Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)

System.DllNotFoundException:

at CorrectAddressProcedures.spFindCityCounty(SqlString _zip, SqlString& _cityname, SqlString& _state, SqlString& _countyname, SqlString& _countynum)

I have restarted the service with memory allocation settings -g512 and -g1024 but still receive the error. The SQL server is an Intel Xeon 2.8GHZ with 2GB of RAM. Any thoughts of how to solve this would be greatly appreciated.

thanks,
Whitney

I failed to mention above that the .dll in question is a rather large one. 154MB to be exact.|||

hi,

how much memory space have you reserved for your SQL 2005 server.

in sql200 Management Studio:

Server Properties\Memory.

Try to enlarge your maximum memory or reduce the minimum memory to zero.

Restart the SQL Server and try again.

regards,

Martin

|||Checking SSMS I'm assuming the defaults were used. The minimum is set to 0MB and maximum is 2147483647MB. I have a startup parameter of 512MB and have also tried 1024MB with no luck.|||

Our vendor sent us a smaller DLL today (98MB vs 154MB) and the CLR procedures now work without code changes. Does anyone know if there is an undocumented ceiling (say around 100MB) that DLLImport has within the SQL CLR?

While I'm happy my code works I would still like to know the deeper detail on why I was getting the error.

Thanks,
Whitney

ClR Function errror cant get to lcoal file system

My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?

Thanks
AdminAnupHow are they connecting to SQL Server ? If you are using SQL Server authenitcation, they will use the SQL Server account permissions to access the files, if this one does not have any permissions, they will get a Access denied. The same situation if you are using Windows authentication and the individual users do not have access to the files. Did you try to run the function individually ? Which error do you get there if you try to let it run at a user context (and not probably one with administrative priviledges :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||it is using sql authentication.

Can you let me know which permission does sql authentication need to execute

and also if i change it to be windows does it need any admin privileges|||If you are using SQL Server Authentication, the SQL Server service account SQL Server is running with, will need to have rights on the accessed files / shares. If you are using Windows authentication, the logged in User wil need to have those rights.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||Has the CLR assembly been granted EXTERNAL_ACCESS?|||Yes that was it as it was in Safe mode
Thanks

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.Krishna
Hi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John
|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

Wednesday, March 7, 2012

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.
> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR and SQL 2005 Compact Edition

I believe that there is a also a new SQL database for the PDA. Am I right? Is it also possible to use (CF) .Net code inside this edition?

Yes. It's called SQL Server 2005 Mobile Edition. This works on Windows Mobile devices (PocketPC and SmartPhone). You need to install .NETCF 2.0 (compact framework) on the device for using SQL Mobile.

You can develop mobile applications using Visual Studio for Devices.

SQL Mobile is part of SQL Server 2005 or Visual Studio 2005.

You can get more information here:

The SQL Mobile page on MSDN : http://msdn.microsoft.com/mobility/sqlmobile/default.aspx

SQL Mobile page from SQL Server home: http://www.microsoft.com/sql/editions/sqlmobile/default.mspx

News groups: microsoft.public.sqlserver.ce. and

Msdn forums: You will find SQL Mobile under SQL Server: http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1

|||But can you also create UDF en UDT with .Net like the desktop editions?|||SQL Mobile does not support UDT or UDF. Also the CLR is not inside SQL Mobile database. The programming model is different.

Simply stated:
SQL Mobile components is a bunch of dlls that are linked in your device application project. So the database routines are embedded in your application code.

You declare some variables, structures
Application logic - pre data processing
query, modify, delete data from SQL Mobile
Application logic - post data processing

Closing all connections

How do I set up a job so that I close all open database connections? I think we have a leak in our code which causes our DB to go down (max connections used) roughly once every month, so we just restart the SQL server. Until we can find the exact problem I'd like to do this.

For simplicities sake let's say my database name is just "test."

Thank you.I've seen 2 ways to do this:

1) an ALTER DATABASE command, setting the database into single user mode and kicking out all connections

ALTER DATABASE Test
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

followed by a

ALTER DATABASE Test
SET MULTI_USER

2) a stored procedure which loops through a cursor of all connections and KILLs them. For example code, look here:Kill all the Active Connections to Your Database.

Terri|||Thank you for your help. Sorry for the immensely delayed response. Things have been far beyond hectic lately.

As far as method 2, do you know how to check how many connections are active? I wanna know if killing it actually DID close the connections.|||I believe you can use sp_who to see this information, and sp_who2 to see more detail.

Terri|||Thank you very much tmorton.

Closed

thx but this code not needed anymore

sry

Please be a little more clear. I can't help, if i don't understand. Smile

Saturday, February 25, 2012

Cloning a table

In my .NET code, with SQL Server 2000 as the backend, I need to create an
exact copy, without data, of an existing table in the same database, that
the user selects from a combo box. Any ideas on how to do this?You can create a copy without constraints or indexes using SELECT ... INTO.
Specify a WHERE clause to exclude data too:

SELECT * INTO MyNewTable FROM MyTable WHERE 1 = 0

Note that the user will need CREATE TABLE permissions. This isn't the kind
of thing one usually does in database applications though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michael Jackson" <michaeldjackson@.cox.net> wrote in message
news:%MsXd.6532$Wy.1516@.okepread02...
> In my .NET code, with SQL Server 2000 as the backend, I need to create an
> exact copy, without data, of an existing table in the same database, that
> the user selects from a combo box. Any ideas on how to do this?

Client-side programming patterns/idioms for SQL Mobile 2005?

Hello,

Where can I get sample code, or a pointer to sample code demonstrating common programming pattern(s) for a client of SQL Mobile 2005 (where the client is also running on the device)?

For eg, with ADO.NET, I'd do something like:

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = conn.CreateCommand();
conn.CommandText = “--...my query…”;
SqlDataReader reader = conn.ExecuteReader();
// loop and process the data

What are the corresponding APIs and calls that I’d use for a SQL Mobile 2005 application using the latest CF.Net 2.0 ADO.Net APIs (or whatever they’re called)? I could only find a couple of articles on MSDN, both applying to SQLCE 2.0 and not SQL Mobile:

[1]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road05222002.asp
[2]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/ppcdatabase.asp

If this is documented in Books Online, how can I get to it? I couldn’t find it.

Thanks in advance!

-Ravi

The samples are in books on line of SQL Mobile and .NET CF 2
http://www.microsoft.com/sql/editions/sqlmobile/overview.mspx
http://msdn.microsoft.com/mobility/sqlmobile/default.aspx|||Navigating from the above links to get to the information I need is extremely cumbersome. For now, a search on MSDN2 led me to the following page, on which the link [Using Programming Interfaces] is exactly what I was looking for:

http://msdn2.microsoft.com/en-us/library/ms172961

Thanks,
ravi|||

For the benefit of others who may have the same question, I thought I would post some sample code that demonstrates a client connection programming pattern for SQL Mobile clients:

string dbFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "/data.sdf";
string connectionString = string.Format("Data Source = {0};", dbFilePath);
using (SqlCeConnection conn = new SqlCeConnection(connectionString)) {
try {
/* open the connection, get a command */
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = @."CREATE TABLE myTable(i int primary key)";
cmd.ExecuteNonQuery();
}
catch (Exception e) {
string logMessage = e.Message;
/* logging code goes here */
}
finally {
conn.Close();
}
} // end using

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

|||

Ravi Subramanian wrote:

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

Can you actually connect to a SQL Mobile db on another device? I thought it always had to be on the same device as your app.
Can anyone confirm this?|||SQL Mobile Client app and the DB should be on the same device except in the following scenarios:
1) SQL Server 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device
2) VS 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Sunday, February 19, 2012

Client Side Printing SQL Report

Can anyone explain and maybe post some code on how to print a SQL Report in asp .net - I'm using SQL Reporting sp2 and I just can't find anything that works out there. Your help would be greatly appreciated... I tried RSClientprint.dll and had no luck...

thanks
rich

We're using "OfficeWriter" by a company called SoftArtisans. Note, it's not cheap.|||

I see - I'm looking for the ability to print the report only no need for all the other features - Thanks for your reply...

rich

|||

rperreta wrote:
Can anyone explain and maybe post some code on how to print a SQL Report in asp .net - I'm using SQL Reporting sp2 and I just can't find anything that works out there. Your help would be greatly appreciated... I tried RSClientprint.dll and had no luck...
thanks
rich

Why aren't you using the exporting features from RS? The client can preview and then export to the available formats, with the capability to save them AND print them.|||You can try Windows Script Host for clientside object creation and printing. It is Free by Microsoft.
http://msdn.microsoft.com/library/en-us/script56/html/wsconswitchingnetworkedprintersonoff.asp?frame=true
Regards,
Hemchand|||Hello!
I may not understand on "How To Print a Report in SQL Report in Asp.Net" but in the latest Service Pack 2 for Reporting Services there was an Active X control that displays an Icon on the toolbar that provides for direct printing of a Reporting Services report directly to any printer on your network.
From MS www site:
Key Functional EnhancementsReports can now be printed directly from within Internet Explorer. A Microsoft ActiveX control is provided to support a rich client-side printing experience including full page preview.|||

hi,

i would like to check where do u find the RSClientprint.dll? i try search my pc for the dll file but invalid.

if any1 can tell mi where to find it..greatly appreciated

your regards

angela

|||Hello:
There is a CAB file RSCLIENTPRINT in
(at least on my server) c:\Program files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin
I just then moved the dll's to the bin directory within the same directory structure above.
Best Reagrds,
|||hi,
i know of the active x print that will call the print frm RSClientPrint but the main issue that i concern is how can i not display the print dialog box where by it prompt the user to select the printer and print. Because i want to print the report automatically once i evoke a print button on my .net application. is there any ways to do it?
cheers Big Smile [:D]

Angela|||

hooligannes97 wrote:
rperreta wrote:
Can anyone explain and maybe post some code on how to print a SQL Report in asp .net - I'm using SQL Reporting sp2 and I just can't find anything that works out there. Your help would be greatly appreciated... I tried RSClientprint.dll and had no luck...
thanks
rich

Why aren't you using the exporting features from RS? The client can preview and then export to the available formats, with the capability to save them AND print them.

RS? Microsoft Reporting Services?|||

rperreta wrote:

RS? Microsoft Reporting Services?

Correct

Tuesday, February 14, 2012

Client found response content type of , but expected text/xml.

Hello, I have this code, and it just doesnt work,I am using rs 2005.

privatevoid cargarReporteFromWS()

{

cargarFormatos();

//Format format = (Format)ddlFormats.SelectedValue;

string encoding;string mimeType;ParameterValue[] parametersUsed;Warning[] warnings;string[] streamIds;

rs =

newReportingService();

rs.Credentials = System.Net.

CredentialCache.DefaultCredentials;

rs.Url =

"http://agamenon:90/reportserver/ReportingServices.asmx";byte[] data;string path ="/GescomRpts/RPT_MediosDesarrollo";

data = rs.Render(path, ddlFormats.SelectedValue,

null,null,null,null,null,out encoding,out mimeType,out parametersUsed,out warnings,out streamIds);FuncionesRS fs =newFuncionesRS() ;string extension = fs.GetExtension(mimeType);string filename = path +"." + extension;

Response.Clear();

Response.ContentType=mimeType;

if(mimeType!="text/html")

Response.AddHeader(

"Content-disposition","attachment; filename="+filename);

Response.BinaryWrite(data);

}

Server Error in '/GescomDllo' Application.

Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.

Source Error:

Line 1704: [return: System.Xml.Serialization.XmlElementAttribute("Result", DataType="base64Binary")]Line 1705: public byte[] Render(string Report, string Format, string HistoryID, string DeviceInfo, ParameterValue[] Parameters, DataSourceCredentials[] Credentials, string ShowHideToggle, out string Encoding, out string MimeType, out ParameterValue[] ParametersUsed, out Warning[] Warnings, out string[] StreamIds) {Line 1706: object[] results = this.Invoke("Render", new object[] {Line 1707: Report,Line 1708: Format,

Source File:c:\WINNT\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\gescomdllo\27bee1ac\cdebe31b\App_WebReferences.4i0hyhkt.0.cs Line:1706

Stack Trace:

[InvalidOperationException: Client found response content type of '', but expected 'text/xml'.The request failed with an empty response.] System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +533395 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +204 RSWebService.ReportingService.Render(String Report, String Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings, String[]& StreamIds) in c:\WINNT\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\gescomdllo\27bee1ac\cdebe31b\App_WebReferences.4i0hyhkt.0.cs:1706 Protected_01_Administradores_rptmediosdesarrollo.cargarReporteFromWS() in c:\Inetpub\wwwroot\GescomDllo\Protected\01_Administradores\rptmediosdesarrollo.aspx.cs:44 Protected_01_Administradores_rptmediosdesarrollo.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\GescomDllo\Protected\01_Administradores\rptmediosdesarrollo.aspx.cs:23 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

http://spaces.msn.com/levalenciam

What exactly are you trying to do with this code?

Tuesday, March 20, 2012

Cluster and User Defined Functions

Monday, March 19, 2012

Clue - Who dun it? The Data Adapter, DataSet, SqlSelect etc...

Sunday, March 11, 2012

CLR Trigger Error: Could not find Type xxxx in Assembly

CLR Stored Procedure Not Visible

CLR Stored Procedure Not Visible

CLR stored procedure crashing SQL Server

CLR stored procedure crashing SQL Server

CLR Stored procedure

Thursday, March 8, 2012

CLR Procedure error with third party .dll

ClR Function errror cant get to lcoal file system

CLR Configuration option problem

Wednesday, March 7, 2012

CLR Configuration option problem

CLR Configuration option problem

CLR and SQL 2005 Compact Edition

Closing all connections

Closed

Saturday, February 25, 2012

Cloning a table

Client-side programming patterns/idioms for SQL Mobile 2005?

Sunday, February 19, 2012

Client Side Printing SQL Report

Tuesday, February 14, 2012

Client found response content type of , but expected text/xml.

Hello, I have this code, and it just doesnt work,I am using rs 2005.

Server Error in '/GescomDllo' Application.

Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.

Clearing LOG Files

Blog Archive

About Me