Wednesday, February 27, 2013

[SQL Server] Remove Leading Zero(s) only if needed

[SQL Server] Remove Leading Zero(s) only if needed

Remove Leading Zero(s) only if needed

Posted: 27 Feb 2013 09:04 AM PST

I have four tables that have a varchar data type which gets imports or daily user updates and this field is an account number field which always has a length of 10, including leading zeros. My current code uses:UPDATE table.StatementsSET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')The issue is that this runs against all four tables and takes around 5 minutes. After the code runs, I see that it touched every record, which I don't want. I just want the code to look at the rows that this field needs to be changed on and disregard the previously trimmed rows.I know I need some kind of IF statement to accomplish this, but not sure how.TIA, Scott

Add existing packages - bulk copy?

Posted: 27 Feb 2013 04:20 AM PST

Hi,We are shutting down a server and I want to get a copy of the packages in the MSDB before it's retired. In Visual Studio I've been told to right click on packages folder and select add existing package. I put in the server and then browse for the package and this works. However there are many and I can only select one at a time. I may have to do this on other servers in the near future. Does anyone know a bulk way of doing this?

MS Access front end to SQL server 2008R2 backend

Posted: 26 Feb 2013 10:13 PM PST

Hello,We use MS Access 2010 to obtain data from our Sybase\SQL anywhere database.The name of this database is called "e2i".We are moving to SQL server 2008 R2 and have converted our database to work on the server.The name of our this database has been called "Radan".I have setup a DSN connection on my client to connect to the "Radan" database. The tests show they are successful.When I go to run MS Access to connect to the "Radan" database to extract the data I get the following message I am presented with SQL server login and must enter loginID and password.After I enter the login credentials I get the following message:"Could not execute query, could not find linked tables... invalid object name".I then use Linked Table Manager but again the SQL server login appears and the username \ password must be entered for each dbo.Is this the correct way to get MS access frontend to work with the new SQL server database?Thanks,Frank

Select.. where column1 in (@var)

Posted: 27 Feb 2013 12:35 AM PST

What am I missing?Why would this work SELECT * FROM #MainQuery WHERE column1 IN ('Value1','Value2')and this not? declare @Var varchar(100) set @TOB = '''Value1'',''Value2''' SELECT * FROM #MainQuery WHERE column1 IN (@Var)

SQL 2008 R2 Problem with TempDB when running SSIS

Posted: 26 Feb 2013 06:26 PM PST

HiI have a problem with some SSIS packages. I use SSIS to extract data from a server that is hosted by someone else. The query extracts about 2 000 000 rows and it has been running for several years. When the source server was upgraded from SQL 2005 to SQL 2008 R2 the SSIS package failed with the following message:"Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."I can´t do anything about the size of TempDB since I´m not hosting it. I´m trying to see if there is anything else I can do to make it work. I have tried setting Maximum Insert Commit Size in the package as well as Rows per Batch (and I´m using OpenRowset Using FastLoad) with no luck.The query looks something like this:SELECT dbo.Kund.Personummer, dbo.Avtal.Status, dbo.Avtal.PartIdbolag, dbo.Avtal.AvtalTypNamn, dbo.Avtal.ProduktId, dbo.Engagemang.EngagemangsnrFROM dbo.AvtalPart INNER JOIN dbo.Avtal ON dbo.AvtalPart.AvtalId = dbo.Avtal.AvtalId INNER JOIN dbo.Kund ON dbo.AvtalPart.PartId = dbo.Kund.PartIdKund INNER JOIN dbo.Engagemang ON dbo.Avtal.AvtalId = dbo.Engagemang.AvtalIdWHERE (dbo.Avtal.Status = 'Gällande') AND (dbo.Kund.PartIdbolag = 6)Can someone please give me som advise on what to do next? Is there anything I can do to make it work?
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog