how to get all data from database on sql server 2005

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - custom subtotals in a matrix I have a similar situation I think my shed more light on the OP's Question. If I have a database of fruit and stores, I can easily link the returns from a dataset into a matrix and get the number of each fruit at each store, and subtotals. Apples Oranges Bananas Total Store 1 5 3 1 9 Store 2 5 2 1 8 Store 3 4 8 4 16 Store 4 8 1 2 11 Store 5 6 7 7 20 28 21 15 64 What I want however, is some way to find the percentage of each fruit at each store. Apples Oranges Bananas Total Store 1 5 ( 56% ) 3 ( 33% ) 1 ( 11% ) 9 Store 2 5 ( 63% ) 2 ( 25% ) 1 ( 13% ) 8 Store 3 4 ( 25% ) 8 ( 50% ) 4 ( 25% ) 16 Store 4 8 ( 73% ) 1 ( 9% ) 2 ( 18% ) 11 Store 5 6 ( 30% ) 7 ( 35% ) 7 ( 35% ) 20 28 ( 44% ) 21 ( 33% ) 15 ( 23% ) 64 This would be easy if there was a way to call the Total cell at the end of each row, (# apples in the store / store total) but if that is possible I am at a loss to find it. Tag: how to get all data from database on sql server 2005
  - 2
    - Duplicate records sureshv wrote: source is a text file.Is it possible to use sort as i am deciding distinct based on four columns. For each row coming i will be looking at four columns to decide if the records are distinct. Yes, pick your four columns and sort by them. Then click the remove duplicates. It will remove duplicates based on the chosen sort columns. However, do you need to pick one row over another, or can you discard any row without knowing which row you are keeping Tag: how to get all data from database on sql server 2005
  - 3
    - Estimating growth of mdf file size... You will have to add the size of the indexed columns in addition. HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: how to get all data from database on sql server 2005
  - 4
    - How do I write multiple pipeline buffer to multiple targets based on a calculated value in the pipeline buffer J.A.J. wrote: I apologize for not being explicit enough. Let me try again. For example Source rows: Row 1, Qty = 5, max_qty_value = 3 Row 2, Qty = 25, max_qty_value = 5 Target rows need to be as follows Row 1, Qty 3 Row 2, Qty 2 Row 3, Qty 5 Row 4, Qty 5 Row 5, Qty 5 Row 6, Qty 5 Row 7, Qty 5 Obviously there are many more columns but this should hopefully describe the situation a little better. How can I use the split transform or multicast transform If I don't know how many rows I need to generate for each source/input row I have in the pipeline thanks Err... OK I still don't really understand but I know for sure that given the very specific nature of this requirement you are going to need a script component. It will be an asynschronous component hence you should search out info on asynchronous components. There is lots on this forum and out on the web. http://search.live.com/results.aspx q=ssis+asynchronous+script&form=QBRE Basically you will be creating new rows so you will have to override the CreateNewOutputRows() method in the script component. -Jamie Tag: how to get all data from database on sql server 2005
  - 5
    - Performance issues with large fact tables(1.5 mill rows) and large dimensions (one dimension 20k another 50k) Did you update the rowcount for all the attributes & measures before building the aggregation. SSAS uses these counts to build the aggregation. If it is 0 or set to less number than the actual count, your aggregation would not be optimal. you can update it manually or use BIDS helper ( http://www.codeplex.com/bidshelper ) from within BIDS Tag: how to get all data from database on sql server 2005
  - 6
    - Assigning a particular flat file record to a string variable The data flow on its own operates on a row by row basis. That's what it's designed to do, and very fast, by the way. Tag: how to get all data from database on sql server 2005
  - 7
    - 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: how to get all data from database on sql server 2005
  - 8
    - 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: how to get all data from database on sql server 2005
  - 9
    - 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: how to get all data from database on sql server 2005
  - 10
    - 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: how to get all data from database on sql server 2005
  - 11
    - SQL EXPRESS TIMEOUT Hi, Their are some command you have to specify in your application like command.timeout=someno , here is a wonder full article on Time Out http://vyaskn.tripod.com/watch_your_timeouts.htm , revert back if your SQL Server gives you time out. Hemantgiri S. Goswami Tag: how to get all data from database on sql server 2005
  - 12
    - Cloud of Confusion: Running SSIS packages on 64 Bit SQL Server What type of data source is the Execute SQL Task pointed at Tag: how to get all data from database on sql server 2005
  - 13
    - 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: how to get all data from database on sql server 2005
  - 14
    - Constant CPU usage on server when using SQL Server 2005 Management Studio I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server): WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe Whenever the CPU usage of services.exe jumps up, almost all of these processes remain idle. The only two processes that occassionally use some cycpes are WmiPrvSE.exe, dwm.exe's svchost.exe, and taskeng.exe's svchost.exe. But they only jump up to about 1% while services.exe jumps up to 50%, and again, those 3 seem random, though after watching it for a couple minutes I have yet to see any of them take any cycles while services.exe was idle (it sticks at around 35-50% for 4-5 seconds and then drops to idle for 1-2 seconds). Let me know if there is anything else I can provide to help with this. Thanks! Tag: how to get all data from database on sql server 2005
  - 15
    - SP_Change_Users_Login If I understand the problem, you have the same user (i.e. the SID is the same) on all databases, correct You can find the SID using the following query: SELECT sid FROM sys.database_principals WHERE name = ' user_name ' And then create a login with the corresponding SID and the password policy setting you need, for example: CREATE LOGIN [ login_name ] WITH PASSWORD = ' password ' , CHECK_POLICY = OFF , SID = 0x<SID_IN_BINARY_FORMAT> Because the users are mapped to logins via the SID, all database users that are mapped to the same login will be fixed. Please let us know if this information helped or if you have any additional questions. Thanks, -Raul Garcia SDE/T SQL Server Engine Tag: how to get all data from database on sql server 2005
  - 16
    - 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: how to get all data from database on sql server 2005
- Next
  - 1
    - Linked server memory consumption 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 Tag: how to get all data from database on sql server 2005
  - 2
    - Querying 1 member from 2 hierarchies in same dimension? Thanks! I must have been mixing up the two definitions. Tag: how to get all data from database on sql server 2005
  - 3
    - Visual Studio closes when data source has changed csi_hugh wrote: Just curious, how do you roll back a package to an earlier version I've never done that before. As long as you can do it without opening the package, that might have worked too. Visual Studio integrates nicely with source control providers like VSS and TFS, and this integration works for SSIS projects just as it does for more "traditional" dev projects like VB and C#. This is where you would get "rollback" capabilities; it's not built into SSIS natively. You can also simply copy and rename your project folder periodically, to maintain a "rolling snapshot" of packages if you don't have a source control repository handy. It's a kludge, but can still save you sometimes... Tag: how to get all data from database on sql server 2005
  - 4
    - 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: how to get all data from database on sql server 2005
  - 5
    - Need drillthrough for just one month--but cube goes back two years In this case limit your view to only the data you'd like to see in the drillthorough. Edward Melomed. -- This posting is provided "AS IS" with no warranties, and confers no rights. Tag: how to get all data from database on sql server 2005
  - 6
    - Data Mining Client for Excel Actually the answer is Yes and No. Yes you can mine the data directly in your Excel worksheet, but no, you can't do it without a connection to SQL Server Analysis Services. With the data mining addins you can mine data in worksheets or in databases, but the "mining" takes place on an Analysis Services server. So you need a connection, regardless of where the data is present. Many people are getting licences for their local machines for SQL Server to do this disconnected. HTH -Jamie Tag: how to get all data from database on sql server 2005
  - 7
    - microsoft decision tree algorithm i'd like to further clarify my previous question for regression tree building: which method is used for splitting a continous variable: information gain or regression approach. The first splitting method (information gain) first bins a continous attribute and turns it into a categorical variable, selects a value which gives highest informtion gain; the second method, according to http://msdn2.microsoft.com/en-us/library/ms175312.aspx , the splitting is performed at the point of non-lineararity(statistical method ). I just wonder which method is used by Microsoft Decision Trees for splitting in the case of a continous variable. Tag: how to get all data from database on sql server 2005
  - 8
    - 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: how to get all data from database on sql server 2005
  - 9
    - Sql 2005 Management Studio The Database Publishing Wizard is quite flexible in scripting. I haven't tried if for the task you want, but it may be worth a look-see. Database Publishing Wizard http://www.microsoft.com/downloads/details.aspx familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en Tag: how to get all data from database on sql server 2005
  - 10
    - 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: how to get all data from database on sql server 2005
  - 11
    - 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: how to get all data from database on sql server 2005
  - 12
    - Synchronization fails That worked wonderfully! Thank you for posting this fix! Teresa Tag: how to get all data from database on sql server 2005
  - 13
    - Help on global variable for insert statement I'm still confused. Please help me clarify. You wish to put all the contents of three textboxes into a single field in the table "Textbox1, 2, 3 Needs to insert values into the first column" If that is the situation, why not just have one textbox -not three Tag: how to get all data from database on sql server 2005
  - 14
    - 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: how to get all data from database on sql server 2005
  - 15
    - Cannot connect to SQL Server DB Engine...Instance Contains SQL Express in Name Thank you for your interest. I am trying to connect to the database engine on the PC I installed SQL Server on. I thought it was only connecting to SQL Express so I removed that connection from Mgt Studio. I cant seem to get rid of sql express (reference), but now I cant even connect to that. There are other SQL Servers on the network, however, I am waiting for permissions to connect to them. For now I just want to be able to connect to the engine on my PC. I hope this is making sense. THanks Tag: how to get all data from database on sql server 2005
  - 16
    - 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: how to get all data from database on sql server 2005

maxicream

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

Top

Re: how to get all data from database on sql server 2005

Louis Davidson

What do you mean Like scripts that insert data Or just to duplicate a database

You can duplicate a database easily using backup and restore.

You can compare the data in two databases using Red Gate's Data Compare Tool http://www.red-gate.com/products/SQL_Data_Compare/index.htm.

Top

Re: how to get all data from database on sql server 2005

Burldo

If you are trying to copy the database to build another for deployment, you can try this sql wizard. It will create the scripts needed to create or deploy. Hope this helps.

http://www.microsoft.com/downloads/details.aspx familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Regards, Burl

Top

Re: how to get all data from database on sql server 2005

Arnie Rowland

Try the Database Publishing Wizard:

Database Publishing Wizard
http://tinyurl.com/35h5dh

Top

how to get all data from database on sql server 2005

Re: how to get all data from database on sql server 2005

Re: how to get all data from database on sql server 2005

Re: how to get all data from database on sql server 2005

Database Publishing Wizardhttp://tinyurl.com/35h5dh

Database Publishing Wizard
http://tinyurl.com/35h5dh