Monday, July 1, 2013

[SQL Server 2008 issues] Export using bcp problem

[SQL Server 2008 issues] Export using bcp problem

Export using bcp problem

Posted: 30 Jun 2013 02:48 PM PDT

Hi AllI have a stored procedure which extracts data and creates a file in the cleansed directory.All works fine except obne thingwhen the filename is longer than 16 characters the procedure works bu the file will not be created in the folder.any ideas why[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 07/01/2013 13:42:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[exporttocsv]@importedquery nvarchar(1000), /* The query to be executed */@importedcsvname nvarchar(150) /* To name the exported file back to the original name */asBEGIN DECLARE @path varchar(50)DECLARE @filename varchar(30)DECLARE @dbname varchar(30)DECLARE @sql varchar(2000)DECLARE @bcpcommand varchar(1000)SELECT @path = 'C:\inetpub\wwwroot\cleansed\'SELECT @filename = @importedcsvname + ' -c -t, -d 'SELECT @dbname = 'TestData -U sa -P sqldba'SELECT @bcpcommand = 'bcp "' + @importedquery + '" queryout 'SELECT @sql = @bcpcommand + @path + @filename + @dbname--print @sqlEXEC master..xp_cmdshell @sqlend[/code]

Missing Index

Posted: 30 Jun 2013 06:28 PM PDT

Good Day,I ran a report on one of the SQL Server 2008R2 databases which identified an index as missing . I looked in the database and the index exists . The statistics are being kept up to date automatically . Is there any reason why SQL Server would keep on identifying this index as being missing ?Thanks in advance .

Get the LOG ( Text ) file name in SSIS Script task

Posted: 30 Dec 2010 01:55 AM PST

Hello,I have configured Logging to generate the log files, filename example given below, using expressionsto create a Logging Text file.Since this Log file is generated dynamically( based on time in milliseconds) , I have problems accessing the Name of the file in the SSIS Script TASK. I would like to Mail this Log file ( or atleast Log file name with with full path to administrator in the last step in case SSIS package FAILS.Is there a System variable name representing the name of LOG file that I Can Access in Script task ? [center]D:\SSIS_ExecutionLogs\Hummer_AutomatedCommissioning\Hummer_AutomatedCommissioning_2010-12-13_173342_Log[/center]Expression to create a Logging Text file.[code="other"]"D:\\SSIS_ExecutionLogs\\" + @[System::PackageName] + "\\" + @[System::PackageName] +"_" + (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "_" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) + "_Log" +".TXT"[/code]Please Help....

Transfer The Logins and The Passwords Between Instances of SQL Server

Posted: 30 Jun 2013 11:09 AM PDT

we want to Transfer The Logins and The Passwords Between Instances of SQL Server 2008we have 78 logins and 78 database userswhat are the clear step by step of going by moving all the logins and users and fixing orphan users in concise manner thanks

Linked Server Creation Stalling

Posted: 30 Jun 2013 07:00 AM PDT

I am trying to create a linked server to a MySQL server. My SQL box has the MySQL 5.2 ODBC drivers installed. This SQL Server installation has two instances on it FlyingAce2008 and Morrow2008, both are running SQL Server 2008.I can create my linked server just fine on the FlyingAce2008 instance, but when I go to set it up on the Morrow2008 instance, with the exact same settings as the FlyingAce2008, I click OK and the green circle animation just sit there and spins and I cannot stop or close the Link Server Properties dialog box without opening the task manager and killing Management Studio.I don't understand why I can set it up just fine on one instance, but when I go to set up the link server on the other instance using the exact same properties, management studio hangs. Why look under my Linked Servers, it shows up, but when I test the connection Management Studio hangs. Any ideas?

Transfer The Logins and The Passwords Between Instances of SQL Server

Posted: 30 Jun 2013 11:08 AM PDT

we want to Transfer The Logins and The Passwords Between Instances of SQL Server 2008we have 78 logins and 78 database userswhat are the clear step by step of going by moving all the logins and users and fixing orphan users in concise manner thanks

credential/proxy

Posted: 30 Jun 2013 01:05 PM PDT

I have a proc as below ALTER PROCEDURE [dbo].[sp_proc_test] WITH EXECUTE AS 'ABCD\svcaccount'AS EXEC msdb.dbo.sp_start_job @job_name = 'JB_ABC_Run'GO I setup a proxy account for windows login and mapped a SQL login to it. When I try to execute through a SQL login, I am getting following error. The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. I have made sure the windows (ABCD\svcaccount) login has full access (owner) to the msdb db. What am I missing?

Bulk insert with data field encryption

Posted: 30 Jun 2013 03:03 AM PDT

We get a daily file of employee ids and their associated social security numbers. Right now, I have a stored procedure [code="sql"]create procedure put_secret(@id int, @ssn int)as begin open symmetric key ... decryption by certificate ... insert into secret(id,ssn) values (@id,EncryptByKey(Key_GUID(...), @ssn)) close symmetric key ...end[/code]and a program that reads one record from the file, calls the stored procedure [i]put_secret[/i] passing the record, and I do this in a while (not EOF). Everything is working well. But it is s-l-o-w.Is there a way to either create the table so that it knows that a column is, by default, encrypted, [code="sql"]create table secret( id int not null, ssn varbinary(MAX) default encryptByKey(Key_GUID(...)), primary key(employee_number))[/code]or a way to have bcp XML format file to do this[code="xml"] <RECORD> <FIELD ID="ID" xsi:type="CharFixed" LENGTH="9" /> <FIELD ID="SSN" xsi:type="CharFixed" LENGTH="9" /> </RECORD> <ROW> <COLUMN SOURCE="ID" NAME="id" xsi:type="SQLINT"/> <COLUMN SOURCE="SSN" NAME="ssn" xsi:type="SQLVARBIN" ENCRYPTION="..."/> </ROW>[/code]or a way to have [b]BULK INSERT[/b], or [b]OPENROWSET(BULK...)[/b] do this?Again, the while loop works and no one is complaining (so I guess it's academic), but I can foresee some shop somewhere in the world with a huge number of records where this might be useful.

Database Mail not sending mail if number of recipients are more

Posted: 26 Jun 2013 12:21 PM PDT

Hi All,In one of the servers, database mail is not sending mails if the To address is more than 4 and CC is more than 2. I am getting the below error[b]"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2013-06-26T18:11:38). Exception Message: Cannot send mails to mail server. (The operation has timed out.)."[/b]There is no issues with SMTP server. It was working last month but stopped working this month. Running out of clues.. Thanks for your help!!!

SQLSERVER INTEGRATION SERVICE

Posted: 30 Jun 2013 02:58 AM PDT

1. How to provide security for the configuration file (xml package configuration file)?2. Different approaches of deployment of package in ssis?3. Three tasks are running in your package and 2 tasks are successfully executed and third task is failed, in this situation I need to rollback 2nd and 3rd tasks, so what can u do in SSIS package and sqlserver?4. There are 10 records in a flat file source, among them 9 will be executed successfully and 10th record is failed, in this scenario I need to get all 10 records source level to target level, in target level then 10th record failure error will be displayed, what I need to do?5. Explain the dynamic behavior of your project?6. Explain the validations of a package at runtime?7. What are isolations in SSIS, and where u can use this?8. What is optimizing a packages?9. Tell me one complex packages in your project?(which task mostly we are used)10. What is linked server?11. I created one package with some file. I have diff servers having that package with diff configuration file. Is possible to execute that package in servers simultaneously?15. What is incremental loading and decremental loading?16. Microsoft office 2007 excel sheet supported by sql server 2005 or not?17. What is the difference between file system and sql server ( at the time of deployment)?I have one package in d(d drive) folder I want move that package in to e(e drive) folder how to move the package?18. I have table like thisSno Sname1,2 Sreenivas3,4 Reddy5,6 Raja7,8 Reddy I want like the following tableSno Sname1 Sreenivas2 Sreenivas3 Reddy4 Reddy5 Raja6 Raja7 Reddy8 reddy19.I have one package that package scheduled by daily 6 am but the job is failed at Saturday then what I need to do?(where we go how to resolve)21. What is parallel execution in ssis?22. What type errors occurred commonly in your project and what are those names?24. I have one package and that package is already scheduled is it possible to apply the transaction for that package?25.suppose I have one folder with 5 file text files by using for each file enumerator we store the files in to one folder but suddenly tomorrow one file add to that folder how to store the file into same destination?26. in source table data having like thisEno Ename Esloc Deptno1 Sreenu Hyd 10,20,30,40,50I want like thisEno Ename Esloc Deptno1 Sreenu Hyd 101 Sreenu Hyd 201 Sreenu Hyd 301 Sreenu Hyd 401 Sreenu Hyd 50 How to do this?27. Suppose I have one destination table with some data suddenly 2 excel files data want to insert into the destination table but how to know this data is already inserted into the destination and only new data is inserted into the destination?28. Why we are using xml file configuration file?29. How to access and execute the packages clients?30. In ssis package I created a data ware house by using slowly change dimension.Cname Cadd StatusSreenu Bangalore TrueSreenu Hyderabad FalseSreenu Kadapa FalseSreenu Badvel FalseSreenu Pml false From the above how to know second row?31. What is smtp server and what is the main purpose?32. In my source table having 1000 records from that I want move 10 to 990 rows then what I need to do?33. I have two sources with two tables and one table is having data and another table having conditions how to use the conditions in the table?34. I have one ssis package. How to know the how much time take for executing this package and after improving the performance how to see the time?35. I developed one package how to know the whether the package having data or not?36, I have one parent, child package in case the errors found child package how to handle that errors?36.in my sql server one package is there how to move that package into some other server?37. How to Concat row data through ssis?Source:Ename EmpNoStev 100Methew 100John 101 Tom 101Target:Ename EmpNoStev methew 100John tom 10138. How to send Unique (Distinct) records into One target and duplicates into another tatget?Source:Ename EmpNoStev 100Stev 100John 101Mathew 102Output:Target_1: Ename EmpNoStev 100John 101Mathew 102Target_2:Ename EmpNoStev 10038. How do u populate 1st record to 1st target , 2nd record to 2nd target ,3rd record to 3rd target and 4th record to 1st target through ssis?39. We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:Col1Col2Col3----------------- a b cThere is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:Col-----abc40. There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.41.There is a source table containing 2 columns Col1 and Col2 with data as follows:Col1 Col2 a l b p a m a n b q x yDesign a mapping to load a target table with following values from the above mentioned source:Col1 Col2 a l,m,n b p,q x yDesign an ssis package to load first half records to 1 target while other half records to a separate target.

upgrade 2005 SP3 to 2008 R2

Posted: 29 Jun 2013 09:08 PM PDT

Hi,maybe already posted somewhere, but I can't find:we want to upgrade "in place" from 2005 SP3 to 2008 R2 RTM which fails(update from SP3 not supported). Because deinstallation of SP3 is not possible,what is the solution ? Can we update to 2008 R2 SP1 ?If yes, where can I find an installation of 2008R2 with SP1 included ?Or do we have to create a slipstream ?

1. How to provide security for the configuration file (xml package configuration file)? 2. Different approaches of deployment of package in ssis? 3. Three tasks are running in your package and 2 tasks are successfully executed and third task is failed, in this situation I need to roll

Posted: 30 Jun 2013 02:56 AM PDT

[email][/email]

DB Size Growing after moving fields

Posted: 10 Jun 2013 12:30 AM PDT

Hi All,I am new here, but have read the site a lot. I have been doing some SQL maintenance on various servers that I administer. One of the scripts takes 20-30 fields or so, and moves the data from the old column to a new column, and once complete it drops the old column. After doing this, the DB file (MDF) grew from about 3.5GB to about 5GB.Why?I did a shrink on both the file and database. This particular box is SQL 2008.Let me know any questions.Thanks for the help!

triggers not found in replicated table

Posted: 29 Jun 2013 07:19 PM PDT

Dear,I have done Snapshot Replication in my database. I noticed that the triggers corresponding to a specific table are not replicated.Please give me a solution so that I can find my triggers in replicated table.ThanksAkbar
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog