I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :o
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :eek:
I am with the response time for a simple count on a fulltext search that is too slow.
Even using the most simple query on a good server (64 bit Dual Opteron 4GB Ram with high speed 16 raid disk storage)):
select count(*) from content_books where contains(searchData,'"english"') Takes 4 seconds to count the avg 500.000 resultsI have removed all the joins with real table data so that the query is only inside the fulltext engine..
I would expect this to be down to 4 milli seconds. Isn't it just getting the size of the "english" word result index?
It seems the engine is going through all the results because if a do a more complex search that returns less results the performance is better.
Any clues of how to do this faster? I never read the thousands of records BUT i need to count them...
Using SSMS 2012, we are experiencing extremely slow response times when opening SQL job steps to edit and also deploying SSIS Pkg's. Sysadmins have no problem. Users in the ssis_admin role have no problem. It's the rest of the users who have issues.
Hi,I have a table with about 70k rows. When I use open table --> returnall rows and then edit a field Enterprise Manager then says it has toreturn the entire result set for the update to take place.This can take a long time and makes editing data a slow process. Isthere any way to get Enterprise Manager just to update the one row??Thanks Tom
We're trying to set a new standard at my office, of using GUIDs as database ID fields - all new tables in all databases will have a GUID as the ID field, using the UniqueIdentifier data type.
The problem that we are running into, is that different applications interpret the UniqueIdentifier data type differently - ADO 2.x interprets it with the opening and closing { } braces. ADO.NET does not include the { and } in the GUID structure, SQL Server Query Analyzer does not include the { } braces, and SQL Server Enterprise Manage does include the { }
The problem with this is in two parts: 1) VB6 does not have a GUID structure or data type, so we are treating GUIDs as strings... but VB6 doesn't recognize "{012345678-abcd-ef01-2345-6789abcd}" and "012345678-abcd-ef01-2345-6789abcd" as the same string 2) when sending a URL link through an email, the { } braces break the link - in all email applications that we have tested. This include Groupwise 6, Groupwise 6.5, IPSwitch Web IMail, Outlook 2000, and Outlook Express (IE 6).
We're becoming very frustrated with the problems at hand, and need to know how others have worked around these problems. Please respond with any kind of advice or any real life situations where you have encountered this and found a solution, etc.
I am executing an update query in access as follows.... UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = IIf([DtePrice1]<=CDate(Date()),[AmtPrice1],IIf([DtePrice2]<=CDate(Date()),[AmtPrice2],0)) WHERE (((tPriceRaw.IdPriceType)=8));
Here's my stab at converting this to a stored proc.
UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = SELECT CASE WHEN DtePrice1 <= GetDate() THEN AmtPrice1 ELSE (CASE WHEN (CASE DtePrice2 WHEN '00/00/0000' THEN '1/1/1900' END) <=GetDate() THEN AmtPrice2 ELSE 0 END) END WHERE tPriceRaw.IdPriceType=8
I kknow the Join Statement works becasue it does work in the context of a select. I also know the case logic is working becasue I am using it in an Insert query elsewhere.
Can't quite pull it together though. I am getting an error at Line #1 "(". I gave it a good try though before I resorted to throwing it into the forum. I am sure it is a stupid syntax error. Any help would be appreciated.
Two problems / questions I could do with some help on please......
1). How do you clear a spreadsheet before loading it ? I am sending a spreadsheet by Email on a regual basis. Each time I run my SSIS package the data is appended to that of the previous run. I need just the contents of this data load to populate the spreadsheet each time. I have tried various methods / techniques for achieveing this so I just need to know the accepted method. Sorry if this is very basic ( as it should be ?!) but I seem to be missing the point somewhere.
2). I have tried to resolve issue 1 above by having an empty 'template' spreadsheet and copying that one over the top of the previously populated one. I get an error saying that the source spreadsheet ..'is in use by another processes.' Amusingly, as a test, I can delete it but I cannot copy it !
Are there any decent workthroughs on working with destination excel speadhseets ? Im struggling.
Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.
I have two tables, 1) Ticket_Purchase, 2) Flight.
The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID
The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination
I am trying to create a query that will tell me: On which flight were the most first class tickets sold?
There are only two types of classes; 'E' for economy and 'F' for First Class.
So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?
The SQL I wrote for the above is:
Select Class_of_Ticket, Flight_Number From Ticket_Purchase Where Class_of_Ticket = ('F') Order By Flight_Number;
And it produces:
Class_of_Ticket Flight_Number --------------- ------------- F 1 F 1 F 1 F 2 F 2 F 3 F 3 F 3 F 3 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 5 F 5 F 6 F 6 F 6 F 7 F 7 F 8 F 8 F 8 F 9 F 9 F 9 F 9 F 9
(38 row(s) affected)
Rather I would like it to produce: First_Class_Seats Purchased Flight_Number --------------------------- ------------ 14 4
I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.
Thank you for any help you could offer. It would be much appreciated.
Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.
Hello! I have an SSIS package that basically collects Excel files from an FTP server, deposits them to a file share on a SQL server and then processes each Excel file into a SQL 2005 database using the ForEach enumerator.
The package runs flawlessly when executed directly on the SQL server. The problem starts when I set this package to run via the SQL Agent. I have looked through these forums and other places and have done what this article suggested http://support.microsoft.com/kb/918760; changing my package protection to "Dont Save Sensitive" and setup the package to store its configuration in a SQL table. Despite all this I still continue to get this error when the package is run via the Agent:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelSourceFile" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
The Excel files are not password protected, no funky settings, nothing complicated, its just a plain Excel file with a few rows of data on one worksheet. The package transfers the files from the FTP server to the share on the SQL server with no problem, I can see that the files exist on the share, so it isnt a case of the Excel connection manager not finding any files to process.
The Agent account is running under administrator privileges and has full access to the share, so it shouldnt be a permissions issue either.
I have one report (R1) which includes a list of ports ("=Fields!Port_Name.Value")
Another report (R2) supplies details for individual reports and has a parameter named PortDWIDPortName which gets port names from the same dimension as those in R1. So in the field properties of the Port field in R1, I selected Navigation > Jump To Report and entered PortDWIDPortName = Fields!Port_Name.Value (also tried changing .Value to .Key or .UniqueName) but although it jumps to report R2 it does not enter a value for the parameter (ie leaves it blank or displays <Select Value> depending on whether multivalue or not).
Does anyone know the right syntax to pass the parameter ? If not, does anyone know to view what Jump To Parameter is returning so I can figure how to doctor it!
All,I have a problem regarding SQL Server 2000 SP3,I have SP that calls other SP and it inserts about 30,000 records as atime,in the development environment (MS Windows 2003 Enterprise, 256 RAM,3.0 GHz Intel Processor) takes about 6 seconds to run this SP.But, with the same Software but, 2.6 GHz Intel and 1 GB Ram, it runsvery slow it takes more than 135 Seconds to run,I have read a lot of articles about expanding the SQL Memory and giveit a higher process privilege but, with no use,I don't know where the problem is, do you have any idea about what isthe problem?Thank you in advance,MAG
Can anyone give me some guidelines as to when to chose JOINS over returning multiple resultsets in a strored procedure.. For eample, I have two tables, Orders and OrderDetails, which are linked by a primary key field. There can be orders w/o a corresponding record in orderdetails. 1.) I can return all orders and their details using a stored preocedure that has: SELECT o.order_id as OrderId, o.customername, od.order_id, od.orderdate FROM orders AS o LEFT OUTER JOIN orderdetails AS od ON (o.order_id=od.order_id) 2.) I can do the same by returning two results sets in a different stored procedure: SELECT order_id, customername FROM orders SELECT order_id, orderdate FROM orderdetails I think the client processing time for the second option will be slightly less, because the resultset I need to filter will only be as big as the orederdetails table (it will not include records for orders that have no details). Regardless, I think this would only make for a small performance gain, so if Option 1 is better in Database performace, I would probably go with that. I assume the method to choose also depends on table size and # of JOINS. Any guidance would be appreciated. Thanks, Al
I have a openquery query like this Select * from openquery([db2],'Select * from tableA')
To only return and process records for a given date range I changed it to be something like this
Select * from openquery([db2],'Select * from tableA') where datefield > '06/06/2001'
While this works fine, my question is that does it copy all the records from the db2 server to the sql server before filtering them. I think it does. The db2 table will have over 1,000,000 records eventually, and the sql server will use records for a given day/date range only.
When using Named Pipes the issue is no longer there.
On massive batch inserts we sometimes get a long pause at the end of one insert and before begining the next one. Example:
1000 inserts in the same table and then repeat. This will work fine for 3 or 4 iterations, then pause during the 5th iteration for up to 40 seconds and then simply continue.
When this exact same procedure is done using Named Pipes as the connection method this never happens.
While this is happening neither the server or the workstation is doing anything, 0% CPU, 0% network, it just sits there.
All this using the SQL Native Client 2005 and ADO.
I created two tables one is based on partition structure and one is non-partition structure.
File Groups= Jan,Feb.....Dec Partition Functions='20060101','20060201'......'20061201' I am using RIGHT Range in Partition function. Then I defined partition scheme on partition function.
I have more than 7,00,000 data in my database. I checked filegroups and count rows. It works fine.
But When I check the estimation plan time out for query it is same for both partition table and non partition table.
HelloI have a VB6 application using classic ado (MDAC 2.8) for connectingms sql 2000 server. Application uses a lot of server side cursors. NowI want to switch to ms sql 2005 server but I have noticed very seriousperformance problem. Sql profiler results of execution of followingcommands:declare @p1 intset @p1=180150131declare @p3 intset @p3=1declare @p4 intset @p4=16388declare @p5 intset @p5=22221exec sp_cursoropen @p1 output,N' Select ... from ... where .... orderby ...',@p3 output,@p4 output,@p5 outputselect @p1, @p3, @p4, @p5on sql server 2000:CPU: 234Reads:82515Writes:136Duration:296and on sql server 2005:CPU: 4703Reads:678751Writes:1Duration:4867Both databases are identical, the servers runs on the same machine(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forumsI've read that Microsoft doesn't recommend using server side cursorson sql 2005 but is there any way to increase performance to someacceptable level?thanks in advanceszymon strus
I am new to SQL Server 2000. I am eager to learn what factors/parameters are key for obtaining good retrieval performance of SQL Server 2000 (prompt response to user query).
I recall that someone told me that a recordset with asOpenStatic cursor type has higher speed than that of a recordset with other cursor types.
Is this true or false. Are there really some key parameters for perfomance tuning .
I have a VB6 application using classic ado (MDAC 2.8) for connecting ms sql 2000 server. Application uses a lot of server side cursors. Now I want to switch to ms sql 2005 server but I have noticed very serious performance problem. Sql profiler results of execution of following commands:
declare @p1 int set @p1=180150131 declare @p3 int set @p3=1 declare @p4 int set @p4=16388 declare @p5 int set @p5=22221 exec sp_cursoropen @p1 output,N' Select ... from ... where .... order by ...',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5
on sql server 2000:
CPU: 234 Reads: 82515 Writes: 136 Duration: 296
and on sql server 2005:
CPU: 4703 Reads: 678751 Writes: 1 Duration: 4867
Both databases are identical, the servers runs on the same machine (Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums I've read that Microsoft doesn't recommend using server side cursors on sql 2005 but is there any way to increase performance to some acceptable level?
If I have a *.dts file, and want to change the originating server database from SERVERA DB_A to SERVERB DB_B, is there an easy method to do this, besides editing the file in the GUI form?
I need help transferring a SQL database. I currently have my website database on a SQL 4.0.27 (vdeck) and want to transfer it to a new server (VPS) which has SQL 4.1.2
I have imported the database to my desktop computer. It will not let me import to the new server. I have to edit/reconfigurate.
Any suggestions, comments, advice, help would be greatly appreciated.
hey guys, is it possible to add or edit columns once there is data in the tables?? not the data in the columns but the columns themselves.. for example lets say i have a table with 4 columns and for some reason or another, i want to add a 5th or 6th column after data has already been entered, like a few months down the line..
I have scheduled a T-SQL job that runs every morning using Enterprise Manager. Now I want to change the SELECT and UPDATE statements that this job runs, but I can't find anywhere to edit a job that has already been scheduled. Any help would be appreciated.
can someone help me why it produces an error.... error is: ERROR 22001 Microsoft ODBC SQL Server Driver SQL Server STRING or data of BINARY was cut short. ERROR 01000 Microsoft ODBC SQL Server Driver SQL Server statement was ended. This is my code here for editing a record....
Protected Sub Button_save2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_save2.Click '||||| Create string connection Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes" '||||| Create connection object Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn) '||||| Open connection MyConn.Open() '|||| Create odbcCommand object Dim Update_record As New Odbc.OdbcCommand("UPDATE TM0001 SET TM0001.syain_name = ?, TM0001.syain_pass = ?, TM0001.office_id = ?, TM0001.birth_date = ?, TM0001.empl_date = ?, TM0001.user_iden = ? ", MyConn) Dim hireYear As String Dim hireMonth As String Dim hireDay As String Dim date_hire As String hireYear = DropDownList_hire_yr.Text hireMonth = DropDownList_hire_mo.Text hireDay = DropDownList_hire_day.Text date_hire = hireYear + "/" + hireMonth + "/" + hireDay '|||| Add command parameters Update_record.Parameters.Add("@P1", OdbcType.Char, 8).Value = TextBox_id.Text Update_record.Parameters.Add("@P2", OdbcType.Char, 20).Value = TextBox_name.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 20).Value = TextBox_pswd.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 40).Value = DropDownList_office.SelectedValue Update_record.Parameters.Add("@P3", OdbcType.Char, 2).Value = date_hire Update_record.Parameters.Add("@P3", OdbcType.Char, 10).Value = TextBox_bday.Text Update_record.Parameters.Add("@P3", OdbcType.Char, 1).Value = DropDownList_iden.SelectedValue '|||| Execute command Update_record.ExecuteNonQuery() '|||| Close connection MyConn.Close() End SubEnd Class
I want to get (SELECT??) data from a db (SQL), edit the data (+1) and then update (UPDATE??) the table with the edited value. How do I get the value from the db and then edit it and then update the field in the db?? Does someone has an example so I get on the way??
Is there anyway possible to edit table design with data already entered in the table.If not, is there anyway I can cut and paste the info back in. Ive tried importing to access and then back to SQL, however, when i tried to view my table design in ASP.NET webmatrix, it gave me an error.
I've just created a new maintenance plan (sql2k sp2) and attempted to re-enter it to make a change. right click - choose properities (or double click)...I see the dialog box 'flash' very briefly but it's not making itself available for editing.
I've deleted and recreated the plan ... same result.
