Linked server memory consumption

SQL Server Database Engine

Index ‹ SQL Server

- Previous
  - 1
    - Duplicate records You can do this with a synchronous non-blocking script component pretty easily, especially if the data is already sorted by the rows you want to check. The following code concatenates all your columns that you want to test for uniqueness into one string "key". If the key of the current row is different from the key of the previous row, then you redirect it to the output. Otherwise it gets dropped. This is so fast and simple that you should try to sort your data as it comes from the source in the order of these columns. Code Snippet Dim PreviousKey As String = "" Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim ThisKey As String = Row.FirstName + "_" + Row.LastName + "_" + Row.LastName + "_" + Row.EmailAddress If ThisKey <> PreviousKey Then Row.DirectRowToOutput0() PreviousKey = ThisKey End If End Sub If the data cannot be sorted, then instead of comparing ThisKey to PreviousKey, you would check for the existence of the current key in a hash table. If it doesn't exist, you redirect the row and add it. This will get slow if you have lots of unique values and you have to have enough memory to hold all that data. Tag: Linked server memory consumption
  - 2
    - Estimating growth of mdf file size... Hi, how can I estimate the mdf file size's growth as record inserted Tag: Linked server memory consumption
  - 3
    - How do I write multiple pipeline buffer to multiple targets based on a calculated value in the pipeline buffer OK. Thanks for pointing me in the right direction. I'll see if I can figure something out. Tag: Linked server memory consumption
  - 4
    - Performance issues with large fact tables(1.5 mill rows) and large dimensions (one dimension 20k another 50k) Yes I did update the row count while proceeding through the aggregate wizard. Thanks ps: The design of the cube is rudimentary. There is a fact table and 10 dimensions which are all derived from 11 separate tables. There are foreign keys between fact table and dimension tables in the data source view. I am using a powerful 64x version of both 2003 win server and sql server. I have no user hierarchies. I have one time dimension. No calculated measures. I am using molap and enabled proactive caching. I am logging performance with both perfmon and profiler and will post anything I observe. Tag: Linked server memory consumption
  - 5
    - Assigning a particular flat file record to a string variable Oh, you're kidding me! Thanks!! Tag: Linked server memory consumption
  - 6
    - Question regarding connections with MSAccess I have a Access 2002 (saved as 2K format) front end to a SQL Server (2000 I believe) and everything worked fine until I added 30 new fields to the main table. All of these fields are data type Bit and all are initialized to zero. I can add new records without any issues. The problem is that any time I try to update or delete a record I get the dreaded "Write Conflict..." msg in Access. If I use an older version of the system without refreshing the linked tables I have no problems. This behavior is also exhibited if I try to update/delete a record directly in the table so I know it is not something in my forms. Any help would be greatly appreciated. Ron Tag: Linked server memory consumption
  - 7
    - Percent Available Measure Problem My novices foray attempt into the field of BI is to build a simple Network Availability solution and is proving challenging. The problem is the Percent Available Measure and how to maintain its value as aggregation distorts its meaning when compared to the Device & Department Dimensions; 100% should remain 100% no matter the aggregation. I tried to convert the following SQL, which is used to return the Percent Availability of a device or group into a Calculation Member: Code Snippet (PercentAvailable*TimeDelta*1.0)/NULLIF(TimeDelta,0) My failed MDX attempt is this: Code Snippet ([Measures].[Percent Available]*[Measures].[Time Delta])/IIF(IsEmpty([Measures].[Time Delta])), 0, ([Measures].[Time Delta])) Please educate as to handling the Percent Available measure. I wish to know if it is possible to use it as a measure, maintaining its DB value, or would a calculated member be more appropriate. The Fact Table is below and contains sample at Daily intervals. Thanking you in advance, Ian Code Snippet AvailabilityFact FK_Time datetime FK_Devices int FK_Departments int Packets_Sent int Packets_Lost int Time_Delta float Percent_Available real Tag: Linked server memory consumption
  - 8
    - Overcoming the openquery 8000 char limit & problem with using Call mystoredproc() in open query Does anyone know why this statement would fail I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error. using an mdx query this works fine call AsmTest.Asm.Analysis.TestClass.NameMe() When using openquery i get an error select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' ) OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV". linked server definition USE master GO /* Add new linked server */ EXEC sp_addlinkedserver @server='NV', -- local SQL name given to the linked server @srvproduct='', -- not used @provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version) @datasrc='nvsifwfp', -- analysis server name (machine name) @catalog='ARTSDW' -- default catalog/database Tag: Linked server memory consumption
  - 9
    - Still getting: "Unable to connect to the remote server..." please help! Hello I'm getting the error messege: " Unable to connect to the remote server. The attempted operation is not supported for the type of object referenced" This message occurs with any report, including other ones that I build. It has this error when when under the http://localhost/reports/ .... path. And also when adding a report viewer component in Visual studio and running the web site project it still get the same error. These reports work properly if I browse anly to http://localhost/reportserver/ ..... ; I have completely removed IIS , & all SQL Server 2005 components, and re-installed. I am still receiving the same error. I saw some same problems with that in other websites and still can't get the right solution I tried to configure the Surface Area Configuration - remote connections to Local and remote connections also TCP & TCP and named pipes- still doesn't work. I tried to configure Sql Server Management studio Express - properties- security and to set SQL server and Windows authentication . still getting the same error I also tried this solusion by this site here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1756698&SiteID=1 But still getting the same problem. please help me to solve this problem I'm so desperate with that after searching so much for a solution. Thanks in Advance Miki Tag: Linked server memory consumption
  - 10
    - SQL EXPRESS TIMEOUT Thanks for the reply! See the problem does not occurr at the ADO nor the queries i run. It acctually happens before any code I wrote runs, it happens at LOGIN. I use the Login controll from VisualStudio 2005, this control sets it self and there are no properties as far as connections The only connection property can ser prior running any code is the one on the Web.config file which have the following string connection: This is my connection string on the web.config file on my site: prity much standard connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" Adding any Timeout option here dosen't do anything because ASP.net 2.0 ignores it. As far as I understand when i try to LOGIN on the site, the controll tries to open the ASPNETDB.MDF but SQL Server EXPRESS aparently dettaches it after some period of unactivity so the controll simply TIMES OUT this is because SQL Server Express have to RE ATTACH IT and of course this will take more than 15 seconds to complete... This is a very Idiotic functionality of SQL express far worse than that there is no way to configure the database unlike SQl server 2000 there is the Enterprise manager the one for SQL server Express is a read only and is USELESS. So the problem as you see is on the Sql server Expresss who after a period of Database inactivity it D-Attaches the ASPNETDB.MDF Database. and when a LOGIN request is received SQL server Express tryies to re attached and Suceeds after 30 seconds, but unfortunately that is too long for the Connection to wait an it craps out.. Any info on how to resolv this problem of hell please let me know THANKS in Advance Tag: Linked server memory consumption
  - 11
    - Cloud of Confusion: Running SSIS packages on 64 Bit SQL Server The problem disapeared after I upgraded to build 3175 (update 2 for SP2). Tag: Linked server memory consumption
  - 12
    - Does the actual update statement of a table affect Merge Replication? Suppose your using Merge Replication. 2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed. Will this break Merge Replications conflict tracking Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns. Tag: Linked server memory consumption
  - 13
    - Constant CPU usage on server when using SQL Server 2005 Management Studio Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home. -Shane Tag: Linked server memory consumption
  - 14
    - SP_Change_Users_Login Raul, I do have the same user (application user) in several databases. But when I check the SID in each database they do not match (they all have different IDs). How to proceed thanks a lot. Tag: Linked server memory consumption
  - 15
    - Partitioned View Problem Hi all, I am designing 3 p:artitioned views for 3 tables. Those tables grow up in 1.5 millions of rows per month (each one), so I decided to partition those tables monthly. The issue is that if I want to create the views with more than 256 months (256 tables) SQL Server says: 'Server: Msg 106, Level 15, State 1, Procedure Jugadas, Line 258 Too many table names in the query. The maximum allowable is 256.' Is there any workaround for this Another solution maybe PD1: I've tested with less than 256 tables and it works fine, I can update and query the tables (except for a couple of querys where I've got to join 2 or more of the involucred views in which case I got a similar error saying about a 260 table limit). Tag: Linked server memory consumption
  - 16
    - how to get all data from database on sql server 2005 how to get all data from database on sql server 2005 in to a file. File is include scripts can build another database as the same old database. include data in all table Tag: Linked server memory consumption
- Next
  - 1
    - Querying 1 member from 2 hierarchies in same dimension? I've tried to do this: select ({[Date].[Fiscal].[Fiscal Year].&[2004], [Date].[Calendar].[Calendar Year].&[2004]}) on 0 from [Adventure Works] but it errors with Members belong to different hierarchies in the function. Then I tried: select ({[Date].[Fiscal].[Fiscal Year].&[2004], [Date].[Calendar].[Calendar Year].[All Periods]}, { [Date].[Fiscal].[Fiscal Year].[All Periods], [Date].[Calendar].[Calendar Year].&[2004]}) on 0 from [Adventure Works] Two sets specified in the function have different dimensionality. Tag: Linked server memory consumption
  - 2
    - Visual Studio closes when data source has changed Just know that using SSIS in a source control environment *can* cause problems. The XML is not easily "diff-able" and as such sometimes versions get corrupt. Just something to consider. I know people use it regularly without problems though, too. Tag: Linked server memory consumption
  - 3
    - OLTP Database Design help for bank's customer table Hello,friends 1) CustomerID 2) FirstName 3) MiddleName 4) SurName 5) Title 6) Marital Status 7) Education 8) Occupation 9) Annual Income 10) Line of Business 11) DOB 12) Father Name 13) Mother Name 14) SpouseName 15) Gender 16) Email 17) MainTel 18) Home Tel 19) Passport Number 20)---------------------- 21)- - - - - - - - - - - 100)------------------- Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer. We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks). A single address includes address1/address2/city/country/state/pincode fields. In context of OLTP database, We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query. As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10) Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches. Tag: Linked server memory consumption
  - 4
    - Need drillthrough for just one month--but cube goes back two years Hey guys - I'm in exactly this boat and had trouble implementing this solution. Said that the rows inbetween the delta (i.e., more than one month old in your case) didn't connect to fact table, so dropped out from the cube entirely rather than just 'not being available for drill through'. I posted this as a separate thread at http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1532618&SiteID=1 Any help would be great! On large cubes, I would think this process has to be SOP... Tag: Linked server memory consumption
  - 5
    - Data Mining Client for Excel Is it at all possible to use this tool without any connection to SQL Server (mine data in Excel Worksheets only) Or is it required to connect to SQL Server From reading the documentation, one minute I get the feeling this is possible, then the next section I get a feeling it's not. Can anyone help Thanks. Tag: Linked server memory consumption
  - 6
    - microsoft decision tree algorithm If the target is a discrete attribute, it uses information gain. For continuous targets, it uses the regression approach Tag: Linked server memory consumption
  - 7
    - DBCC SQLPERF WaitStates / OLEDB OK ... how many of you run dbcc SQLPERF(waitstats) to display analyze SQL Server 2000 resource utilization How about sp_waitstats This is a very useful stored procedure that takes the sqlperf output and aggregates it over common wait types. If you've had to work on a sick SQL Server box, I've found this pretty useful to help pinpoint bottlenecks. While there's plenty written about the various wait types, there seems to a shortage of stuff written about acceptable or expected values! I am experiencing some performance problems with some procedures running on the EE of SQL 2000 w/SP3 on a server with 12 GB of RAM & 8 CPUs. Since no major locking issues stood out, I ventured to see what types of waits were occurring. Using both dbcc SQLPERF(waitstats) and sp_waitstats I found 98% of the waits where the OLEDB type. First I've never seen a single wait type be responsible for such a large %. For this type of wait, SQL Server is waiting for a OLE DB provider to service its request. High transactions or slow connection speeds in a distributed env are a big cause of these. This can be caused by slow authentication, outdate MDAC or incorrect DB Providers. (OK, I have to admit I haven't seen this type of wait before) Has anyone else encountered this type of wait I've seen various I/O Latch waits in the past but this is the first time I've seen OLEDB. If you've seen this, what were the steps taken to remedy this situation Thanks, JoeT Tag: Linked server memory consumption
  - 8
    - Sql 2005 Management Studio Thanks Arnie, The database publishing wizard doesn't help. It seems to be a scaled down version script wizard in sql 2005 mgmt studio. This is really frustrating. Table and object scripting in SQL 2005 appears to be broken...or for some reason MS has decided to remove functionality that was available in SQL 2000 Enterprise Mngr script wizard. I've also checked out the SQL Integration Services and it also doesn't do what i need. In fact the "transfer sql server object" task is really messed up because it doesn't take into dependencies and I keep getting errors. VERY UNUSABLE! Does anybody know if there is a way to generate PRIMARY key, FOREIGN Key, DEFAULTS, CHECK constraints, TRIGGERS and INDEXES in a separate script from the "CREATE TABLE" script in SQL Management Studio I'm even willing to consider 3rd party tools. Below is a simple example showing how i need to generate PRIMARY KEYS: Instead of this (generated in SQL 2005 Mgmt Studio): Code Snippet CREATE TABLE [dbo] . [tblAppointments] ( Field 1 .... Field 2 .... Field 3 .... CONSTRAINT [PK_tblCalendarAppointments] PRIMARY KEY CLUSTERED ( [SiteID] ASC , [ItemID] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 90 ) ON [PRIMARY] ) ON [PRIMARY] I need this (from SQL 2000 Enterprise Mngr): Code Snippet ALTER TABLE [dbo].[tblAppointments] WITH NOCHECK ADD CONSTRAINT [PK_tblCalendarAppointments] PRIMARY KEY CLUSTERED ( [SiteID], [ItemID] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO Instead of being included in the CREATE TABLE statement, I need it generated on it's own using the ALTER TABLE statement. PLEASE HELP! Tag: Linked server memory consumption
  - 9
    - DTS Package problem? I have set up a couple of DTS local packages to run ActiveX scripts creating XML files and copying them to our webserver (on the same domain). I can run them OK manually via the 'Execute' package command in the drop down list when I right click on them and also get the 'Package successfully run' message back on running them, but when I schedule them to run overnight I find the SQL Server Agent reports them as 'failed' . What am I doing wrong Tag: Linked server memory consumption
  - 10
    - SQL mail using external mail server Hello We are using SQL Server 2000 SP4 Standard Edition and would like to use the e-mail function to monitor certain alerts. However, an e-mail server (SMTP server) is not installed on that box, but on a different IP address within the network. I have set up the e-mail operator, enabled e-mail in SQL Agent and set the Mail Configuration in Support Services. However, on testing I get the following error : Error 18030: xp_test_mapi_profile : Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client. As I cannot install Outlook on this server, I am now stuck. Has anyone a suggestion how I can overcome this issue Thanks, Marcel Tag: Linked server memory consumption
  - 11
    - Synchronization fails I am experience that problem with that support code error message. However when I press and hold th "OK" key the email program initiates. Tag: Linked server memory consumption
  - 12
    - Help on global variable for insert statement No, sorry Arnie, Textbox 1,4,7 go in column 1 TextBox 2, 5, 8 Go in column 2 TextBox 3, 6, 9 Go in column 3 I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record. I think the code should go something like this: If Textbox2 = '' then oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')" Elseif Textbox3 = '' then oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')" And oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')" As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct. Thanks for the patience Arnie Tag: Linked server memory consumption
  - 13
    - Network share/Multi user deployment Can a SQL Server 2005 Compact Edition database file be deployed on a file share and be accessed by multiple (5-10) concurrent users This is a current scenario being implemented at several sites using MS Access databases. Each client would have the SQLce engine installed on it and would only access the database via a managed C# application. Tag: Linked server memory consumption
  - 14
    - Cannot connect to SQL Server DB Engine...Instance Contains SQL Express in Name How do you want to use your SQL Server I'm not sure you have uninstalled it or not However, to be sure that your SQL Server service is running, you must take a look at the SQL Server Configuration Manager. Check SQL Server services using that configuration tool which is can be found Start\Programs\Microsoft SQL Server 2005\Configuration Tools. If they are running, then you can access your SQL Server databases using SSMSE for example. If you have not it, you can download it from the following link. SSMSE is a GUI that makes you available to control your SQL Server environment using an user interface. https://www.microsoft.com/downloads/details.aspx familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en Tag: Linked server memory consumption
  - 15
    - continuous attribute & Complexity_Penalty I am having one question about discretization of continous attributes values. How does it work I need this information for my thesis. I have a continous attribut, namely SKS, with range 0-20. When I use Microsoft Decision Tree algorithm, this attribut split in SKS <= 18 and SKS > 18. I want to know how does it find 18 as a number to split not the other. One question again about Microsoft Decision Tree algorithm, about Complexity_Penalty parameter. How does it affect the algorithm For example, if I set this value=0.1 what does it mean and how does it correspond with growth tree Thanks a lot before for your kindness to answer my questions.. :-) Tag: Linked server memory consumption
  - 16
    - Deploying subscriptions on different server Hi, I have three environments which are exactly the same. A dev, prod and backup server. Everyone has its own installation of SQL. What is the best practice to re-use the subscriptions for the other environments I know I could restore the test database but I would prefer not. Thanks in advance for any input. Best regards, Jam Tag: Linked server memory consumption

Hery Susanto WR

Hi all,

I have two database server, named it DB_A and DB_B. I want to establish a linked server of DB_A in my DB_B. There's a script in DB_B that queries data from DB_A but only execute periodically. Does linked server consume large memory or CPU when it didn't use

Thanks in advance,

Hery

Top

Re: Linked server memory consumption

Tom Phillips

No. A linked server, not in use, has no affect on the server at all.

Top

Re: Linked server memory consumption

Hery Susanto WR

Thanks Tom for your reply.

Best regards,

Hery

Top