I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).
I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.
Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
hi i'm having this error on my application"cannot allocate more connection.connect pool is at maximum increase max pool size" the proble is when i do testing this error does not apply it only Appears when the application is been used by many people How can I resolve this? Thanks
I am loosing my mind on this one. I select info from a database. I then take that selected info, and select info from another table based on that. Straight forward. The CompanyKey attached to my username is 7 which displays in the ReaderResults.Text. I the following line: selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"
I can change it to:
selectSQL2 = "SELECT * FROM Company_Membership WHERE companyKey = 7 "
and it works. I have no idea what is going on. Dissapearing string? Here is the code: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'Job1 InfoDim currentUserID currentUserID = Context.User.Identity.Name.ToString() Label1.Text = currentUserID Dim selectSQL1 As String selectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReaderDim CompanyKey 'Job2 Info Dim selectSQL2 As String selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"Dim cmd2 As New SqlCommand(selectSQL2, con) 'Job1 Select Try con.Open() reader = cmd1.ExecuteReader()Do While reader.Read() CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally ReaderResults.Text = CompanyKey con.Close() End Try 'Job2 Select Try con.Open() reader = cmd2.ExecuteReader() GridView1.DataSource = reader GridView1.DataBind() reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally ReaderResults.Text = CompanyKey con.Close() End Try
Hello everyone... i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
My company asked me to do a tech interview on a potential contractor for a three month assignment writing SQL Server stored procedures.
I have some things I can ask him to test his qualifications, such as cross-tab query design, UDF usage, running total queries, select first 'N' queries, Primary Keys vs Clustered Indexes, etc...
I was curious what questions other people might ask in order to assess a person's skill. Remember that this is SQL programming only, not admin stuff, and I want to ask questions with a range of difficulty from easy to head-cracker.
I dont know where to put this thread, I just wanna ask a question..actually what is happen when subscription synchronized the publications among IIS process.. (SQLMobile)
I don't have any knowledge about SQL Server at all... I wanna know:
1. What is SQL Server? 2. Have SQL Server something to do with databases and Microsoft Access to do? 3. What is the link between Visual C++ Express Edition and SQL Server Express Edition? 4. Where on the web can I learn (tutorials) how to use SQL Server Express Edition?
Dear Friends, please suggest me the best book to gain sound knowledge in DBA concepts as well as writing in sub queries, complex queries as well as inner queries.
I've been working as a .NET developer for many years. However, my educational background isn't in IT at all, so I occasionally find that there are big gaps in my knowledge when I'm called on to do certain things.
My current role is the first one I've had where the efficiency of my database calls has needed to be top-notch. Most of my previous posts have been in building intranet functionality for medium-sized companies so it's just not been an issue and my fairly basic level of SQL knowledge has been enough to get me by.
However, I'm increasingly finding I'm needing to look stuff up, ask for help and so forth and I'm not at all sure that what I'm putting out is up to scratch. I have three main areas of concern:
1) The developers here seem to like doing a lot of the "work" of data sorting and manipulation in SQL whereas I'd previously have just grabbed the whole table and done the manipulation in .NET. I'm finding building my complex queries is just doing my head in - I'm just not used to "thinking" in the way that SQL requires which seems very different from procedural programming.
2) A lot gets said about properly indexing tables for maximum efficiency. I know what an index is and vaguely what it does, but the means of applying them to ensure the best performance is pretty much beyond me.
3) In the same manner I'm now having to worry a lot more about performance issues in my database design. I now the five normal forms and so on, but is there anything else here I need to do to maximise performance?
What I'm asking, really, is whether anyone can recommend a good source for SQL knowledge (book, site, whatever) that specifically focuses on my first problem - it'll teach me the proper way to structure my queries and think about them properly so that they don't look so damn impenetrable. It then needs to go on and address my other issues.
Greetings,I have to admit that I'm still a beginner in the database field, but I'mactively studying, and this is why I will be utterly grateful for theproper, accurate, and wise guidance to the right direction or specificpaths of database studies.I simply want to make my first major database project a "personalknowledge database" or a "personal encyclopedia", so to speak. By this Imean that I want to gather diverse, multi-format bits of knowledge intoa single database divided into major categories, sub-categories, and soon. For example, I want to scan collected articles from newspapers &magazine and save them as images, type hand-written notes into thecomputer and save them as MS Word or Text files, save audio files withsearchable keywords, even save searchable MS OneNote audio & text files,all into directories & sub-directories. Then I want to be able to searchall these files for specific words or subjects; sort them according todate, subject, etc; update, add to or append, edit, or even cut & paste(within) any of those records in the database, which rely on easilycustomizable formats like text & images (in contrast with audio files orOneNote files, for example).I also want to prevent wasting time in the future, by avoidingrepetitive, same-keywords typing for every non-text new entry, like animage or audio file, by having a customizable keyword list open (byright clicking for example), where one could mark all the relevantkeywords for the picture or audio file. Also have a short descriptionfor each one of those entries when needed. And I want to be able to addslick, good-looking graphics to the interface of the database and theforms of the records.Basically, my priorities include: saving time filling the database withknowledge, quickly & accurately finding the future targets of anysearch, presenting the search results in a highly comprehensible &organized form, and a good looking interface & forms for every entry,which are pleasing to the eye and encouraging to study.I already use graphics applications for the "looks" part. So my questionis: what are the technologies or applications that I should learn byheart and use in order to design such a database in the best way? WouldMS Access alone do it all for me? Or should I learn other technologies?I'll be grateful for anyone taking the time to help. Thanks.Sincerely,Yasso"Claiming that God does not exist because there are people suffering anddying is like saying that barbers do not exist because there are peoplewith long hair! Truth is: they suffer because they did not find God ordo not go to Him, just like the others did not find a barber or do notgo to one."*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
Hello, I've been trying for a couple of days now with the following problem in SQL Ive a table called tblRresults that has the following Date - just a regular date OfficeLocation - just a unique id such as a zip code SampleType - contains things like wall, floor, beam SampleResult - "Detected" or "NotDetected" When populated the table can look like 12/12/05 99505 Wall Detected 12/12/05 99505 Wall NotDetected 10/04/05 99211 Beam Detected 10/04/05 99211 Beam Detected 10/04/05 99111 Floor NotDetected 10/04/05 99111 Floor NotDetected What I want is (I guess) a crosstab query that produces the following Date Location SampleType CountDetected CountNotDetected 12/12/05 99505 Wall 1 1 10/04/05 99211 Beam 2 0 (or null would be ok) 10/04/05 99111 Floor 0 2 Any help gratefully accepted, this has been driving me mental! thanks
1) What is the use of pooling actually? I have an online application using SQL server, where I can check the connection pool from, SQL Server Enterprise Manager->Microsoft SQL Server->SQL Server Group->LOCAL->Management->Current Activity->Process Info. 2) In the connection pool, when I log into my system, a connection is made as I run query using stored procedure thus making the process available in the connection pool. Now when I am done and log out of the system, does the process suppose to stay there or not (the connection closed) and making it disappear from the pool? I read from some forum that pooling is a good thing as it make accesing data faster.
HiI have written the following function public System.Data.SqlClient.SqlDataReader ExcuteReader(string strQuery, string strConnection) { System.Data.SqlClient.SqlCommand myCommand; System.Data.SqlClient.SqlDataReader myReader; myCommand = new System.Data.SqlClient.SqlCommand(strQuery, new System.Data.SqlClient.SqlConnection(strConnection)); myCommand.Connection.Open(); myReader = myCommand.ExecuteReader(); return myReader; }to return a datareader for me but right now I am facing the problem that it seems this function isn't closing the connection by itself which is resulting in an error saying "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached" and if I close it manually I will lose the data in the readerso are there any suggestions of what I should do?thank you
I build an asp.net 2.0 (VS 2005) web application and put it on the server. The installed SQL server is SQL server 2003. I get this error message when i keep the application running for 10 or 15 mins. "Timeout expired. The timeout period elapsed prior obtaining a connection from the pool. This may have occure because all pooled connections were in use and max pool size was reached." I made the server timeout unlimited but the error keeps coming up after some time of openning the application. The application session time out redirects the user to the login page and doesn't throw an error like the one i mentioned above. Any idea. Thanks in advance.
hello to all my problem is when in database it reaches nearly 100 active connections (i check it from detach database) , my application does not allow me to open new browser page and show me error like: time out expire. the max connection pool is reached.
Hello Experts,I make a connection with .Net Sqlclient Data Provider tomy server and I adjust the min pool size = 0, but when Icheck the sysprocesses I see that 2 connections have beenmade. Why is that? am I missing something here?Conn. string"server=MYSERVER;user id=sa;database=MYDB;min poolsize=0;max pool size=10;packet size=3072;ApplicationName=MYAPP"Thanks in advanceMichas KonstantinosSQL Sewrver Developer, DBA*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have pages which are using a master page. An example page on my site would be the homepage, it makes 3 connections to mssql. 1) Get the keywords of the page 2) Get a list of news articles 3) Get the content of the page. When I visual web express to debug the site it's giving me a pool error message, neither can I get the site to load directly via IIS. It says theres been a pool timeout. I've read on the internet about making sure connections are closed when you are finished and I've checked that all database connections are closed using finally{conn.Close();} does anyone have any idea why I would be having this problem? How can I see what connections the site is opening, or maybe theres a limit on my server? I'm using my own test server running windows 2003 and IIS
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I have a site which provides online studying for students in a graduate program. Apparently their final was today because lastnight everybody was on. I know, however, that this is a small class, so no more than about 80 people could be on the site at one time. I still got SQL errors (emailed to me from Application_Error). I read on MSDN that by default connection pooling should be in place, with a pool size of 100... that would mean I wouldn't have this problem if only 80 students were on the site.
Why is this happening?
Below is my connection string from web.config. <add key="connStr" value="Server=xxxxxx;Database=xxxx;UID=xxx;PWD=xxx;"></add>Error: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Hi guys! I have a dev server that has about 15-25 connections which is fine. ** Looking at performance counters: _global_ under SqlClient: Current # connection pools under SqlClient: Current # pooled connections under SqlClient: Current # pooled and nonpooled connections **
BUT when I look at staging and production I have only 4 or so... WHAT'S GOING ON?!?! The querystring doesn't specify the max pool size, pooling, nor timeout values. So I'm guessing it uses the defaults. ( I also tried putting in max pool size, but it didn't change anything! )
Does anyone know how I can increase the value? It is impacting the performance of the site considerably. Thank you!
Hi,Can anyone tell me if he ever got this issue and how he get the solution. In my asp.net 2.0 application i close always ma connexions to sqlserver in the finally clause (the open is in the try); I open 1 ou 2 connecxions per aspx page, i close them always (in the finally clause or at the bottum of the form). Sometimes, the application is down with the messageCan not open a new connexion, max pool size is reached !!!!I host my application on a remote server, i have no performance issues, only this great problem !!!i cant view the status of connexion on the sqlserver, so what can i do, or how shoud i develop my connection.thanks in advance to all of yuo who will help me.Imed.
When using connection string connect to sql server, there is a item max pool size. The default is 100. I set it to 500, but still get max pool size is reached and timeout error.
I checked my code and do open and close each connection each time using connection, so should have no memory leak.
What is the maximun value for max pool size? how to calc this size based on the memory used by SQL Server?
From what I understand, some database drivers provide automatic database pooling, in the sense that when an application closes a connection, it is not dropped until after a certain timeout. If the same, or another application, request a connection then the "pooled" one is returned.
What's the situation with C#, .NET Compact Edition and SQLce? Is it a performance hit to open and close a connection, or is some kind of automatic pooling in effect?
Hi All,I'm having a bit of a problem with a c# program which creates and index file/table. The number of records I need to process is around 3500, the main loop. Each of thoes records I need to look at a 2 text fields and create an entry in an index table for each valid word found in thoes fields. I call a stored proc to save the key word. So this stored proc get called numerous time per record.What seems to be happening is proceese about 400 main rcords the errors out with the following error message: {"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." }Has anyone had a similar problem? if so any suggestions on how to fix, I've been looking at this problem and recoding for the last 4 days! I need some help! Using SQL Server 2000 & .net v1.1Thanks in advance Davej
Hi All, Where Connection pool reside, is in client side(in .net framwork) memory or at server(in sql server) momory or at both side it maintains pool information? Best RegardsImran Zubair
I'm opening a large number of connections to a SQL server 2005 database using a SQL object based on the System.Data.SqlClient library. When I log out of the application, I call the ClearSQLConnectionPool method of the SQL object and this should clear the pool of these connections. This has worked in the past, but lately I have noticed some odd and very annoying behaviour.Very often I log out of the application and few, if any of the connections disappear. Just this morning I opened the application, logged out and all but one of the connections disappeared. Then I opened it again, logged out and none of the connections had gone. If I open the application again all of the past connections are still there plus the new ones.I know it works, its just very inconsistent. The problem may be with IIS, SQL or with library; I just can't tell which.Any ideas?Many thanks
