Non-deterministic System Function Suser_sname
Dec 8, 2007
Hi,
I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.
It's been a long question but I desperately need the answer. Any thanks will be appreciated.
View 6 Replies
ADVERTISEMENT
Dec 8, 2007
Hi,
I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.
It's been a long question but I desperately need the answer. Any thanks will be appreciated.
View 1 Replies
View Related
Oct 9, 2006
Gentlemen What is "Non_Deterministic" about the function below?
I pass DATETIME Column and a DECIMAL column ti the function. It keeps yelling at me saying it is a non-deterministic function.
I am using this function to PERSIST a Computed Column.
I have tried converting all NVARCHARs to VARCHARs.
Tredi returning a VARCHAR instead of a DATETIME, but still did not succeed.
Am I doing something wrong, I must be.....
CREATE FUNCTION [dbo].[udf_GetDateTime](@Date DATETIME, @TimeDecimal DECIMAL)
RETURNS DATETIME
AS
BEGIN
DECLARE @DateStr NVARCHAR(23)
DECLARE @TimeStr NVARCHAR(12)
DECLARE @DateTimeResult DATETIME
SET @TimeStr = RIGHT('000000' + CONVERT(NVARCHAR(6), @TimeDecimal), 6)
SET @DateStr = CONVERT(NVARCHAR(10), @Date, 120) + ' ' +
SUBSTRING(@TimeStr, 1, 2) + ':' +
SUBSTRING(@TimeStr, 3, 2) + ':' +
SUBSTRING(@TimeStr, 5, 2)
RETURN CONVERT(DATETIME, @DateStr, 120)
END
View 6 Replies
View Related
Oct 13, 2005
which one should I use to record update made by users
suser_sname() or Current_User?
Current_User
System_User
User_Name
Suser_Sname()
View 8 Replies
View Related
Oct 19, 2006
I've just broken out in a bit of a cold sweat on reading a couple of other posts in here...My scenario is that I've developing an intranet only application which will use Windows authentication at the back end, with users allocated into several NT groups (Users, Managers, PowerUsers, Admin etc) and given permissions to the appropriate stored procedures/ views. Windows security will also be used on the front end, so that less privileged users won't be able to see web pages or their menu entries. I'm assuming this is a common scenario and have been blithely developing away without giving much extra consideration to it. However, I'm using SUSER_SNAME() in some of my procs and triggers to record who updated or inserted records. From what I've read, it appears that this function will only return the ASP.NET user, not the Windows NT username of the person under a Windows authenticated ASP.NET application. Is this correct? If so, does all the security have to be scripted in the front end, and given that triggers don't take parameters, how can I supply a trigger with the actual name of the person who made the change.Apologies if I have the wrong end of the stick, I have no way of actually testing the security out at present as I'm working from home, but this is quite alarming, as you can imagine.
View 2 Replies
View Related
Sep 13, 2006
Per 2005 BOL:
Determinism
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is deterministic. For example, if the function calls other functions that are non-deterministic, or if the function calls extended stored procedures, then the Database Engine marks the function as non-deterministic. For common language runtime (CLR) functions, the Database Engine relies on the author of the function to mark the function as deterministic or not using the SqlFunction custom attribute.
Now my question. When wouldn't a function return the same result under these circumstances? When wouldn't any query do this for that matter? What would possibly cause different result sets when the same input parameters are supplied?
TIA, cfr
View 6 Replies
View Related
May 3, 2008
Hi all... This is the definition on the M/S site:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Good... straight forward, right? Ok.... try entering thses command seperately:
Create Table Readings (ReadingDate DateTime Not Null);
Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)
Returns DateTime
As
Begin
return @datReadingDate;
End
Alter table Readings add
[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;
Error on last command returns:
Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.
Can someone please explain this to me? The same value is always being returned.
This does work:
Create Table Readings (ReadingDate DateTime Not Null);
Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;
I obviously want to do more things inside the function, but I can't get by the first step.
Any suggestions?
Thanks!
Forch
View 6 Replies
View Related
Sep 25, 2007
Shows whether the data type of the selected column can be determined with certainty. (Applies only to Microsoft SQL Server 2000 or later.)
This is what Microsoft documentation says for this column property. How I can use his feature for database application development? What is the practical use of this property?
SQL Server 2005.
Thank you,
Smith
View 1 Replies
View Related
May 22, 2008
Hi
I am new to SQL Server and am migrating another database
In my original database I have a default(constant) type field and a calculated field both of which call the same user defined function: GetMyUID()
My Function GetMyUID() returns the current date, time and users initials, i.e. "20080522T09:31:15.250LSG"
When a record is first created both fields have identical values
As the record is updated over time my constant field stays constant and my calculated field reflects the time the record was last updated and the initials of that person. So my first field is called 'Created' and my second is called 'Updated'
I would have thought that something like this would be a pretty bog standard and very straightforward requirement in any database
However in SQL I am getting error messages about the return value being non deterministic
I searched the web and found advice that to sort the problem I need to use WITH SCHEMABINDING in my function definition
Unfortunately I am still getting the same 'non deterministic' error
I wonder if (in the quest to not have an overlong field) by looking up the persons initials from a 'STAFF' file rather than leaving the username in full tacked on to the end that this is causing the problem?
I can't imagine that what I am trying to achieve is rocket science but unfortunately have not been able to find any resource on the web that solves this issue for me
In desperation I turn to you
Please help (preferably by letting me have a few lines of code that return the current date/time followed by the username lookup of a Username's initials, here is a snippet of my code...
RETURN (Convert(VarChar(8),@DateTimeNow,112)+ Right(Convert(VarChar(30),@DateTimeNow,126),13)+dbo.myInitials())
Where the dbo.myInitials() calls:
RETURN (SELECT STAFF.Code from dbo.STAFF where STAFF.Login = dbo.myLogin())
and dbo.myLogin() calls
return UPPER(Right(System_User,PATINDEX('%\%',System_User)))
View 7 Replies
View Related
Feb 13, 2008
Greetings,
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
Rob
View 5 Replies
View Related
Jul 15, 2005
In SQL 2000 for Procedures
View 5 Replies
View Related
Feb 1, 2008
We're building a simple logging facility and would like to systematically determine the name of the current T-SQL object(i.e., the procedure name or function name) to provide this as a logging parameter. For example, in procedure FooBar, I'd like to be able to call a system function that will return 'FooBar', the name of the current object. Does such a feature exist?
Thanks - Dana
View 3 Replies
View Related
Mar 16, 2007
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies
View Related
Mar 4, 2008
I created a clr proc that gets the most recent file within a directory based on the creation time property, see code below. I have attempted to replicate this within a clr scalar valued function in order to assign the resulting value to a variable within SQL server. I am getting the error message:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_fn_recentfile":
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at clr_fn_recentfile.clr_fn_recentfile.clr_fn_recentfile(SqlString Filepath)
After trying to troubleshoot this I am aware that this error is rather generic and have not been able to find any specific documenation regardint the use of file system objects with clr functions. I am at a loss. Any help would be appreciated!
STORED PROC CODE WORKS
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_recentfile
_
Public Shared Sub clr_recentfile(ByVal Filepath As SqlString)
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Return
End If
If fil <> Nothing Then
qry.CommandText = " SELECT '" & fil & "'"
'Execute the query and pass the result set back to SQL
sp.ExecuteAndSend(qry)
sp.Send(qry.CommandText.ToString)
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Sub
End Class
FUNCTION CODE DOES NOT WORK WITH ABOVE ERROR
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_fn_recentfile
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function clr_fn_recentfile(ByVal Filepath As SqlString) As SqlString
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Exit Function
End If
If fil <> Nothing Then
Return fil
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Function
End Class
View 3 Replies
View Related
Apr 4, 2007
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
AdminAnup
View 6 Replies
View Related
Apr 30, 2008
Hi, I am trying to create a maintenance plan on an MSX server and push it out to a TSX server. When I try to run the job on the TSX server it goes into suspended status.
If I look in the SQLAGENT log I see the following error
2008-04-30 15:04:11 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:08:19 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:09:28 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:27:36 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended
2008-04-30 15:35:13 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:50:27 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended
my sql info is
Microsoft SQL Server 2005 - 9.00.3239.00 (X64)
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
any advice would be helpful
View 6 Replies
View Related
Jan 16, 2007
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
View 9 Replies
View Related
Apr 20, 2007
I created a CLR function based on following VB code:
Imports Microsoft.SqlServer.Server
Public Partial Class SqlClrVB
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function GetTotalPhysicalMemory() As Integer
GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory
End Function
End Class
The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:
create assembly totalmem from '!WORKINGDIR! otalmem.dll'
WITH PERMISSION_SET=UNSAFE
go
create function fnGetTotalMem()
returns int
as external name totalmem.SqlClrVB.GetTotalPhysicalMemory
go
When I call this function, it returned following error:
select dbo.fnGetTotalMem()
Msg 6522, Level 16, State 2, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':
System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()
at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()
at SqlClrVB.GetTotalPhysicalMemory()
.
Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.
Thanks much,
Zhiqiang
View 2 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related
Mar 25, 2007
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose
Thanks Steve
View 5 Replies
View Related
Mar 21, 2007
Reporting services is configured to use a custom security extension in the following Environment.
Windows xp
IIS 5.0
when i go to http://servername/reports , the UIlogon.aspx page loads fine and i enter username and password. but i get logon failed.
when i go to http://servername/reportserver , the logon.aspx does not load and i get the following error message :
"Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
any idea ? .
chi
View 6 Replies
View Related
Apr 3, 2007
Ok guys, I am trying to install Sql server 2005 on Vista but I am still stuck with this warning message in the System Configuration Check during Sql server 2K5 installation :
SQL Server Edition Operating System Compatibility (Warning)
Messages
* SQL Server Edition Operating System Compatibility
* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
Now, I know I need to install SP2 but how the hell I am going to install SP2 if Sql server 2005 doesn't install any of the components including Sql server Database services, Analysus services, Reporting integration services( only the workstation component is available for installation)?
Any work around for this issue?
P.S.: I didn't install VS.NET 2005 yet, can this solve the problem?
Thanks.
View 8 Replies
View Related
Jan 29, 2007
Hello!
Hopefully someone can help me.
I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.
Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK
BackupMedium::ReportIoError: read failure on backup device '\XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).
View 1 Replies
View Related
Jul 20, 2005
Hi All,I use Bulk insert to put data to myTable.When the SQL server is in local machin, it works well. But when I putthe data in a sql server situated not locally, then I get a errormessage like this:Could not bulk insert because file 'C:Data2003txtfilesabif_20031130.txt' could not be opened. Operating systemerror code 3(The system cannot find the path specified.).BULK INSERT myTableFROM 'C:Data2003 txtfilesabif_20031130.txt'with (-- codepage = ' + char(39) + 'ACP' + char(39) + ',fieldterminator = ';',rowterminator = '',keepnulls,maxerrors=0)Someone can explan me what the error shows upThanks in advance- Loi -
View 3 Replies
View Related
Jul 5, 2007
Hello,
I am getting the following error from my SqlClr proc. I am using a third party API, which is making call to some webservice.
System.InvalidOperationException: There is an error in XML document (11, 2). ---> System.ArgumentException: Item has already been added. Key in dictionary: 'urn:iControl:Common.ULong64' Key being added: 'urn:iControl:Common.ULong64'
System.ArgumentException:
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
at System.Collections.Hashtable.Add(Object key, Object value)
at System.Xml.Serialization.XmlSerializationReader.AddReadCallback(String name, String ns, Type type, XmlSerializationReadCallback read)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.InitCallbacks()
at System.Xml.Serialization.XmlSerializationReader.ReadReferencingElement(String name, String ns, Boolean elementCanBeType, String& fixupReference)
at System.Xml.Serialization.XmlSerializationReader.ReadReferencingElement(String name, String ns, String& fixupReference)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read5926_get_failover_stateResponse()
at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer8221.Deserialize(XmlSerializationReader reader)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, Xml
...
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at iControl.SystemFailover.get_failover_state()
at F5CacheManager.GetActiveLBIPaddress()
Found a similar problem in a different thread, but couldn't find any solution. I was wondering if there is an open case for this issue?
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=398142&SiteID=1
View 4 Replies
View Related
Jul 20, 2005
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View 1 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. 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.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Nov 25, 2007
System.Data.SqlClient.SqlError: Cannot open backup device '\.Tape0'. Operating system error 5(error not found). (Microsoft.SqlServer.express.Smo)
i have only one sql instance and tape is istalled successfully.
please help me to find solution for this error.
Thanks,
View 2 Replies
View Related
May 17, 2006
Dear all,
I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this.
At first I have just a Flat File Source and then Script Component, nothing else.
Error:
[Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Script Code (from Script Component):
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim nDTS As IDTSConnectionManager90
Dim sqlConnecta As SqlConnection
Dim sqlComm As SqlCommand
Dim sqlParam As SqlParameter
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim valorColumna As String
Dim valorColumna10 As Double
valorColumna = Row.Column9.Substring(1, 1)
If valorColumna = "N" Then
valorColumna10 = -1 * CDbl(Row.Column10 / 100)
Else
valorColumna10 = CDbl(Row.Column10 / 100)
End If
Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)
Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)
Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)
Me.Output0Buffer.CODPROV = CInt(Row.Column7)
Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)
Me.Output0Buffer.NIFPERC = CStr(Row.Column3)
Me.Output0Buffer.NIFREP = CStr(Row.Column4)
Me.Output0Buffer.NOMBRE = CStr(Row.Column6)
Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)
With sqlComm
.Parameters("@Ejercicio").Value = CInt(Row.Column2)
.Parameters("@NIFPerc").Value = CStr(Row.Column3)
.Parameters("@NIFReP").Value = CStr(Row.Column4)
.Parameters("@Nombre").Value = CStr(Row.Column6)
.Parameters("@CodProv").Value = CInt(Row.Column7)
.Parameters("@Modalidad").Value = CInt(Row.Column8)
.Parameters("@ImpBase").Value = valorColumna10
.Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100)
.Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100)
.Parameters("@EjerDev").Value = CDbl(Row.Column13)
.ExecuteNonQuery()
End With
End Sub
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80
sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _
"VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta)
sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal)
sqlComm.Parameters.Add(sqlParam)
End Sub
Public Sub New()
End Sub
Public Overrides Sub ReleaseConnections()
nDts.ReleaseConnection(sqlConnecta)
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
Thanks a lot for your help
View 13 Replies
View Related
Apr 23, 2007
I get the following message in the vs2005 querybuilder when i do a preview:
***********************************
SQL Execution Error.
Executed SQL statement: SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = @PARAM1)
Error Source: SQL Server Compact Edition ADO.NET Data Provider
Error Message: @PARAM1 : Unable to cast object of type 'System.Data.SqlTypes.SqlInt32 to type System.IConvertable'.
****************************************
The same querypreview works fine without the parameter:
SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = 186)
Can anybody tell me why this is?
And tell me a way to get the tableadapter working?
Anne-Jan Tuinstra
View 3 Replies
View Related
Mar 12, 2008
I created a .net console application within which I connect to a sql server (not local) as follows,
string conn_str = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=QuantEquitySql;Data Source=server name";
SqlConnection conn = new SqlConnection(conn_str);
conn.Open();
.
Everything works fine when I run it from my computer. When I try to run it from a network share I get the following error,
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am using Visual Studio 2005 and .Net framwork v2.0.50727.
Does anybody know a fix to this problem?
Thanks
View 3 Replies
View Related
Jun 20, 2007
I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Here is the code :
************************************************************************
Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string
i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()
for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j
rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function
******************************************************************
Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes
how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?
Chi
View 7 Replies
View Related
Feb 10, 2007
Hi,
Im Designed a Webpage .It consosts of a Button, whenever i click on
Button report must generated.When the report is in my system report
generated successfully.when I try to access a report from other
system.I cudnot able to access report.Can any1 hlp me inthis aspect
.any procedure in order to access the report of other system.wht shud
be url for Reportserverurl and reportpath.In short
When i click on btn i shud get report of other system.what previleges shud be given for other system in other to access
With regards,
mahender
View 3 Replies
View Related
