#bulk copy program BCP

[thedbahub]

Ad-hoc Copying of Large SQL Tables from Production to Development in SQL Server 2022: Best Methodologies

Picture this: You’re a database whiz, knee-deep in the nitty-gritty of moving colossal tables from the bustling world of production to the calmer waters of development or testing. It’s no small feat, especially when you’re juggling tables that are bursting at the seams with data, running into the hundreds of millions of rows. The challenge? Doing this dance without stepping on the toes of data…

[siva3155]

300+ TOP MSBI Objective Questions and Answers

MSBI Multiple Choice Questions :-

1. What is the difference between UNION and UNION ALL? A. Union selects only unique row (data) from all queries and Union all selects all rows from all queries. B. Union selects row (data) from all queries and Union all selects all rows from all queries. C. Union selects only unique row (data) from all queries and Union all selects rows from all queries. D. Union selects row (data) from all queries and Union all selects rows from all queries. Ans: a 2. Define Row Number function? A. To generate sequence number. B. To generate sequence number based on order by column. C. To generate number. D. None. Ans: b 3. What is Normalization? Types of normalization? A. Join the tables B. Move the data into tables. C. Split the table to reduce duplicates and it has five types. D. None Ans: c 4. What are different types of joins? A. Inner, outer, self join. B. Equi join and cross join. C. Right outer join. D. All above. Ans: d 5. What are the advantages of using stored procedure? A. Pre executable program. B. We can get the data. C. Security. D. It is similar to function. Ans: a 6. What is BCP? A. Bulk copy program. B. Business continuity program. C. A and B. D. None. Ans: a 7. Define Index? A. Predefined pointers to data page. B. It is primary key. C. It is foreign key. D. None. Ans: a 8. What command do we use to rename db, table, and column? A. Invoke command B. Commit C. Rename D. None. Ans: c 9. Define having clause? A. It is similar to where but must use group by clause. B. It is equal to where clause. C. None. D. It is equal to from clause. Ans: a 10. What are the basic functions system databases? A. To create functions. B. To create stored procedure. C. To maintain metadata information. D. None. Ans: c

MSBI MCQs 11. Explain ETL process? A. Extract, Translation, Loading. B. Extract, Transformation , Loading C. Both a and b. D. None. Ans: b 12. Difference between for loop and for each loop container? A. For loop based on conditions and for each loop based on objects collections. B. For loop based on objects collections and for each loop based on objects collections. C. None. D. Both a and b. Ans: a 13. What does a control flow do? A. To control workflow. B. To control events. C. To control data flows. D. To control lookup. Ans: a 14. Explain event handlers? A. To handle data validations. B. To handle errors. C. To handle events. D. None. Ans: c 15. SSIS 2008 configuration types? A. Five. B. Four. C. Three. D. One. Ans: a 16. Define Lookup transformation? A. Used as reference table. B. Used as master table. C. Used as fact table D. None. Ans: a 17. Define synchronous transformations? A. Input rows count and Output rows count are same. B. Input rows count and Output rows count are not same. C. None. D. Input rows count and Output rows count are different. Ans: a 18. How to rename file using SSIS tasks? A. Using lookup. B. Using Data flow. C. Using File system task. D. None. Ans: c 19. What does mean by data auditing? A. To maintain data load statistics in ETL process. B. To know the errors. C. To know the Primary keys. D. None. Ans: a 20. How many container tasks available in SSIS? A. One. B. Two. C. Three. D. Four. Ans: d 21. Define role play dimensions? A. One dimension used multiple times in the cube. B. One dimension used in different cubes. C. Shared across cubes. D. None. Ans: a 22. Define dimension usage in SSAS Cube? A. To maintain cube data size. B. To know the relationships between dimensions and fact. C. To build business logic. D. None. Ans: b 23. MDX stands? A. Memory, Distribution, extensions. B. Memory, dimension, expressions. C. Multi, dimension, expressions. D. None Ans: c 24. DSV stands? A. Data source View. B. It is view. C. It has relationships and metadata. D. All above. Ans: d 25. How many types of actions available in the Cube? A. One. B. Two. C. Three. D. None. Ans: c MSBI Objective Type Questions with Answers 26. What do you understand by dynamic named set (SSAS 2008)? A. To create dynamic set of members. B. To create set of members. C. None. D. Both a and b. Ans: a 27. How many types of dimension are possible in SSAS? A. Four. B. Five. C. Six. D. Seven. Ans: c 28. In which scenario, you would like to go for materializing dimension? A. To increase spee B. To decrease memory size. C. Both. D. None. Ans: a 30. What are the options to deploy SSAS cube in production? A. Using BIDS. B. Using SSMS. C. Using XMLA. D. All the above. Ans: d 31. Describe Reporting Lifecycle? A. Reports and reports models. B. Report authoring, Management, Delivery. C. Both. D. None. Ans: b 32. What can SQL Server Reporting Services do? A. To create files. B. To create tables. C. To create Reports. D. None. Ans: c 33. Define reporting processing? A. Report data processing. B. Report rendering process. C. Report delivery process. D. All the above. Ans: d 34. How many default rending options are available in SSRS2008? A. Four. B. Five. C. Two. D. One. Ans: a 35. What is the usage of Report Builder? A. To create reports. B. To create reports for users. C. Business users create reports. D. None. Ans: c 37 How many architecture components of SSRS 2008? A. Five. B. Six. C. Seven. D. None. Ans: b 38. Explain on SSRS2008 Reporting Items? A. Tablix,Chart,List B. Sub Report. C. Both A and B. D. None. Ans: c 39. What does mean by nested datasets? A. Used datasets with in dataset. B. A dataset. C. None. D. A table. Ans: a 40. How many command line utilities of SSRS2008? A. Three. B. Four. C. One. D. None. Ans: a 41. You are creating a SQL Server 2008 Integration Services (SSIS) package for Company.com. The package contains six Data Flow tasks and three Control Flow tasks. You should alter the package Which is the correct answer? A. You should increase the two Control Flow tasks and one Data Flow task to a container. Change the TransactionOption property of the container to Supported. B. You should increase the two Control Flow tasks and one Data Flow task to a container. Change the TransactionOption property of the container to Disabled. C. You should increase the two Control Flow tasks and one Data Flow task to a container. Change the TransactionOption property of the container to Required. D. You should increase the two Control Flow tasks and one Data Flow task to a container. Change the TransactionOption property of the container to RequiredNew. Ans: C 42. You are managing a SQL Server 2008 Reporting Services (SSRS) sample which does not give some same rendering extensions for Company.com. You should make sure that you could set the server in order to render to Microsoft WorWhich is the correct answer? A. You should change the AppSetttings.config file. B. You should change the Global.asax file. C. You should change the Machine.config file. D. You should change the RSReportServer.config file Ans: D 43. You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server 2008 database for Company.com. In order to develop a failure recovery plan that is published for a SQL Server. Which is the correct answer? A. You should back up the master database. B. You should back up the local database. C. You should back up the system database. D. You should back up the systemdb databse. Ans: A 44. You are developing a SQL Server 2008 Reporting Services (SSRS) instance of report model for Company.com.In the Report Builder tool, the users should need to create their SSRS reports. The data source they used will include a Microsoft SQL Server 2008 database. Which include 1000 tables? You should design the report model for users, and allow access to only the 20tables which they require for reporting. Which is the correct answer? A. You should develop DataSet using the Web Service to Schema(s) option. B. You should develop DataTable using the Web Service to Schema(s) option. C. You should develop a data source view and select only the required tables and views. D. You should set the data source view in the setting file. Ans: C 45. You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com.A sales manager called Clerk is responsible for the sales of bikes in the Northeast region. You decide to give some rights to the rights to Clerk to visit the database.You won two roles below called Southern Region and Nikes. Their schemas are listed below:You have make The Visual Totals properties attribute true for roles above.You should make sure that Clerk could browser the workers in the Product dimension which link to the Nikes category in the Southern region. Which is the correct answer? A. You should increase Clerk to a Nikes role B. You should increase Clerk to the Southern Region role. C. You should increase Clerk to a new role which owns components below: D. .} as the permitted configuration.{...} as the permitted E. You should increase Clerk to the default Region role. Ans: C 46. You are managing a SQL Server 2008 Analysis Services (SSAS) database which includes a Sale dimension that includes the Category and Subcategory properties for Company.com.There is a rigid relationship type for properties. The data source for the Sale dimension alters the relationship between the Type and Sub Type values. You should make sure that you could run an XML to operate he dimension to reflect the change normally for Analysis (XMLA). Which is the correct answer? A. You should utilize the ProcessDefault command. B. You should utilize the ProcessClear command. C. You should utilize the ProcessIndexes command. D. You should utilize the ProcessDefault and the ProcessClear commands. Ans: D 47. You are managing a SQL Server 2008 Analysis Services (SSAS) instance for Company.com. In order to execute the Usage-Based Optimization Wizard you should make query logging enable. Which is the correct answer? Which is the correct answer? A. You should make the QueryLogSampling server attribute default value. B. You should make the QueryLogSampling server attribute 5. C. You should configure the server property of DefaultFolders. D. You should configure the QueryLogConnectionString server attribute and set a valid connection string. Ans: D 48. You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com.You get the Duplicate Key error when you operate the Analysis Services database.You should alter the ErrorConfiguration attribute in order to make processing run normally. Which is the correct answer? A. You should alter the Dynamic Management View (DMV) B. You should alter the Local Group C. You should alter the dimension D. You should alter the Transactions Log Ans: C 49. You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com. In order to update data in a partition each hour you should run the incremental processing method. In order to solve the problem, which is the correct answer? A. You should utilize ProcessAdd for Analysis (XMLA) command B. You should utilize default command for Analysis (XMLA) C. You should utilize ProcessNone for Analysis (XMLA) command D. You should utilize ProcessView for Analysis (XMLA) command Ans: A 50. You are developing a SQL Server 2008 Reporting Services (SSRS) report for Company.com.Assembly should be created in order to run real-time lookup and currency conversion. The assembly has a static class named daily which lives in the namespace HomeCalc.there is a method called DMO which need two arguments, Cuurnt and HomeCalcWhen the report is operating, you should reference the ToEUR method in an expression to convert USD to USO. Which is the correct answer? A. You should use the expression of =Code. HomeCalDaily. USO (Fields! Cuurnt.Value,” DMO”) B. You should use the expression of =Code! HomeCalDaily. USO (Fields! Cuurnt.Value,”DMO”) C. You should use the expression of = HomeCalDaily.USO (Fields! Cuurnt.Value,”USD”) D. You should use the expression of = HomeCalc! Daily.USO (Fields! Cuurnt.Value,”USD”) Ans: C MSBI Questions and Answers pdf Download Read the full article

[marcosplavsczyk]

This article will explain different ways of exporting data from SQL Server to the CSV file. This article will cover the following methods:

Export SQL Server data to CSV by using the SQL Server export wizard

Export SQL Server data to CSV by using the bcp Utility

Export SQL Server data to CSV by using SQL Server Reporting Services (SSRS) in SQL Server Data Tools (SSDT) within Visual Studio

Export SQL Server data to CSV by using the ApexSQL Complete Copy results as CSV option

Export SQL Server data to CSV by using SQL Server export wizard

One way to export SQL Server data to CSV is by using the SQL Server Import and Export Wizard. Go to SQL Server Management Studio (SSMS) and connect to an SQL instance. From the Object Explorer, select a database, right click and from the context menu in the Tasks sub-menu, choose the Export Data option:

The SQL Server Import and Export Wizard welcome window will be opened:

Click the Next button to proceed with exporting data.

On the Choose a Data Source window choose the data source from which you want to copy data. In our case, under the Data source drop down box, select SQL Server Native Client 11.0. In the Server name drop down box, select a SQL Server instance. In the Authentication section, choose authentication for the data source connection and from the Database drop down box, select a database from which a data will be copied. After everything is set, press the Next button:

On the Choose a Destination window, specify a location for the data that will be copied from SQL Server. Since the data from the SQL Server database will be exported to the CSV file under the Destination drop down box, select the Flat File Destination item. In the File name box, specify a CSV file where the data from a SQL Server database will be exported and click the Next button:

On the Specify Table Copy or Query window, get all data from a table/view by choosing the Copy data from one or more tables or views radio button or to specify which data will be exported to the CSV file by writing an SQL query by choosing the Write a query to specify the data to transfer radio button. For this example, the Copy data from one or more tables or views radio button is chosen. To continue, press the Next button:

Under the Configure Flat File Destination window, choose the table or view from the Source table or view drop down box for exporting data to the CSV file:

To view which data will be exported to the CSV file, click the Preview button. The Preview Data window will appear with data that will be exported:

If you are satisfied with the preview data, click the Next button in order to continue with exporting data. The Save and Run Package window will appear. Leave settings as they are and click the Next button:

The Complete Wizard window shows the list of choices that were made during of exporting process:

To data from SQL Server to CSV file, press the Finish button. The last window shows information about exporting process, was it successful or not. In this case, the exporting process was finished successfully:

On the image below, the ExportData.csv file in Excel and Notepad is shown with the exported data:

SQL Server Import and Export Wizard can be initiated without using SSMS, go to start and type word “Export”, from the search results choose 64-bit or 32-bit version of SQL Server Import and Export Wizard:

Export SQL Server data to CSV by using the bcp Utility

The bcp (bulk copy program) utility is used to copy data between SQL Server instance and data file. With the bcp utility, a user can export data from an instance of SQL Server to a data file or import data from a data file to SQL Server tables.

To start export SQL data to CSV file, first open Command Prompt (cmd), go to start and type cmd and click on the Command Prompt item:

The Command Prompt window will appear:

Then type bcp ? and press the Enter key in order to see if everything works as it should. In our case, an error occurs:

As it can be seen from the error message box, the msodbcsql13.dll file is missing. To resolve this problem, download and install Microsoft ODBC Driver 13 for SQL Server.

Now, when in the Command Prompt window, the bcp ? command is executed, the following information will appear:

The screen above shows all the different switches that can be used in bcp utility. So, let’s use some of these switches and export SQL Server data to CSV.

In the Command Prompt window, type the word bcp followed by the name of the SQL table from which exporting data should be done by typing the following steps, first type the name of the database which contains the table from which you want to export data, followed by dot. After the dot, type the schema name of the table, after the schema name, type dot and after the dot, type the table name which contains data for exporting (e.g. AdventureWorks2014.Person.AddressType):

After the name of the SQL table, press the Space key and type the word out:

out copy data from the database table or view to a specified file.

Also, the queryout command exists which copies data from an SQL query to a specified file.

The in command copies data from a file to a specified database table.

After the out command, add a location of a CSV file where the data from the SQL table will be placed, for example (C:\Test\ExportData.csv)

Now when the csv file is specified, there are a few more switches that need to be included in order to export SQL Server data to CSV file.

After the CSV file type the -S switch and the name of an SQL Server instance to which to connect (e.g. WIN10\SQLEXPRESS):

Then type the -c switch and, after that, type the -t switch to set the field terminator which will separate each column in an exported file. In the example, the comma ( , ) separator will be used:

At the end, enter a switch which determines how it will be accessed to the SQL Server. If the -T switch is put, that means the trusted (Windows authentication) will be used to access to SQL Server. For SQL authentication, use the -U switch for the SQL Server user and -P for the SQL Server user password.

In this example, the trusted connection (-T switch) will be used:

Now, when the Enter key is pressed, the similar message will appear with information about copied data:

To confirm that data from a specified table have been copied to CSV file, go to the location where the file is created, in our case that will be C:\Test:

And open the ExportData.csv file:

Export SQL Server data to CSV by using SQL Server Reporting Services (SSRS) in SQL Server Data Tools (SSDT) within Visual Studio

SSRS allows to save exported data in one of the following formats PDF, Excel, XML, MHTML, Word, CSV, PowerPoint and TIFF format.

To start creating a report server project first open SSDT. Go to File menu and under the New sub-menu, choose the Project option:

Under Business Intelligence, select the Reporting Services item and on the right side, choose the Report Server Project Wizard option:

Note: In any case, the Business Intelligence or Report Server Project Wizard options don’t appear, SSDT needs to be updated with the Business Intelligence templates. More about this can be found on the Download SQL Server Data Tools (SSDT) page.

In the Name box, enter the name of the project (e.g. ExportData) and in the Location box, choose where the project will be created:

After that is set, press the OK button, the Report Wizard window appears, press the Next button to continue:

The Select the Data Source window will appear:

In the Connection string box, a connection string to the SQL Server database can be entered from which a report can be created or press the Edit button on the Select the Data Source window and in the Connection Properties window, set the connection string to the desired database, like from the image below, and press the OK button:

This will be in the Connection string box on the Select the Data Source window. Press the Next button to continue with the settings:

In the Design the Query window, specify a query to execute to get data for the report. There are two ways for getting a query to execute. One way is to use the Query Designer window by clicking the Query Builder button on the Design the Query window:

And the second way is to type the desired query in the Query string box:

After setting the query, click the Next button. On the Select the Report Type window, leave default settings and press the Next button:

On the Design the Table window, we will leave everything as it is and press the Next button:

The Completing the Wizard window shows all steps/settings that are taken during the process creating the report. Press the Finish button to create the report:

After we pressed the Finish button, the created report will show. Under the Preview tab, click the Export button and, from the menu, choose in which format generated data will be exported (e.g.CSV):

Export SQL Server data to CSV by using the ApexSQL Complete Copy results as CSV option

The Copy code as is a feature in ApexSQL Complete, a free add-in for SSMS and Visual Studio, that copy the data from the Results grid to a clipboard in one of the following data files: CSV, XML, HTML in just one click.

In a query editor, type the following code and execute:

USE AdventureWorks2014 SELECT at.* FROM Person.AddressType at

The following results will be displayed in the Results grid:

In the Results grid, select the part or all data, right click and from the context menu, under the Copy results as sub-menu, choose the CSV command:

This will copy the selected data from the Results grid to the clipboard. Now, all that needs to be done is to create a file where the copied data should be pasted:

The ApexSQL Complete Copy code as an option can save you a great amount of time when you need to copy repetitive SQL data to another data format.

See also:

Import and Export Bulk Data by Using the bcp Utility (SQL Server)

Start the SQL Server Import and Export Wizard

SQL Server Data Tools

Create a Basic Table Report (SSRS Tutorial)

  The post How to export SQL Server data to a CSV file appeared first on Solution center.

[xlmcv]

How are you fulfilling the FDA's Audit Trail expectations for Data Integrity?

FDA put out its DRAFT guidance on Data Integrity and Compliance with cGMP in April 2016. This guidance is important because we come to understand FDA's thinking on Data Integrity.

"For the purposes of this guidance, data integrity refers to the completeness, consistency, and accuracy of data. Complete, consistent, and accurate data should be Attributable, Legible, Contemporaneously recorded, Original or a true copy, and Accurate (ALCOA)."  -- FDA Guidance on Data Integrity and Compliance With CGMP, April 2016

My focus in this post will be limited to IT applications. Needless to say the guidance has big repercussions for anyone in areas of IT Quality and Computer Systems Validation. Many predicate rules that were written for the paper world still hold good and can be applied to e-records as well. FDA does cite quite a few of them in the guidance.

The two words "Data Integrity" has far reaching implications for computer systems. Any FDA regulated company need to carefully look at their CSV program and revisit, but not limited to, the following areas:

System Design & Implementation

Validation

User Access Controls

Segregation of Duties

Audit Trail Design and Capture

Review of Audit Trails

Data Backup and Archiving

Data Retention

Disaster Recovery & Business Continuity (BCP)

Electronic Signature Design & Implementation

Training

I would like to focus on only one topic in this post: Audit Trail reviews. There can be two (2) types of reviews:

Regular Review: Audit Trails that need to be reviewed with the "parent GxP record".

"FDA recommends that audit trails that capture changes to critical data be reviewed with each record and before final approval of the record. Audit trails subject to regular review should include, but are not limited to, the following: the change history of finished product test results, changes to sample run sequences, changes to sample identification, and changes to critical process parameters."  -- FDA Guidance on Data Integrity and Compliance With CGMP, April 2016

Scheduled Review: Audit Trails that need to be reviewed at regular intervals.

"FDA recommends routine scheduled audit trail review based on the complexity of the system and its intended use."  -- FDA Guidance on Data Integrity and Compliance With CGMP, April 2016

Let us take regular review of audit trails. The expectation here is that the audit trail associated with a "critical GxP record" must be reviewed along with the record itself (e.g.: a batch record). A "data integrity friendly" application must present the audit trail data to the reviewer in a way that is easily understandable and user friendly for this criteria to be met. Otherwise the reviewer will be spending 10x the time reviewing the audit trail rather than the parent record. Most modern and well designed IT apps can accomplish this out of the box (e.g.: Atlassian Jira).

Now let us move on to the trickier "scheduled review". Anyone who is familiar with an Enterprise application realizes that this can be a daunting task. Even if you decide to perform such reviews, you need to ask: How often? What to Review? How to make this a value added exercise? What is the cost of doing such reviews? How many resources do I need?

Some of the larger pharma companies are mandating that SOPs must be developed and audit trail reviews must be conducted on a periodic basis. System owners and administrators are struggling with this mandate. Most of the legacy applications generate logs and audit trails that can be read only by software programmers rather than end users. And also the sheer volume of audit trail records make this an insurmountable task (for example: a cloud enterprise application can generate hundreds of audit trail records for a single workflow execution). Imagine a situation where one has to review a million records generated in one quarter!

Big Data Analytics and Machine Learning can be your savior. A Big Data system can be setup to consume audit trail logs and records and display meaningful information in a dashboard format. Such a system can also send notifications of any unusual activity. Also, such a system can categorize transactions and provide statistical information. Such an Enterprise system can consume logs and records from various apps and perform bulk of the scheduled reviews.

Such a system can:

Look for unusual login activity

Monitor record deletion (if such an activity is not permissible)

Monitor changes to critical system configuration records

Monitor user role changes

Monitor abnormal, disallowed or unusual record state changes

Monitor system logs for critical application errors and correlate them with user activity

And much more...

There are many advantages in designing such a Big Data system. Once a good baseline is established by providing it with historical data, it can learn from it and flag unusual activity in near real-time. For example: such a system can flag a particular sequence of record status change as "unexpected" based on the historical data.

An intelligent Big Data system can be your big friend to handle audit trail reviews. Such a system should be integral part of your data integrity and cyber-compliance toolset.

"xLM provides Big Data Analytics for Audit Trail reviews as part of its Continuous Validation managed service."