How To Set ConnectionTimeout In Context Connection

Dec 18, 2007



I have a CLR stored procedure written in C#. It use context connection to connect into the SQL Server database. Since the stored procedure is handling a large amount of records, it cost a long time to complete the process. And I will got exception that indicate the connection timeout.

So can I set the value of ConnectionTimeout more large and how to set it?



Thanks!

View 3 Replies


ADVERTISEMENT

Context Connection Transaction

Feb 24, 2006

Hello Guys,

I need some some clarifications on how Context connections and transactions inter operate in CLR.

The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.

SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);

I guess my ambiguity comes from the fact that the Transaction is not specifically specified.

In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.

Thank you,

Lubomir

View 1 Replies View Related

SqlBulkCopy And Context Connection = T

Oct 6, 2006

I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?

Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean

Dim success As Boolean = False

emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf

Dim insertStr As String = "INSERT INTO " & _tblName & "("

Dim values As String = ") Values("

Dim drow As DataRow = Nothing

Dim dCol As DataColumn = Nothing

'add the column names

For Each dCol In _dt.Columns

insertStr = insertStr & dCol.ColumnName.ToString & ", "

values = values & "@" & dCol.ColumnName.ToString & ", "

Next

'remove the last comma & form the final string

insertStr = insertStr.Substring(0, insertStr.Length - 2)

values = values.Substring(0, values.Length - 2)

insertStr = insertStr & values & ")"



Dim connStr As String = "context connection = true"

Dim conn As New SqlConnection(connStr)

Dim cmd As SqlCommand = Nothing



Using conn

Try

conn.Open()

For Each drow In _dt.Rows

cmd = New SqlCommand(insertStr, conn)

For Each dCol In _dt.Columns

cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))

Next

SqlContext.Pipe.ExecuteAndSend(cmd)

Next

success = True

Catch ex As Exception

emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf

success = False

Finally

Try

conn.Close()

conn.Dispose()

Catch ex As Exception

success = False

End Try

End Try

End Using

Return success

End Function



View 4 Replies View Related

@@IDENTITY Vs. SCOPE_IDENTITY() On CLR Context Connection

Aug 1, 2006

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @@IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection) Try NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @@IDENTITY", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)


Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @@IDENTITY would be the better option.

Thankful in advance for advice.

View 5 Replies View Related

Context Connection And MultipleActiveResultSets ...Can Have Both At The Same Time?

Jul 15, 2006

Hi,

When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:

System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.

Can we have MARS in the "context connection"?

note: I'm doing this to support multiple open datareaders in a CLR stored procedure.

Thanks!

Andy

View 5 Replies View Related

Context Connection And Command Timeout

Feb 6, 2008

Hello,

I'm using a context connect inside a CLR stored procedure. The CommandTimeout property has no functionality when used with a context connection. See remarks in this url:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

This is a problem for me because I'm at times call a link server that has been known to wait indefinitely. I've tried using a regular conneciton that supports command timeouts within my CLR stored procedure but I get a permissions error (Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0,0, Culture=neutral failed.)

How can I prevent my CLR stored procedure from waiting indefinitely?

Thanks.

View 2 Replies View Related

SQL 2012 :: Passing Through User Context On A Connection

Mar 3, 2014

I would like to know if we can have a generic "customer database account" to connect to the database (from a PHP layer) and then pass through the current web logged in user name for auditing purposes.

In the oracle world I would be using the setClientIdentifier function on any connection returned from a pool, but I cannot see anything obvious (to me) on the APIs to support this.

Our backup plan, because we only access table data through stored procedures, is to extend the API to have the username passed through - but this is a little ugly and less than transparent.

View 6 Replies View Related

Connection Error: Cannot Generate SSPI Context

Nov 11, 2007

hi,
While trying to connect to SQLSever 2000 or SQLSever 2005 from a vb.net application ,
a user is getting the error "Cannot generate SSPI context" while other users
can still connect to the server.
and moreover, this user were able to connect before.
and if he restarts his computer, then try to connect after a lap of time, it will connect.
but if he waits and opens other applications,and then tries to open the application,it will not work.

so how to resolve this problem?

Regards,

View 3 Replies View Related

CLR With Thread Pooling Using Context Connection=true

Mar 15, 2008

Hi

I have a DLL deployed in SQL2005 which uses Thread pool. Since DLL exists in DB we can directly access it using context connection = true.

In my case the following works fine, if i execute the same out side Threadpool

SqlCommand cmd = new SqlCommand("select 1...", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);


When i execute inside threadpool, either by passing opened connection object, or tried to open connection to "context connection = true" i am getting following error.


System.InvalidOperationException: The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code. at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at hiqidty4.hiqidty4.Identify_Search(Object identifyin)


Let me know if i am missing any more here


Thanks in advance

View 1 Replies View Related

Recovery :: Connection Failed Cannot Generate SSPI Context

Oct 26, 2015

I have 2 servers. On one server i install sql server 2014 and its working fine, i am able to get connected with sql with domain account.

From second machine i want to connect my sql server, for that i create udl file when i give sql server address and click refresh getting following error: Connection failed cannot generate SSPI context

Firewall is off, created fire rule too, i am able to get ping first machine by ip and servername but still getting issue.

View 3 Replies View Related

Cannont Create SSPI Context (SQL Connection Problem After 30 Minutes)

Dec 3, 2005

It makes no difference if I'm working with Enterprise Manager, QueryAnalyzer, Access, self written app with OleDb or Visual studio 2003builtin DB manager.Everything is fine but aufter some time - about 30 minutes I getmessage "cannot create SSPI context"After rebooting my machine I can work for another few minutes.Configuration:NB01 is my notebook with Windows XP ProSQL01 is another Windows XP Pro machine with SQL Server 2000 desktopDC01 is the domain controllerall machins within the same domain DOMAIN01The user is a domain admin and has every rightI'v seen some postings for this problem but not one useful idea how toresolve.As it runs perfectly for some time I don't thint it is a DNS problem orsomething like this.As there is no useful error message it must be some kind of bug.Has anybody resolved such a problem?

View 3 Replies View Related

3709-The Connection Cannot Be Used To Perform This Operation. It Is Either Closed Or Invalid In This Context

Feb 10, 2008


We have a nagging issue here in our application side, which I was trying to troubleshoot and reach no where. Can I have your valuable inputs for resolving/understanding the root cause of the issue?

3709-The connection cannot be used to perform this operation. It is either closed or invalid in this context

This error is not coming regularly (twice it happened and after the program is running successfully with out any problem). Problem having exe is running every 2 minutes interval.

Most of the sites saying this is something related to code written in the application but if that is the case why this error is not happening continuously? The problem having exe is running past 4 months with 2 minutes interval and suddenly thrown this error.

I found one MS site describing about this error but not able to find any fixes for this issue (http://support.microsoft.com/kb/839428). We are on the process of upgrading the operating system with SP2; will this help us to resolve this issue? Please advice.

Details

1. Windows 2003 with SP1
2. MDAC 2.8
3. SQL 2005 with SP1
4. VB Application.

View 1 Replies View Related

Database Context

Apr 29, 2003

Hi,

I am trying to create a block of sql statements that will read the sysdatabases table, and store the value of the database in a variable.

I want to use a cursor to step through all these databases (excep of course the control databases).

For each of the databases I want to run a backup log followed by dbcc shrinkfile. For dbcc shrinkfile, I need to change the database context.

What I am finding is that I cannot use the variable to hold the database name.

here is the code

-------------------------

SET NOCOUNT ON
DECLARE @db_name varchar(100)
DECLARE @db_filename varchar(100)

DECLARE list_dbs Cursor
FOR select [name] from master..sysdatabases

OPEN list_dbs

FETCH NEXT FROM list_dbs into @db_name

WHILE @@FETCH_STATUS = 0
BEGIN

select name = @db_filename from @db_name..sysfiles

backup log @db_name with no_log

use @db_name

dbcc shrinkfile (@db_filename, 25)


FETCH NEXT FROM list_dbs into @db_name

END

CLOSE list_dbs
DEALLOCATE list_dbs
-----------------------

I get a syntax error where ever the @db_name is used (on the select and on the use).

Any suggestions?

Thanks,

Jim

View 7 Replies View Related

Dynamic Sql Context ...

Apr 11, 2008

Hi,
I need to build up dynamically cursor and fetch variables in a script. I use exec(@sql) to declare the cursor and my hope was I could use the same method for doing the fetch. Unfortunately I run into a

Must declare the scalar variable "@c1".

Here's the part of the code:
... snippet

DECLARE @c1 varchar(max)
DECLARE @c2 char(3)
DECLARE @c3 char(3)
DECLARE @c4 char(3)
DECLARE @c5 char(3)
DECLARE @c6 char(3)
DECLARE @c7 char(3)
DECLARE @c8 char(3)
DECLARE @c9 char(3)
DECLARE @c10 char(3)
DECLARE @c11 char(3)
DECLARE @c12 char(3)
DECLARE @c13 char(3)
DECLARE @c14 char(3)
DECLARE @c15 char(3)
DECLARE @c16 char(3)
DECLARE @c17 char(3)
DECLARE @c18 char(3)
DECLARE @c19 char(3)
DECLARE @c20 char(3)
DECLARE @c21 char(3)
DECLARE @c22 char(3)
DECLARE @c23 char(3)
DECLARE @c24 char(3)
DECLARE @sql VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @fetch varchar(max)


SET @sql ='DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET(' +
@apo+@int_provider_name+@apo+ ',' +
@apo+@int_provider_string+@apo + ',' +
@apo+@int_query_string+@apo +
') FOR READ ONLY'
PRINT @sql
EXEC(@SQL)

OPEN c1


set @fetch = 'FETCH c1 INTO '+@int_fetch
print @fetch
EXEC (@fetch)

... snippet

Output:
DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:work2007schenker.xls','SELECT * FROM [Datei$]') FOR READ ONLY

FETCH c1 INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@c1".

How can I declare the dynamically built 'fetch into' variables (@c1 etc.) so they are in the context of dynamic sql ?

Thanks: Peter

View 1 Replies View Related

Not Permitted In This Context

Jul 19, 2006

Hi,I am having a SQL Server 2005 problem with my Insert statement. I amsending a command via my website to my database. It comes up with anerror I'll put below. The code is here:"INSERT INTO tblUsers (userName) VALUES ( userNameTest)"This is the error I get:The name "userNameTest" is not permitted in this context. Validexpressions are constants, constant expressions, and (in some contexts)variables. Column names are not permitted.Now, userName is a varchar field in the database. What is wrong?Kivak

View 2 Replies View Related

Context Search

Jul 6, 2006

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

View 6 Replies View Related

Context Switch

Sep 28, 2007


There are some concept about context switch block me .

---executor: dbo
create user u1 without login


go

create table t

(

col int

)

go


create proc proc1

with execute as 'u1'
as

insert into t values(1)


go

exec proc1

I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and
I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'

To my surprise, the "exec p1" command could run successfully..

Why?

View 12 Replies View Related

Trigger Security Context

Oct 5, 2006

I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.

View 5 Replies View Related

Cannot Generate SSPI Context

Sep 21, 2004

I was trying out the Building an End-to-End Application (VB.Net) exercises on Web Matrix Guided Tour and encountered the mentioned errors. Please help. Thanks.

The code is as follows :

<%@ Page Language="VB" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)
Welcome.Text = "Hello, " + User.Identity.Name
If Not Page.IsPostBack Then

' Databind the master grid on the first request only
' (viewstate will restore these values on subsequent postbacks).

MasterGrid.SelectedIndex = 0
BindMasterGrid()
BindDetailGrid()

End If

End Sub

Sub MasterGrid_Select(Sender As Object, E As EventArgs)
BindDetailGrid()
End Sub

Sub MasterGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

If MasterGrid.SelectedIndex <> -1 Then

' unset the selection, details view
MasterGrid.SelectedIndex = -1
BindDetailGrid()

End If

MasterGrid.CurrentPageIndex = e.NewPageIndex
BindMasterGrid()

End Sub

Sub BindMasterGrid()

' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"

Dim CommandText As String = "select OrderID, OrderDate, CustomerName from Orders"

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

MasterGrid.DataSource = ds
MasterGrid.DataBind()

End Sub

Sub BindDetailGrid()

' get the filter value from the master Grid's DataKeys collection
If MasterGrid.SelectedIndex <> -1 Then

' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"

' TODO: update the CommandText value for your application
Dim filterValue As String = CStr(MasterGrid.DataKeys(MasterGrid.SelectedIndex)).Replace("'", "''")
Dim CommandText As String = "select OrderDetailID, ProductName, Quantity, UnitPrice from OrderDetails where OrderID = '" & filterValue & "'"

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)

myConnection.Open()

DetailsGrid.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

End If

DetailsGrid.DataBind()

End Sub

</script>


And this is the compilation error :

Cannot generate SSPI context.
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.Data.SqlClient.SqlException: Cannot generate SSPI context.

Source Error:


Line 49:
Line 50: Dim ds As New DataSet()
Line 51: myCommand.Fill(ds)
Line 52:
Line 53: MasterGrid.DataSource = ds


Source File: C:WorksWebWISHDefault.aspx Line: 51

Stack Trace:


[SqlException: Cannot generate SSPI context.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +311
System.Data.SqlClient.SqlConnection.Open() +383
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.Default_aspx.BindMasterGrid() in C:WorksWebWISHDefault.aspx:51
ASP.Default_aspx.Page_Load(Object Sender, EventArgs E) in C:WorksWebWISHDefault.aspx:14
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731

View 1 Replies View Related

Changing Database Context

Mar 15, 2001

Hi all,

I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:

Error 5701
Severity Level 10
Message Text:
Changed database context to '%.*ls'.

However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).

Any ideas why this would start happening?

View 1 Replies View Related

Current Context From A Trigger

Oct 16, 1998

I am writing a trigger to audit changes to certain columns. In the trigger I
would like to record to the "Audit" table as much information about the
current execution context as possible (current user, login, nt user,
inputbuffer, etc.). I couldn`t find the way to find out what is the
currently executed* stored procedure. @@PROCID returns object id for the
trigger itself. Any help would be highly appreciated as this is an urgent
production environment issue.

regards,

Anatol

View 1 Replies View Related

SQLServeragent Security Context Does Not Have....

Feb 2, 2004

Hi,

We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.

"SQLServeragent security context does not have server-autorestart privileges"

Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?

Ted

View 1 Replies View Related

How To Get Maximum Value Of Active PDP Context

Feb 4, 2014

I have two questions about date formatting in MS SQL 2008.

1. I have column name called DATETIME which have the value like this: 2013-11-12 05:00 what function should I have to use to get only 05:00?

2. I have some rows as below picture:

how can I get the maximum value of "Active PDP Context" and the Time column becomes only 2014-02-03 00:00:00.000?

View 2 Replies View Related

Cannot Generate SSPI Context

Feb 5, 2007

All of a sudden I cannot connect to sql server from Visual Studio. Nor I can't conenct to Sql server through IIS and it gives me the same error saying "Login failed: Cannot Generate SSPI Context". Lots of my development projects got stuck because of the SSPI Issue. I know there was a network upgrade from Windows NT (users) to Active directory. I can only access the sql server from the host machine.if someone can guide or give me a quick run down on things to look for that would be wonderful.

View 2 Replies View Related

Subqueries Are Not Allowed In This Context. Only S

Feb 20, 2007

Um, still trying to transpose MySQL into T-SQL.

Inserting info into a table, where one of the columns meets a certain criteria.

insert into employee (/*emp_id,*/ fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (/*null,*/ 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));

But I'm getting this error:

Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Any help would be greatly appreciated.

View 2 Replies View Related

Execution Context For SSIS

Jul 9, 2007

I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.

The package needs to be executed on a regular basis.

I have given RO access for a regular user on production DB, he is executing the package from his client desktop.

I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.

I understand from the above experience that there is an “execution context� for SSIS execution. Can someone tell me how can I define the execution context for SSIS?


------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Cannot Generate SSPI Context

Jul 23, 2005

HiMy company uses a Windows 2000 server with MS SQL Server 2000.We have many clients with administrative software that use the MS SQLserver, the problem is that we experimented randomly the error"Cannot Generate SSPI Context" and the only way to work with DB is logout the client.I'm looking the MS Technical sheet but there's no resolving hints thatcan help.Anyone has a similar problem ?Thanks.

View 1 Replies View Related

Cannot Generate SSPI Context

Feb 6, 2007

Win Server 2003
SQL Server 2000 SP4
SharePoint Server

Win Server 2000 (Domain Controller / Active Drectory)

Clients: Win XP / Access XP

I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.

The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials

we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.

Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?

View 1 Replies View Related

Cannot Change DB Context In Cursor

Jun 18, 2006

Hi There

I am trying to loop through databases to gather information.

However if i loop though datbases in a cursor, by using a select from sysdatabases, once i am in the cursor and SET @Sql = 'use ' + DBName and EXEC(SQL), the database context never changes from the one the cursor is executing in.

Is there a way to loop through database contexts and execute sql ?

Thanx

View 3 Replies View Related

DTC - Transaction Context In Use By Another Session

May 5, 2006

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

View 6 Replies View Related

Cannot Generate SSPI Context

Jul 27, 2006

I have a SQL Server 2000 in a specific W2K server, that belongs to a domain that is administered by a W2003 server.

There is a client that has a new computer with WXP in the same domain. It used to have an old machine with W2K pro. and with the same user it worked OK.

When accesing SQL from the client with an Office2003 application via ODBC with windows authentication, after several minutes working OK, suddenly it gets this message:

Conection error. SQL state: S1000

Cannot generate SSPI context.

In the client I have executed cliconfg.exe and created a piped name protocol, but still it won't work.

View 6 Replies View Related

C++ Assemblies And Security Context

Mar 23, 2008

I have two questions.



How can I run my C++ assembly in safe mode?

I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:



Code Snippet

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."






Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?



Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)



---8<----



My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?



I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).

Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?



If you have any points to C++ sample code, then please forward this to me.

View 4 Replies View Related

Cannot Generate Sspi Context

Jul 3, 2007

Howdy,

Firstly, I have read through so much information on this, that my eyes are starting to bleed and I am right at the end of my rope with it all..



This issue has only started showing since I re-installed my dev box, so my server has not changed at all..



Server is 2003 with SQL 2005.



Dev box is

Vista (Ultimate)

Visual Studio Pro 2005..



A quick but painful solution is for me to log out of my box and then back in, but this is unworkable at times... about the only difference I can think of this install from the last is that now I have also installed SQLExpress on the dev box. I have gone into services and made sure that the service does not start auto. I have connection to the server and am running as administrator on both until such time as I can sort this out, but the error still occurs.



Authentication for the dbase is both windows and credential.



Can someone please give me a stratight out solution for this.



Cheers



View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved