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


ADVERTISEMENT

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,


Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?

May 2, 2007

If anyone could confirm...

SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.

E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.

First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.

However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.

Question: I thought the filters would remain dynamic and be applied on each sync?

I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!

Question: I wonder why if parent records are supplied, why not child records?

If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...

Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?

View 4 Replies View Related

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

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

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

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

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

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

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

Cannot Generate SSPI Context

Mar 12, 2008

I'm signing into a sqlexpress 2005 local server using windows integration and it fails with a "Cannot generate SSPI context". After 2 years of working with this, it just started to happen after I installed a new piece of software to test. My colleague thinks it hosed our domain names. I've read all the articles and most of them have to do with a non-local server. I also read that this is caused with TCP/IP so I disabled it for sqlexpress and tried again. Then I get "An error has occurred while establishing a connection to the server. When connecting to the sql server 2005, this failure may be caused by the fact that under the default settings sql server does not allow remote connections." I know I have remote enabled so that isn't true. If it uses VIA, "Login failed for user '(null)'. Not a trusted sql connection" or something like that. Anyway, I think something is hosed in my domain and my colleague fixed it by downloading some xp tool that he can't remember where it came from. Any chance anyone else knows about this tool?

View 2 Replies View Related

Transaction Context In Use By Another Session.

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

Indexed Views And Context

Apr 30, 2007

Hi guys ,
we had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?


If yes how can I implement .Because data depends on context is there any way to create global indexed views.


In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

View 2 Replies View Related

Package Performance Context

Jan 17, 2008

One question I haven't found to date is concerning context. First of all my setup is importing 250 fox tables into sql 2005. I have one main package with 4 child packages. These child packages have in turn on average 3 packages which in turn contain several tables each. This setup is due to resource limitation. Currently it takes about 12 hours, mainly due to my transformations, table size and resource limitations.

Anyway, my question is if i simply changed the defaultbuffermaxrows from default 10,000 in one of my tables to say 100,000, then executing this table import ad-hoc to analyse import time would not be a true reflection of package performance?

What I mean is if I analyse this table stand alone and due to change above time is reduced by say 1 minute, i might think great. But overall, I reckon increasing defaultbuffermaxrows to 100,000 will probably reduce resource allocation to other tables in package and thus could have a detriment to package, so the only way to accurately compare package performance is to make changes at table level and the run main package? - which of course taking 12 hours is not very practical......

Thanks in advance

View 3 Replies View Related

Has_perms_by_name Database Context

Feb 6, 2008

In reading Has_Perms_By_Name documentation in BOL, it appears that using a three-part name will make no assumption on the database context. However, when I run the code below, I get results that are database context sensitive.




Code Snippet

USE [master];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')
print ''
USE [msdb];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')
print ''
USE [AdventureWorks];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')




The results are:


1

1

1



1

1

1



0

0

0

I created a login called 'temp' and added a user for this login in the AdventureWords database. I then added a securable for sys.sysusers for this user in the Adventureworks database. I then selected the "Deny" checkbox for the Select permission. I connected as temp and ran the T-SQL above.

It appears that the database name is being ignored when included in the name of the securable. What am I missing? Is this a bug?


My version: Standard SQL Server 2005 9.0.3054 on Windows 2003 R2 SP2 (3790).

Thanks,
Randy

View 1 Replies View Related

Security Context Could Not Be Retrieved

Mar 23, 2006

some time back i had problem with sending the service broker messages on remote machine,

I had some security issues and they were resolved by the help of Remusu.

since the ip of the remote machine was changed in between so i just re-executed the same scrips which used to successfully send messages on the remote machine.(I just updated the IP in the route )

To my surprise same script did not work now where as no change have been made.

I am doing the following:

1.Created the certificate and end point on the sender side. back up the certificate in a file and copied to the other machine.
same step was repeated for receiving side as well.

2.both the side i created the certificates using the back up files from other sidend proper authorization

3.Then I created the database,route,messagetypes,contract,queues,services etc both the side.

4. then i setup dialog security(ie.created the dialog security certificates both the sides and back up them). Later I copied these back up files to each other, create some dialog user and create certificate using authorization to these remote dialog users created.

5.I also created remote service binding on both the sides and granted send permission to the remote dialog user.

When i send the message from sender to the receiver, and run the profiler, I see that on the sending side none of the broker event gives any error.

In the recever side I get the followng Event:
Broker:Message Undeliverable

This message could not be delivered because the security context could not be retrieved.
Error 11229.


I m surprised that the same script was run in the same order,Why was it running before and not now.

I also checked the End points using telnet and they seem to be fine. Also the firewall was "Off" on both the machines i.e. there was no change in system state also. 

Please provide the solution. Thanks in advance.

View 12 Replies View Related







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