Expression: !&&"No Exceptions Should Be Raised By This Code&&" When Selecting From System Function
One of my users is receiving the error below when selecting from this function:
select * from sys.dm_db_index_operational_stats (null,null,null,null)
I have already granted him "grant view server state" rights and this did not change anything. However, as a test I temporarily granted him sysadmin rights and he did not receive the error. I am curious as to what missing rights are causing the error.
The server is sql version is 2005 standard edition 64 bit SP2: Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
All the posts about this error that I have seen indicate that this error is resolved after applying SP2. Anyone have any other suggestions?
Error
Location: qxcntxt.cpp:956
Expression: !"No exceptions should be raised by this code"
SPID: 58
Process ID: 2000
Msg 21, Level 20, State 1, Line 3
Warning: Fatal error 1203 occurred at Jun 15 2007 10:32AM. Note the error and time, and contact your system administrator.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The following is from the SQL Log:
Process 58 unlocking unowned resource: DATABASE: 35
Error: 1203, Severity: 20, State: 1.
Process ID 58 attempted to unlock a resource it does not own: DATABASE: 35 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Process 58 unlocking unowned resource: DATABASE: 35
Using 'dbghelp.dll' version '4.0.5'
**Dump thread - spid = 58, PSS = 0x00000000A11EDBE0, EC = 0x00000000A11EDBF0
***Stack Dump being sent to D:Datase-k-MSSQLServer-OCSQLOGSQLDump0027.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 06/15/07 10:32:59 spid 58
*
* Location: qxcntxt.cpp:956
* Expression: !"No exceptions should be raised by this code"
* SPID: 58
* Process ID: 2000
*
* Input Buffer 276 bytes -
* execute as login = 'INTNPU22537' select suser_name() selec
* t * from sys.dm_db_index_operational_stats (null,null,null,null)
*
*
* MODULE BASE END SIZE
* sqlservr 0000000001000000 0000000003501FFF 02502000
* ntdll 0000000077EC0000 0000000077FF7FFF 00138000
* kernel32 0000000077D40000 0000000077EB2FFF 00173000
* MSVCR80 0000000078130000 00000000781FEFFF 000cf000
* msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000
* MSVCP80 000000007C420000 000000007C530FFF 00111000
* ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000
* RPCRT4 000007FF7FD30000 000007FF7FED8FFF 001a9000
* USER32 0000000078C30000 0000000078D3BFFF 0010c000
* GDI32 000007FF7FC90000 000007FF7FD28FFF 00099000
* CRYPT32 000007FF7D180000 000007FF7D2DEFFF 0015f000
* MSASN1 000007FF7D150000 000007FF7D179FFF 0002a000
* Secur32 000007FF7E7F0000 000007FF7E811FFF 00022000
* MSWSOCK 000007FF76FE0000 000007FF7705BFFF 0007c000
* WS2_32 000007FF77150000 000007FF7717FFFF 00030000
* WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000
* USERENV 000007FF7C4A0000 000007FF7C5A8FFF 00109000
* opends60 00000000333E0000 00000000333E7FFF 00008000
* NETAPI32 000007FF77370000 000007FF77407FFF 00098000
* SHELL32 000007FF7F190000 000007FF7FB97FFF 00a08000
* SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000
* comctl32 0000000000AF0000 0000000000C76FFF 00187000
* psapi 000007FF7E1B0000 000007FF7E1BFFFF 00010000
* instapi 0000000048060000 000000004806CFFF 0000d000
* sqlevn70 000000004F610000 000000004F7B8FFF 001a9000
* SQLOS 00000000344D0000 00000000344D5FFF 00006000
* rsaenh 000000000FFB0000 000000000FFEDFFF 0003e000
* AUTHZ 000007FF7E2C0000 000007FF7E2ECFFF 0002d000
* MSCOREE 000006427EE60000 000006427EED3FFF 00074000
* ole32 000007FF7ECE0000 000007FF7EF51FFF 00272000
* msv1_0 000007FF7E330000 000007FF7E373FFF 00044000
* iphlpapi 000007FF57250000 000007FF57280FFF 00031000
* kerberos 000007FF77410000 000007FF774C2FFF 000b3000
* cryptdll 000007FF7DAB0000 000007FF7DABEFFF 0000f000
* schannel 000007FF7DB70000 000007FF7DBB0FFF 00041000
* COMRES 000007FF7E920000 000007FF7E9E5FFF 000c6000
* XOLEHLP 000007FF5C560000 000007FF5C566FFF 00007000
* MSDTCPRX 000007FF67140000 000007FF67210FFF 000d1000
* msvcp60 0000000007420000 0000000007509FFF 000ea000
* MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000
* VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000
* WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000
* OLEAUT32 000007FF7E9F0000 000007FF7EB03FFF 00114000
* CLUSAPI 000007FF7B1C0000 000007FF7B1E3FFF 00024000
* RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000
* DNSAPI 0000000007520000 000000000756DFFF 0004e000
* winrnr 000007FF7E820000 000007FF7E82AFFF 0000b000
* WLDAP32 000007FF7E780000 000007FF7E7E5FFF 00066000
* rasadhlp 0000000007610000 0000000007616FFF 00007000
* security 000007FF77530000 000007FF77534FFF 00005000
* msfte 0000000049980000 0000000049D2DFFF 003ae000
* dbghelp 0000000009630000 000000000978CFFF 0015d000
* WINTRUST 000007FF7E210000 000007FF7E25DFFF 0004e000
* imagehlp 000007FF7E2A0000 000007FF7E2B2FFF 00013000
* dssenh 000000000FF70000 000000000FFABFFF 0003c000
* hnetcfg 000007FF6D1F0000 000007FF6D280FFF 00091000
* wshtcpip 000007FF76FA0000 000007FF76FAAFFF 0000b000
* NTMARTA 000007FF7E2F0000 000007FF7E32BFFF 0003c000
* SAMLIB 000007FF76F80000 000007FF76F95FFF 00016000
* ntdsapi 000007FF7DAD0000 000007FF7DAF3FFF 00024000
* xpsp2res 000007FF5C210000 000007FF5C4D6FFF 002c7000
* CLBCatQ 0000000003510000 00000000035ECFFF 000dd000
* sqlncli 00000000337A0000 0000000033A5FFFF 002c0000
* COMCTL32 0000000003610000 00000000036FDFFF 000ee000
* comdlg32 000007FF7D360000 000007FF7D3D6FFF 00077000
* SQLNCLIR 0000000035000000 0000000035032FFF 00033000
* msftepxy 000000000AA10000 000000000AA30FFF 00021000
* xpsqlbot 000000004A7C0000 000000004A7C7FFF 00008000
* xpstar90 0000000053C30000 0000000053CB5FFF 00086000
* SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000
* ODBC32 000007FF63EB0000 000007FF63F16FFF 00067000
* BatchParser90 00000000520C0000 00000000520ECFFF 0002d000
* ATL80 000000007C630000 000000007C64DFFF 0001e000
* odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000
* xpstar90 000000000B440000 000000000B465FFF 00026000
* xplog70 0000000034730000 000000003473FFFF 00010000
* xplog70 000000000B480000 000000000B482FFF 00003000
* odsole70 0000000033380000 0000000033397FFF 00018000
* SQLDMO 000000001C0B0000 000000001C6E2FFF 00633000
* odbcbcp 000007FF63E90000 000007FF63E97FFF 00008000
* SQLDMO 000000001C000000 000000001C08DFFF 0008e000
* SXS 000007FF7CA80000 000007FF7CC6CFFF 001ed000
* SQLSRV32 000007FF5FD60000 000007FF5FE05FFF 000a6000
* sqlsrv32 00000000093C0000 00000000093D6FFF 00017000
* odbccp32 000007FF63E20000 000007FF63E44FFF 00025000
* sqlevn70 000000000C5D0000 000000000C7D4FFF 00205000
* sqlevn70 000000000C7E0000 000000000C9E2FFF 00203000
* sqlevn70 000000000C9F0000 000000000CB98FFF 001a9000
* sqlevn70 000000000CBA0000 000000000CD8BFFF 001ec000
* sqlevn70 000000000CD90000 000000000CF87FFF 001f8000
* sqlevn70 000000000CF90000 000000000D173FFF 001e4000
* sqlevn70 000000000D180000 000000000D33FFFF 001c0000
* sqlevn70 000000000D340000 000000000D51FFFF 001e0000
* sqlevn70 000000000D520000 000000000D6F9FFF 001da000
* sqlevn70 000000000D700000 000000000D8A8FFF 001a9000
* sqlevn70 000000000D8B0000 000000000DA58FFF 001a9000
* sqlevn70 000000000DA60000 000000000DC08FFF 001a9000
*
* P1Home: 0000000000000001:
* P2Home: 000000000FAD3E00: 0030004600420044 000000000000000A 0000000000000000 0000000000000000 0000000500000030 0000000000000000
* P3Home: 0000000000000000:
* P4Home: 0000000077EF40D2: 00000320249C8B48 0000031824B48B48 0000031024BC8B48 0000030824A48B4C 0000030024AC8B4C 000002F824B48B4C
* P5Home: 0000000000000000:
* P6Home: 0000000000000000:
* ContextFlags: 000000000010000F:
* MxCsr: 0000000000001FA0:
* SegCs: 0000000000000033:
* SegDs: 000000000000002B:
* SegEs: 000000000000002B:
* SegFs: 0000000000000053:
* SegGs: 000000000000002B:
* SegSs: 000000000000002B:
* EFlags: 0000000000000206:
* Rax: 000000000FAD3E88: 0000000077D67D4D 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC
* Rcx: 000000000FAD39B0: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000
* Rdx: 0000000000000000:
* Rbx: 0000000000000000:
* Rsp: 000000000FAD3E90: 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC 0000000000000000
* Rbp: 000000000FAD5620: FFFFFFFF000003BC 000000000FAD9324 000003E500000001 0000000000000001 0000000000000007 000000000FAD935C
* Rsi: 000000000FAD4140: 0000000009307780 0000000000000000 0000000000000000 0000000000000000 0000000000000001 0000000000000000
* Rdi: 00000000A11EDBE0: 00000000A11ECB00 0000000000000000 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08
* R8: 0000000000000000:
* R9: 0000000000000000:
* R10: 0000000000000000:
* R11: 000000000FAD4680: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000
* R12: 000000000000003F:
* R13: 00000000A11EDBF0: 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08 00000000A11EDC08 00000000A11EDC18
* R14: 000000000FAD5530: 00610063006F004C 006E006F00690074 007100200009003A 0074006E00630078 0063002E00740078 0039003A00700070
* R15: 0000000000000001:
* Rip: 0000000077D67D4D: C3000000C8C48148 15FF984B8D49D233 8699E990FFFD971F 9090909090900000 834853C08B909090 0C0D8A158B4830EC
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
0000000077D67D4D Module(kernel32+0000000000027D4D)
0000000001D345DE Module(sqlservr+0000000000D345DE)
0000000001D3F3A9 Module(sqlservr+0000000000D3F3A9)
000000000247E28C Module(sqlservr+000000000147E28C)
00000000015D069F Module(sqlservr+00000000005D069F)
00000000781CF4B0 Module(MSVCR80+000000000009F4B0)
000000007816AA10 Module(MSVCR80+000000000003AA10)
0000000077EF3722 Module(ntdll+0000000000033722)
00000000010DEBF1 Module(sqlservr+00000000000DEBF1)
00000000010DE6EF Module(sqlservr+00000000000DE6EF)
00000000010DF551 Module(sqlservr+00000000000DF551)
00000000010E2B62 Module(sqlservr+00000000000E2B62)
00000000010E4115 Module(sqlservr+00000000000E4115)
00000000010E4695 Module(sqlservr+00000000000E4695)
00000000010E375D Module(sqlservr+00000000000E375D)
00000000781CF4B0 Module(MSVCR80+000000000009F4B0)
000000007816AA10 Module(MSVCR80+000000000003AA10)
0000000077EF3722 Module(ntdll+0000000000033722)
000000000102F94A Module(sqlservr+000000000002F94A)
000000000102F375 Module(sqlservr+000000000002F375)
0000000001030872 Module(sqlservr+0000000000030872)
0000000001270CE7 Module(sqlservr+0000000000270CE7)
000000000100685E Module(sqlservr+000000000000685E)
0000000001006F11 Module(sqlservr+0000000000006F11)
0000000001006A01 Module(sqlservr+0000000000006A01)
00000000010EF7A7 Module(sqlservr+00000000000EF7A7)
00000000010F051A Module(sqlservr+00000000000F051A)
00000000010F03F1 Module(sqlservr+00000000000F03F1)
00000000010F0289 Module(sqlservr+00000000000F0289)
00000000781337A7 Module(MSVCR80+00000000000037A7)
0000000078133864 Module(MSVCR80+0000000000003864)
0000000077D6B69A Module(kernel32+000000000002B69A)
Stack Signature for the dump is 0x00000000A0A891FD
External dump process return code 0x20000001. External dump process returned no errors.
Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
View Complete Forum Thread with Replies
Related Forum Messages:
Error From Sys.dm_db_index_physical_stats &&"No Exceptions Should Be Raised By This Code&&"
I'm setting up a routine to do some reindexing. To get the info I need I'm trying to use the new function. The DB I think is causing the problem does pass DBCC CHECKDB. I'm not sure why I'm getting this but can not find anyone else that is getting this same error. When I run this.... SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'detailed') I get this.... Location: qxcntxt.cpp:954 Expression: !"No exceptions should be raised by this code" SPID: 236 Process ID: 1060 Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. If I run only for the DB I think is causing the problem I get this... Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
Capture Oracle Raised Error Code In DTS Execute SQL Task
When a procedure in Oracle has an exception when executed from DTS, it is not aware an error occured. Can a DTS Execute SQL Task executing an Oracle procedure get a raised error code from Oracle? Or can a variable be set. If you know how Please let me know. And Thanks in advance. Heres current code. declare a varchar2(30) := 'LOAD_ORS_TEST'; b varchar2(30) := 'ORS_TEST'; c varchar2(30); begin LOAD_ORS_TEST(a,b,c); end;
View Replies !
System.Data.SqlServerCe.SqlCeCommand Has A Bug Related To SqlCeParameter Order That Causes Exceptions.
I came across a frustrating bug last week. Basically, whenever I tried to execute almost any sql query with unnamed parameters (i.e. using "?" instead of "@param_name" in the SQL text), an exception would be thrown. After trying lots and lots of things and navigating my way through the internals of System.Data.SqlServerCe.dll, I discovered that the method System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(...) has a bug. The bug is that the private arrays "parameters" and "metadata" are ordered differently, yet at one point in CreateParameterAccessor(...) they are compared using the same index. Here are the two lines: p = this.parameters[ i ]; and MetaData info = metadata[ i ] and then the column data types of "info" & "p" are incorrectly compared in a later method, ValidateDataConversion(...). So take a step back... how are they ordered differently? From observation, I concluded the following: The "parameters" array is ordered exactly in the order that the DbParameter's were added to the DbCommand object. The "metadata" array is ordered according to the column order of the table in the database. So what causes the exception? Well, CreateParameterAccessor(...) passes data types from two different columns (one type taken from parameters[ i ] and the other from metadata[ i ]) on to SqlCeType.ValidateDataConversion(...). And, of course, if they differ (e.g. one column is of type DateTime and the other is a SmallInt), an exception is thrown. I've found two workarounds, and both seem to work well. The first is to name the SqlCeParameters (e.g. "SELECT ... WHERE id=@id"). This causes the buggy branch of code to be completely bypassed. The second is to add the SqlCeParameters in the exact same order as the columns exist in the table you are accessing. Note, I do *not* mean the order that you select the columns (e.g. "SELECT column1, column2, ..."). I mean the actual order of the columns in the database. I've included my setup and a stack trace below to help if it can. My setup is: .Net CF 3.5 SqlServer CE 3.5 Visual Studio 2008 Deployed to Pocket PC 2003 Here is the stack trace (note the variables passed to ValidateDataConversion): System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeType.ValidateDataConversion(System.Data.SqlDbType expectedType = DateTime, System.Data.SqlDbType actualType = SmallInt) + 0x4a bytes System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(System.Data.SqlServerCe.MetaData[] metadata = {System.Data.SqlServerCe.MetaData[34]}) + 0x89 bytes System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CreateDataBindings() + 0x5e bytes System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() + 0x16f bytes System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(System.Data.CommandBehavior behavior = Default, string method = "ExecuteNonQuery", System.Data.SqlServerCe.ResultSetOptions options = None) + 0xa7 bytes System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() + 0x7 bytes
View Replies !
SQLServer JDBC Exceptions :Controlling Exceptions Text Format
Hi, Using RAISERROR from within a stored prcedure will result in a SQLException being thrown at the client side of a JDBC connection. I am trying to provide a meaningfull error message to be shown to the end user. However, all exceptions thrown will inevitably start with : "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]". The following questions then arise : - Is there a way of configuring SQL Server not to display this header ? alternatively, - Is there another way of conveying error messages to the client through JDBC ? Thank you for your answers, Ben
View Replies !
Operating System Error Code 3(The System Cannot Find The Path Specified.).
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 Replies !
Assign Expression Value Using Code
I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated. Thank you in advance.
View Replies !
Sort Expression - Custom Code
Hi, I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that. My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example: Code Snippet Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression ' Perform some calculation ' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with End Function then in say the Interactive Sort Sort Expression, i'll have the code: Code Snippet =code.MyClass.mySortExpression(myID) I hope i've made myself clear enough. Cheers
View Replies !
Selecting Rows From SQL Server Dbs With A Where Clause Using The System Date.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated. Many thanks, James.
View Replies !
Edit Expression Dialogue Box - Code On Multiple Lines
Hi there Is it possible to write code in the edit expression dialogue boxes on multiple lines? At the moment I have some extremely long expressions on just the one line and it makes it hard to read and add to as you have to use the scroll bar to view all your code. Any ideas are much appreciated John
View Replies !
Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services... I am using RS2000 I am trying to apply cint function in the expression editor for the textbox eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value). This is returning error The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow. Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
View Replies !
Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it. CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int, @as_of_date datetime) RETURNS TABLE AS RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value FROM dbo.Issue INNER JOIN dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date) GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date HAVING (dbo.Assets.deal_id = @deal_id) ) I need to do calculations on market value based on the default date. If default date isn't specified then it should be 100% of par amount. If default date is less than one year ago - 65% of the par_amount. If default date is one or more years ago - 0. I have no idea about how to do this and everything I try wont work. I created another function to do the calculations and this seems to work, but it only does one record instead of all of them. CREATE FUNCTION dbo.tf_Asset_Portfolio2 (@deal_id int, @as_of_date datetime) RETURNS @Market TABLE (issue_id int, par_amount money, market_value money) AS BEGIN DECLARE @ReturnDate datetime DECLARE @DD datetime DECLARE @PA money DECLARE @MV money DECLARE @ID int DECLARE @DateD int SELECT TOP 1 @ReturnDate = LAST_BATCH FROM master..sysprocesses WHERE SPId = @@SPID SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID SET @DateD = DateDiff("yyyy", @DD, @ReturnDate) If @DD = Null BEGIN SET @MV = @PA END Else If @DD > @ReturnDate BEGIN SET @MV = @PA END Else If @DateD < 1 BEGIN SET @MV = @PA * .65 END Else If @DateD >= 1 BEGIN SET @MV = 0 END insert into @Market (issue_id, par_amount, market_value) values (@ID,@PA,@MV) RETURN END I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
View Replies !
What Is Wrong With This Code? Selecting Data With Stored Procedure With Multiple Parameters
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
How To Find Terms In A File Name: Regular Expression OR A String Function
hi, i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files. the file are named as: ff_inbound_20071008_1.csv ff_inbound_20071008_2.csv where for file ff_inbound_20071008_1.csv: ff => flat file inbound => dataset of this csv 20071008 => date 1=> file count having in mind that they are store in the variable as a full path: z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv i need to extract the dataset and the date for each file. how can i extract these terms from the file name? many thanks, nicolas
View Replies !
Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG' I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression. Do you have a recommended implementation for this situation? Thanks, Toni Fielder
View Replies !
How Do I Access Sql System Stored Procs From Code
Hello: Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated! Thanks
View Replies !
Errors In Function Code
Hi, I'm pretty new to SQL and I'm having trouble with a function, probably just syntax. I'm trying to consolidate several rows of data into a single row text field. The calling program can use views but not functions so I need to get a table into a view with the consolidated lines. Also, I would think this could be consolidated to a single view that calls one function. Is that possible, how would I do it? Thanks for any help, Steve VIEW WHERE DATA COMES FROM: CUST_ORDER_ID | LINE_TEXT number1 | first line item text number1 | Second line item text number1 | Third line item text number2 | first line item text number2 | Second line item text ... VIEW I WANT: CUST_ORDER_ID | CO_LINES_TEXT number1 | Second line item text 'crlf' Third Line item text number2 | Second line item text FUNCTION CODE: This is the function code with errors CREATE FUNCTION dbo.tvf_NC_CO_LINES_TEXT (@ORDER_ID VARCHAR(20)) RETURNS @NC_CO_LINES TABLE ( CUST_ORDER_ID VARCHAR(20) PRIMARY KEY NOT NULL, CO_LINES_TEXT VARCHAR(1000) NULL ) AS BEGIN DECLARE @COLinesString VARCHAR(1000); SET @ORDER_ID = CUST_ORDER_ID; SELECT @COLinesString = COALESCE(@COLinesString + Char(13) + Char(10), '') + LINE_STRING FROM NC_CO_LINES_STRING WHERE NC_CO_LINES_STRING.CUST_ORDER_ID = @ORDER_ID SET @COLinesString = CO_LINES_TEXT RETURN END; GO ERROR MESSAGES: Msg 207, Level 16, State 1, Procedure tvf_NC_CO_LINES_TEXT, Line 16 Invalid column name 'CUST_ORDER_ID'. Msg 207, Level 16, State 1, Procedure tvf_NC_CO_LINES_TEXT, Line 20 Invalid column name 'CO_LINES_TEXT'. VIEW CODE: SELECT dbo.CUST_ORDER_LINE.CUST_ORDER_ID, CAST(CAST(dbo.CUST_ORDER_LINE.ORDER_QTY AS INT) AS VARCHAR(2)) + ' ' + dbo.CUST_ORDER_LINE.PART_ID + ' ' + dbo.PART.DESCRIPTION AS LINE_STRING FROM dbo.CUST_ORDER_LINE INNER JOIN dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID WHERE (dbo.CUST_ORDER_LINE.LINE_NO > 1) Steve Bermes Novae Corp.
View Replies !
Converting Some VBA Code To SQL Function
Hi, I'm new to this SQL thing, and I need some help with s stored procedure/function. First, the scenario: We are a social service agency, and like all such organizations, we have requirements for perodic reports about our clients. There are lots of them, but if I figure how to do one, I think I can apply the theory to the others. First, this is some sample VBA code I've tested and which proved satisfactory for the basic task, just for a form. It is passed a date (dteDOP), then adds 3 months, (quarterly report)and loops until it exceeds the current date, thus generating the a due date for the the next report. 'initialize the due date dteDueDate = dteDOP 'Add quarterly intervals, starting with (DOP + 3 months), until 'you exceed today's date Do Until dteDueDate > Date dteDueDate = DateAdd("m", 3, dteDueDate) Loop 'set a text box to the next due date after today Me.txtNextQuarDue = dteDueDate Obviously, this isn't really a function at the moment, but it worked as a test of the logic, with instantly visible results. This works, but I'd like to do it on the server end, so I can send out notifications. How would this be accomplished as an SQL stored procedure/function? Obviously, for that I'll need to again do a comparison of the current date with the due date for timing concerns, but that should be relatively simple. Also, I know that the '@' symbol is somehow part of variables in SQL Procedures/functions, could you give me a very basic explanation of this, especially the difference between @xxx and @@xxx? Thanks in advance, Stephen ----------------------------------------------- ----------------------------------------------- Gary Getsum: What happened to my mule? DM: It's dead; It got stung by a giant wasp. Gary: Can't I heal it? DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen! Gary: So? DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".
View Replies !
Non-deterministic System Function Suser_sname
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 Replies !
How To Create Custom System Function
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 Replies !
System Function To Get Current T-SQL Object Name?
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 Replies !
Help With 1205 Not Raised
Hi,I have an application that causes a dead lock at random. The issue I amhaving is, when the deadlock occurs, my applications is not recievingany errors from the DB. ie, during the deadlock SQLServer is returningan empty recordset and user is seeing a blank screen. The app logicdoes not go into the Try Catch statment in the C# code. I can'tunderstand why my app is not receiveing 1205 error from SQL server whendead lock occurs.Any ides why this is happening?Thanks_GJK
View Replies !
Custom Code Error With VB Function
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report: Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function ...and after much research cannot figure out the solution to this error: [rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected. I'm new to SSRS so is there a syntax error I'm missing? Thanks in advance, Buster
View Replies !
Access Linked Server System Function
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 Replies !
CLR Function Error 6522 Using System.IO Namespace
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 Replies !
ClR Function Errror Cant Get To Lcoal File System
My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work? Thanks AdminAnup
View Replies !
Slower Function Than Same Code In Query Analyzer
I have a problem (sorry if this has been posted before but I did a search and couldn't find it) I have a UDF that creates a cursor, loops through it and concatanates all of the fields into one field "|" dilimeited (it's what the client needed) and returns the value. Running this as "Select @book_type = dbo.Get_Book_Type(1)" where 1 is the ID of the book takes about 1.5-2 minutes. If I copy the code from the function and paste it into Query Analyzer, change the final return statement to a select statement it runs in about 2 seconds. Any idea why a function would run so much slower than the same code in QA? Maybe I have a setting set wrong on my database, or is this a common phenomenon? Thanks!
View Replies !
Can't See Function For Code Added In Report Properties
I put a function in the code section of Report Properties. I went to add an expression and can't find the function. I type =code. and did not see the function I created in the list. Here's my code below: Code Snippet Public Function ConvertToStartDate(ByVal StartDate As String) As DateTime Select Case StartDate Case "Today" Return DateTime.Today Case "Yesterday" Return DateTime.Today.AddDays(-1) Case "30DaysOut" Return DateTime.Today.AddDays(+30) Case "2DaysPrior" Return DateTime.Today.AddDays(-2) ' Additional cases Case Else Throw New ArgumentException("Unknown argument, " & StartDate, "StartDate") End Select End Function I should be able to put in expression =code.ConvertToStartDate.StartDate("Today"), but that does not work. I get a red squiggly line under StartDate. Thanks for your help, Iris
View Replies !
Non-deterministic System Function Suser_sname-I Think Here Is The Right Place For My Question
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 Replies !
Report Server Error: This Function Is Not Supported On This System
Hello, i have configured the report server with a special user account. after this the report server throws exceptions. In the browser: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: This function is not supported on this system. (Exception from HRESULT: 0x80070078) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: This function is not supported on this system. (Exception from HRESULT: 0x80070078) I have reinstalled the reporting services but nothing happens. The exceptions will still thrown. The EventLog shows: Report Server (MSSQLSERVER) cannot connect to the report server database. The user is Administrator and sysadmin ( only for testing ). Has anyone a idea, fix or workaround?
View Replies !
Trap The Sql Errors Raised By Sql Server In Asp.net
hi i am running a stored procedure and i want to trap the error of that stored procedure and pass it on the user in the asp.net prog. my stored procedure are running thru a class library using C#. i tried various options of creating a new sql connnection with out using the classlibrary, but i dont get any errors. the code s used is as follows Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("dbconnection1")) Dim cmd As New SqlCommand("usp_ImportPlanDetails", conn) cmd.CommandType = CommandType.StoredProcedure'cmd.Parameters.AddWithValue("@ClientId", Session("ClientId")) AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf MessageEventHandler) conn.Close() Private Sub MessageEventHandler(ByVal sender As System.Object, ByVal e As SqlInfoMessageEventArgs) Dim strMessage As String For Each sqle As SqlError In e.Errors strMessage = "Message:" + sqle.Message + "Number:" + sqle.Number + "Procedure:" + sqle.Procedure + "Server:" + sqle.Server + "Source:" + sqle.Source + "State:" + sqle.State + "Line Number:" + sqle.LineNumber ' strMessage = String.Format("Message: {1}, Number: {2}, Procedure: {3}, Server: {4}, Source: {5}, State: {6}, Line Number: {7}" , new Object[]{sqle.Message, sqle.Number, sqle.Server, sqle.Source, sqle.State, sqle.LineNumber}) 'Console.WriteLine(strMessage) lblErrorMsg.Visible = True lblErrorMsg.Text = strMessage and also tried doing this oImportPlan = New ImportPlan(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)) With oImportPlan Try .ClientID = Session("ClientId") ' .FileName = strFilePath1 .save() Catch sqlex1 As SqlException LogException(sqlex1) End Try End With Catch sqlex As SqlException LogException(sqlex) End Try Catch ex As Exception lblErrorMsg.Text = "Error: Import Failed" + ex.Message lblErrorMsg.Visible = True End Try but have no luck in displaying the errors, My stored procedure has raiseerrors, Please help me out
View Replies !
Code A Function To Return A Dataset In Which There Are Two Tables And Relationship
I used a function to create dataset as below: Public Function GetSQLDataSet(ByVal SQL As String) As DataSet ...... MyConnection = New SqlConnection(MyConnectionString) MyCommand = New SqlCommand(SQL, MyConnection) MyDataSet = New DataSet MySQLDataAdapter = New SqlDataAdapter(MyCommand) MySQLDataAdapter.Fill(MyDataSet) ...... End function It works fine. How to code a function to return a dataset in which there are two tables and relationship?
View Replies !
Cannot Bulk Load Because The File Could Not Be Opened. Operating System Error Code 5(Access Is Denied.).
I am facing a issue with bulk upload on Test Server. Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error. Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "\ServerNameinputFileName.csv" could not be opened. Operating system error code 5(Access is denied.). For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine. If the command is run from Test Server connecting to any SQL Server instance including Test Server it is running fine. Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server 2) Given the read/write rights on shared folder to everyone. Query: SELECT DISTINCT * FROM OPENROWSET ( BULK '\ServerNameinputFileName.csv', FORMATFILE='\ServerNameFormat.xml' ) AS FileList Please provide me with some solution. What can be the reason for such behaviour?
View Replies !
System.DirectoryServices Performance Issue In Table-valued Function
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 Replies !
Subsystem 'SSIS' Could Not Be Loaded (reason: This Function Is Not Supported On This System)
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 Replies !
EXCEPTION_ACCESS_VIOLATION Raised - Critical Error Has Occured - Help !?!
SQL Server has stopped a critical task, with the following error log. I have executed the task again and the same symptons occur. -- EXCEPTION_ACCESS_VIOLATION raised, attempting to create sympton dump ***BEGIN STACK TRACE*** 0x0040DC1A in SQLSERVR.EXE, ubfree() + 0x009A ... ... -- Has anyone any had a simliar experience or have any suggestions for investigation? Cheers Jasper
View Replies !
VB6 ADO 2.7 SQL 6.5 Error Not Raised If Execute Produces A Warning First
I am running an environment as below ADO 2.7 SQL Sever OLE DB Provider VB 6 SQL Server 6.5 SP 5 A Update If I run a stored procedure that contains a Print statement or gives a warning. Then any error that occurs after that is ignored. No error state is raised in VB and the Errors collection only contains the print or warning. Can anyone tell me why? Thanks Chris
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
OLEDB TRANSACTION MAKING DIFFERENCE And Error Is Raised
OLEDB transaction is making difference in my migrated application powerbuilder 10.5 which has new oledb driver which is replaced by MSS driver in previous Powerbuilder version. 1.)I am facing the below error on update/save this was not faced during my previous versino drive MSS in powerbuilder.This is oocure only after the change to oledb driver. "Row change between retrieve and update.". in front end of my application the error is poped as "(STD1007) This row has been modified by another user. Save is canceled! ". Please help to resolve this error.
View Replies !
|
TRACKER
