Thursday, May 9, 2013

[SQL Server 2008 issues] Simple 'front ends' for non-developer?!

[SQL Server 2008 issues] Simple 'front ends' for non-developer?!

Simple 'front ends' for non-developer?!

Posted: 08 May 2013 06:57 PM PDT

Guys,I've been thinking of a few different ways to tackle a new project, using Excel, third party web-based tools, some other 'corporate' tools etc, at the end of the day the flexibility of Excel works well, however, the further I delve into this the more I'm trying to shoe-horn Excel into being a database! - With far more SQL knowledge than Excel knowledge this doesn't seem to make sense (I was even Googling how to replicate select top x in Excel this morning!).The only stumbling block for me using SQL is that I'm not a front end developer - I have no idea how I'd actually insert and update my data besides using SSMS, clearly using SSMS would work, however, I would prefer some form of front end.So, my question is what methods are available (aside from building a .net website!) for managing the data within the structures I'll create? - For example can Excel be used? Can I create a front end in SSRS? Is there a stupidly simple way of creating a webpage which just has in effect raw tables? - In essence I'd like to see an entire table in Excel and be able to add and edit rows with the changes being replicated in SQL.Sorry for this rather newbie/odd question - In all my SQL time I've just been querying data, creating SPs and reports, I've never got involved in front end presentation (besides a report).Thanks!

Cannot delete a SQL Server login

Posted: 08 May 2013 12:33 PM PDT

I've got the following error when I try to delete a login using MS SQL Server Management Studio:Drop failed for Login 'test'. (Microsoft.SqlServer.Smo)Additional information:Cannot drop the user 'test', because it does not exist or you do not have permission.I've checked there's no session with this login, no SQL jobs or objects are owned by this login. Can anyone help with this? Thanks.

Additional Fields in ssrs to be displayed at the end of the report

Posted: 08 May 2013 05:47 PM PDT

Hi everyonenewbie here... :)okay so I have this question ....is it possible to display additional fields at the end of a report?currently I have an additional fields selection , whereby if the user wants more than just the standard parameter selection, they can select additional fields...this has really puzzled me....please do assist :)much appreciated. thanks all

Insertion in table

Posted: 17 Mar 2013 09:28 PM PDT

HI,This is the table structure and script below:CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT NULL, [VoucherNo] [varchar](20) NOT NULL, [Quantity] [int] NULL, [CreateDate] [datetime] NULL, [ModifyDate] [datetime] NULL, [ExpiryDate] [datetime] NULL, [UserId] [int] NULL, [VoucherStatusId] [int] NOT NULL, [TransactionID] [varchar](20) NOT NULL, CONSTRAINT [PK_GV_Booklet] PRIMARY KEY CLUSTERED ( [BookletId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Script:Declare @vouchertype varchar(20) = 'Percentage Discount', @denomination int, @userid int, @voucherstatus int, @transactionID varchar(20), @quantity int = 2, @count int =1, @innercount int =1, @leaf int =10, @max intWHILE @count <= @quantity BEGIN WHILE @innercount < @leaf BEGIN SET @max = (select vouchervaluebased from gv_maxvouchervalue) SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue) INSERT INTO GV_Booklet VALUES ( @leaf, 100, 1, 'VB' + Replace( Str( @max+1, 7), ' ', '0') , @quantity, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') ) SET @innercount = @innercount + 1 UPDATE gv_maxvouchervalue SET vouchervaluebased = @max + 1 END SET @innercount = 1 SET @count = @count + 1 ENDUPDATE gv_maxvouchervalueSET vouchertransactionID = @transactionID + 1select * from GV_BookletNow let me explain you guys the scenario-- This query is for a voucher booklet. If the quantity is 2 and leaf ( describing pages in a booklet) is 10 then 20 unique vouchersno should get generated with 2 unique Transactionno as quantity of booklet is 2. How to achieve this?plz help

excluding CDC enabled tables during a full backup of db

Posted: 08 May 2013 04:19 PM PDT

I have a database with approximately 4000 tables on SQL 2008R2 x64Of those tables, approximately 70 are tracked by CDC (Change Data Capture) process.The CDC job is running constantly across this database.Question: when taking a nightly full backup of this database, is it possible to exclude the CDC enabled mirror tables ?The CDC job is crashing virtually on a nightly basis, as of late. We'd like to rule out if the backup process is coming into play. Unfortunately, I cannot change the backup timings at the moment.Thank you !!

do Distinct, Group By, Order By & Union (without All)

Posted: 08 May 2013 12:05 PM PDT

all take the same hit on Sort?Is there any way to mitigate that or conditions that make one cheaper than another forcing uniqueness?thanks very much drew

Databse auditing and management of audit logs

Posted: 08 May 2013 12:54 PM PDT

Need assistance on the following :a) What are the common SQL database events/activities that should be audited?b) How long should the audit logs be kept?Would appreciate any thoughts on the matters.

Row Compression vs. Page compression

Posted: 02 Sep 2008 12:39 AM PDT

Ok - trying to get a better understanding of when it's best to use Row compression vs. Page compression?

Automate new partition creation in table partitioning

Posted: 08 May 2013 07:58 AM PDT

Hi All, we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year. Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated Thanks all

Can someone tell me what's happening with this query?

Posted: 08 May 2013 09:36 AM PDT

Hello,I'm seeing a difference in a query between SQL 2005 and SQL 2008 and I'm not understanding. The below demonstrates it. On SQL 2008, the second query below will return 3 records. The first query is the same query but without the where clause. It returns one record. On SQL 2005, both queries return one record. If you run the inner query by itself, it will only return one record. So I don't see how the second query can select three records from a set of one.I know the join is not needed but this is a generated query. It just so happens that the select clause didn't include anything from the joined table in this case.Any help with what is happening would be appreciated. This seems at least like a behavior difference between SQL 2005 and 2008.Thanks,Kevincreate table table1(Id int, Name varchar(10))create table table2(Id int)insert into table1 values(101, 'Test')insert into table2 values(101)insert into table2 values(101)insert into table2 values(101)SELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumberFROM [Table1]LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTableSELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumberFROM [Table1]LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTableWHERE RowNumber BETWEEN 1 AND 10drop table table1drop table table2

Best method of handling optional parameters in WHERE clause

Posted: 08 May 2013 05:39 AM PDT

Hi all,I'm trying to determine what the preferred method is for handling optional parameters in WHERE clauses from a theory point of view. I've come up with three different ways: LIKE, OR, and CASE. From searching the web, CASE seems to be the most common, but I'm not seeing any differences in CPU, disk, or table usage. Here's a test script I've run with different ways of handling optional parameters.[code="sql"]DECLARE @SC VARCHAR(2) --Our optional parameterCREATE TABLE #OPTStates --Test table( StateCode CHAR(2) ,StateName VARCHAR(20))--Now, fill the table with dataINSERT INTO #OPTStates(StateCode, StateName) SELECT 'CA', 'California' UNION SELECT 'AZ', 'Arizona' UNION SELECT 'CO', 'Colorado' UNION SELECT 'NM', 'New Mexico' UNION SELECT 'NY', 'New York' --For the tests, set the optional parameter to either blank or a value by uncommenting one of the below lines--SET @SC = '' --All queries take the same amount of batchSET @SC = 'CO' --All queries take the same amount of batch--Test 1: LIKESELECT * FROM #OPTStatesWHERE StateCode LIKE LEFT(@SC + '%', 2)--Test 2: ORSELECT * FROM #OPTStatesWHERE (@SC <= '' OR StateCode = @SC)--Test 3: CASESELECT * FROM #OPTStatesWHERE StateCode = (CASE WHEN @SC > '' THEN @SC ELSE StateCode END)--Now clean upDROP TABLE #OPTStates[/code]From a theory perspective, it would seem the CASE would be preferred, since it causes tautologies. However on a more complicated query I ran, the CASE took a larger cost relative to the batch, with the LIKE leading the OR.From how you've handled optional parameters, which method do you prefer? Is there another method that works even better?Thanks!

CmdExec Job Step just hangs

Posted: 08 May 2013 06:06 AM PDT

Hello. Anyone seen a CmdExec job step hang before? When I manually invoke the job, nothing but the spinning green progress wheel. All the job is doing is:robocopy "\\Server\UNCShare" "C:\LocalFolder" *.txt /MOVI've never seen this before. I have run enable xp_cmdshell and reconfigure. Anyone have any ideas?Thanks in advance.

Can I keep x number of iterations in XP_CMDSHELL

Posted: 08 May 2013 05:49 AM PDT

HiI'm using the following to write a query out to a file...I will be running this daily, but I want to keep 30 iterations of it?and to be honest, I figured this out via google and I get what I want but if someone know a better solution or where I can read up on the switches etc.. that would be great.. thanksd[b]eclare @sql varchar(255)set @sql='bcp "SELECT lname as last, fname as first FROM db.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\1.xml" -c -T -SUCDB01 -Usa -Ppas'exec master..xp_cmdshell @sql[/b]

SSIS Data flow failing

Posted: 08 May 2013 01:00 AM PDT

I have a data flow that has been working for months. Starting this week it will transfer some of the rows then just fail. I am copying data from a DB2 database to SQL Server. Here is the error that I get from the agent:[i]The step did not generate any output. The return value was unknown. The process exit code was -529697949. The step failed.[/i]That's all it shows which seems odd, ever other error I've looked at there is usually a lot more information.I see no errors in the sysssislog for the process failing.This process moves 3 different tables from DB2 to SQL Server. This morning the first 2 data flows worked, al data moved. The third data flow moved around 376,000 rows of the 393,000 rows it should have. When I tried to rerun the data flow it only moved about 99,000 rows.Any thoughts? Any thing else you need to know?Thanks in advance for your help.

SSIS Data flow to AS400/DB2 looses records

Posted: 08 May 2013 03:33 AM PDT

We insert data into DB2 tables on an AS400. The transfer happens over the WAN. The source server is SQL Server 2008 R2 Enterprise editionSSIS reports all records were uploaded successfully but when we do an actual record count on the DB2 table we find records missing.The records are seemingly random and we experience this record loss on random tables.We then reinsert the missing records using the same SSIS package with no failure.The strangest thing about this scenario is that the records 'disappear' randomly whilst SSIS reports a green light with the correct record counts. Furthermore, we upload the record set today with no data loss and tomorrow the same record set looses records (seemingly randomly).For example we transfer 100 records to the AS400/DB2. SSIS reports success and all green with 100 records inserted. We do a record count on the DB2 table only to find 95 records for example. Tomorrow we transfer the exact same batch with no data loss.We use the Microsoft OLEDB DB2 drivers with the FastLoad option set.Does anybody know what could cause this or how to get around the problem?

Has anyone used Data Domain storage for SQL 2008 backups?

Posted: 01 Jun 2010 11:05 PM PDT

We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like the DBAs to use Data Domain storage ([url]http://www.datadomain.com/[/url]) for our disk based backup device. The backups will likely be slower, but we should have lots of disk space to keep the backups online. In essence, he will be creating us a file share on the Data Domain box. I have heard that SQL backups on a cluster do not like to write to a file share. Does anyone have experience with Data Domain in their environment? Is Data Domain storage feasible for use with a cluster?

Letting Users Modify Other Users Jobs

Posted: 08 May 2013 05:48 AM PDT

There is a team of five people that are currently sysadmins and I want to get them out of that role. The hanging point is an issue related to jobs:Currently they all can create and modify each others' jobs and need to be able to continue to do that. Furthermore, I'd like them to be able to do that with their Win logins and not using a SQL Server login. How can I accomplish this?I have tried some of the server roles (dbo, SQLServerOperator, etc.), but they do not allow this. I guess I'm looking for how a jobs ownership can be granted to other team members and am hoping there is some clever way to do this?

Maintenance Plan - Unable to Modify in SQL 2005 - However after upgrade it works !?

Posted: 07 May 2013 11:18 PM PDT

Hi all,I am using a Test and production envoironment - both were 2005 -I have created several Maintenance Plans in the past - I now want to modify one of them .....So a simple right click on the selected plan and ..... nothing ... ziltch ... I can Execute and view history ... but not modify - it does appear in the list in a BOLD FONT.I have now tested this issue on the Test Server that is running SQL 2008 R2 - Modify is still in bold - but it does allow me to modify !?Is there a permissions or "Microsoft Feature" issue here ? Any help would be appreciated.

How to define SPID of default trace

Posted: 08 May 2013 02:37 AM PDT

We have our default background trace enabled. I am trying to find its SPID number. Does it have spid at all, and how to define it ?

need Top 1 record

Posted: 08 May 2013 01:31 AM PDT

Hi Friends, I have some rows where all the fields are very much similar to each other except date & time field. I need to select top 1 row based on date & time field in descending order. I used row number method to achieve this. But i just want to know if there is any other better way. Since i am dealing with millions of data so i am facing performance issue as well as tempdb drastically increasing. Any suggestions would be really appreciated.I have attached a screen shot of the sceneraio. Friends, let me know if you need DDL and DML also.

Need some help generating an XMF file using SSIS

Posted: 08 May 2013 12:27 AM PDT

Hi Guys,I have a requirement to produce daily price files for transmition to our website hosting team who require a XMF manifest file which basically is set out so:<?xml version="1.0"?><Files> <File><FileName>DailyPrices_20130417_201304171215.csv</FileName><Type>ABPERFORMANCE</Type> </File></Files>The File name needs to be created at run time and added to the code above. I have never used XML really and am uncertain how to proceed. Any help you could offer would be gratefullly received.Many thanks Neil

Adding license key though command prompt

Posted: 08 May 2013 12:18 AM PDT

We have an eval version of Sql Server 2008 installed on one of our servers, and we want to add a license key. We used the following command, and the message returned said it completed successfully, but SSMS still says it will expire in 100 days.[center]Setup.exe /SkipRules=EditionRequirementCheck /ACTION=EditionUpgrade /INSTANCENAME=SKATESQL /PID=<the_product_key>[/center]We have to do it through the command prompt because our key is for the SBS version and the OS is Server 2008. Any ideas why this might not have worked, or what we can do instead?

DB400 to SQL Server data extraction

Posted: 08 May 2013 12:01 AM PDT

Hi all,We have a DB400 (DB2 based AS400 platform) database and I'd like to pull data from it using SQL Server, I'm sure can I can do this using OPENROWSET but could anyone advise how please i.e. do I need specific drivers etc?KR, M

Cannot get to install SQL 2008 R2 on Windows 2003 x64

Posted: 07 May 2013 10:17 PM PDT

Hi,Error is: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was expected to contain an assembly manifest.I have looked everywhere for assistance on this but just cannot get it right. I have reloaded the OS even :) Summary file contents below.------------------------------------------------Overall summary: Final result: Failed: see details below Exit code (Decimal): 1150839443 Exit facility code: 1176 Exit error code: 27283 Exit message: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was expected to contain an assembly manifest. Start time: 2013-05-08 13:09:18 End time: 2013-05-08 13:15:14 Requested action: RunRules Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130508_130906\Detail_GlobalRules.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0x6083527E%400x16A19545Machine Properties: Machine name: DB Machine processor count: 4 OS version: Windows Server 2003 OS service pack: Service Pack 2 OS region: United States OS language: English (United States) OS architecture: x64 Process architecture: 64 Bit OS clustered: NoProduct features discovered: Product Instance Instance ID Feature Language Edition Version Clustered Package properties: Description: SQL Server Database Services 2008 R2 ProductName: SQL Server 2008 R2 Type: RTM Version: 10 SPLevel: 0 Installation location: E:\x64\setup\ Installation edition: ENTERPRISEUser Input Settings: ACTION: RunRules CONFIGURATIONFILE: ENU: True FARMACCOUNT: <empty> FARMADMINPORT: 0 FARMPASSWORD: ***** FEATURES: HELP: False IACCEPTSQLSERVERLICENSETERMS: False INDICATEPROGRESS: False INSTANCENAME: <empty> PASSPHRASE: ***** QUIET: False QUIETSIMPLE: False RULES: GlobalRules UIMODE: Normal X86: False Configuration file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130508_130906\ConfigurationFile.iniDetailed results:Rules with failures:Global rules:There are no scenario-specific rules.Rules report file: The rule result report file is not available.Exception summary:The following is an exception stack listing the exceptions in outermost to innermost orderInner exceptions are being indentedException type: System.BadImageFormatException Message: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was expected to contain an assembly manifest. Data: DisableWatson = true Stack: at System.RuntimeTypeHandle._GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, Boolean loadTypeFromPartialName) at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark) at System.RuntimeType.PrivateGetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark) at System.Type.GetType(String typeName) at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceFactoryService.CreateActionHandler(ActionHandlerInfo actionHandlerInfo, String formType) at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceFactoryService.Create(String moniker) at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceService.Start(String moniker) at Microsoft.SqlServer.Configuration.UIExtension.StartAction.ExecuteAction(String actionId) at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream) at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog