static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

.NET Framework inside SQL Server

Index ‹ SQL Server

- Previous
  - 1
    - Select statement to output a date Hello Guys, Please could you help me formulate a SELECT statement to output a date in the format (Month, Date Year), eg October, 07 2007. Thanks. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 2
    - Dumb question about compatibility level Hi everyone, This is my first psot so I am hoping for success.. I had been asked to web enable a sql 2000 database. We upgraded to sql 2005 and I imported the data. All went well until a tried to run a CTE ( common table ) routine and it kept on coming back with an error. On searching the web it turns out that the compatibility level must be set to 2005. When I tried to set the compatibility level the only two options are sql 7 and sql 2000. I thought it might be due to the fact I had restored from a 2000 database so I tried to create a new database in SSMS but again only sql 7 and 2000 options were available from the drop down box.. What am I doing wrong Am I not running in sql 2005 at all Any help would be appreciated Peter Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 3
    - Updating the variable's value Hello People, I'm using SSIS and I want to send a report to the admin about how many rows are new, updated or unchanged in a mail. Everything is working fine except that the values that are sent are always zeros. I'm using a Row Count Transformation and configuered it to update the approperiate User Variable which I priviously created. However, the initial values in these variables are always Zeros. What can I do Thanks, SHIKO Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 4
    - BCP (BULK INSERT) with only <lf> as line delimiter (not <cr><lf>)... Hi, This might be a noob question, but is there any way to specify just <lf> as a line delimiter, and not <cr><lf> ( /n ) as line delimiter in T-SQL call for BULK INSERT (or BCP utility). How please TIA. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 5
    - Replication using SQL server 2000 (SP4) .. urgent... Hi, We have few branches with individual dbs are sitting in the branches itself. Periodically all the data need to be updated to a single HQ database. At the moment, all the tables's primary key are unique and each branch will have branch ID's in the transaction tables. e.g. Branch1 data need to be updated to HQ db. However, HQ db records which are related to Branch 1 data only need to be updated back to Branch 1. other branch records should not goto Branch1. Similaryly other branch records need to be updated/replicated back to HQ database. And only records related to the particular branch need to be updated from HQ (if any changes made).. At the moment, i can do simple replication from one db to another db only... Can anyone guide/give suggestions on how to go about doing this If possible, please give steps involved... TX Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 6
    - New Database I want to design a new database. When I right-click on 'Data Connections' in the Database Explorer from VB I notice that the 'Create New SQL Server Database...' is greyed out, so I can't create a new database. What is going wrong here, please help. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 7
    - Analysis Services Lock-ups I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were: Poorly formed / unreasonable / just-plain-stupid Very intensive but we-must-have-the-data In the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users. In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time. I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query. Any thoughts or references to documention about this Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run Thanks in advance, David Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 8
    - Version for procedures Hi Is there any way to check a version of a procedure I mean I need to have version or comment for my procedure to indicate wicth procedure is currently is used in database Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 9
    - table column extended field properties through MS SQL Server Management Studio I have a database defined as: dbname - Tables - dbo.xyz -Columns Field definition_a Field definition_b Field definition_c ... For each "Field definition" I can define "extended properties" So the question is, from a stored procedure in C#, how can I read through the "Field definitions" looking for an extended property of a specific integer value /Boyd Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 10
    - Checking to see if a record exists and if so update else insert I've decided to post this as a sticky given the frequency this question is asked. For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you. http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx Thanks Jamie! If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here: http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/ Phil Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 11
    - DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL) Hello, I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of pulications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects ] = NULL ,0,[Measures].[ Distinct amount of projects ]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Data_Projects -data_id -years Data_Publications -data_id -years Data_E_books -data_id -years For example, when I browse the cube: prof_id Amount of projects Distinct amount of projects Amnt_Projects 1032 30 1 1 1070 90 2 2 1111 0 1 0 1137 0 1 0 1234 1404 9 9 1721 504 7 7 2661 85 5 5 ... ... ... ... 6999 20 1 1 9956 50 5 5 Uknown 0 Grand Total 2421 11 11 Grand Total ЁА11ЁА is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be 59ЁА for all professors). How could I get the right value to be shown in Grand Total Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 12
    - Cannot Login VB express and SQL express are on the same desktop. I have an application in VB that works fine.The issue I have is if I go into SQL Management to work on the DBand then close SQL Management Express. When I go back to execute my VB application I get a message like this " Login failed for "user name" file is in use by another application". If I reboot my system and then run the VB application it works OK. I've tried doing a disconnect before I exit SQL Management but this had no affect on the problem. Is there something I need to do when I exit SQL Management Express so I can immediatley run my VB applicaton without having to reboot Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 13
    - SQL Server dropping comma in German language All, I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language. I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database. My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected. Can anyone suggest a solution on my software end Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "." Or is there a setting in SQL Server to compensate I appreciate all and any help. Thanx! Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 14
    - Maximum length of an expression Is there a maximum number of characters in an expression thx Helen Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 15
    - AMO Role Creation Hi, I have just created a role using AMO. I'm supposed to see this role in the Analysis Services Project once I open it, right But I cannot. I tried executing again my codes, and it prompted me an error, which means that it was created successfully. Error: Another 'Role' object has the 'TestRole' name. Why can I not see that in the AS Project By here is my code: Svr = New Server Svr.Connect( "Data Source=localhost" ) DB = Svr.Databases( "AS17" ) Cub = DB.Cubes( "Test" ) UserRole = DB.Roles.Add( "TestRole" ) UserRole.Members.Add( New RoleMember( "xa\chemps" )) UserRole.Update() thanks a lot! cherriesh Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 16
    - Proclarity analytic Views without using sharepoint Hi All, May I know there is any way to show the proclarity analytic views or reports in a web portal without using sharepoint server If there is no way, can u brief me how to create proclarity analytic views and how to show those views in web portal ( browser) by using sharepoint server What kind and what version of SSAS , proclarity , and Sharepoint do I need to use I have no experience in BI tools and Data Warehouse. I am very interested in that field. Moe Min Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
- Next
  - 1
    - Add list as parent for rectangle in rdl Hi! I have a report designed with all the tables in the rectangles on the rdl. Right now, I need add "list" control on the top of those rectagles(including all the tables) so all the rectangles have list as parent I don't want to start over to just for adding the list. I was trying to edit report xml file to add the "list" on the top of rectangles but the compiler complains. The rectangle property has "parent" field ,which is "body" right now. How can I make it's parent as "list" This field is grey out so I can not change it in the property window. Any idea Thanks. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 2
    - Printing in Reporting Services hangs Please Help!!! We have never been able to get the printing to work on any of our reports using SQL Server Reporting Services 2005 SP2 in our production environment. Without this feature of printing a report, we cannot go live with SSRS at all. We have tried adding the SP2 after a clean install on a new server -- yet we still have this problem. I read a few some posts about uninstall the old ActiveX control used for printing, so I did this and installed the new one but that didn't help either. We can print to files without issue - only when we try to print to a real printer does it hang forever. It doesn't matter how big the report is, 100 pages or 1/4 of a page, same problem. We do the following: 1) Run the report 2) Click the print button 3) Select a printer and press ok 4) "Printing Now..." dialog box appears 5) "Print Error" dialog appears and never goes away - and nothing is printed out. Have to close IE and crash the report session to get out of it. This is the result on every client computer we have ever used in trying to access the report server. It also hangs in running a Print Preview for all reports. We are printing to network printers from IE6 and IE7. Since this is on a server, we do not have a local printer to this server computer to try to print to. Even if it worked on a local printer, this is supposed to be an enterprise solution. Almost all of our employees print to network printers. I am logged into my machine as a domain admin. I have no printing problems in any program printing to any printer, except when using Reporting Services. We are trying to print from an ASP web application which runs the reports in an iframe using URL access. We are able to print the same reports on all of our development environments using the same ASP web application and RDL. Again, Please help!!! Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 3
    - MICR Font Issue When Exporting to PDF Hi, I'm using Reporting Service to print checks. Everything seems ok when I design and deploy and view the reports. But when I try to export to pdf, the MICR font I'm using does not work. I've tried a couple of different MICR fonts from the web and they either get replaced by a standard font or disappear altogether. I would love to know why that is. Thanks in advance, Tom Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 4
    - Stored Procedure adding new row hello i am trying to write a stored procedure that add a new row to a table, this part i can do using INSERT. I then want to access the ID for the row i have just add, which is the primary key for the table and produced by a auto number. I will then use this value combined with a string to produce a report number which i will put back in a field in the row i just added, and i also need to pass this new value out the procedure. How do i get the id of the new row from within the stored procedure that just created it How do i add the new value in the new rows approtate field How do i pass the new report number out the procedure could i use the return statment with the varible that holds the value. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 5
    - Member Property length limitation I have a member property in a shared dimension MSAS 2000. The length of column from which this member property fetches value is nvarchar(2000). But the OLAP cube is reading only 255 characters ONLY. Is this a common limitation If yes, what is the alternative to increase its length to grater than 255 Any help will be greatly appreciated. Thanks. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 6
    - SQL Server 2005 Evaluation Install Error I downloaded the SQL Server 2005 Evaluation setup .exe. When I wen to install it by clicking "SQLEVAL.EXE", I received an eror stating "There is not enough space on Drive C:\ to extract this package. I have over 50 gig of available spac eon my C drive. What gives When I downloaded the .exe, I had the Norton AntiVirus Auto-Protect enabled. Could that have caused it Also, 2 days ago, I installed MS-Accounting 2007. Could that have caused a problem I have seen this error posted on the SS2005 forums and the standard response is to look at the installation logs. I have no logs. The extraction never completed. Thanks. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 7
    - Custom Control Flow Item Issue I've been using Konesan's FileWatcher control-flow item successfully in design-mode on my PC, which runs the package on a remote server. I have installed the Konesan's FileWatcher on the remote SQLServer machine. I then imported the package to the server (Files System folder). I then select the package, right-click 'Run Package', then Execute, and receive the error: "Error: The task 'File Watcher Task' cannot run on this edition of Integration Services. It requires a higher level edition" ..in the 'Package Execution Progeress' dialog. All other validation seem to be ok. (Note, I'm executing the above steps using SQLServer Mgt Studio from my PC ; I'm not doing it from the SQLServer machine itself...not sure if this matters or not.) The SSIS version installed on the server is 9.0.3054. It shouldn't be an "SSIS version issue", as it is the same SQLServer that I used (successfully) from my PC in design mode... Thanks, Allen Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 8
    - Move distribution data/log files to another drive I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it Thanks Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 9
    - Data Source and Connection Manager Hi all, I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution. I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager !! What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct If it is I dont't understand the avantage of making a new data source .... Thanks for your response Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 10
    - Query Execution failed for dataset error / the syntax for " " is incorrect Hi, Am totally new to MDX Queries. I got this error while previewing a report that we had made. It goes like this -- Query execution failed for dataset 'some_dataset'. Parser: the syntax for 'some_character' is incorrect. What was figured out was this error shows up only for those values in a particular dropdown list which are multiword. Eg : My Company Technology and not for those values which a re single word. Eg : Company what could be the possible problem and the solution to that. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 11
    - Month's end Hi, I am working on a cube that contains month standings and month totals. This works fine if I select one month. It goes wrong when I do a year to date selection because it sums up the month standings. How can I make a cube that does sum up the month total but only returns the last month standings. With regards, Constantijn Enders Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 12
    - Backup File Name Dear all, On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day. Thanks Imran Hasware Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 13
    - CONTAINS and WHERE Clause Combination taking too long Hi, I have a table with 3 columns and 20 million records. first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000). I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column. Now when i try to search SELECT TOP 20 col1, col3 FROM tbl WHERE col1 = '1234' AND CONTAINS(col3,'"market*"') I am facing following problems 1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second. 2- Some time it show criteria is too complex, although i am only requesting a single word in col3. I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information. Please assist. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 14
    - stored procedures, compile or not I am writing stored procedures to retrieve data from an OLTP application that will be adding several hundred thousand transactions a day. The user will be presented a screen to select up to a dozen or so search criteria; date range, color, size, processing plant, etc. They may select all criteria, one or two, or none. Currently I am creating a stored procedure "on the fly" by replacing temp vars with search criteria, compiling and executing. My option would be to compile the stored procedure, pass it parameters, and run the precompiled stored procedure. I am wondering, as the database is changing so much, if I am better off to compile the procedure every time it is run. Or if stored procedures are always the best way to go. I plan to incorporate an update statistics in my maintenance plan with each night's back ups. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 15
    - Add connection error: 40 Hi i just reinstalled windows, and installed visual studio .net 2005 and the Sql server. But when im trying to add connection, select my computer at Server name, type a databasename then pressing OK i get this message: An error has occured while estabilishing a connection to the server. When connection to SQL server 2005, this faliure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Names pipes provider, error: 40 - Could not open a connection to SQL Server) Does anyone know what todo herman Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp
  - 16
    - is it possible to connect to SQL Express without network? I have notebook, client server application and sql express sp2 on this notebook. Also, I know domain user login/password, but I don't know any local user name/password and I don't know any sql server user name/password. Domain user is not a local administrator on this notebook. When notebook connected to the office LAN I am working good with my app-> my sql express as a domain/user. I'd like to work at home too. I can logon to the system (XP SP2) using domain account when notebook has no network connection. (probably OS caches something) Is it possible to work with sql express using domain login when no network connection PS. old version of this software used MSDE 2000 and it was possible(!). Thank you. Tag: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

db042188

after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected Is the fact that debugger doesnt show this behavior also expected

Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

nielsb

You should be very, very careful using static variables!!! Yes, it is expecte behavior that they are being re-used, that are static after all. And in order to use static variables, your assembly has to be deployed as UNSAFE.

As for the debugger not showing this, I am a bit surprised by that.

Niels

Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

db042188

is it fair to assume then that the sp's method(s) itself is also remaining in "memory" (ie not getting reloaded) between executions, unless SQL had to reclaim that memory from SQLCLR previously

Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

nielsb

Well, as the methods are static - the class constructor (cctor) will not run until the memory is being reclaimed (or the app-domain has been torn down etc). So in essence you are right with your assumption above.

Niels