Monday, September 16, 2013

[SQL Server 2008 issues] trim column values

[SQL Server 2008 issues] trim column values

trim column values

Posted: 15 Sep 2013 02:40 PM PDT

Hi ProfessionalsI have a varchar column in my table with pricing information in it that has up to 4 decimal placeshow do i update the column to make it only 2 decimal placesI know I could substring through the column and look for the decimal piont + 2 places but I dont know how to put this into an update to update the specific column in questionany ideasthanks in advance

Cannot figure out my update correctly

Posted: 15 Sep 2013 11:24 AM PDT

Hi professionalsI have a table like so[code]departmentid,Barcode,Invoiceno,expirydate2036,xyz,12345,23/01/20132077,xyz,56245,05/05/20122036,abc,67654,02/11/2012[/code]my employer wants me to update the table column invoiceno and concatonate the invoiceno column with barcode column so i can then free up the barcode column so they can enter some other criteria based on our 2036 departmentid. so the new updated table should show[code]departmentid,Barcode,Invoiceno,expirydate2036,12345 xyz,NOW EMPTY,23/01/20132077,xyz,56245,05/05/20122036,67654 abc,NOW EMPTY,02/11/2012[/code]here is my initial code to update the invoice column which shows an error "Incorrect Syntax near L1"[code]update licensedetails L1set L1.invoiceno = (select L2.barcode + ' ' + L2.invoiceno from licensedetails L2 where L1.departmentid = L2.departmentid and L2.departmentid = '2036')[/code]am i going wrong somewherethanks in advance

Issues with msdb.dbo.sp_send_dbmail procedure

Posted: 14 Sep 2013 08:42 PM PDT

Hi All,For the last several days I'm trying to resolve issue with sending emails by following procedure (I've ommited some parts):DECLARE [...]@Product varchar(64),@Subj varchar(255),[...]SELECT @Subj = 'New ' + @ListType + ' Release in ''' + @Product + ' ' + @Country + ''' ' + @StatusChange[...]EXEC msdb.dbo.sp_send_dbmail@recipients = @EMail,@copy_recipients = @CCMail,@subject = @Subj [...]If in @Product variable is whitespace (from query from other table) i.e oranges (white) the email is not being sent with error message as follow: "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for a subject.).)". It seems that the message cannot be sent if there is a whitespace in the variable, but on the other hand if I change query:SELECT @Subj = 'New ' + @ListType + ' Release in ''' + ' oranges (white) ' + ' ' + @Country + ''' ' + @StatusChangeNo problem with sending email. Also if I assign the string directy to @subject there is no issue with sending emails too. If there is not white spaces in variable @Product everything is working fine...I've already tried several different approaches to resolve that issue but still no success (and reviewed a tons of entries on different forums...):1. On one of the forums I found to check SQLAgent entry regarding account for sending email - didn't resolve the issue2. Restarting server/services - didn't help3. Do a cast on @product variable Cast(@product as CHAR(25)) the same issue occures4. Assign the @subj variable to different variable and the latter one use as the @subject - didn't helpOS: win 2008 std x86SQL: 2008 std x86 (with all current updates)Messaging server: Exchange 2010 (with all current sp's and updates)Telnet: working fine...No problem to send test email. No other issues from SQL end...Thanks for Your help on that!Best Regards,Tomasz

Error during SQL 2008 ( Enterprise edition) command prompt installation

Posted: 17 Aug 2010 12:59 AM PDT

Hi All ,I am trying to install MSSQL 2008 using the below command.Setup.exe /CONFIGURATIONFILE=" C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100817_185628 \ConfigurationFile.ini".Find attach the word doc ( it contains configuration file ) .Can anyone please help on this. I was able tp install MSSQL successfully without using the configuration file. ;-)ERROR MESSAGE :D:\ >Setup.exe /q /action=install /CONFIGURATIONFILE=" C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100817_185628 \ConfigurationFile.ini"Microsoft (R) SQL Server 2008 Setup 10.00.1600.22Copyright (c) Microsoft Corporation. All rights reserved.The following error occurred:'<null or empty string>' is not a valid value for setting 'ACTION'. Refer to Help for more information.Error result: -2068578304Result facility code: 1204Result error code: 0Please review the summary.txt log for further detailsFor parameter Action its value is already set in the configuration file.

I Can’t Connect to Analysis Services in SSMS 2008R2

Posted: 14 Sep 2013 09:56 PM PDT

Installed SQL Server 2008 R2 and chose to install SSIS, SSRS and SSAS. now I am unable to connect SSRS and SSAS in SSMS.as you can see by attached screen shots,I navigated to the SQL Server Configuration Manager under the SQL Server menu option in windows. my SQL Server Analysis Services service is running! I have set this to start automatically. (SQl SeverBroser running automatic ) following errors from log file:(2013/09/08 01:40:09 PM) Message: Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn't contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001B)(9/8/2013 1:40:09 PM) Message: The file '\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Data\CryptKey.bin' could not be opened. Please check the file for permissions or see if other applications locked it. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210023)(9/8/2013 1:40:10 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/8/2013 1:40:10 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/8/2013 1:48:52 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/8/2013 1:48:52 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/8/2013 1:57:54 PM) Message: Service stopped. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)(9/8/2013 1:58:03 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/8/2013 1:58:03 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/8/2013 2:11:20 PM) Message: Service stopped. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)(9/8/2013 2:11:27 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/8/2013 2:11:27 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/9/2013 9:09:03 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/9/2013 9:09:03 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/10/2013 10:18:40 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/10/2013 10:18:40 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/11/2013 7:24:20 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/11/2013 7:24:20 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/12/2013 6:32:19 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/12/2013 6:32:20 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/13/2013 7:17:30 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/13/2013 7:17:31 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/13/2013 2:21:47 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/13/2013 2:21:47 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/14/2013 12:46:08 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/14/2013 12:46:08 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/14/2013 12:51:02 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/14/2013 12:51:03 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/15/2013 9:07:02 AM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/15/2013 9:07:03 AM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/15/2013 12:33:03 PM) Message: Service stopped. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)(9/15/2013 12:33:11 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/15/2013 12:33:11 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)(9/15/2013 12:35:11 PM) Message: Service stopped. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)(9/15/2013 12:35:19 PM) Message: The flight recorder was started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)(9/15/2013 12:35:19 PM) Message: Service started. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GITS\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)Any ideas as to what is wrong?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog