Search Results

Search found 30023 results on 1201 pages for 'sql to linq'.

Page 77/1201 | < Previous Page | 73 74 75 76 77 78 79 80 81 82 83 84  | Next Page >

  • LINQ - Contains with anonymous type

    - by Marlos
    When using this code (simplified for asking): var rows1 = (from t1 in db.TABLE1 where (t1.COLUMN_A == 1) select new { t1.COLUMN_B, t1.COLUMN_C }); var rows2 = (from t2 in db.TABLE2 where (rows1.Contains(t2.COLUMN_A)) select t2; I got the following error: The type arguments for method 'System.Linq.Enumerable.Contains(System.Collections.Generic.IEnumerable, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly. I need to filter the first result by COLUMN_B, but I don't know how. Is there a way to filter it?

    Read the article

  • Linq like or other construction

    - by Yauhen Kavalenka
    I have DB oracle in my solution. I want to have some results in this query. Query example: select * from doctor where doctor.name like '%IVANOV_A%'; But if i do it at LINQ i cannot get any result. from p in repository.Doctor.Where(x => x.Name.ToLower().Containsname)) select p; Where 'name' is variable of string parameter. Web layout request next string: "Ivanov a" or "A Ivanov" But i suggest for user choose you pattetn for query. How i can to get "patient by name" if name consist of "First name" and "Last name" but user doesn't know your doctor's full name?

    Read the article

  • LINQ to SQL: Issue with concurrency

    - by Gib
    I’m working on a sandwich ordering app in ASP.NET MVC, C# and LINQ to SQL. The app revolves around the user creating multiple custom-made sandwiches from a selection of ingredients. When it comes to confirming the order I need to know that there’s enough portions of each ingredient to fulfil all the sandwiches in the user’s order before I commit to the DB as it is possible that an ingredient will go out of stock between adding it to their basket and confirming the order. A bit about the database: Ingredient – Stores ingredient details including number of portions Order – Header table for an order, simply stores the order time OrderDetail – Stores a record of each sandwich in an order OrderDetailItem – Stores each ingredient in each sandwich in an order So basically I’m wondering what the best approach to ensuring that before I add records to Order, OrderDetail and OrderDetailItem I can ensure that the order can be met.

    Read the article

  • Linq to sql Incorrect varchar length

    - by scott
    I have a table with a nullable varchar(50) column in it. When I am updating the value through linq to sql and trace the call in profiler it is defining the parameter as varchar(36). This is obviously causing some minor issues when we are trying to insert data that is between 37 and 50 characters long. I have tried removing the table and re-adding it to the design surface but the same thing happens. I also tried removing that property and adding it manually, same issue. When I look at the designer.cs code it shows the attribute properly: [Column(Storage="_Name", DbType="VarChar(50)")] I am out of ideas, anybody seen this before? Every other column is correct.

    Read the article

  • Cannot Translate to SQL using Select(x => Func(x))

    - by Dan
    I'm slowly learning the ins and outs of LINQtoSQL, but this is confusing me. Here is the statement I have: IQueryable<IEvent> events = (from e in db.getEvents() select e).Select(x => SelectEvent(x, null)); What the SelectEvent does can be explained in this answer here. I am not using the .toList() function as I don't want potentially thousands of records brought into memory. public IEvent SelectEvent(SqlServer.Event ev, EventType? type) { // Create an object which implements IEvent // I don't have the code in front of me, so forgive the lack of code } My question is really for the Select() method. I get the "Cannot translate to SQL" error and the Select() is listed in the error message. Clueless on this one :-/.

    Read the article

  • Linq issue retrieving single value from mssql

    - by Anicho
    Hey Guys, having an issue with linq, I am basically doing the following but it is saying it is "UserProfile does not contain definition for Username" in the current context where I wrote "u.Username" but it does exist I have added UserProfile table to MyDbml.dbml and if I connect to another table it works fine. TiamoDataContext context = new TiamoDataContext(); var user = (from u in context.UserProfiles where u.Username == UsernameLabel select u).FirstOrDefault(); I am flustered by what small detail I have missed out to get this to work, anyone have any clues it would be much appreciation.

    Read the article

  • Will this LINQ-TO-SQL query fetch all records from the table ?

    - by Puneet Dudeja
    public long GetNewCRN() { return ((from c in DataContext.GetTable<Cust_Master>() select c.CUSTSERH_CRN).Max() + 1); } Will this Linq to Sql query fetch all records from the table first and then select the maximum of the column ? If yes, then isn't it a bad idea using linq to sql instead of normal SqlCommand ? Or is there any other way of doing it in linq to sql ? When I attach Console.Out, I see nothing(command prompt does not even open). But when I include following:- context.Log = new System.IO.StreamWriter("d:\\abcd.txt"); I get an error, that "The process can not access the file because it is being used by another process" and that process is "w3wp.exe". How can I see the sql commands being executed by DataContext then ?

    Read the article

  • LINQ to Entity, joining on NOT IN tables

    - by SlackerCoder
    My brain seems to be mush right now! I am using LINQ to Entity, and I need to get some data from one table that does NOT exist in another table. For example: I need the groupID, groupname and groupnumber from TABLE A where they do not exist in TABLE B. The groupID will exist in TABLE B, along with other relevant information. The tables do not have any relationship. In SQL it would be quite simply (there is a more elegant and efficient solution, but I want to paint a picture of what I need) SELECT GroupID, GroupName, GroupNumber, FROM TableA WHERE GroupID NOT IN (SELECT GroupID FROM TableB) Is there an easy/elegant way to do this? Right now I have a bunch of queries hitting the db, then comparing, etc. It's pretty messy. Thanks.

    Read the article

  • Make a usable Join relationship with LINQ on top of a database CSV design error

    - by jdk
    I'm looking for a way to fix and/or abstract away a comma-separated values (CSV) list in a database field in order to reconstruct a usable relationship such that I can properly join the two tables below and query them using LINQ and its Join method. Following is a sample showing the Person table and CsvArticleIds field having a CSV value to represent a one-to-many association with Article records. TABLE [dbo].[Person] Id Name CsvArticleIds -- ---------- -------- 1 Joe "15,22" 5 Ed "22" 10 Arnie "8,15,22" ^^^(Of course a link table should have been created; nonetheless the relationship with articles is trapped inside that list of CSV values.) TABLE [dbo].[Article] Id Title -- ---------- 8 Beginning C# 15 A Historic look at Programming in the 90s 22 Gardening in January Additional Info the fix can be at any level: C#.NET or SQL Server something easy because I will be repeating the solution for many other CSV values in other tables. Elegant is nice too. not looking for efficiency because this is part of a one-time data migration task and can take as long as it wants to run.

    Read the article

  • LINQ saving images to varbinary

    - by m4rc
    I'm having issues saving images to a varbinary(Max) field using LINQ. I can save files in the region of 10KB to the database no problems, but when it comes to files bigger than that, it's as though it doesn't even try. I've had a look in the SQL Server Profiler and when the file is around 10KB I can see the full insert statement in the detail pane. However, when the file is a bit bigger, the detail pane doesn't show anything, although any data besides the varbinary field is written to the database. The data is in the Data Object just before SubmitChanges so I can't figure out what's happening between now and then!

    Read the article

  • Next Identity Key LINQ + SQL Server

    - by user569347
    To represent our course tree structure in our Linq Dataclasses we have 2 columns that could potentially be the same as the PK. My problem is that if I want to Insert a new record and populate 2 other columns with the PK that was generated there is no way I can get the next identity and stop conflict with other administrators who might be doing the same insert at the same time. Case: A Leaf node has right_id and left_id = itself (prereq_id) **dbo.pre_req:** prereq_id left_id right_id op_id course_id is_head is_coreq is_enforced parent_course_id and I basically want to do this: pre_req rec = new pre_req { left_id = prereq_id, right_id = prereq_id, op_id = 3, course_id = query.course_id, is_head = true, is_coreq = false, parent_course_id = curCourse.course_id }; db.courses.InsertOnSubmit(rec); try { db.SubmitChanges(); } Any way to solve my dilemma? Thanks!

    Read the article

  • Hype and LINQ

    - by Tony Davis
    "Tired of querying in antiquated SQL?" I blinked in astonishment when I saw this headline on the LinqPad site. Warming to its theme, the site suggests that what we need is to "kiss goodbye to SSMS", and instead use LINQ, a modern query language! Elsewhere, there is an article entitled "Why LINQ beats SQL". The designers of LINQ, along with many DBAs, would, I'm sure, cringe with embarrassment at the suggestion that LINQ and SQL are, in any sense, competitive ways of doing the same thing. In fact what LINQ really is, at last, is an efficient, declarative language for C# and VB programmers to access or manipulate data in objects, local data stores, ORMs, web services, data repositories, and, yes, even relational databases. The fact is that LINQ is essentially declarative programming in a .NET language, and so in many ways encourages developers into a "SQL-like" mindset, even though they are not directly writing SQL. In place of imperative logic and loops, it uses various expressions, operators and declarative logic to build up an "expression tree" describing only what data is required, not the operations to be performed to get it. This expression tree is then parsed by the language compiler, and the result, when used against a relational database, is a SQL string that, while perhaps not always perfect, is often correctly parameterized and certainly no less "optimal" than what is achieved when a developer applies blunt, imperative logic to the SQL language. From a developer standpoint, it is a mistake to consider LINQ simply as a substitute means of querying SQL Server. The strength of LINQ is that that can be used to access any data source, for which a LINQ provider exists. Microsoft supplies built-in providers to access not just SQL Server, but also XML documents, .NET objects, ADO.NET datasets, and Entity Framework elements. LINQ-to-Objects is particularly interesting in that it allows a declarative means to access and manipulate arrays, collections and so on. Furthermore, as Michael Sorens points out in his excellent article on LINQ, there a whole host of third-party LINQ providers, that offers a simple way to get at data in Excel, Google, Flickr and much more, without having to learn a new interface or language. Of course, the need to be generic enough to deal with a range of data sources, from something as mundane as a text file to as esoteric as a relational database, means that LINQ is a compromise and so has inherent limitations. However, it is a powerful and beautifully compact language and one that, at least in its "query syntax" guise, is accessible to developers and DBAs alike. Perhaps there is still hope that LINQ can fulfill Phil Factor's lobster-induced fantasy of a language that will allow us to "treat all data objects, whether Word files, Excel files, XML, relational databases, text files, HTML files, registry files, LDAPs, Outlook and so on, in the same logical way, as linked databases, and extract the metadata, create the entities and relationships in the same way, and use the same SQL syntax to interrogate, create, read, write and update them." Cheers, Tony.

    Read the article

  • Automating deployments with the SQL Compare command line

    - by Jonathan Hickford
    In my previous article, “Five Tips to Get Your Organisation Releasing Software Frequently” I looked at how teams can automate processes to speed up release frequency. In this post, I’m looking specifically at automating deployments using the SQL Compare command line. SQL Compare compares SQL Server schemas and deploys the differences. It works very effectively in scenarios where only one deployment target is required – source and target databases are specified, compared, and a change script is automatically generated and applied. But if multiple targets exist, and pressure to increase the frequency of releases builds, this solution quickly becomes unwieldy.   This is where SQL Compare’s command line comes into its own. I’ve put together a PowerShell script that loops through the Servers table and pulls out the server and database, these are then passed to sqlcompare.exe to be used as target parameters. In the example the source database is a scripts folder, a folder structure of scripted-out database objects used by both SQL Source Control and SQL Compare. The script can easily be adapted to use schema snapshots.     -- Create a DeploymentTargets database and a Servers table CREATE DATABASE DeploymentTargets GO USE DeploymentTargets GO CREATE TABLE [dbo].[Servers]( [id] [int] IDENTITY(1,1) NOT NULL, [serverName] [nvarchar](50) NULL, [environment] [nvarchar](50) NULL, [databaseName] [nvarchar](50) NULL, CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED ([id] ASC) ) GO -- Now insert your target server and database details INSERT INTO dbo.Servers ( serverName , environment , databaseName) VALUES ( N'myserverinstance' , N'myenvironment1' , N'mydb1') INSERT INTO dbo.Servers ( serverName , environment , databaseName) VALUES ( N'myserverinstance' , N'myenvironment2' , N'mydb2') Here’s the PowerShell script you can adapt for yourself as well. # We're holding the server names and database names that we want to deploy to in a database table. # We need to connect to that server to read these details $serverName = "" $databaseName = "DeploymentTargets" $authentication = "Integrated Security=SSPI" #$authentication = "User Id=xxx;PWD=xxx" # If you are using database authentication instead of Windows authentication. # Path to the scripts folder we want to deploy to the databases $scriptsPath = "SimpleTalk" # Path to SQLCompare.exe $SQLComparePath = "C:\Program Files (x86)\Red Gate\SQL Compare 10\sqlcompare.exe" # Create SQL connection string, and connection $ServerConnectionString = "Data Source=$serverName;Initial Catalog=$databaseName;$authentication" $ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString); # Create a Dataset to hold the DataTable $dataSet = new-object "System.Data.DataSet" "ServerList" # Create a query $query = "SET NOCOUNT ON;" $query += "SELECT serverName, environment, databaseName " $query += "FROM dbo.Servers; " # Create a DataAdapter to populate the DataSet with the results $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerConnection) $dataAdapter.Fill($dataSet) | Out-Null # Close the connection $ServerConnection.Close() # Populate the DataTable $dataTable = new-object "System.Data.DataTable" "Servers" $dataTable = $dataSet.Tables[0] #For every row in the DataTable $dataTable | FOREACH-OBJECT { "Server Name: $($_.serverName)" "Database Name: $($_.databaseName)" "Environment: $($_.environment)" # Compare the scripts folder to the database and synchronize the database to match # NB. Have set SQL Compare to abort on medium level warnings. $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/AbortOnWarnings:Medium") # + @("/sync" ) # Commented out the 'sync' parameter for safety, write-host $arguments & $SQLComparePath $arguments "Exit Code: $LASTEXITCODE" # Some interesting variations # Check that every database matches a folder. # For example this might be a pre-deployment step to validate everything is at the same baseline state. # Or a post deployment script to validate the deployment worked. # An exit code of 0 means the databases are identical. # # $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/Assertidentical") # Generate a report of the difference between the folder and each database. Generate a SQL update script for each database. # For example use this after the above to generate upgrade scripts for each database # Examine the warnings and the HTML diff report to understand how the script will change objects # #$arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/ScriptFile:update_$($_.environment+"_"+$_.databaseName).sql", "/report:update_$($_.environment+"_"+$_.databaseName).html" , "/reportType:Interactive", "/showWarnings", "/include:Identical") } It’s worth noting that the above example generates the deployment scripts dynamically. This approach should be problem-free for the vast majority of changes, but it is still good practice to review and test a pre-generated deployment script prior to deployment. An alternative approach would be to pre-generate a single deployment script using SQL Compare, and run this en masse to multiple targets programmatically using sqlcmd, or using a tool like SQL Multi Script.  You can use the /ScriptFile, /report, and /showWarnings flags to generate change scripts, difference reports and any warnings.  See the commented out example in the PowerShell: #$arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/ScriptFile:update_$($_.environment+"_"+$_.databaseName).sql", "/report:update_$($_.environment+"_"+$_.databaseName).html" , "/reportType:Interactive", "/showWarnings", "/include:Identical") There is a drawback of running a pre-generated deployment script; it assumes that a given database target hasn’t drifted from its expected state. Often there are (rightly or wrongly) many individuals within an organization who have permissions to alter the production database, and changes can therefore be made outside of the prescribed development processes. The consequence is that at deployment time, the applied script has been validated against a target that no longer represents reality. The solution here would be to add a check for drift prior to running the deployment script. This is achieved by using sqlcompare.exe to compare the target against the expected schema snapshot using the /Assertidentical flag. Should this return any differences (sqlcompare.exe Exit Code 79), a drift report is outputted instead of executing the deployment script.  See the commented out example. # $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/Assertidentical") Any checks and processes that should be undertaken prior to a manual deployment, should also be happen during an automated deployment. You might think about triggering backups prior to deployment – even better, automate the verification of the backup too.   You can use SQL Compare’s command line interface along with PowerShell to automate multiple actions and checks that you need in your deployment process. Automation is a practical solution where multiple targets and a higher release cadence come into play. As we know, with great power comes great responsibility – responsibility to ensure that the necessary checks are made so deployments remain trouble-free.  (The code sample supplied in this post automates the simple dynamic deployment case – if you are considering more advanced automation, e.g. the drift checks, script generation, deploying to large numbers of targets and backup/verification, please email me at [email protected] for further script samples or if you have further questions)

    Read the article

  • Heroku Postgres: A New SQL Database-as-a-Service

    Idera, a Houston-based company known worldwide for its SQL Server solutions in the realms of backup and recovery, performance monitoring, auditing, security, and more, recently announced that it had won five of SQL Server Magazine's 2011 Community Choice Awards. SQL Server Magazine, a publication produced by Penton Media, offers SQL Server users, both beginning and advanced, a host of hands-on information delivered by SQL Server experts. The magazine presented Idera with 2011 Community Choice Awards for five separate products which will only serve to boost the already strong reputation of it...

    Read the article

  • Idera Releases SQL Diagnostic Manager v7.1

    Idera recently beefed up its portfolio of SQL Server management and administration tools with the release of SQL diagnostic manager 7.1. Idera has enhanced SQL diagnostic manager's already impressive set of features in version 7.1 with new additions that should appeal to database administrators. The release is another example of Idera's dedication to growing its portfolio of SQL Server solutions that has allowed the Microsoft Managed Partner to expand its client base to over 10,000 customers worldwide. The highlights of SQL diagnostic manager 7.1's new features begin with an impressive Serve...

    Read the article

  • How to Schedule Backups with SQL Server Express

    - by The Official Microsoft IIS Site
    Microsoft’s SQL Server Express is a fantastic product for anyone needing a relational database on a limited budget. By limited budget I’m talking free. Yes SQL Server Express is free but it comes with a few limitations such as only utilizing 1 GB of RAM, databases are limited to 10 GB, and it does not include SQL Profiler. For low volume sites that do not need enterprise level capabilities, this is a compelling solution. Here is a complete SQL Server feature comparison of all the SQL Server...(read more)

    Read the article

  • Linq To Sql Concat() dropping fields in created TSQL

    - by user191468
    This is strange. I am moving a stored proc to a service. The TSQL unions multiple selects. To replicate this I created multiple queries resulting in a common new concrete type. Then I issue a return result.ToString(); and the resulting SQL selects have varying numbers of columns specified thus causing an MSSQL Msg 205... using (var db = GetDb()) { var fundInv = from f in db.funds select new Investments { Company = f.company, FullName = f.fullname, Admin = f.admin, Fund = f.fund1, FundCode = f.fundcode, Source = STR_FUNDS, IsPortfolio = false, IsActive = f.active, Strategy = f.strategy, SubStrategy = f.substrategy, AltStrategy = f.altstrategy, AltSubStrategy = f.altsubstrategy, Region = f.region, AltRegion = f.altregion, UseAlternate = f.usealt, ClassesAllowed = f.classallowed }; var stocksInv = from s in db.stocks where !fundInv.Select(f => f.Company).Contains(s.vehcode) select new Investments { Company = s.company, FullName = s.issuer, Admin = STR_PRS, Fund = s.shortname, FundCode = s.vehcode, Source = STR_STOCK, IsPortfolio = false, IsActive = (s.inactive == null), Strategy = s.style, SubStrategy = s.substyle, AltStrategy = s.altstyle, AltSubStrategy = s.altsubsty, Region = s.geography, AltRegion = s.altgeo, UseAlternate = s.usealt, ClassesAllowed = STR_GENERIC }; var bondsInv = from oi in db.bonds where !fundInv.Select(f => f.Company).Contains(oi.vehcode) select new Investments { Company = string.Empty, FullName = oi.issue, Admin = STR_PRS1, Fund = oi.issue, FundCode = oi.vehcode, Source = STR_BONDS, IsPortfolio = false, IsActive = oi.closed, Strategy = STR_OTH, SubStrategy = STR_OTH, AltStrategy = STR_OTH, AltSubStrategy = STR_OTH, Region = STR_OTH, AltRegion = STR_OTH, UseAlternate = false, ClassesAllowed = STR_GENERIC }; return (fundInv.Concat(stocksInv).Concat(bondsInv)).ToList(); } The code above results in a complex select statement where each "table" above has different column count. (see SQL below) I've been trying a few things but no change yet. Ideas are welcome. SELECT [t6].[company] AS [Company], [t6].[fullname] AS [FullName], [t6].[admin] AS [Admin], [t6].[fund] AS [Fund], [t6].[fundcode] AS [FundCode], [t6].[value] AS [Source], [t6].[value2] AS [IsPortfolio], [t6].[active] AS [IsActive], [t6].[strategy] AS [Strategy], [t6].[substrategy] AS [SubStrategy], [t6].[altstrategy] AS [AltStrategy], [t6].[altsubstrategy] AS [AltSubStrategy], [t6].[region] AS [Region], [t6].[altregion] AS [AltRegion], [t6].[usealt] AS [UseAlternate], [t6].[classallowed] AS [ClassesAllowed] FROM ( SELECT [t3].[company], [t3].[fullname], [t3].[admin], [t3].[fund], [t3].[fundcode], [t3].[value], [t3].[value2], [t3].[active], [t3].[strategy], [t3].[substrategy], [t3].[altstrategy], [t3].[altsubstrategy], [t3].[region], [t3].[altregion], [t3].[usealt], [t3].[classallowed] FROM ( SELECT [t0].[company], [t0].[fullname], [t0].[admin], [t0].[fund], [t0].[fundcode], @p0 AS [value], [t0].[active], [t0].[strategy], [t0].[substrategy], [t0].[altstrategy], [t0].[altsubstrategy], [t0].[region], [t0].[altregion], [t0].[usealt], [t0].[classallowed] FROM [zInvest].[funds] AS [t0] UNION ALL SELECT [t1].[company], [t1].[issuer], @p6 AS [value], [t1].[shortname], [t1].[vehcode], @p7 AS [value2], @p8 AS [value3], (CASE WHEN [t1].[inactive] IS NULL THEN 1 ELSE 0 END) AS [value5], [t1].[style], [t1].[substyle], [t1].[altstyle], [t1].[altsubsty], [t1].[geography], [t1].[altgeo], [t1].[usealt], @p10 AS [value6] FROM [zBank].[stocks] AS [t1] WHERE (NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [zInvest].[funds] AS [t2] WHERE [t2].[company] = [t1].[vehcode] ))) AND ([t1].[vehcode] <> @p2) AND (SUBSTRING([t1].[vehcode], @p3 + 1, @p4) <> @p5) ) AS [t3] UNION ALL SELECT @p11 AS [value], [t4].[issue], @p12 AS [value2], [t4].[vehcode], @p13 AS [value3], @p14 AS [value4], [t4].[closed], @p16 AS [value6], @p17 AS [value7] FROM [zMut].[bonds] AS [t4] WHERE NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [zInvest].[funds] AS [t5] WHERE [t5].[company] = [t4].[vehcode] )) ) AS [t6]

    Read the article

  • Querying Visual Studio project files using T-SQL and Powershell

    - by jamiet
    Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents. Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes: Two views and a stored procedure, nothing fancy. I suppressed warnings for [View1] & [Procedure1] and hence the pertinent part my project file looks like this:   <ItemGroup>    <Build Include="Schema Objects\Schemas\dbo\Views\View1.view.sql">      <SubType>Code</SubType>      <SuppressWarnings>4151,3276</SuppressWarnings>    </Build>    <Build Include="Schema Objects\Schemas\dbo\Views\View2.view.sql">      <SubType>Code</SubType>    </Build>    <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql">      <SubType>Code</SubType>      <SuppressWarnings>4151</SuppressWarnings>    </Build>  </ItemGroup>  <ItemGroup> Note the <SuppressWarnings> elements – those are the bits of information that I am after. With a lot of help from folks on the SQL Server XML forum  I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods: DECLARE @xml XML; SELECT @xml = CAST(pkgblob.BulkColumn AS XML) FROM   OPENROWSET(BULK 'C:\temp\QueryingProjectFileDemo\QueryingProjectFileDemo.dbproj' -- <-Change this path!                    ,single_blob) AS pkgblob                    ;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns) SELECT  REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName]        ,[SuppressedWarnings] FROM   (        SELECT  build.query('.') AS [_node]        ,       build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings]        ,       build.value('@Include','nvarchar(1000)') AS [ObjectPath]        FROM    @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build)        )q And here’s the output: And that’s it – an easy way of discovering which warnings have been suppressed and for which objects in your database projects. I won’t bother going over the code as it is fairly self-explanatory – peruse it at your leisure.   Once I had the SQL above I figured I’d share it around a little in case it was ever useful to anyone else; hence I’m writing this blog post and I also posted it on the Visual Studio Database Development Tools forum at FYI: Discover which objects have had warnings suppressed. Luckily Kevin Goode saw the thread and he posted a different solution to the same problem, one that uses Powershell. The advantage of Kevin’s Powershell approach is that it is easy to analyse many .dbproj files at the same time. Below is Kevin’s code which I have tweaked ever so slightly so that it produces the same results as my SQL script (I just want any object that had had a warning suppressed whereas Kevin was querying specifically for warning 4151):   cd 'C:\Temp\QueryingProjectFileDemo\' cls $projects = ls -r -i *.dbproj Foreach($project in $projects) { $xml = new-object System.Xml.XmlDocument $xml.set_PreserveWhiteSpace( $true ) $xml.Load($project) #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings=4151]/@Include"} #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[contains(e:SuppressWarnings,'4151')]/@Include"} $xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings]/@Include"} $ns = @{ e = "http://schemas.microsoft.com/developer/msbuild/2003" } $xml | Select-Xml -XPath $xpath.Start -Namespace $ns |Select -Expand Node | Select -expand Value } and here’s the output: Nice reusable Powershell and SQL scripts – not bad for an evening’s work. Thank you to Kevin for allowing me to share his code. Don’t forget that these techniques can easily be adapted to query any Visual Studio project file, they’re only XML documents after all! Doubtless many people out there already have code for doing this but nonetheless here is another offering to the great script library in the sky. Have fun! @Jamiet

    Read the article

  • Top 10 Transact-SQL Statements a SQL Server DBA Should Know

    Microsoft SQL Server is a feature rich database management system product, with an enormous number of T-SQL commands. With each feature supporting its own list of commands, it can be difficult to remember them all. MAK shares his top 10 T-SQL statements that a DBA should know. Join SQL Backup’s 35,000+ customers to compress and strengthen your backups "SQL Backup will be a REAL boost to any DBA lucky enough to use it." Jonathan Allen. Download a free trial now.

    Read the article

  • SQL Server 2012 RTM Available!

    - by Davide Mauri
    SQL Server 2012 is available for download! http://www.microsoft.com/sqlserver/en/us/default.aspx The Evaluation version is available here: http://www.microsoft.com/download/en/details.aspx?id=29066 and along with the SQL Server 2012 RTM there’s also the Feature Pack available: http://www.microsoft.com/download/en/details.aspx?id=29065 The Feature Pack is rich of useful and interesting stuff, something needed by some feature, like the Semantic Language Statistics Database some other a very good (I would say needed) download if you use certain technologies, like MDS or Data Mining. Btw, for Data Mining also the updated Excel Addin has been released and it’s available in the Feature Pack. As if this would not be enough, also the SQL Server Data Tools IDE has been released in RTM: http://msdn.microsoft.com/en-us/data/hh297027 Remember that SQL Server Data Tool is completely free and can be used with SQL Server 2005 and after. Happy downloading!

    Read the article

  • C# Sql Connection Best Practises 2013

    - by Pete Petersen
    With the new year approaching I'm drawing up a development plan for 2013. I won't bore you with the details but I started thinking about whether the way I do things is actually the 'correct' way. In particular how I'm interfacing with SQL. I create predominantly create WPF desktop applications and often some Silverlight Web Applications. All of my programs are very Data-Centric. When connecting to SQL from WPF I tend to use Stored Procedures stored on the server and fetch them using ADO.NET (e.g. SQLConnection(), .ExecuteQuery()). However with Silverlight I have a WCF service and use LINQ to SQL (and I'm using LINQ much more in WPF). My question is really is am I doing anything wrong in a sense that it's a little old fashioned? I've tried to look this up online but could find anything useful after about 2010 and of those half were 'LINQ is dead!' and the other 'Always use LINQ' Just want to make sure going forward I'm doing the right things the right way, or at least the advised way :). What principles are you using when connecting to SQL? Is it the same for WPF and Silverlight/WCF?

    Read the article

  • Delphi Application using COMMIT and ROLLBACK for Multiple SQL Updates

    - by Matt
    Is it possible to use the SQL BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION when embedding SQL Queries into an application with mutiple calls to the SQL for Table Updates. For example I have the following code: Q.SQL.ADD(<UPDATE A RECORD>); Q.ExecSQL; Q.Close; Q.SQL.Clear; Q.SQL.ADD(<Select Some Data>); Q.Open; Set Some Variables Q.Close; Q.SQL.Clear; Q.SQL.ADD(<UPDATE A RECORD>); Q.ExecSQL; What I would like to do is if the second update fails I want to roll back the first transaction. If I set a unique notation for the BEGIN, COMMIT, ROLLBACK so as to specify what is being committed or rolled back, is it feasible. i.e. before the first Update specify BEGIN TRANSACTION_A then after the last update specify COMMIT TRANSACTION_A I hope that makes sense. If I was doing this in a SQL Stored Procedure then I would be able to specify this at the start and end of the procedure, but I have had to break the code down into manageable chunks due to process blocks and deadlocks on a heavy loaded SQL Server.

    Read the article

  • Minimum privileges to read SQL Jobs using SQL SMO

    - by Gustavo Cavalcanti
    I wrote an application to use SQL SMO to find all SQL Servers, databases, jobs and job outcomes. This application is executed through a scheduled task using a local service account. This service account is local to the application server only and is not present in any SQL Server to be inspected. I am having problems getting information on job and job outcomes when connecting to the servers using a user with dbReader rights on system tables. If we set the user to be sysadmin on the server it all works fine. My question to you is: What are the minimum privileges a local SQL Server user needs to have in order to connect to the server and inspect jobs/job outcomes using the SQL SMO API? I connect to each SQL Server by doing the following: var conn = new ServerConnection { LoginSecure = false, ApplicationName = "SQL Inspector", ServerInstance = serverInstanceName, ConnectAsUser = false, Login = user, Password = password }; var smoServer = new Server (conn); I read the jobs by reading smoServer.JobServer.Jobs and read the JobSteps property on each of these jobs. The variable server is of type Microsoft.SqlServer.Management.Smo.Server. user/password are of the user found in each SQL Server to be inspected. If "user" is SysAdmin on the SQL Server to be inspected all works ok, as well as if we set ConnectAsUser to true and execute the scheduled task using my own credentials, which grants me SysAdmin privileges on SQL Server per my Active Directory membership. Thanks!

    Read the article

< Previous Page | 73 74 75 76 77 78 79 80 81 82 83 84  | Next Page >