Thursday, August 15, 2013

[SQL Server 2008 issues] Import\export wizard

[SQL Server 2008 issues] Import\export wizard

Import\export wizard

Posted: 14 Aug 2013 12:09 AM PDT

I am currently using the wizard to import some data from excel to SQL.typical values im importing..16773 1 8362 19277742516114 8 8028 19277377616885 7 8064 19281108916116 5 4561 192977000it all works fine until the third fourth colum has a letter involved such as 16112 2 5551 16401127XIf i was doing this through script.. i would obviously put the fourth value in single quotes '16401127X' which works fine. And in this case if i insert it manually.. it works fine.But as there are thousands of rows this isnt practical. What do i need to do for the import\export wizard to recognise these values and insert them properly?

Better insert performance on a table only with a clustered index or a table without any index on SqlServer 2008?

Posted: 14 Aug 2013 07:01 PM PDT

[b]Note, I am not talking about a clustered index against a non-cluster index, I mean a clustered index comparing to no index at all for inserting performance.[/b]I saw lots of links as below said that, a clustered index has better performance against no index even for insert operation, due to IAM/PFS/bitmap/..., but with my testing, seems no index is faster than cluster index, what's the problem?http://stackoverflow.com/questions/7264820/removing-a-primary-key-clustered-index-to-increase-insert-performancehttp://support.microsoft.com/kb/297861my test scripts:[quote]---------------------------------------------prepare table with clustered indexCREATE TABLE [dbo].[BigTable_CI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL) GOCREATE CLUSTERED INDEX CIX_BigTable_CI ON BigTable_CI(id)GOALTER TABLE [dbo].[BigTable_CI] ADD CONSTRAINT [DF_BigTable_BigChar_CI] DEFAULT ('a') FOR [BigChar]GOCREATE PROCEDURE [dbo].[AddDataToBigTable_CI](@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_CI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN;---------------------------------------------prepare table without any index at all.CREATE TABLE [dbo].[BigTable_NI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL) GOALTER TABLE [dbo].[BigTable_NI] ADD CONSTRAINT [DF_BigTable_BigChar_NI] DEFAULT ('a') FOR [BigChar]GOCREATE PROCEDURE [dbo].[AddDataToBigTable_NI](@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_NI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN; ---------------------------------------------prepare the results table create table dbo.Results ( StartTime datetime, EndTime datetime, ElapsedTime int, NumberOfRows int, RowsPerSecond int )---------------------------------------------run scripts: exec [dbo].[AddDataToBigTable_NI] 1000000 exec [dbo].[AddDataToBigTable_CI] 1000000[/quote]

How to create a time based trigger in sql server 2008?

Posted: 14 Aug 2013 05:11 PM PDT

Hi, I need How to create a time based trigger in sql server.Pls....and thanks :-)

Identify string or integer

Posted: 13 Aug 2013 11:20 PM PDT

Hi Team,am passing a parameter to a stored procedure, i want to know whether the parameter is string or integeri want a print statement in stored procedure to print the parameter is string or intEg : EXEC Stored_procedure1 ('abc')output : stringEXEC Stored_procedure1 ('123')output : integerEXEC Stored_procedure1 ('abc112')output : string

Maintain the sort order of data file while using bcp

Posted: 14 Aug 2013 12:05 AM PDT

Sql server 2008 r2 service pack 2When trying to import a single varchar column file using bcp utility (no format file)to a temporary table (## ) the rows are inserted not int the order of the file.Doing the same thing with a regular table the order is maintaind1. Is there a way to ensure that the file will be bulked insert with its order?2. Is it a known Issue ?:w00t:

select statement blocking update statement

Posted: 14 Aug 2013 01:16 PM PDT

Hi Guys,Understand that by default, SELECT statement on a table will block update statement on the table.By setting READ_COMMITTED_SNAPSHOT, update statement (writer) will not block select statement (reader) as the pre-image of the update will be stored in tempdb for select statement to access.However, by setting READ_COMMITTED_SNAPSHOT, will reader (select) still block writer (update)?Any simple sql statement to test this out?thanks

Urgent help with Date format needed please

Posted: 14 Aug 2013 06:59 AM PDT

Dear friends,I have date field in the date time stamp format as shown-Create Date2012-06-21 07:01:03.000I need to display it in the report as -Format as DD-MMM-YYYY, e.g., 21-Sep-2013Need help on this please.Kind RegardsDhananjay

need help transforming row values to a table

Posted: 14 Aug 2013 08:31 AM PDT

my table select name,value from xxtable gives the resultnodename valueDTP02_DateTimePeriodFormatQualifier D8DTP03_AdjudicationorPaymentDate 20130408SVD_LineAdjudicationInformation NULLC003_CompositeMedicalProcedureIdentifier_3 NULLC00301_ProductorServiceIDQualifier HChow do i transform the result set or table columns eg. using tsqlDTP02_DateTimePeriodFormatQualifier | DTP03_AdjudicationorPaymentDate ....... etc... ----------------------------------------------------------------------- D8 20130408

sql server automatically opening a file handle for every database file

Posted: 14 Aug 2013 08:49 AM PDT

Why does sql server automatically open a file handle for every database file when the service first starts up (even before anybody logs in to the sql server)? I want to ask if there's a way to stop it doing that, but if there's a good reason it does so then I probably won't try to stop it.

Script to reset password for SQL 2000

Posted: 14 Aug 2013 09:09 AM PDT

Hi,Does anyone have t-sql script to change password for SQL 2000? I tried the below script but I am getting an error, not exactly sure where I am off. Please advise.ALTER LOGIN 'LoginName' WITH PASSWORD=N'Password' MUST_CHANGEgetting error as below.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'LOGIN'.

increment id based on column value

Posted: 14 Aug 2013 05:36 AM PDT

Hi !I hope that you can show me how to do it avoiding using loop. I need to group records imported from flat file by ID. Column LegacyID is identity column. I need new ID incremented on next value='H'. Thank you.Mark Gorelikcreate table LoadTest (legacyid int not null ,field1 varchar(5) not null, newid int null)insert into LoadTest (legacyid ,field1)SELECT 2, 'H'UNION ALLSELECT 3, 'C'UNION ALLSELECT 4, 'S'UNION ALLSELECT 5, 'O'UNION ALLSELECT 6, 'I'UNION ALLSELECT 7, 'I'UNION ALLSELECT 8, 'I'UNION ALLSELECT 9, 'H'UNION ALLSELECT 10, 'C'UNION ALLSELECT 11, 'S'UNION ALLSELECT 12, 'O'UNION ALLSELECT 13, 'I'UNION ALLSELECT 14, 'I'UNION ALLSELECT 15, 'I'UNION ALLSELECT 16, 'I'UNION ALLSELECT 17, 'I'UNION ALLSELECT 18, 'I'UNION ALLSELECT 19, 'N'UNION ALLSELECT 20, 'H'UNION ALLSELECT 21, 'C'UNION ALLSELECT 22, 'S'UNION ALLSELECT 23, 'O'UNION ALLSELECT 24, 'I'UNION ALLSELECT 25, 'I'SELECT * FROM LoadTest legacyid field1 newid======================================2 H 13 C 14 S 15 O 16 I 17 I 18 I 19 H 210 C 211 S 212 O 213 I 214 I 215 I 216 I 217 I 218 I 219 N 220 H 321 C 322 S 323 O 324 I 325 I 3

Allow developers to install sql express on desktops?

Posted: 14 Aug 2013 01:48 AM PDT

Hi! i'd like to know your thoughts about this:is it ok to allow developers to install sql express on their work pcs?is there any security issue or danger because of having sql instances all over the network that are not administered by the dba team?thanks!

Send email notifcation if condition is failed

Posted: 14 Aug 2013 04:44 AM PDT

Hi Experts,How can i setup send a mail when a condition is not satisfied. Select count(* ) from tablewhere conditions x,y,zif count(*) is < 1 send mail.Thanks in advance

Triggers on Tables

Posted: 14 Aug 2013 12:27 AM PDT

Is there a way to turn a table trigger off in a stored procedure, then turn it back on after it skips that table.We have the trigger in place for a 3rd part to insert(trigger point) into that table and automatically the trigger is fired off to inserts into the the database.

SQL Cluster Migration

Posted: 05 Aug 2013 01:59 AM PDT

Hi there,I have the task of migrating two 2-node SQL 2008 R2 clusters on to new hardware. Currently the plan is to create new clusters from scratch, script all the logins, jobs, linked servers etc. and then just restore all the databases onto it. Finally I will alter the DNS name of the cluster to be the same as the existing one so I don't have to modify all the applications.This will probably be fairly time consuming as there are a large number of databases on the servers.Is this the best way to do it or could someone suggest a easier method?! ....or do I just need to stop whinging and get on with it? ;-)Thanks,Matt

Security Problems after SSRS Domain Migration

Posted: 14 Mar 2013 09:41 PM PDT

Morning all,I recently carried out an SSRS Migration from one domain to another.I used the backup and restore method, and all reports are generating fine and all access for existing users appears to be fine.However, there is one (fairly significant) problem;Some users appear to have been duplicated (seems that a problem with new SIDs being generated / mapped for each domain user affected, and this has resulted in me being unable to edit the security on the site levels/folders where this is a problem.I can neither add, amend or delete users and was advised to delete the first instance of a user to correct this, but it seems this has to be ruled out, due to the fact that even the top level folder contains duplicate users, and an error occurs when I attempt to delete thr user via the site security.The original error was:"Response is not well-Formed XML"The error when I try to delete a user now in one of the affected folders is;"role assignment is not valid. The role assignment is either empty or it specifies a user or group name that is already used in an existing role assignment for the current item. (rsInvalidPolicyDefinition)"I tried amending the ownership and modifiedby entries in the database for one of the specific users to myself, and then deleted the user and policies assigned to him, but it unfortunately never resolved the problem (The user still remains in place with security entries on the site, even if deleted within the database).Has anyone else ever encountered this problem and is there a way of resolving this which doesn't mean having to resort to backing up the original db with the affected users stripped out, in order for a restore to be carried out.A lot of further configuration work and report imports has occurred since this was put in place.Thanks in advance for any responses.

REBUILD 1625 indexes weekly !!??

Posted: 14 Aug 2013 05:03 AM PDT

I just joined this companyand look at some SQL jobs they run on production server.One of them looks really odd.It's a weekly job that rebuilds 1625 indexes.There are about 920 tables so it looks like they rebuild a lot of their indexes.I am just curious if it slows the performance on the serverand why why would they do it so frequently.It's a SQL Server 2008 R2 SP2 databse behind online Financial Application (BST Enterprise).

Log LDF file

Posted: 13 Aug 2013 07:25 PM PDT

I came to know that some records are deleted from Table in SQL SERVER 2008...We want to track it & see what happened & who deleted :w00t:No log backup is taken.. only LDF file is there which is almost 5GB now..please suggest how will i be able to read the LDF file to track the operation done yesterday when records were deleted :w00t:Please help its urgent.. Client need answer :crazy:

VMWare administrator moved my server without telling me

Posted: 09 Jan 2013 01:01 AM PST

Good Morning,How would you be able to determine that the server that houses your SQL Server has been moved (vmotion) by the VMWare administrator?Yesterday the VMWare administrator vmotioned the server that housed my SQL Server. The hardware that he moved the server to was having problems.Users were complaining that they were having connection issues, but I connected fine to the database database. Using SSMS & profiler, I saw that the database working. I could not find anything in the logs, including the Windows operating system logs. The only thing I found was in the SSMS Activity Monitor under the Resource Waits section, the Network I/O - Wait Category had the highest Wait Time.To resolve the issue, the VMWare administrator had to move my server back to the original hardware.We had database administrators, network administrators, and Windows system administrators all looking for the cause of the issue without finding anything for an hour before the VMWare administrator confessed that he moved the server to different hardware that afternoon.Has anyone run into this issue, and is there a way to tell that you have been moved. How would you determine that your server had been moved.Anyone's input on this issue is appreciated & thank you in advance.Mike

Data Flow from Firebird server fails in SSIS with [IM014] Error

Posted: 14 Aug 2013 02:15 AM PDT

HelloI'm wondering if someone out there can help restore my sanity. Here's the scenario:A third-party Firebird server holds data I retrieve on an instant/hourly/daily basis. In a 32bit world all is fine - everything works just dandy. However, like many, our kit is being 'upgraded' to 64 bit (one-way ticket, no option or argument).The Visual Studio install on my new 64bit PC turns out be 32 bit version. No bother, we've installed both the 64 and 32 bit ODBC driver for Firebird and the Connection Test succeeds in both cases.My MS Office install also turns out to be 32 bit, so firing up the 32bit ODBC driver I get a perfect linked-table connection to Firebird and the data is there to play with - all good so far.Scheduled updates of Firebird data to our data warehouse (SQL 2008 R2) are achieved via SSIS packages. The ones designed and run on a 32 bit PC using VS2008 32 bit application all work.Back to my 64 bit PC...Setting up the Data Source / Connection Manager; As the VS2008 install is 32 bit, we assumed the 32 bit ODBC driver would be invoked and it is. The connection string is correct. The Test Connection button provides a friendly 'succeeded' message.Pulling in a ADO NET data source task: My configured ODBC CM is available so it's plugged in. Building the query I can list all the tables on the distant Firebird server. Choosing a table, I can list all the fields in the table. Accepting the query, and clicking the "Preview..." button I get a friendly dialog box listing all the records in the table I just queried - all seems well.And then I run the package; the ADO NET Source task instantly turns red, the package fails and I am gifted the following Debug messages:SSIS package "CheckConn.dtsx" starting.Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.Error: 0xC0047062 at Data Flow Task, ADO NET Source [31]: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "ADO NET Source" (31) failed validation and returned error code 0x80131937.Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.Error: 0xC0024107 at Data Flow Task: There were errors during task validation.SSIS package "CheckConn.dtsx" finished: Failure.I note the "IM014: The specified DSN contains an architecture mismatch between the Driver and Application" message. Yet I'm using a 32 bit App with a 32 bit Driver.Can anyone explain why this is happening please? And what can I do to fix this and get it to work please? Apologies that this is a lengthy post, but your insight and wisdom would be very warmly welcomed.RegardsGraham

Sql Server 2008 R2 Partitioning

Posted: 13 Aug 2013 10:33 PM PDT

Hi,I have a Sql Server 2008 R2 instance and in one of my databases I have a table which has >100 million records and we have decided to partition this table as the volumes of data is set to increase dramatically over the next 6 months.Due to certain business rules the "Date" column in the table alone could not be used as the partition key, it had to also include a value which relates to a "BusinessLine".Therefore I created a new [INT] column on the table which will be the [PartitionKey] column and now holds a combination of the data in the [Date] and [BusinessLine] columns. For example, for the "Date" 01/01/2013 and for the "BusinessLine" 1 the value in the [PartitionKey] cell for that row is 201301011.The table already has a Primary Key covering certain columns required for the front end application to search on. The system is highly transactional and I am a little reluctant to just remove the existing Primary Key.So basically my question is...Is it good practise to add the [PartitionKey] column to the existing Primary Key?Thanks in advance.

Do we have any chance to remove a physical file that has data

Posted: 14 Aug 2013 01:34 AM PDT

Hi All,Do we have any chance to remove a physical file that has data... Can any one please suggest !!!

Email notification in job manager

Posted: 13 Aug 2013 11:57 PM PDT

I want to send an email notification in job manager as following instructions but there is no email list to select.http://msdn.microsoft.com/en-us/library/ms191130.aspxHow to add my email address to drop down list?

Can we assign port 1433 to a named instance

Posted: 13 Aug 2013 07:09 PM PDT

Can we assign port 1433 to a named instance ? I know that these 1433 as port number to default instance. Please clarify, Can we allocate port 1433 to named instance..

Linked Servers with service account - Error 18456 state 6

Posted: 13 Aug 2013 09:35 PM PDT

Hi guys,I have a central server and n other servers that are collected on using linked server connections.My original setup (for testing) was:Local login: my usernameRemote user: a SQL Server login (if mixed mode)For a login not defined in the list above: the same SQL Server loginNow I've been given a service account (no password expiry) and was planning to use only this. The steps I've done are:1. Add the user to the central server in an Administrator group.2. Granted this login sysadmin on the central server3. Added the user to a "remote users" group on a remote server4. Granted the login remote the same privileges I granted the SQL Server login (master/msdb read/exec)I tested and I can log on to the central server with the service account, open mstsc & remote to the other server with that login & connect to the remote instance & run the queries I want; it's just not doing it with the Linked Server.My new linked setup attempt is:Local login: service account (in the admin group)Remote user: I have tried both Impersonate & entering the DOMAIN\USER & password for the service account as the Remote User & passwordFor a login not defined in the list above: the same service account (domain\user & pass)Anyone know why it's failing when my the user on the central server has sysadmin & I can remote to the other & run queries fine with mstsc? Thanks for any infoI found this but I'm not sure if it's relevent to me: [url=http://www.sqlservercentral.com/Forums/Topic1222568-146-1.aspx]http://www.sqlservercentral.com/Forums/Topic1222568-146-1.aspx[/url] -- ideally looking to limit the dependency on other teams

Remove unnecessary indexes from Replication

Posted: 13 Aug 2013 07:12 PM PDT

Hi,i'd like to remove some unecessary indexes from my replication:SQL-Versions:Publisher: SQL 2008 StandardDistributor: SQL 2012 StandardSubscriber: SQL 2008OS-Versions:Windows 2008 Standard-Server.I read about some problems, if I just drop an index wich is replicated to an subscriber database. Is there a possibility to delete/drop an index which is no longer used. The index are very big, and it needs a lot of time, if I reorg or rebuild the index.kind regards,Andreas

SQL Server 2012 - Column Store Index

Posted: 09 Mar 2012 09:37 PM PST

Has anyone tried this?Is it really as fast as they say ?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog