Identity colunm things in sql server

1) How to update values in identity column in sql server..?
sol:  You cannot update an IDENTITY column. You need to move the data into another table make the changes over there and insert the back the data into original table.

2) How to see next identity value in sql server through query..?
sol: DBCC Checkident ('TableName')
ex: DBCC Checkident ('EMP')

3) How to reset identity seed value in sql server.?
Sol: DBCC CHECKIDENT ('TableName', RESEED, number)
ex: DBCC Checkident ('EMP',RESEED,11)

Flat file of types fixed width and Ragged right problem, while exporting..?

I’m using the Import\Export wizard to export the top 5 lines from a MS Sql table into a fixed format (“ragged”) file. But I want the first record to contain the column names of the exported fields so I selected the “Column names in the first data row” option of the “Choose a Destination” box. When I run the Package I get:

>>>

· Information 0x402090dc: Data Flow Task: The processing of file "C:\barkingdog\ExportWithheader.txt" has started (SQL Server Import and Export Wizard)

· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)

>>>

When I de-select the “Column names” option, the package works fine. Other than manually, how can I et the column names in output file?

 

 

 

 

Answer:

 

 

The problem here is that when you include column names in the first data row, then you might need to modify the column width (for a ragged right file). For example, if you have a column CustomerID which is of type DT_STR(5), it will fail trying to write "CustomerID" in that space, which takes 10 characters. Use the Edit Transform button on configure Flat File Destination page of the Import Export Wizard to adjust the length specified for each fixed width column.


hope that helps.

What are the Data types used in excel EXCEL..?

Yes, string fields in Excel are treated as Unicode string fields by the driver. Here is some content written about moving data in the other direction that applies here too:

Data types. The Excel driver uses only six data types, which Integration Services maps as follows:

• Numeric – double-precision float (DT_R8)
• Currency – currency (DT_CY)
• Boolean – Boolean (DT_BOOL)
• Date/time – date (DT_DATE)
• String – Unicode string, length 255 (DT_WSTR)
• Memo – Unicode text stream (DT_NTEXT)

What is line feed and Carriage return in Flat file in SSIS

A line feed means moving one line forward. The code is \n. A carriage return means moving the cursor to the beginning of the line. The code is \r.

The separation comes from typewriter times, when you turned the wheel to move the paper to change the line and moved the carriage to restart typing on the beginning of a line. This was two steps.
Windows editors often still use the combination of both in text files. Unix uses mostly only the \n.

Data types in SQL Server

6. What's the difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale. DECIMAL (p [, s ]) NUMERIC (p [, s ]) In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value. The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type: Precision Storage Size 1 - 9 5 bytes 10- 19 9 bytes 20-28 13 bytes 29-38 17 bytes

7. What's the difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented. The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table: Data Type n Minimum Value Maximum Value Precision Storage Size float [(n)] 1-24 -1.79E + 308 1.79E + 308 7 digits 4 bytes 25-53 -1.79E + 308 1.79E + 308 15 digits 8 bytes real -3.40E + 38 3.40E + 38 7 digits 4 bytes For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53. If not specified, this defaults to 53. In SQL Server, the synonym for REAL data type is FLOAT(24). If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).

SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).
Maximum size supported by TEXT and Varchar(max) are same.
Maximum size supported by NTEXT and NVarchar(max) are same.
Maximum size supported by Image and Varbinary(max) are same.

 

How to Use the Microsoft Batch File Language

 

Batch files are DOS command line commands batched together. In Linux they are known as shell scripts, and follow a completely different syntax.

I am aware of these batch files....

What is Index Position in Cache Transformation in SSIS

 
The Cache connection manager reads a reference dataset from the Cache transform or from a cache file (.caw), and can save the data to a cache file. The data is always stored in memory.

- The Cache connection manager does not support the Binary Large Object (BLOB) data types DT_TEXT, DT_NTEXT, and DT_IMAGE




Specify which columns are index columns by specifying the index position of each column. The index is a collection of one or more columns.

For non-index columns, the index position is 0.

For index columns, the index position is a sequential, positive number. This number indicates the order in which the Lookup transformation compares rows in the reference dataset to rows in the input data source. The column with the most unique values should have the lowest index position.


The column which we specified as index Position, those all should be used as lookup search.


If you configure the cache connection manager to use a cache file, the connection manager will do one of the following actions:

- Save data to the file when a Cache Transform transformation is configured to write data from a data source in the data flow to the Cache connection manager. For more information, see Cache Transform.
- Read data from the cache file.