copying a DB from one server to another

SQL Server Tools General

Index ‹ SQL Server

- Previous
  - 1
    - Why can't I use columns based on case down in my where clause I am writing a fairly simple sql, and I would like to write something like Code Snippet select firstname as firstname, case when firstname = 'Peter' then 'yes' else 'no' end as whatever from MyTable where whatever = 'yes' And this should then select out the rows where column number 2 is 'yes'. It doesn't work, and I have to copy the firstname = 'Peter' into the where clause. But why Tag: copying a DB from one server to another
  - 2
    - The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data) Following article talks about the Jet database engine installation: http://support.microsoft.com/kb/239114 Basically, if your system is XP SP2, you should have MSJET40.DLL and msjetoledb40.dll on your system. If you could not find those files on your system, you could install WindowsXP-KB829558-x86-ENU.exe Tag: copying a DB from one server to another
  - 3
    - Service accounts, local admin, and sysadmin question! Yes, there is a BUILTIN\Administrators login on the server, with sysadmin privs. There is also a separate login for the SQL Server agent local administrator account. Perhaps this is the problem, the fact that the administrator account has its own login AND is part of the BUILTIN\Administrators group Because the separate login does not have sysadmin privs checked off. I think this separate login got added by accident when I scripted all the logins from a 2000 box to the 2005 server, and even though it in 2000 it is clear it is a login via group membership, the script added it like a separate login. Q. I suppose it would be safe to remove this login, yes Thanks Tag: copying a DB from one server to another
  - 4
    - Incorrect Totals This seems like a good scenario for "measure expressions" - if you inspect the Adventure Works cube, various Sales Amount measures are multiplied by currency rates. For example, you could create a new "sum" measure like [Value] on the MG1 "Units" field. Then, for this new measure, configure the "Measure Expression" property as: [Measures].[Value] * [Measures].[Price] This entry in Chris Webb's blog discusses measure expressions: ... But here's a cool cube design feature that doesn't cause any conflict of interest for me - measure expessions. The easiest way to explain what they are is to explain one scenario where they're useful. In AS2K, if you've tried to model currency conversion, you're probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can't then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house - they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define. ... Tag: copying a DB from one server to another
  - 5
    - Error: Subreport could not be shown. Hey, I just tried that with my nwifes non-MS-priviledged Account and I can’t find it either. Seems thats you will have to be invited to put feedback on the portal. There is a link where you can post your questions (also this for your invitation) mailto:sqlconne@microsoft.(here_c0/\/\es_a_D0t_c0m ) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Tag: copying a DB from one server to another
  - 6
    - Converting XML Datetime to SQL Datetime Hi Galex I tried: SELECT Assessmentxml . value ( 'xs:dateTime(/Abc/AbcDate)[1]' , 'datetime' ) AS [AssessmentExpectedStartDate] FROM dbo . Assessment I got: Msg 2365, Level 16, State 1, Line 10 XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime' Then I tried: SELECT Assessmentxml . value ( '(/Abc/AbcDate)[1] CAST AS xs:dateTime' , 'datetime' ) AS [AssessmentExpectedStartDate] FROM dbo . Assessment and got Msg 2370, Level 16, State 1, Line 2 XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'. What is the correct syntax to make it work Thanks Tag: copying a DB from one server to another
  - 7
    - how to get YYYYMMDD formate I see that you have broken the date into at least four columns (century, year, month, day) -that is a major design error and will 'haunt' you and continually cause problems in accessing, using, and presenting the data. It would be so much simpler if you stored the date as a single datetime datatype column. However, assuming that you can't correct this very bad design mistake, you can 're-create' the dates in a fashion such as this. (I recommend creating the dates in the format of 'yyyymmdd -that is a ISO standard format that will be properly accepted by SQL Server regardless of the local/regional date settings. SELECT convert( datetime, ( PYBTHC + right(( '00' + PYBTHY ), 2 ) + right(( '00' + PYBTHM ), 2 ) + right(( '00' + PYBTHD ), 2 )), 112 ) This assumes that PBYTHC is always two digits... (By with this incredibly bad design, that may be a falicious assumption...) Tag: copying a DB from one server to another
  - 8
    - Comparison of SQL Server Reporting Services to Access Reports, Crystal Reports, Cognos or other options Hello SQL Server Experts, Data Analysts, and Report Writers et al: re: Reporting Options with SQL Server I wanted to propose an offshoot to the pryor thread: Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part of SQL Server , especially as to extracting data from SQL Server into a report format I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product Thank you to all, and I hope this is a beneficial discussion to others. Hal1490 Hal9000 Tag: copying a DB from one server to another
  - 9
    - Changing the default database files path fails dear MVPs, I tried to loop through the inner exception of the failedOperationException and it turned out that the problem is that Database.Create(), Database.Drop() and connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'" ); cannot be contained in a transaction this answers my question about transactions thank you very much Tag: copying a DB from one server to another
  - 10
    - SSRS in Sharepoint, WrapperReportRenderingException/JobCanceledException for large Matrix (OLAP) reports Hi Prash That's spot on - thank you so much, really appreciated! Regards Alex Tag: copying a DB from one server to another
  - 11
    - AMO:Can any one knows hoe to rename a role and permissions to that role Hi All, I am working on AMO and vb.net.Here with this I am managing Analysis Server 2005 with AMO. My problem is , am able to create a role with role members . but when i try to rename a existing role i couldnt see any direct methods available with AMO.So what i did is first i created a role with normal procedure and then after , i am planning to drop the existing one. here when i did this i am getting some errors while dropping the role like meta data permissions are not able to drop. so could any one come across this kind of situation please help me. your help is valuable to the go further. Regards, vishweshwar.P Tag: copying a DB from one server to another
  - 12
    - RDA - multi table error for tracking Thanks Erik! I appreciate all your help. Tag: copying a DB from one server to another
  - 13
    - What is the easy way to do this?. Alter the table to add the new column Code Block CREATE TABLE #Test ( [TestID] INT IDENTITY(1, 1), [enddt] DATETIME, ) GO INSERT INTO #Test(enddt) SELECT '20071210' UNION SELECT '20071028' SELECT * FROM #Test GO ALTER TABLE #Test ADD [status] AS CAST((CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END) AS VARCHAR(20)) GO SELECT * FROM #Test GO Tag: copying a DB from one server to another
  - 14
    - Add SQL database - "login failed for user" error Hi I've installed 'SQL Server Express 2005 SP2'. When I select 'SQL database' from 'Add new item ' (either in Visual C# or Visual Web Developer express) I get the following errror: "Login failed for user'. My OS is Vista. I've tried reinstalling SQL Server express, but I still get the same error. Any ideas on how to fix this Thanks Jon Tag: copying a DB from one server to another
  - 15
    - MULTIPLE COLUMNS using and GROUP BY I hope there's someone that is able to help me. I'm new to SQL Server 2005 and have come across a pretty tough problem. I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though. Here's the SQL statement: "SELECT City_Name, Province_ID, City_PostCode FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude ORDER BY Province_ID ASC, City_PostCode ASC "; I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem. Can anyone help me Tag: copying a DB from one server to another
  - 16
    - Service Pack How would one determine the SP for Reporting Services. Is it different from the SP of SQL Server itselves. This is for 2005 Tag: copying a DB from one server to another
- Next
  - 1
    - Default message for data unavailability What version of SSRS are you using BobP Tag: copying a DB from one server to another
  - 2
    - Schema use in SQL2005 When I upgraded from SQL 2000 to SQL 2005 the upgrade process created a schema for each user, and set the default schema for each user to the schema the upgrade created. After I saw that behavior I created an SMO program to remove the schemas created and set the default schema back to dbo. Tag: copying a DB from one server to another
  - 3
    - SQL Server 2005 Standard will not install MSSQL default instance I installed SQL Server 2005 flawlessly yesterday. When login as "sa" through SQL Server Management Studio, I get an error message saying it cannot connect to my PC. I checked the MSSQL Server Service. It is not started and cannot be started because it's looking for a path that does not exist!. The folder in question is missing. C:\Program Files\Microsoft SQL Server\MSSQL\ The following folders do exist: C:\Program Files\Microsoft SQL Server\MSSQL.1 C:\Program Files\Microsoft SQL Server\MSSQL.2 I initially suspected this problem was cause by a previous MSDE 2000 installation. I uninstalled SQL Server 2005 & MSDE 2000. I re-installed SQL Server 2005. Same problem. I executed http://support.microsoft.com/default.aspx/kb/320873 to make sure all registries are removed and manually deleted C:\Program Files\Microsoft SQL Server\’s content Re-installed SQL Server 2005. Same problem. I do not know what do in order to get SQL Server 2005 up & running. All help is greatly appreciated. Frank Tag: copying a DB from one server to another
  - 4
    - Limited Remote Login db access These resources might help. Configuration - Configure SQL Server 2005 to allow remote connections http://support.microsoft.com/default.aspx scid=kb;EN-US;914277 Configuration -Connect to SQL Express from "downlevel clients" http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx Tag: copying a DB from one server to another
  - 5
    - Dynamically display/hide the parameter input I think another way of achieving this is writting specific RDL code. Tag: copying a DB from one server to another
  - 6
    - Shredding XML to Relational Data: Query Processor Out of Resources Hi there, I have an irritating problem which I just cant seem to solve. I am trying to retrieve the relational dataset from an xml column using the nodes method provided by SQL Server 2005. This shredding to relational data is done by one stored procedure which is triggered when the xml document is retrieved by a soap service. The stored procedure first inserts the xmldocument into an xml column after which its start shredding the data to relational data. The error I receive is: Msg 8623, Level 16, State 1, Line 139 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Below is a short code snippet of the stored procedure. This snippit only represents one sction. In total the stored procedure contains three more sections like the one displayed here. PRINT 'STAP2A: Update Home Data' UPDATE tb_Import_WoningGegevensRelationeel SET cv_aanwezig = q . cv_aanwezig , stadsverwarming = q . stadsverwarming , cv_combi = q . cv_combi , warmtemeter = q . warmtemeter , oppberging = q . oppberging , verhuurdatum = q . verhuurdatum , garage_carport = q . garage_carport , bouwjaar = q . bouwjaar , lift_aanwezig = q . lift_aanwezig , codebuitenruimte = q . codebuitenruimte , marktkenmerk = q . marktkenmerk , gebruik = q . gebruik FROM ( SELECT VHE . C . value ( '(./vhe)[1]' , 'nvarchar(max)' ) As VheNummerUitgebreid , cv_aanwezig . C . value ( '(.)[1]' , 'nvarchar(max)' ) cv_aanwezig , stadsverwarming . C . value ( '(.)[1]' , 'nvarchar(max)' ) stadsverwarming , cv_combi . C . value ( '(.)[1]' , 'nvarchar(max)' ) cv_combi , warmtemeter . C . value ( '(.)[1]' , 'nvarchar(max)' ) warmtemeter , oppberging . C . value ( '(.)[1]' , 'nvarchar(max)' ) oppberging , verhuurdatum . C . value ( '(.)[1]' , 'nvarchar(max)' ) verhuurdatum , garage_carport . C . value ( '(.)[1]' , 'nvarchar(max)' ) garage_carport , bouwjaar . C . value ( '(.)[1]' , 'nvarchar(max)' ) bouwjaar , lift_aanwezig . C . value ( '(.)[1]' , 'nvarchar(max)' ) lift_aanwezig , codebuitenruimte . C . value ( '(.)[1]' , 'nvarchar(max)' ) codebuitenruimte , marktkenmerk . C . value ( '(.)[1]' , 'nvarchar(max)' ) marktkenmerk , gebruik . C . value ( '(.)[1]' , 'nvarchar(max)' ) gebruik FROM @vheupdate . nodes ( '//row' ) VHE ( C ) OUTER APPLY VHE . C . nodes ( './cv_aanwezig' ) cv_aanwezig ( C ) OUTER APPLY VHE . C . nodes ( './stadsverwarming' ) stadsverwarming ( C ) OUTER APPLY VHE . C . nodes ( './cv_combi' ) cv_combi ( C ) OUTER APPLY VHE . C . nodes ( './warmtemeter' ) warmtemeter ( C ) OUTER APPLY VHE . C . nodes ( './oppberging' ) oppberging ( C ) OUTER APPLY VHE . C . nodes ( './verhuurdatum' ) verhuurdatum ( C ) OUTER APPLY VHE . C . nodes ( './garage_carport' ) garage_carport ( C ) OUTER APPLY VHE . C . nodes ( './bouwjaar' ) bouwjaar ( C ) OUTER APPLY VHE . C . nodes ( './lift_aanwezig' ) lift_aanwezig ( C ) OUTER APPLY VHE . C . nodes ( './codebuitenruimte' ) codebuitenruimte ( C ) OUTER APPLY VHE . C . nodes ( './marktkenmerk' ) marktkenmerk ( C ) OUTER APPLY VHE . C . nodes ( './gebruik' ) gebruik ( C ) ) q WHERE vhe = q . VheNummerUitgebreid AND WoningXML_id = @WoningXML_id When I execute each section individually there is no problem. But when all four sections are run sequentially, by executing the stored procedure, SQL Server 2005 just says no Tag: copying a DB from one server to another
  - 7
    - How do I stop replicating deletes without a Snaphot? I can switch off replication deletes be running the following: EXEC sp_changearticle @publication = 'PUBNAME' , @article = 'ARTICLE1' , @property = 'del_cmd' , @value = 'NONE' I believe I can the effectively then remove the delete stored procedure from the replicated database. And all this wroks no problem. However, when I come to add a new article, "Article2" to my publication it also snapshots and reinitialised the "Article1". How can I prevent the snapshot agent from re-initialising "Article1". Thanks. Tag: copying a DB from one server to another
  - 8
    - Sql Express Vista trouble #2 Dear Alex, When you run the command sp_configure ''user instances enabled' ,1, all it does is just control the enabling or disabling of user instance. So I am not very sure what do you mean when you say "I think the statement I ran was supposed to reconfigure everything." You should read the link that Hemantgiri pointed you to, to understand this better. Out of curiosity, do you have Sp1 of SQL Express installed. How many SQL instances do you have on your machine HTH, Suprotim Agarwal ----- http://www.dotnetcurry.com ----- Tag: copying a DB from one server to another
  - 9
    - Deploying reports/TargetServerURL - beginner question I am trying to figure out Reporting Services and to to deploy results -- and really don't know what I'm doing. I'm going through some books and it looks like our TargetServerURL would either be http://SERVER NAME/reports or http://SERVER NAME/reports erver. Either one will give me a prompt for user & pswd. When I enter my network user & password, I get this message: "You are not authorized to view this page" (It's set up for Windows authentication.) Any suggestions Tag: copying a DB from one server to another
  - 10
    - LastNonempty return null when filter time hierarchy Hi there, I am using SSAS 2005 SP2. Now I have a big problem. My scenario is that: I have a measure, inventory and set its aggregateFunction to "LastNonempty". I have a Time dimension and create a hierarchy for it. The hierarchy contains two levels, namely Year and Month. Everything goes fine when I display the report using BIStudio. However, if I filter the Time hierarchy and set it only to display two months like 2007, 04 and 2007, 05, the measure become null. If I clear the filter and show all 2007's months the measure displays correctly. Does anything also have that problem BTW, I also tried this scenario in MS example database "AdventureWorksDW". Set one measure to be lastnonempty, and create a time hierarchy. Then, set filter to show only two months. The null problem shows again. Hopefully someone can help me to solve this problem. Thanks in advance. Best regardly, Tom Tag: copying a DB from one server to another
  - 11
    - I have been told to run the following SQL script against the database Deepak!! I would like to thank you for taking the time to inform me and point me in the right direction to expand my learning. and everyone else who as also helped. Today is Friday here so please have a good weekend and Bless you. Tag: copying a DB from one server to another
  - 12
    - How do I supress duplicate data Cannot find this anywhere while creating a report. Is this an option thanks. Tag: copying a DB from one server to another
  - 13
    - Project 2007 Server Cube Build Fails Connecting To Another SQL 2005 Analysis Server Help! I've given my SSP account every permission under the sun. (domain admin, local domain admin, analysis services repository db_owner, sql server db admin. Can you specify a) how to check to see that it is indeed the SSP account that is failing to access the server and b) what are the elevated permissions to give this user to get the cube to build Thanks so much! Tag: copying a DB from one server to another
  - 14
    - Passing multi value parameters to CreateSubscription Cato, Congrat's on successfully incorporating the CreateSubscription. I'm trying to incorporate, the CreateSubscription into a SSIS package any suggestion for a best approach. Kinda wrap a For Loop around it and have a Variable that is table driven Tag: copying a DB from one server to another
  - 15
    - Data Flow Task - Conditions to add value for a field. So the term lookup component would sit before the Derived Column component Tag: copying a DB from one server to another
  - 16
    - Search by multiple date range options I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data. Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger. ... FirstPub as datetime LastPub as datetime FirstSale as datetime LastSale as datetime Name as varchar(50) Addr as varchar(50) DetailText as text.... I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field. I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields. I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided. Questions: 1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures 2) How to query either a specific date, as well as a range between the first and last date specified Can it be done efficiently without needing additional screen fields for the range specification If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem. 3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place 4) Should we split the large text field to another table in our search database It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field Tag: copying a DB from one server to another

EugeneB

Hi

I have a PC with MSDE2000 and need to copy the data to another PC running SQL2005.

I have registered the MSDE server on my SQL2005 PC and can see the tables etc.

Can this be done automatically, say every day

Is there a way of mirroring the DB

Cheers

Eugene

Re: copying a DB from one server to another

Prakash P

Hello,

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx. You can find more info from this about copying the database from MSDE to SQL2005.

Thanks

Re: copying a DB from one server to another

Steven Twitchell - MSFT

If your SQL Server 2005 machine is running Standard Edition or higher, you can create a package in SQL Server Integration Services to automate moving data. You can schedule running the package using SQL Server Agent.

Database replication (publisher/subscriber) requires higher SKUs (Workgroup and up) for the publisher. SQL Server 2005 Express can be a subscriber though. You can see a comparison of features here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Hope this helps,
Steve

Re: copying a DB from one server to another

EugeneB

Hi Steve

I Am using 2005 Std, I have also just installed SP1

I was hoping to use the mirroring function but it looks like it only works with sql 2005 DB! The same is true of Log shipping I think

So is there any samples of how you use this integration service

I am not that familiar with SQL as a whole and it would be good if there we're simple ways around the problems.

I would of thought there is an easy way to automatically copy a DB to another Server , like wizards....

Any Help Would be Appreciated

Cheers

Eugene