We've been using Sql Server 2005 for some time now and we've found it to be stable and pretty much bug free, well done! (as opposed to Oracle 10g, which is bug-ridden) I'm not sure if this is the right forum to post this in, but it felt like the best place to start. Please move my post if it is not correct.
In the last few weeks we've had a very serious problem; whenever we try to execute our upgrade scripts the CPU usage spikes to 100%. And then every insert takes > 0.5 seconds. Needless to say, but I'll do it anyway, this is a huge problem. Some of our scripts have > 100 000 inserts, so the upgrade process takes "forever".
At this point it might be useful to talk just briefly about how we use our Sql Server; We have an application that is configured via the database (we support Sql Server and Oracle) and so we use the database for a lot more than just a "dumb" datastore. We do massive amounts of insert/update for every version of our application and we add new tables quite often. We have a build system, using TFSBuild, where we do nightly builds. That build will drop the current database (example of name: MONDAY, TUESDAY etc.) and restore it again using an image. Then we upgrade the new database to the most current version of our scripts. On our Sql Server 2005 server we have 4 such databases so the process is repeated 4 times each night. And since we build our database using a baseline image and scripts (both stored in source control) we do not need backups of the databases, so no backup plan is running. I'm only mentioning that because we've had a theory that this might be the cause of our problems...
So, does anybody have an idea how we might go about solving this?
I try to consume a SSL Endpoint that is locate in localhost, but i get the follow error msg:
The underlying connection was closed: An unexpected error occurred on a receive.
m_safeCertContext is an invalid handle.
I do not know what gone wrong with my VS2005, i tried in IE with https://localhost/test?wsdl, and it was okay and display the wsdl document successfully. Beside i also tried with CLEAR port, everything is ok and i managed to consume the web service too.
I believe this question has been covered quite a bit, but none the less I wanted to ask it as I still have no resolution at the moment. I am consuming a table from Oracle which has a data type of NUMBER. When I use a .Net Provider to consume the data, the SCALE defaults to 0 for these select columns and thus we lose any decimal point information for these columns. I have tried using both the .Net Provider and the .Net for OLEDB providers for ORACLE. Both seem to suffer from this. The SSIS server itself is a Win 03' server on x64 hardware architecture. Also, when trying to use the Oracle Provider for OLEDB it throws a design-time error stating "Error in Initializing Provider".
I've just faced quite interesting case. I have two SQL Servers 7.0 running on two W2K(sp1) Server boxes (Compaq ProLiant 1600 and HP NetServer LC3) both with RAID5. One of the task these servers peform is logging MS Proxy. For more convenient way I created the procedure that show me proxy users, their IP address and URL head where they hit for the last one minute and half. This one as follow:
----- REATE proc usp_showusers as select clientip, clientusername, substring(stuff(uri, 1, 7, ''), 1, patindex('%/%', stuff(uri, 1, 7, ''))) short_uri into #whoweb from msplog where logtime > getdate()-0.001 select clientip, clientusername, short_uri from #whoweb group by clientip, clientusername, short_uri -----
So, when I run this proc on HP box it executes well and SQL keeps on consuming the same amount of memory (approximately 60MB) When I run on Compaq, the proc executes well as well, but promptly gets memory consume up to 150MB (before execution it did ~50(60)MB). In half an hour it pulls down 90MB. In addition to SQL Server the boxes run Lotus Domino R5 and Active Directory. The installed software on them is absolutely identical.
Hi there, I've been following Vineets and David's procedures to consume web services using SQL CLR to the t. I created my web service in C#.NET 2005, and generated my proxy using this command:
I added both files to the project, set Generate Serialization Assembly to on and compiled it.I then generated a strong name key for the assembly and signed my assembly with that key.Inside my post-build event I added the following script:
"E:DevelopmentMicrosoft Visual Studio 8SDKv2.0Binsgen.exe" /force /compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures $(TargetDir)$(TargetName).dll
This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.
In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my first assembly with permissions EXTERNAL ACCESS and then the XmlSerializer assembly with permissions SAFE. I created my stored procedure and ran it. When I did I got this error which I assumed the XmlSerializer was supposed to solve for me:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
I have seen alot of posts about this error, but none of them has been able to solve my problem.
Hi, Ive been taking a look at how to consume events from a package when executing programatically.
Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.
My class that implements IDtsEvents simply output a message to the console for each event type.
Weird thing is, when I execute, this is the only output I get:
What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.
Anyone know why i dont see all of the events?
Thanks Jamie
Heres the code:
Code Snippet using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Tasks.ScriptTask; namespace Package_API { class Program { static void Main(string[] args) { Console.WriteLine("Starting..."); Package p = new Package(); p.InteractiveMode = true; p.OfflineMode = true; // Add a Script Task to the package. Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence"); TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask"); // Run the package. DtsEvents events = new DtsEvents(); p.Execute(null,null,events,null,null); //p.Execute(); if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend) Console.WriteLine("Package failed or abended"); else Console.WriteLine("Package ran successfully"); Console.ReadLine(); } } }
I have a sotred procedure using a cursor which sort data and create subsets based on same oid and same decision_date columns, for each subset I am trying to order them and affect values 1, 2,... for each record in a different subset. The stored procedure seems to work very well and fast against a small tables (during tests). When used against a table with 200,000 records it takes more than 24 hours... I am looking for a help to make it work faster, thanks guys. here is the stored procedure:
CREATE PROCEDURE ORDERING_TEST_PROCEDURE AS
DROP TABLE REVIEWS_TEST_TABLE
SELECT OID,DECISION_DATE,DECISION_ID,VOTES_REQUIRED, ORDERING INTO REVIEWS_TEST_TABLE FROM DECISION_FLAGS WHERE VOTES_REQUIRED IN ('1','2','3') and FINAL_DECISION_CODE NOT IN ('0800','0810','0840') and DECISION_TYPE_CODE < '0100' ORDER BY OID, DECISION_DATE,VOTES_REQUIRED DESC
CREATE INDEX OID_DECISIONID_DATE_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID, DECISION_DATE, OID)
CREATE INDEX VOTESREQUIRED_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (VOTES_REQUIRED)
CREATE INDEX DECISIONID_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID)
set @oldoid = 'space' set @olddecision_date = 'space' set @oid = 'space' set @decision_date = 'space' set @votes_required='space' set @Decision_id = 'space' set @ordering = '0' set @ordering_count = 0
declare review_test_cursor cursor for select oid,decision_date,votes_required,ordering,decision _id from CRIMS.dbo.reviews_test_table order by oid,decision_date,votes_required asc open review_test_cursor fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
while (@@fetch_status = 0 ) begin if @oldoid <> @oid or @olddecision_date <> @decision_date begin set @oldoid = @oid set @olddecision_date = @decision_date set @ordering_count=0 end update reviews_test_table set ordering = CAST ((@ordering_count + 1) as VARCHAR) where decision_id = @Decision_id set @ordering_count = @ordering_count + 1
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id end
close review_test_cursor deallocate review_test_cursor
/*********************************/ UPDATE DECISION_FLAGS SET ORDERING = '0'
UPDATE DECISION_FLAGS SET DECISION_FLAGS.ORDERING = TEM.ORDERING FROM DECISION_FLAGS DEC, REVIEWS_TEST_TABLE TEM
Hi, The sqlsrv.exe process has been consuming a high number of cpu cycles and time and seems not be decreasing at all. Can somebody point me in the right direction as to how to identify the bottleneck and resolve the same at the earliest
Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.
I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.
Now I want to write that recordset to a flat file.
There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.
Anyone have any ideas on how to do this in a script transformation?
There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.
We have a webservice, which we are able to consume in the SSRS report. Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension. We did this and just changed the connection string from http to https.
But when we try to run the report, we get the following error message. Copied from BIDS.
TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. Failed to prepare web request for the specified URL. ------------------------------ ADDITIONAL INFORMATION: Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions) ------------------------------ The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System) ------------------------------ The remote certificate is invalid according to the validation procedure. (System) ------------------------------ BUTTONS: OK ------------------------------
Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).
This is my SProc: CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime /* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT AS SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname /* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount GO And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue Dim con As New SqlClient.SqlConnection con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime" myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered) myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int) myParam.Direction = ParameterDirection.Output myCommand.Parameters.Add(myParam) myCommand.Connection = con con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected numberParts.Text = rowCount.ToString con.Close() End Sub
Dear All,This is a query surrounding a problem I encounteredyesterday.In SQL Server, it is possible to write a procedure thathas one or more select statements in it.The results from these select statements will all beindividually returned to SQL Query Analyser where theycan be viewed in "grid" views. Also, these individualresults sets can be consumed by eg ADO.NET by steppingthrough each results set in turn and processing therespective results.My question is, can you do the same in a SQL Serverprocedure? ie:Create Procedure Proc1ASbeginselect Col1, COl2from Table1select Col1, Col2, Col3from Table2endCreate Procedure Proc2ASbeginexec Proc1endCan both/either of the results sets from Proc1 beconsumed by the calling procedure Proc2?I can see that you could design the procedures up-front to do almost anything without consuming theresult sets in this way, but if the proceduresreturning the results sets are already built andin use in other places (for instance in client code),can they be re-used on server-side SQL procedures?Thanks in anticipation!Paul.
Assume I've created a web service named 'SaveActivityData' for some SQL 2005 database on some remote server. Also assume that the stored proc takes two parameters; the first is an integer value and the second an XML value.
1) Is it possible for a stored procedure on a completely different SQL 2005 server to consume this web service?
2) If the above is possible, could someone show me an example of the T-SQL syntax required to consume this web service, passing the two required parameters? In your example, you can make up whatever URL you like for the remote web service location.
I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.
I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following
I am very new to Reporting Services. What I would like to know is if there is anyway for me to consume the raw data on a specific report from within excel. That is, can I call a report from reporting services directly from excel.
Maybe someone can outline the supported interaction between excel and reporting services for me. I have been having lots of trouble locating information about the interaction between these two.
I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?
or can anyone give me any example how to use/handle error output collection in SSIS?
Need a guidance on consuming the WCF service with complex type in the SSIS package. I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee { FName String; LName String; Age int }
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee { FName String; LName String; Age String Type EmployeeType; }
EmployeeType { type string; }
Now I get the error Web Method has unsupported arguments.
Hello All, I’m looking for a solution to timeouts that occur when I’m executing a stored procedure from my web application. Most of the SPs will run from 3 to 15 minutes, and, unfortunately, modifying/optimizing them isn’t an option at the moment. I tried setting the CommandTimeout to 0 with no luck. Unless, I didn’t use it properly. Here’s my code: 1 2 try 3 { 4 string dbConn = ConfigurationManager.ConnectionStrings["ConStringNTMTLDEV"].ToString(); 5 OleDbConnection connection = new OleDbConnection(dbConn); 6 7 lbl_SearchResult.Text = dbConn; 8 9 //OleDbDataAdapter adapter = new OleDbDataAdapter(); 10 OleDbCommand cmd = new OleDbCommand("SP_CallHistoryLookUp", connection); 11 cmd.CommandType = CommandType.StoredProcedure; 12 13 cmd.Parameters.Add(new OleDbParameter("@phoneNumber", "1234567890")); 14 cmd.Parameters.Add(new OleDbParameter("@email", "123@123.com")); 15 cmd.Parameters.Add(new OleDbParameter("@WebUser", "123")); 16 connection.Open(); 17 cmd.CommandTimeout = 0; 18 cmd.ExecuteNonQuery(); 19 cmd.Dispose(); 20 connection.Close(); 21 }catch(OleDbException ex) 22 { 23 lbl_SearchResult.Text += "<br/> Something went wrong </br>"; 24 lbl_SearchResult.Text += ex.Message.ToString(); 25 } 26 27 28 Is it possible to launch a stored procedure and close the connection without waiting for a result?Would the stored procedure still run on the SQL server? I’m using MSSQL 7. Would you have any examples that would solve this problem? Thank you for your help. R.
My Sql express will use up to 1G or more memory and never release. I had the same problem on SQL 2005 standard, I solved by adding /3G in boot.ini and turn on AWE. but it seems SQL express doesn;t support AWE. so how could I do here?
Hi, I have a scenario in which an application generates events upon updates to the database, which need to be reliably transmitted to consuming applications running a) other SQL Server instances and b) as remote web services.
Now,
1) I am planning to use SQL Server Notification Services to generate these events, and allow the consuming applications to subscribe to them.
2) The events will be sent to a ReceiveEvent service, which will then process the event and put it in the respective queues for the Subscriber (consumer) applications.
3) The services representing these consumer applications will then get activated, and sent the message across directly to the service broker on the consumer side in case of situation a - where there is a SQL Server on the other side
4) In case the other side is a web service, I plan to make a SOAP request directly from my queue handler, and depending on the response received, decide whether to commit or roll back that particular transaction.
Does this seem like a good solution? I am very new to the Service Broker, and I am still getting my hands dirty. Any comments/suggestions would be very highly appreciated!
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues. Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions: 1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server? 2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected problems when running both 2000 and 2005 SQL Server at the same database server? 3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
