Setting A Schema Context For A Session
Apr 25, 2006
Is there a way to specify a given Schema as the currently active one for a given database session ? i.e. im looking for somthing like:
Use Schema=<name>
Execute Queries / Stored Procs, etc
.....
I want all the Queries and Stored Procs to execute on the Schema set initially.
I can't use the Default_Schema on the User to do this due to certain restrictions in the way we create users and Schemas.
I know that i can always qualify the objects in the Queries with a Schema Name, but i have a requirement where the Queries need to be generic in nature and need to run on one of many identical schemas in the Database. Is this Possible in Sql Server?
Any help will be greately appreciated.
Thanks
View 3 Replies
ADVERTISEMENT
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
Jun 6, 2006
I'm having a SP1 on server A.
This procedure has a begin trans and a commit. In this procedure i'm inserting data into tables. This data is provided by an extrenal app in staging tables. On the tables i'm inserting data on i'm having triggers that again are starting a SP2. I'm using a cursor on the inserted table to achieve this. The SP2 only insert a record on a table that is on a linked server.
For testing reasons at this moment this is my dev machine too.
The error 'Trans ... ' pops up ....
Any suggestions ..
Kind Regards, Harry
View 5 Replies
View Related
Aug 3, 2006
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
View 1 Replies
View Related
May 12, 2006
I have written a stored procedure that calls a C# class. The sp is called by a trigger on table A. The class has 2 methods. Method 1 is the main call from the stored procedure. Inside it, it sets a connection to the db. Then it calls another private method which has it's own connection and returns a dataset so that I can close that connection by the end of the method. When I execute the sp from within sql management studio, it runs just fine. But when being called from the trigger, I get the "transaction context in use by another session error" error. I read that I needed to wrap my 2nd connection in ...
using (new TransactionScope(TransactionScopeOption.Suppress)){ open conn here, do work, etc.}
I've tried that and I still get the same error from the trigger. Anyone know what else I can try? Thanks.
View 9 Replies
View Related
Feb 29, 2008
Im using SQL Server 2000 sp2 for ASP application on windows server 2003 sp2.
Im getting an error 'Transaction context in user by another session'.
I have not at all used BEGINTRANS / committrans , No Insert / Update statements.
the kind of SQL statements that i was using in that page load is only "SELECT statements"
Can any one give me answer how to get rid of this. I have used only one connection object. and result set as a loop containing few more result sets. But I m sure they are so complex statements to block the SQL server transactions.
Awaiting for reply..................
View 1 Replies
View Related
Oct 30, 2006
I am programming in ASP and SQL server.
I am using this tag <%Transaction=Required%> to do the transaction.
And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...
It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.
I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.
View 2 Replies
View Related
May 12, 2006
I have a c# stored procedure that is being called from a trigger. When I execute it from management studio, it works just fine. But, when I update a record in the table that has the trigger that calls the sp, I get the error "transaction context in use by another session error". I've tried a few of the "fixes" that I found through searching, but so far nothing seems to work. What I've tried so far is...
Removing the transaction from my code
making sure my code is only using 1 connection
setting XACT_ABORT ON
Any other ideas? Thanks.
View 1 Replies
View Related
Nov 3, 2005
I am using sql 2005 beta
View 23 Replies
View Related
Oct 19, 2000
Hi:
I like to know how I may store some user-specific data at the beginning of a session and retrieve the same data later during the session in a SQL command. For those of you familiar with ORACLE, I am trying to mimic the SET_CLIENT_INFO and GET_CLIENT_INFO functions of ORACLE's DBMS_APPLICATION_INFO package on SQL Server.
Any suggestions?
thanks,
Peter
View 1 Replies
View Related
May 28, 2007
hi guys , does anyone of you have a good solution on the Error 1413: Communications to the remote server instance failed before database mirroring was fully started issue?
Best Regards,
Hans
View 1 Replies
View Related
Jan 30, 2008
Hi,
I would like to demonstrate mining temporary models in an ASP.NET application.
Creating, trainning, predicating actions are all witten at C# codes as follows:
Code Snippet
using (AdomdCommand cmd = new AdomdCommand())
{
// Build temporary mining model
cmd.Connection = asConn;
cmd.CommandText = "CREATE SESSION MINING MODEL " + modelName +
" (" +
"HCVS_MemberId Text KEY," +
"HCVS_MeasureDate DATE KEY TIME, " +
"SysPressure LONG CONTINUOUS PREDICT, " +
"DiaPressure LONG CONTINUOUS PREDICT," +
"Pluse LONG CONTINUOUS PREDICT" +
") " +
"USING Microsoft_Time_Series(Missing_Value_Substitution='Mean' ) "; // Periodicity_Hint = '{12}'
cmd.ExecuteNonQuery();
// Train Data
cmd.CommandText = "INSERT INTO " + modelName + " (HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse) " +
"OPENQUERY([Healthcare], " +
" 'SELECT HCVS_MemberId, HCVS_MeasureDate, SysPressure,DiaPressure,Pluse" +
" FROM v_VitalSignForecast WHERE HCVS_MemberId=''" + id + "'' AND HCVS_MeasureDate>=''" + from.ToShortDateString() + "'' AND HCVS_MeasureDate<=''" + to.ToShortDateString() +"'' ')";
cmd.ExecuteNonQuery();
// Predict upon the Train Data. In addition, the standard deviation of each predicated value is retrieved
cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, " +
" SysPressure + PredictStdev(SysPressure) AS [SysPressure_PlusStdev], " +
" SysPressure - PredictStdev(SysPressure) AS [SysPressure_MinusStdev] " +
"FROM PredictTimeSeries(SysPressure, " + fDays + ") AS SysTable " +
") " +
"FROM " + modelName ;
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
DataSet sysDS = new DataSet();
adapter.Fill(sysDS);
The problem is that I do not know how to configure my Analysis Service Server to let ASP.NET account can utilize it. And ASP.NET account in trun impersonates the account who is authorized to use Healthcare DB in the Openquery. Please give a help. Thanks a lot.
Ricky.
View 4 Replies
View Related
Feb 21, 2008
Hi Guys,
I wanted to know how to set the schema for a stored procedure/function. I have some tables, T-SQL stored procedures and fuctions under schema MYSOFT and I call them as follows
MYSOFT.LOOKUP_REC
I have now written some c# SP's and Functions and want to add them to the MYSOFT Schema, how would I do this. I have tried setting the Assemly Owner value to MYSOFT in the project properties but it doesnt seem to work. I can see all my other functions & SP's having the schema name as their post fix in braces
FIND_NAMES (MYSOFT)
but not my c# ones.
When I try to access my C# Function with the schema name MYSOFT I get an error message
'Cannot find either column 'MYSOFT' or the user-defined function or aggregate 'MYSOFT.FIND_NAMES' or the name is ambiguous'
I would appreciate any help
Thanks & Regards
View 4 Replies
View Related
Jan 16, 2008
In my SQL Server 2005, we stored multiple application tables in a single database. All of them is using the dbo schema in the past.
Now, we would like to change this practice and adopt a better one. However, we got some problems in the configuration.
If I want to create such a database, should I first create multiple logins and multiple users for all the application. The mulitple logins are used in the connectionstring of my web. While each login can only have 1 user in a database, I need to create a user for each login as well. (Am I correct in doing so? It seems a bit messy.)
The other problem is what should the database role of all that users? If I don't grant them the db_owner. They can't create the required tables. If I grant it, it voliate the intension of separating different application...
Please help. I am a bit lost on this now. Thanks!
View 1 Replies
View Related
Sep 27, 2007
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!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
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?
View 2 Replies
View Related
May 8, 2007
Hello,
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.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Apr 19, 2006
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
View 3 Replies
View Related
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Sep 20, 2001
Hi all,
I need some solution..
When i execute a program it first deletes all datas from a
'X' table .Then fetches data from various other tables and
inserts into this 'X' table.I finally generate a report from
this 'X' table.
Now the problem is,
USER 1 : Executes the program - Datas in 'X' table got deleted and
filled up with new data.Report is getting processed.....
USER 2 : Executes the same program - Datas in the same 'X' table
getting deleted ..
So the report process of USER 1 got affected.
Solution 1 : Using IP address of local machine i solved the problem.But
i need a concrete solution for this ..something related to sessions..
CAN ANYONE HELP ??
Thanx and Regards
Prabhu
View 2 Replies
View Related
Feb 1, 2006
Does Session term exist in SQL Server?If yes, when it creates, how can monitor it. And where does itsinformation store?Thanks alot
View 1 Replies
View Related
Jun 5, 2008
How do you return the ID of an item to a Session in a ASP forum?
I have a MS-SQL Server 2005 database.
My table looks like this QuoteID, QuoteNumber, Date.
In SQL:
INSERT INTO table (QuoteNumber, Date) values ('123490WC1", '06/2/2008')DECLARE @ItemID INTSET @ItemID=IDENT_CURRENT('table')
What I would like to do is return @ItemID to the ASP forum; I assume Session would be the best.
After I get the @ItemID I have a Grid which I will to insert the Quote Items into.
Thanks,
marly
View 1 Replies
View Related
Jun 16, 2008
Hi, Can somebody tell me what's wrong with this statement or why the Session parameter is not working.
<asp:SqlDataSource ID="datalist_SQL" runat="server"
ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString2 %>"
SelectCommand="SELECT * FROM tbl_subProduct, tbl_material, tbl_product, tbl_subCategory, tbl_topCategory, tbl_topLevelProduct WHERE [tbl_subProduct].[numProductID] = [tbl_product].[numProductID] AND [tbl_product].[numTopLevelProductID] = [tbl_topLevelProduct].[numTopLevelProductID] AND [tbl_topLevelProduct].[numSubCategoryID] = [tbl_subCategory].[numSubCategoryID] AND [tbl_subCategory].[numTopCategoryID] = [tbl_topCategory].[numTopCategoryID] AND [tbl_material].[numMaterialID] = [tbl_product].[numMaterialID] AND @fldArray ">
<SelectParameters>
<asp:SessionParameter Name="fldArray" SessionField="ids_array" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource> Many Thanks
View 10 Replies
View Related
Oct 29, 2004
Hi...
I'm developing a web app with VS 2005 Beta and I do not know how to include a seasson variable value into my code.
This is my code:
Function GetData() As System.Data.SqlClient.SqlDataReader
Dim sqlconn As System.Data.SqlClient.SqlConnection
Dim SqlCommand1 As System.Data.SqlClient.SqlCommand
Dim sqlRdr As System.Data.SqlClient.SqlDataReader
sqlconn = New SqlConnection("SERVER=SRVR;UID=USR;Password=PWD;DATABASE=MyDatabase")
sqlconn.Open()
SqlCommand1 = New SqlCommand("MyStrProc", sqlconn)
SqlCommand1.CommandTimeout = 1800
sqlRdr = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)
Return sqlRdr
End Function
MyStrProc is:
CREATE PROCEDURE MyStrProc
@MyVar int
AS
IF @MyVar IS NOT NULL
BEGIN
.................
......t12.acct_nbr IN (@MyVar)
.....
END
GO
The variable @MyVar get its value from user log info in login page, and create a session variable.
My Question:
How and where to call seasson variable @MyVar, because now I'm getting this error:
Procedure 'MyStrProc' expects parameter '@MyVar', which was not supplied.
Any help will be great.
Tks
View 4 Replies
View Related
Jan 13, 2006
I'm new on ASP.NET 2.0Can anyone tell me how to get data from SQL to Session properties?
In Classic ASP, it looks very simple as below.dim connset conn = server.createobject("adodb.recordset")rssql = "select myid, mypw from mydb where myid = '"& request("myid") &"' and mypw = '"& request("mypw") &"'"set rs = conn.execute(rssql)if rs.eof = true thensession("myid") = rs("myid")session("mypw") = rs("mypw")end if
How to do the same things on ASP.NET?Many thanks!
View 6 Replies
View Related
Sep 1, 2006
How to find user session id in mssql
View 1 Replies
View Related
Sep 16, 2004
Hi:
I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
I ask this because I need a new sesssion with its own transaction.
Thanks,
Rui Ferreira
View 6 Replies
View Related
Jun 22, 2007
Can we use session variable in reporting services.. say go to report -> properties and create a VB function that get ans set session variable.. If yes, how. If not, any workaround?
View 2 Replies
View Related
May 9, 2006
Is there any way in stored procedure to find out a name of terminal session initiated connection to SQL Server?
Thanks for response
View 10 Replies
View Related
Oct 17, 2007
Hi guys,
I would like to manage the following:
BEGIN TRANSACTION;
SELECT * From SomeTable;
INSERT INTO SomeTable (SomeData) VALUES(12);
COMMIT TRANSACTION;
This should be easy usually. However, we need to have each row/command executed individually (using the SqlDataSource object in ASP.NET).
Is there a way how to make the transactions work over multiple executions?
Thanks,
Martin
View 4 Replies
View Related
Jul 11, 2006
Hi
I have designed reports using SQL Server Business Intelligence Development Studio tool (Sql Server reporting service). I have uploaded these reports to report manager.
I am displaying list of reports in tree view control, in my application. I am viewing report in Report Viewer control as per the report selection in tree view control, in same page. I am getting
Server Error in '/Application_name' Application or
ASP.NET session has expired error frequently while switching between various reports. Kindly provide me solution
.
Regards
Sagayaraj Rayappan
View 11 Replies
View Related
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
