Tuesday, September 24, 2013

[SQL Server 2008 issues] Problem to create a view with SHEMABINDING OPTION

[SQL Server 2008 issues] Problem to create a view with SHEMABINDING OPTION

Problem to create a view with SHEMABINDING OPTION

Posted: 23 Sep 2013 06:09 PM PDT

Hello,I met a problem when I tried to create a view with option SCHEMABINDING. I need to do that because I would to create Index.When I ues the following script, it works :SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [forms].[VUE_DOMINHO_NOYAU_sFichesIdAutresMetier]ASSELECT FI.dispo_doc_id, FOD.fod_ordre_affichage AS ORDRE_PRESENTATION, FOD.fod_libelle AS LIBELLE, FI.fic_date_creation AS DATE_CREATION, FI.service_id, CR.cr_libelle_court, FI.intervenant_id, INTER.int_nom_complet, FI.fic_intervenant_fonctionnel_id, RESP.int_nom_complet AS RESPONSABLE, FI.fic_resume, PAT.pat_ancien_id, FOD.dossier_specialite_idFROM forms.FICHE AS FI INNER JOIN forms.FORMULAIRE_DESCRIPTION AS FOD ON FOD.formulaire_id = FI.formulaire_id AND FOD.dossier_specialite_id = FI.dossier_specialite_id INNER JOIN NOYAU.PATIENT.PATIENT AS PAT ON PAT.pat_id = FI.patient_id INNER JOIN NOYAU.COEUR.INTERVENANT AS INTER ON INTER.int_id = FI.intervenant_id INNER JOIN NOYAU.COEUR.INTERVENANT AS RESP ON RESP.int_id = FI.fic_intervenant_fonctionnel_id INNER JOIN NOYAU.COEUR.CENTRE_RESPONSABILITE AS CR ON CR.cr_code = FI.service_idBut when I add the option WITH SCHEMABINDING I got a error. I think I can't create aa view with schemabinding option because I try to make a view between two databases but I'm not sure.Do you have a idea ?Thanks for your help,EC

Tracking database changes

Posted: 23 Sep 2013 07:00 AM PDT

Hey all,I am in need of obtaining software or a process to automatically find DDL changes to my enterprise environment (hundreds of instances from SQL2000 to SQL2014). Years back, I used to use Bill Wunder's products (the original DTS version, then DDL AU, and finally SQL Clue). Fantastic products; however appears he has stopped development on this product, which is a shame as I found his software to be incredibly useful and insightful. Anyway, curious to if any of you are aware of any similar products out there. The ideal world is recreating what Bill did in by scripting out all database objects to all servers (with filters), comparing and checking into a source control system with front end reporting. Thanks!

Symptoms Memory Problems

Posted: 22 Sep 2013 10:33 PM PDT

what are the symptoms of Memory Problems in sql server

Hierarchical Data Type vs Parent-Child

Posted: 23 Sep 2013 03:47 PM PDT

Hi,Could someone please tell me when to (not)use what? From what I read it seems that using Hierarchical type is not always preferred to using "normal" Parent-Child cte, for example.Or it depends? And if yes, then on what? So, basically any thoughts and/or real life experiences/cases would be greatly appreciated... Just an overall picture, if I mayThanks!

subtract from two tables

Posted: 22 Sep 2013 11:59 PM PDT

hi everyone,Hope you are good. Need some little help. I am developing some inventory app and now need some help on the stock status. please find below.table1 consists of all items In.(may consist of duplicate entries)table2 consists of items that will be out.table1- PurchaseOrderNo SerialNo Qty001 I000 20001 I001 10002 I000 50003 I002 20 table2-IssueNo SerialNo QtyS001 I000 10S002 I001 5I will need as follows if possibleSerialNo BalanceQtyI000 60I001 5I002 20Can you guys help me with the query? I came up with something but the item should be in both tables. For instance For item I002, I don't get the balance quantity.please see query below. Thanks to helpSELECT s.Item, SUM(m.Qty) as total, SUM(s.Qty) as used, SUM(m.Qty) - SUM(s.Qty) as balFROM Store_Out_Details sJOIN Store_PO_Details m ON m.Item=s.ItemGROUP BY s.Item,s.Qty

Invalid object name ??!

Posted: 23 Sep 2013 06:30 AM PDT

Run:use xxxxgoselect * from [xxxx].[xxxxxx]Got:Msg 208, Level 16, State 1, Line 1Invalid object name 'xxxxxx'.i double checked:1) run it under right database and right table, i run dbcc checktable see 55 rows inside;2) can run sp_help [xxxx].[xxxxxx] without any issues3) dbcc checkdb without any errorsAnything looks good, just not able to run simple "SELECT", what else could be wrong ? Please help, it's Production issue. Thanks a lot !!!

SSIS to download .txt file from URL and import data in SQL Server table

Posted: 10 Sep 2013 04:18 AM PDT

There is a text file placed on a URL lets say http://mysubsite.domain.com/Customers.txtThis text file has \t \Column1 \t \Coilumn2 and so on then to have new line data for column1 and then Column2 with n rowsI have to pull this list of customers in existing customer table. if this customer already exists then do nothing if this customer is not in list then add in Customer table. I need some guidance if this could be done through SSIS, if yes then how? I have no idea how to implement this in SSIS. I was trying to write a win32 service in c# to download text file locally and then run BCP to export data in a temp file then call SP to see if the customer is updated then update else if not found then add in Customer table. I am not sure if this could be done through SSIS and if yes i hope it would be fast and quick to implement, if someone already worked on it. What is the best option to choose, I need to know what is the best solution and how it could be done if possible in SSIS? I need to do this on daily bases to get text file up to date with my Customer table.Shamshad Ali

Script to check the progress of rebuild Index?

Posted: 18 Sep 2013 08:50 PM PDT

Hi,Is there any good script available to check the progress of Rebuild Index?Would be helpful.

TSQL/SSRS: How to add "dynamics" without dynamic SQL

Posted: 23 Sep 2013 04:50 AM PDT

Hi,I'm facing a task how to deal with unknown number of user entered params, could be from 1 to 10.User actually will type this into text box comma separated, that what they want, we can't go with with param list, as it tooooooo big, (1000+)And I don't know how put this into my code for SSRS report, it not desired to be dynamic but if not choice we probably can go with too.So, user want manually enter list of params and then I need to find WHERE all of them are true,[code="sql"]@param1 = 'Alpha, Bravo, Charlie, Delta' --up to 10 maxselect * from T1 where c1 = 'Alpha' or c1 = 'Bravo'or C1 = 'Charlie'or C1 = 'Delta'[/code]Not sure if I can use arrays in my situation, I'm on TSQL 2008 and SSRS, that's it. The only way I see it now to split by ',' into param list (or load it into table), then make max number of WHERE .something like 'Where c1 = param1a' and param1a is not NULL and ..... up to max 10 occurences...Thanks to all, hope somebody can push me to the right place how to do this nicely.Mario

Backup SQL agent Job

Posted: 18 Sep 2013 11:20 PM PDT

Can I setup a single agent job to handle Full(Every Sunday at 00:00), Differential(Everyday at 00:00) and log backups(every 30 mins) in a single job without stored proc logic, or do I need to setup 3 different jobs?If different jobs are needed because of the different schedules, how can I make sure that jobs don't overlap(I don't need the Diff job at 00:00 if a full backup is running, or a log backup when I also do either of the previous two). I cant seem to figure this out in the job schedulerThanks.

adding another target server to a designated MSX

Posted: 23 Sep 2013 02:54 AM PDT

HiWe have a MSX server with a number of target servers already added. I am trying to add a new server to an existing list. On the MSX server i right click SQL Server Agent(msx) - multi server administration - Add Target servers...The 'Welcome to the Matser Server Wizard' appears telling me it helps me make this server a Master server. I can add the new server with no problems but do i need to re-add all the other servers as if I was doing this from the start ? Does this process in effect undo the current list of targeted servers because it gives me the impression I will just have the new server in my targeted servers if i complete the process. (I havent completed it yet for this reason)

help needed in power shell script to script out sql server jobs

Posted: 23 Sep 2013 01:46 AM PDT

Hello,I created this script to script out SQL server jobs, the script creates a file matching the job name and script it out as a sql script.the script works most of the time, the only time it generates exception, when I have non conventional job names like "load/MTS Error Check" for example where I have "/", so I tried to add some logic to replace those wearied characters without lock, I want to change the file names not the job name on the server.SQL server replication throws the same exception "Out-File : The given path's format is not supported."what I'm doing wrong?. Import-Module SQLPS -DisableNameChecking#Script out all job steps and created a file per job#created by Mulham Hafiez on 9/18/2013#map a drive to a list of serversNew-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\Share1\jobsforeach ($instancename in get-content -path Microsoft.PowerShell.Core\FileSystem::\\Share1\Servers\Servers.txt) { $Date =Get-date -Format "yyyy-M-d_hmmtt" $folder= "UNCPath:\servers\jobs" $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName#extract each job the outer foreach loop and script itforeach ($job in $server.JobServer.Jobs){#IF the output folder does not exist then create it $OutputFolder = "$folder\$instancename\" $DoesFolderExist = Test-Path $OutputFolder $null = if (!$DoesFolderExist){MKDIR "$OutputFolder"} # $job | foreach {$_.Script()} | out-file $fullpath $JobName =$job.Name # remove non conventional charachters $JobName |ForEach-object { $_ -replace "\\",'_' ` -replace "//" ,'_'` } $jobNameFile = $jobName+"_"+$date+".sql" $fullpath =Join-Path $OutputFolder $jobNameFile $job | foreach {$_.Script()} | Out-File $fullpath } }

identity to Flase

Posted: 23 Sep 2013 01:23 AM PDT

I want to change the identity of the column to false when the column is primary key , how to change the identity property is to be False in one of the table to update a record ?

How to change MSDTC name on cluster.

Posted: 23 Sep 2013 01:02 AM PDT

Hi ALL,I have configured MSDTC on win 2008 r2 cluster as a separate group.My client want to change MSDTC name to different name.Can any one tell me what are the steps to change the MSDTC name on cluster.or any information how to change that.....Thanks in advance...

Understandig row length calculation for sparse columns

Posted: 12 Sep 2012 05:52 AM PDT

Hello,I hope someone can explain the internals for row length.We have a 3rd party vendor table with following definition: 7 non-sparse columns and 340 varchar(max) sparse columns:CREATE TABLE [dbo].[ReportSparseData]( [ReportSparseDataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [ReportMainId] [int] NOT NULL, [ReportYearId] [char](2) NOT NULL, [UserId] [int] NOT NULL, [DateAdded] [datetime] NOT NULL, [DateLstMod] [datetime] NOT NULL, [ts] [timestamp] NOT NULL, [C1] [varchar](max) SPARSE NULL, [C2] [varchar](max) SPARSE NULL, [C3] [varchar](max) SPARSE NULL, [C4] [varchar](max) SPARSE NULL, [C5] [varchar](max) SPARSE NULL, [C6] [varchar](max) SPARSE NULL, ------ [C339] [varchar](max) SPARSE NULL, [C340] [varchar](max) SPARSE NULL, CONSTRAINT [Pk_ReportSparseData_[ReportSparseDataId] PRIMARY KEY CLUSTERED ( [ReportSparseDataId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO -------------------------------------------------------------------Question 1: What would be the row size for this table definition? -------------------------------------------------------------------We need to start replicating this table to another environement for reporting. Our publisher, subscriber and distributor are sql server 2008 r2.When I try to create snapshot for this table, it fails with the following error:Message: Cannot create a row of size 8875 which is greater than the allowable maximum row size of 8060.Stack: (Source: MSSQLServer, Error number: 511)------------------------------------------------------------------------------------------------Question 2: How do I get transactional replication to work for this table? Is it possible with all columns? --------------------------------------------------------------------------------------------------Say If every page has row overflow allocation pointer for all columns, then also page size will be (340*24) + 38 bytes = 8198 for the first 7 columns. That still does not add up to 8875 bytes.I checked the data in the table. All rows have in-line data and no row-overflow allocation despite varchar(max) data type. We added total bytes for all columns for each row in the table. The max row size was 3071 bytes.------------------------------------------------------------------Question 3: where does this total 8875 bytes coming from?------------------------------------------------------------------I just would like to clear my concepts here on what would be the row size for the table definition I provided? What overheads am I missing in calculation?We have case opened with Microsoft for over a week now since this table needs to be replicated in prod environemnt in a week from now but they are unable to provide any help on this matter so far. I hope to get some answer here. Please let me know if I am not clear in my question. Thanks,

SQL Server 2008 R2 SP1 security patch failed

Posted: 22 Sep 2013 10:57 PM PDT

We have a SQL server instance whose patch level isMicrosoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)We need to apply SQLServer2008R2-KB2716439-x64Security patch is getting failed with below error in summry_...txt fileOverall summary: Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Exit code (Decimal): -2058354685 Exit facility code: 1360 Exit error code: 3 Exit message: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Start time: 2013-09-23 12:12:40 End time: 2013-09-23 12:32:11 Requested action: PatchInstance MSSQLSERVER overall summary: Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Exit code (Decimal): -2058354685 Exit facility code: 1360 Exit error code: 3 Exit message: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Start time: 2013-09-23 12:18:11 End time: 2013-09-23 12:32:04Found below error message in detail.txt file2013-09-23 12:13:00 Slp: Skipping property InstallMediaPath of class: InitializeMsiExtension2013-09-23 12:13:00 Slp: Input of property will not be set due to the following failed restriction(s): 2013-09-23 12:13:00 Slp: Condition "Is the datastore flag /Scenario/GlobalFlags/IsMediaRequired set" did not pass as it returned false and true was expected.Please advice.

Unique Constraint

Posted: 22 Sep 2013 09:51 PM PDT

I have two columns Student_ID and MAC_ID in my table.I want to put Unique constraint on these two columns together.I am using SQL Server Management Studio 8 .Can you please tell me the steps ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog