Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

Page 424/1156 | < Previous Page | 420 421 422 423 424 425 426 427 428 429 430 431  | Next Page >

• When a problem is resolved

  - by Rob Farley

  This month’s T-SQL Tuesday is hosted by Jen McCown, and she’s picked the topic of Resolutions. It’s a new year, and she’s thinking about what people have resolved to do this year. Unfortunately, I’ve never really done resolutions like that. I see too many people resolve to quit smoking, or lose weight, or whatever, and fail miserably. I’m not saying I don’t set goals, but it’s not a thing for New Year. The obvious joke is “1920x1080” as a resolution, but I’m not going there. I think Resolving is a strange word. It makes it sound like I’m having to solve a problem a second time, when actually, it’s more along the lines of solving a problem well enough for it to count as finished. If something has been resolved, a solution has been provided. There is a resolution, through the provision of a solution. It’s a strangeness of English. When I look up the word resolution at dictionary.com, it has 12 options, including “settling of a problem”. There’s a finality about resolution. If you resolve to do something, you’re saying “Yes. This is a done thing. I’m resolving to do it, which means that it may as well be complete already.” I like to think I resolve problems, rather than just solving them. I want my solving to be final and complete. If I tune a query, I don’t want to find that I’m back in there, re-tuning it at some point. Strangely, if I re-solve a problem, that implies that I didn’t resolve it in the first place. I only solved it. Temporarily. We “data-folk” live in a world where the most common answer is “It depends.” Frustratingly, the thing an answer depends on may still be changing in the system in question. That probably means that any solution that is put in place may need reinvestigating at some point later. So do I resolve things? Yes. Am I Chuck Norris, and solve things so well the world would break first? No. Do these two claims happily sit beside each other? No, unfortunately not. But I happily take responsibility for things, and let my clients depend on me to see it through. As far as they are concerned, it is resolved. And so I resolve to keep resolving, right through 2011.

  Read the article

• Testing and Validation – You Really Do Have The Time

  - by BuckWoody

  One of the great advantages in my role as a Technical Specialist here at Microsoft is that I get to work with so many great clients. I get to see their environments and how they use them, and the way they work with SQL Server. I’ve been a data professional myself for many years. Over that time I’ve worked with many database platforms, lots of client applications, and written a lot of code in many industries. For a while I was also a consultant, so I got to see how other shops did things as well. But because I now focus on a “set” base of clients (over 500 professionals in over 150 companies) I get to see them over a longer period of time. Many of them help me understand how they use the product in their projects, and I even attend some DBA regular meetings. I see the way the product succeeds, and I see when it fails. Something that has really impacted my way of thinking is the level of importance any given shop is able to place on testing and validation. I’ve always been a big proponent of setting up a test system and following a very disciplined regimen to make sure it will work in production for any new projects, and then taking the lessons learned into production as standards. I know, I know – there’s never enough time to do things right like this. Yet the shops I see that do it have the same level of work that they output as the shops that don’t. They just make the time to do the testing and validation and create a standard that they will follow in production. And what I’ve found (surprise surprise) is that they have fewer production problems. OK, that might seem obvious – but I’ve actually tracked it and those places that do the testing and best practices really do save stress, time and trouble from that effort. We all think that’s a good idea, but we just “don’t have time”. OK – but from what I’m seeing, you can gain time if you spend a little up front. You may find that you’re actually already spending the same amount of time that you would spend in doing the testing, you’re just doing it later, at night, under the gun. Food for thought.  Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Sampling SQL server batch activity

  - by extended_events

  Recently I was troubleshooting a performance issue on an internal tracking workload and needed to collect some very low level events over a period of 3-4 hours.  During analysis of the data I found that a common pattern I was using was to find a batch with a duration that was longer than average and follow all the events it produced.  This pattern got me thinking that I was discarding a substantial amount of event data that had been collected, and that it would be great to be able to reduce the collection overhead on the server if I could still get all activity from some batches. In the past I’ve used a sampling technique based on the counter predicate to build a baseline of overall activity (see Mikes post here).  This isn’t exactly what I want though as there would certainly be events from a particular batch that wouldn’t pass the predicate.  What I need is a way to identify streams of work and select say one in ten of them to watch, and sql server provides just such a mechanism: session_id.  Session_id is a server assigned integer that is bound to a connection at login and lasts until logout.  So by combining the session_id predicate source and the divides_by_uint64 predicate comparator we can limit collection, and still get all the events in batches for investigation. CREATE EVENT SESSION session_10_percent ON SERVER ADD EVENT sqlserver.sql_statement_starting(     WHERE (package0.divides_by_uint64(sqlserver.session_id,10))), ADD EVENT sqlos.wait_info (        WHERE (package0.divides_by_uint64(sqlserver.session_id,10))), ADD EVENT sqlos.wait_info_external (        WHERE (package0.divides_by_uint64(sqlserver.session_id,10))), ADD EVENT sqlserver.sql_statement_completed(     WHERE (package0.divides_by_uint64(sqlserver.session_id,10))) ADD TARGET ring_buffer WITH (MAX_DISPATCH_LATENCY=30 SECONDS,TRACK_CAUSALITY=ON) GO   There we go; event collection is reduced while still providing enough information to find the root of the problem.  By the way the performance issue turned out to be an IO issue, and the session definition above was more than enough to show long waits on PAGEIOLATCH*.        

  Read the article

• Multitenancy in SQL Azure

  - by cibrax

  If you are building a SaaS application in Windows Azure that relies on SQL Azure, it’s probably that you will need to support multiple tenants at database level. This is short overview of the different approaches you can use for support that scenario, A different database per tenant A new database is created and assigned when a tenant is provisioned. Pros Complete isolation between tenants. All the data for a tenant lives in a database only he can access. Cons It’s not cost effective. SQL Azure databases are not cheap, and the minimum size for a database is 1GB.  You might be paying for storage that you don’t really use. A different connection pool is required per database. Updates must be replicated across all the databases You need multiple backup strategies across all the databases Multiple schemas in a database shared by all the tenants A single database is shared among all the tenants, but every tenant is assigned to a different schema and database user. Pros You only pay for a single database. Data is isolated at database level. If the credentials for one tenant is compromised, the rest of the data for the other tenants is not. Cons You need to replicate all the database objects in every schema, so the number of objects can increase indefinitely. Updates must be replicated across all the schemas. The connection pool for the database must maintain a different connection per tenant (or set of credentials) A different user is required per tenant, which is stored at server level. You have to backup that user independently. Centralizing the database access with store procedures in a database shared by all the tenants A single database is shared among all the tenants, but nobody can read the data directly from the tables. All the data operations are performed through store procedures that centralize the access to the tenant data. The store procedures contain some logic to map the database user to an specific tenant. Pros You only pay for a single database. You only have a set of objects to maintain and backup. Cons There is no real isolation. All the data for the different tenants is shared in the same tables. You can not use traditional ORM like EF code first for consuming the data. A different user is required per tenant, which is stored at server level. You have to backup that user independently. SQL Federations A single database is shared among all the tenants, but a different federation is used per tenant. A federation in few words, it’s a mechanism for horizontal scaling in SQL Azure, which basically uses the idea of logical partitions to distribute data based on certain criteria. Pros You only have a single database with multiple federations. You can use filtering in the connections to pick the right federation, so any ORM could be used to consume the data. Cons There is no real isolation at that database level. The isolation is enforced programmatically with federations.

  Read the article

• VSDB to SSDT Part 2 : SQL Server 2008 Server Project &hellip; with SSDT

  - by Etienne Giust

  With Visual Studio 2012 and the use of SSDT technology, there is only one type of database project : SQL Server Database Project. With Visual Studio 2010, we used to have SQL Server 2008 Server Project which we used to define server-level objects, mostly logins and linked servers. A convenient wizard allowed for creation of this type of projects. It does not exists anymore. Here is how to create an equivalent of the SQL Server 2008 Server Project  with Visual Studio 2012: Create a new SQL Server Database Project : it will be created empty Create a new SQL Schema Compare ( SQL menu item > Schema Compare > New Schema Comparison ) As a source, select any database on the SQL server you want to mimic Set the target to be your newly Database Project In the Schema Compare options (cog-like icon), Object Types pane, set the options as below. You might want to tweak those and select only the object types you want. Then, run the comparison, review and select your changes and apply them to the project.

  Read the article

• Best practices: Ajax and server side scripting with stored procedures

  - by Luka Milani

  I need to rebuild an old huge website and probably to port everyting to ASP.NET and jQuery and I would like to ask for some suggestion and tips. Actually the website uses: Ajax (client site with prototype.js) ASP (vb script server side) SQL Server 2005 IIS 7 as web server This website uses hundred of stored procedures and the requests are made by an ajax call and only 1 ASP page that contain an huge select case Shortly an example: JAVASCRIPT + PROTOTYPE: var data = { action: 'NEWS', callback: 'doNews', param1: $('text_example').value, ......: ..........}; AjaxGet(data); // perform a call using another function + prototype SERVER SIDE ASP: <% ...... select case request("Action") case "NEWS" With cmmDB .ActiveConnection = Conn .CommandText = "sp_NEWS_TO_CALL_for_example" .CommandType = adCmdStoredProc Set par0DB = .CreateParameter("Param1", adVarchar, adParamInput,6) Set par1DB = .CreateParameter(".....", adInteger, adParamInput) ' ........ ' can be more parameters .Parameters.Append par0DB .Parameters.Append par1DB par0DB.Value = request("Param1") par1DB.Value = request(".....") set rs=cmmDB.execute RecodsetToJSON rs, jsa ' create JSON response using a sub End With .... %> So as you can see I have an ASP page that has a lot of CASE and this page answers to all the ajax request in the site. My question are: Instead of having many CASES is it possible to create dynamic vb code that parses the ajax request and creates dynamically the call to the desired SP (also implementing the parameters passed by JS)? What is the best approach to handle situations like this, by using the advantages of .Net + protoype or jQuery? How the big sites handle situation like this? Do they do it by creating 1 page for request? Thanks in advance for suggestion, direction and tips.

  Read the article

• SQL Server Database Settings

  - by rbishop

  For those using Data Relationship Management on Oracle DB this does not apply, but for those using Microsoft SQL Server it is highly recommended that you run with Snapshot Isolation Mode. The Data Governance module will not function correctly without this mode enabled. All new Data Relationship Management repositories are created with this mode enabled by default. This mode makes SQL Server (2005+) behave more like Oracle DB where readers simply see older versions of rows while a write is in progress, instead of readers being blocked by locks while a write takes place. Many common sources of deadlocks are eliminated. For example, if one user starts a 5 minute transaction updating half the rows in a table, without snapshot isolation everyone else reading the table will be blocked waiting. With snapshot isolation, they will see the rows as they were before the write transaction started. Conversely, if the readers had started first, the writer won't be stuck waiting for them to finish reading... the writes can begin immediately without affecting the current transactions. To make this change, make sure no one is using the target database (eg: put it into single-user mode), then run these commands: ALTER DATABASE [DB] SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE [DB] SET READ_COMMITTED_SNAPSHOT ON Please make sure you coordinate with your DBA team to ensure tempdb is appropriately setup to support snapshot isolation mode, as the extra row versions are stored in tempdb until the transactions are committed. Let me take this opportunity to extremely strongly highly recommend that you use solid state storage for your databases with appropriate iSCSI, FiberChannel, or SAN bandwidth. The performance gains are significant and there is no excuse for not using 100% solid state storage in 2013. Actually unless you need to store petabytes of archival data, there is no excuse for using hard drives in any systems, whether laptops, desktops, application servers, or database servers. The productivity benefits alone are tremendous, not to mention power consumption, heat, etc.

  Read the article

• How do I filter out NaN FLOAT values in Teradata SQL?

  - by Paul Hooper

  With the Teradata database, it is possible to load values of NaN, -Inf, and +Inf into FLOAT columns through Java. Unfortunately, once those values get into the tables, they make life difficult when writing SQL that needs to filter them out. There is no IsNaN() function, nor can you "CAST ('NaN' as FLOAT)" and use an equality comparison. What I would like to do is, SELECT SUM(VAL**2) FROM DTM WHERE NOT ABS(VAL) > 1e+21 AND NOT VAL = CAST ('NaN' AS FLOAT) but that fails with error 2620, "The format or data contains a bad character.", specifically on the CAST. I've tried simply "... AND NOT VAL = 'NaN'", which also fails for a similar reason (3535, "A character string failed conversion to a numeric value."). I cannot seem to figure out how to represent NaN within the SQL statement. Even if I could represent NaN successfully in an SQL statement, I would be concerned that the comparison would fail. According to the IEEE 754 spec, NaN = NaN should evaluate to false. What I really seem to need is an IsNaN() function. Yet that function does not seem to exist.

  Read the article

• Using SQL Alchemy and pyodbc with IronPython 2.6.1

  - by beargle

  I'm using IronPython and the clr module to retrieve SQL Server information via SMO. I'd like to retrieve/store this data in a SQL Server database using SQL Alchemy, but am having some trouble loading the pyodbc module. Here's the setup: IronPython 2.6.1 (installed at D:\Program Files\IronPython) CPython 2.6.5 (installed at D:\Python26) SQL Alchemy 0.6.1 (installed at D:\Python26\Lib\site-packages\sqlalchemy) pyodbc 2.1.7 (installed at D:\Python26\Lib\site-packages) I have these entries in the IronPython site.py to import CPython standard and third-party libraries: # Add CPython standard libs and DLLs import sys sys.path.append(r"D:\Python26\Lib") sys.path.append(r"D:\Python26\DLLs") sys.path.append(r"D:\Python26\lib-tk") sys.path.append(r"D:\Python26") # Add CPython third-party libs sys.path.append(r"D:\Python26\Lib\site-packages") # sqlite3 sys.path.append(r"D:\Python26\Lib\sqlite3") # Add SQL Server SMO sys.path.append(r"D:\Program Files\Microsoft SQL Server\100\SDK\Assemblies") import clr clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll') clr.AddReferenceToFile('Microsoft.SqlServer.SqlEnum.dll') clr.AddReferenceToFile('Microsoft.SqlServer.ConnectionInfo.dll') SQL Alchemy imports OK in IronPython, put I receive this error message when trying to connect to SQL Server: IronPython 2.6.1 (2.6.10920.0) on .NET 2.0.50727.3607 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlalchemy >>> e = sqlalchemy.MetaData("mssql://") Traceback (most recent call last): File "<stdin>", line 1, in <module> File "D:\Python26\Lib\site-packages\sqlalchemy\schema.py", line 1780, in __init__ File "D:\Python26\Lib\site-packages\sqlalchemy\schema.py", line 1828, in _bind_to File "D:\Python26\Lib\site-packages\sqlalchemy\engine\__init__.py", line 241, in create_engine File "D:\Python26\Lib\site-packages\sqlalchemy\engine\strategies.py", line 60, in create File "D:\Python26\Lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 29, in dbapi ImportError: No module named pyodbc This code works just fine in CPython, but it looks like the pyodbc module isn't accessible from IronPython. Any suggestions? I realize that this may not be the best way to approach the problem, so I'm open to tackling this a different way. Just wanted to get some experience with using SQL Alchemy and pyodbc.

  Read the article

• SQL Server CLR stored procedures in data processing tasks - good or evil?

  - by Gart

  In short - is it a good design solution to implement most of the business logic in CLR stored procedures? I have read much about them recently but I can't figure out when they should be used, what are the best practices, are they good enough or not. For example, my business application needs to parse a large fixed-length text file, extract some numbers from each line in the file, according to these numbers apply some complex business rules (involving regex matching, pattern matching against data from many tables in the database and such), and as a result of this calculation update records in the database. There is also a GUI for the user to select the file, view the results, etc. This application seems to be a good candidate to implement the classic 3-tier architecture: the Data Layer, the Logic Layer, and the GUI layer. The Data Layer would access the database The Logic Layer would run as a WCF service and implement the business rules, interacting with the Data Layer The GUI Layer would be a means of communication between the Logic Layer and the User. Now, thinking of this design, I can see that most of the business rules may be implemented in a SQL CLR and stored in SQL Server. I might store all my raw data in the database, run the processing there, and get the results. I see some advantages and disadvantages of this solution: Pros: The business logic runs close to the data, meaning less network traffic. Process all data at once, possibly utilizing parallelizm and optimal execution plan. Cons: Scattering of the business logic: some part is here, some part is there. Questionable design solution, may encounter unknown problems. Difficult to implement a progress indicator for the processing task. I would like to hear all your opinions about SQL CLR. Does anybody use it in production? Are there any problems with such design? Is it a good thing?

  Read the article

• pass username and password to get-credential or run sql query without using invoke-sqlcmd in Powersh

  - by Emo

  I am trying to connect to a remote sql database and simply run the "select @@servername" query in Powershell. I'm trying to do this without using integrated security. I've been struggling with "get-credential" and "invoke-sqlcmd", only to find (I think), that you can't pass the password from "get-credential" to another Powershell cmdlets. Here's the code I'm using: add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100 load assemblies [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91") connect to SQL Server $serverName = "HLSQLSRV03" $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $serverName login using SQL authentication $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential $userName = $credential.UserName -replace("\","") $pass = $credential.Password $server.ConnectionContext.set_Login($userName) $server.ConnectionContext.set_SecurePassword($credential.Password) $DB = "Master" invoke-sqlcmd -query "select @@Servername" -database $DB -serverinstance $servername -username $username -password $pass If if just hardcode the password in at the end of the "invoke-sqlcmd" line, it works. Is this because you can't use "get-credential" with "invoke-sqlcmd"? If so...what are my alternatives? Thanks so much for you help Emo

  Read the article

• How can I stop an auto-generated Linq to SQL class from loading ALL data?

  - by Gary McGill

  DUPLICATE of http://stackoverflow.com/questions/2433422/how-can-i-stop-an-auto-generated-linq-to-sql-class-from-loading-all-data post answers there! I have an ASP.NET MVC project, much like the NerdDinner tutorial example. (I'm using MVC 2, but followed the NerdDinner tutorial in order to create it). As per the instructions in part 3 of the tutorial, I've created a Linq-to-SQL model of my database by creating a "Linq to SQL Classes" (.dbml) surface, and dropping my database tables onto it. The designer has automatically added relationships between the generated classes based on my database tables. Let's say that my classes are as per the NerdDinner example, so I have Dinner and RSVP tables, where each Dinner record is associated with many RSVP records - hence in the generated classes, the Dinner object has a RSVPs property which is a list of RSVP objects. My problem is this: it appears (and I'd be gladly proved wrong on this) that as soon as I access a Dinner object, it's loading all of the corresponding RSVP objects, even if I don't use the RSVPs member. First question: is this really the default behavior for the generated classes? In my particular situation, the object graph contains many more tables (which have an order of magnitude more records), and so this is disastrous behaviour - I'd be loading tons of data when all I want to do is show the details of a single parent record. Second question: are there any properties exposed through the designer UI that would let me modify this behavior? (I can't find any). Third question: I've seen a description of how to control the loading of related records in a DataContext by using a DataShape object associated with the DataContext. Is that what I'm meant to do, and if so are there any tutorials like the NerdDinner one that would show not only how to do it, but also suggest a 'pattern' for normal use?

  Read the article

• SQL INSTR() using CSV. Need exact match rather than part

  - by Alastair Pitts

  This is a follow up issue relating to the answer for http://stackoverflow.com/questions/2445029/sql-placeholder-in-where-in-issue-inserted-strings-fail Quick background: We have a SQL query that uses a placeholder value to accept a string, which represents a unique tag/id. Usually, this is only a single tag, but we needed the ability to use a csv string for multiple tags, returning a combined result. In the answer we received from the vendor, they suggested the use of the INSTR function, ala: select * from pitotal where tag IN (SELECT tag from pipoint WHERE INSTR(?, tag) <> 0) and time between 'y' and 't' This works perfectly well 99% of the time, the issue is when the tag is also a subset of 2 parts of the CSV string. Eg the placeholder value is: 'northdom,southdom,eastdom,westdom' and possible tags include: north or northdom What happens, as north is a subset of northdom, is that the two tags are return instead of just northdom, which is actually what we want. I'm not strong on SQL so I couldn't work out how to set it as exact, or split the csv string, so help would be appreciated. Is there a way to split the csv string or make it look for an exact match?

  Read the article

• In SQL, if we rename INNER JOIN as INTERSECT JOIN, LEFT OUTER JOIN as LEFT UNION JOIN, and FULL OUTE

  - by Jian Lin

  In SQL, the name Join gives an idea of "merging" or a sense of "union", making something bigger. But in fact, as in the other post http://stackoverflow.com/questions/2706051/in-sql-a-join-is-actually-an-intersection-and-it-is-also-a-linkage-or-a-sidew it turns out that a Join (Inner Join) is actually an Intersection. So if we think of Join = Inner Join = Intersect Join Left Outer Join = Left Union Join Full Outer Join = Full Union Join = Union Join then we always get a feel of what's happening, and maybe never forget what they are easily. In a way, we can think of Intersect as "making it less", therefore it is excluding something. That's why the name "Join" won't go with the idea of "Intersect". But in fact, both Intersect and Union can be thought of as: Union: bringing something together and merge them unconditionally. Intersect: bringing something together and merge them based on some condition. so the "bringing something together" is probably what "Join" is all about. It is like, Intersection is a "half glass of water" -- we can thinking of it as "excluding something" or as "bringing something together and accepting the common ones". So if the word "Intersect Join" is used, maybe a clear picture is there, and "Union Join" can be a clear picture too. Maybe the word "Inner Join" and "Outer Join" is very clear when we use SQL a lot. Somehow, the word "Outer" tends to give a feeling that it is "outside" and excluding something rather than a "Union".

  Read the article

• How do I implement page authorizaton in ASP.NET using a SQL store instead of web.config?

  - by drachenstern

  For instance, the way we're doing it now is like thus: (in the web.config) <location path="somePath"> <system.web> <authorization> <allow roles="approvedRoles"/> <deny users="*"/> </authorization> </system.web> </location> And what I would like to do instead is to store this information in SQL somewhere so that we can manipulate the information more easily. But we want to keep the same functionality that having the information in web.config provides, just like we can use a SqlRoleProvider instead of hardcoding roles in the app. So in other words, if a user currently tries to goto "somePath" and they're not a member of "approvedRoles" then they get redirected back to default.aspx, and if they are a member of "approvedRoles" then they get the page. I want to do the same thing, but without using web.config as the authorization mechanism. So what I'm NOT asking is how do I go about defining roles, or how do I handle logging in to the database, but specifically how do I store the above information in SQL instead of web.config. Actually, I'll take "anywhere but web.config" for now. Any ideas? Is this possible using a "Provider" class? I'm just looking for pointers on what to inherit and maybe some technet documentation. In this regard my googlefoo is lacking since I don't really know where to point. Am I really only looking for AzMan? Is this location-authorization-via-SQL already defined in the default aspnetdb somewhere and I'm missing it? For that matter, has this question already been asked on SO and I've missed it? What would you google?

  Read the article

• Why Sql not bringing back results unless I set varchar size?

  - by Tom

  I've got an SQL script that fetches results based on the colour passed to it, but unless I set the size of the variable defined as a varchar to (50) no results are returned. If I use: like ''+@Colour+'%' then it works but I don't really want to use it in case it brings back results I don't need or want. The column FieldValue has a type of Varchar(Max) (which can't be changed as this field can store different things). It is part of aspdotnetstorefront package so I can't really change the tables or field types. This doesn't work: declare @Col VarChar set @Col = 'blu' select * from dbo.MetaData as MD where MD.FieldValue = @Colour But this does work: declare @Col VarChar (50) set @Col = 'blu' select * from dbo.MetaData as MD where MD.FieldValue = @Colour The code is used in the following context, but should work either way <query name="Products" rowElementName="Variant"> <sql> <![CDATA[ select * from dbo.MetaData as MD where MD.Colour = @Colour ]]> </sql> <queryparam paramname="@ProductID" paramtype="runtime" requestparamname="pID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> <queryparam paramname="@Colour" paramtype="runtime" requestparamname="pCol" sqlDataType="varchar" defvalue="" validationpattern=""/> </query> Any Ideas?

  Read the article

• What is the best way to auto-generate INSERT statements for a SQL Server table?

  - by JosephStyons

  We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables. Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query. The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data. I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset? I'm thinking of something like in TOAD (for Oracle) where you can right-click on a grid and click Save As-Insert Statements, and it will just dump a big sql script wherever you want. The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way. I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.

  Read the article

• SQL2008R2 install issues on windows 7 - unable to install setup support files?

  - by Liam

  I am trying to install the above but am getting the following errors when its attempting to install the setup support files, This is the first error that occurs during installation of the setup support files TITLE: Microsoft SQL Server 2008 R2 Setup ------------------------------ The following error has occurred: The installer has encountered an unexpected error. The error code is 2337. Could not close file: Microsoft.SqlServer.GridControl.dll GetLastError: 0. Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup. For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0xDF039760%25401201%25401 This is the second error that occurs after clicking continue in the installer after the first error is generated TITLE: Microsoft SQL Server 2008 R2 Setup ------------------------------ The following error has occurred: SQL Server Setup has encountered an error when running a Windows Installer file. Windows Installer error message: The Windows Installer Service could not be accessed. This can occur if the Windows Installer is not correctly installed. Contact your support personnel for assistance. Windows Installer file: C:\Users\watto_uk\Desktop\In-Digital\Software\Microsoft\SQL Server 2008 R2\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi Windows Installer log file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20110713_205508\SqlSupport_Cpu64_1_ComponentUpdate.log Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup. For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0xDC80C325 These errors are generated from an ISO package downloaded from Microsoft. I have also tried using the web platform installer to install the express version instead but the SQL Server Installation fails with that also. The management studio installs fine but not the server. I have checked to make sure that the Windows Installer is started and it is. Cant seem to find an answer for this anywhere as all previous reported issues appear to be related to XP. I did have the express edition installed on the machine previously but uninstalled it to upgrade to the full version, I wish I hadn't now. Can anyone kindly offer any advice or point me in the right direction to stop me going insane with this? Any advice will be appreciated. Update======================= After digging a bit deeper ive located details of the error from the setup log file, i can also upload the log file if required. MSI (s) (E8:28) [23:35:18:705]: Assembly Error:The module '%1' was expected to contain an assembly manifest. MSI (s) (E8:28) [23:35:18:705]: Note: 1: 1935 2: 3: 0x80131018 4: IStream 5: Commit 6: MSI (s) (E8:28) [23:35:18:705]: Note: 1: 2337 2: 0 3: Microsoft.SqlServer.GridControl.dll MSI (s) (E8:28) [23:35:22:869]: Product: Microsoft SQL Server 2008 R2 Setup (English) -- Error 2337. The installer has encountered an unexpected error. The error code is 2337. Could not close file: Microsoft.SqlServer.GridControl.dll GetLastError: 0. MSI (s) (E8:28) [23:35:22:916]: Internal Exception during install operation: 0xc0000005 at 0x000007FEE908A23E. MSI (s) (E8:28) [23:35:22:916]: WER report disabled for silent install. MSI (s) (E8:28) [23:35:22:932]: Internal MSI error. Installer terminated prematurely. Error 2337. The installer has encountered an unexpected error. The error code is 2337. Could not close file: Microsoft.SqlServer.GridControl.dll GetLastError: 0. MSI (s) (E8:28) [23:35:22:932]: MainEngineThread is returning 1603 MSI (s) (E8:58) [23:35:22:932]: RESTART MANAGER: Session closed. Installer stopped prematurely. MSI (c) (0C:14) [23:35:22:947]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (0C:14) [23:35:22:947]: MainEngineThread is returning 1601 === Verbose logging stopped: 13/07/2011 23:35:22 ===

  Read the article

• How can I access mainframe data with .Net applications and SQL Queries?

  - by orandov

  We have a large amount of data stored on an IBM mainframe using VSAM files. A lot of this data is dropped on the network every night in the form of text files to be processed and dumped into FoxPro and SQL Server databases. There are also many text files produced nightly by custom applications that get uploaded to the mainframe to keep everything in sync. Keeping the everything in sync is very tricky, to say the least. We are not getting rid of the mainframe any time soon and we would like to replace all the nightly batch processing with real time access to the mainframe data. We would like to be able to: Read data directly from the mainframe and produce reports based on it. Possibly using SQL queries. Read and Write data from custom .Net applications. We are not looking for a new platform to interface with the mainframe like Information Builders offers. We don't want to build application modules or reports with new "Business Intelligence" tools. We already know how to generate reports and write custom applications using SQL,.Net, Visual Studio, etc. All we are looking for is some sort of adapter to connect to our mainframe data. Any ideas are appreciated.

  Read the article

• How to upload files and store them in a server local path when MS SQL SERVER allows remote connectio

  - by user193655

  I am developing a win32 windows application with Delphi and MS SQL Server. it works fine in LAN but I am trying to add the support for SQL Server remote connections (= working with a DB that can be accessed with an external IP, as described in this article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277). Basically I have a Table in DB where I keep the DocumentID, the document description and the Document path (like \FILESERVER\MyApplicationDocuments\45.zip). Of course \FILESERVER is a local (LAN) path for the server but not for the client (as I am now trying to add the support for remote connections). So I need a way to access \FILESERVER even if of course I cannot see it in LAN. I found the following T-SQL code snippet that is perfect for the "download trick": SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\FILESERVER\MyApplicationDocuments\45.zip' , SINGLE_BLOB) AS X With the code above I can download a file on the client. But how to upload it? I need an "Uppload trick" to be able to insert new files, but also to delete or replace existing files. Can anyone suggest? If a trick is not available could you suggest an alternative? Like an extended stored procedure or calling some .net assembly from the server.

  Read the article

• How do I connect to SQL Server with VB?

  - by Wayne Werner

  Hi, I'm trying to connect to a SQL server from VB. The SQL server is across the network uses my windows login for authentication. I can access the server using the following python code: import odbc conn = odbc.odbc('SignInspection') c = conn.cursor() c.execute("SELECT * FROM list_domain") c.fetchone() This code works fine, returning the first result of the SELECT. However, I've been trying to use the SqlClient.SqlConnection in VB, and it fails to connect. I've tried several different connection strings but this is the current code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As New SqlClient.SqlConnection conn.ConnectionString = "data source=signinspection;initial catalog=signinspection;integrated security=SSPI" Try conn.Open() MessageBox.Show("Sweet Success") 'Insert some code here, woo Catch ex As Exception MessageBox.Show("Failed to connect to data source.") MessageBox.Show(ex.ToString()) Finally conn.Close() End Try End Sub It fails miserably, and it gives me an error that says "A network-related or instance-specific error occurred... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I'm fairly certain it's my connection string, but nothing I've found has given me any solid examples (server=mySQLServer is not a solid example) of what I need to use. Thanks! -Wayne

  Read the article

• For a set of sql-queries, how do you determine which result-set contains a certain row?

  - by ManBugra

  I have a set of sql - queries: List<String> queries = ... queries[0] = "select id from person where ..."; ... queries[8756] = "select id from person where ..."; Each query selects rows from the same table 'person'. The only difference is the where-clause. Table 'person' looks like this: id | name | ... many other columns How can i determine which queries will contain a certain person in their subset? For example: List<Integer> matchingQueries = magicMethod(queries, [23,45]); The list obtained by 'magicMethod' filters all sql queries present in the list 'queries' (defined above) and returns only those that contain either the person with id 23 OR a person with id 45. Why i need it: I am dealing with an application that contains products and categories where the categories are sql queries that define which products belong to them (queries stored in a table also). Now i have a requirement where an admin has to see all categories an item belongs to immediately after the item was created. Btw, over 8.000 categories defined (so far, more to come). language and db: java && postgreSQL Thanks,

  Read the article

• Can I force the auto-generated Linq-to-SQL classes to use an OUTER JOIN?

  - by Gary McGill

  Let's say I have an Order table which has a FirstSalesPersonId field and a SecondSalesPersonId field. Both of these are foreign keys that reference the SalesPerson table. For any given order, either one or two salespersons may be credited with the order. In other words, FirstSalesPersonId can never be NULL, but SecondSalesPersonId can be NULL. When I drop my Order and SalesPerson tables onto the "Linq to SQL Classes" design surface, the class builder spots the two FK relationships from the Order table to the SalesPerson table, and so the generated Order class has a SalesPerson field and a SalesPerson1 field (which I can rename to SalesPerson1 and SalesPerson2 to avoid confusion). Because I always want to have the salesperson data available whenever I process an order, I am using DataLoadOptions.LoadWith to specify that the two salesperson fields are populated when the order instance is populated, as follows: dataLoadOptions.LoadWith<Order>(o => o.SalesPerson1); dataLoadOptions.LoadWith<Order>(o => o.SalesPerson2); The problem I'm having is that Linq to SQL is using something like the following SQL to load an order: SELECT ... FROM Order O INNER JOIN SalesPerson SP1 ON SP1.salesPersonId = O.firstSalesPersonId INNER JOIN SalesPerson SP2 ON SP2.salesPersonId = O.secondSalesPersonId This would make sense if there were always two salesperson records, but because there is sometimes no second salesperson (secondSalesPersonId is NULL), the INNER JOIN causes the query to return no records in that case. What I effectively want here is to change the second INNER JOIN into a LEFT OUTER JOIN. Is there a way to do that through the UI for the class generator? If not, how else can I achieve this? (Note that because I'm using the generated classes almost exclusively, I'd rather not have something tacked on the side for this one case if I can avoid it).

  Read the article

• Is there any performance issue using Row_Number to implement table paging in Sql Server 2008?

  - by majkinetor

  I want to implement table paging using this method: SET @PageNum = 2; SET @PageSize = 10; WITH OrdersRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum ,* FROM dbo.Orders ) SELECT * FROM OrdersRN WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ORDER BY OrderDate ,OrderID; Is there anything I should be aware of ? Table has millions of records. Thx. EDIT: After using suggested MAXROWS method for some time (which works really really fast) I had to switch back to ROW_NUMBER method because of its greater flexibility. I am also very happy about its speed so far (I am working with View having more then 1M records with 10 columns). To use any kind of query I use following modification: PROCEDURE [dbo].[PageSelect] ( @Sql nvarchar(512), @OrderBy nvarchar(128) = 'Id', @PageNum int = 1, @PageSize int = 0 ) AS BEGIN SET NOCOUNT ON Declare @tsql as nvarchar(1024) Declare @i int, @j int if (@PageSize <= 0) OR (@PageSize > 10000) SET @PageSize = 10000 -- never return more then 10K records SET @i = (@PageNum - 1) * @PageSize + 1 SET @j = @PageNum * @PageSize SET @tsql = 'WITH MyTableOrViewRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNum ,* FROM MyTableOrView WHERE ' + @Sql + ' ) SELECT * FROM MyTableOrViewRN WHERE RowNum BETWEEN ' + CAST(@i as varchar) + ' AND ' + cast(@j as varchar) exec(@tsql) END If you use this procedure make sure u prevented sql injection.

  Read the article

• How should I name my SQL query files? Should I use some methodology?

  - by Mehper C. Palavuzlar

  We have an Oracle 10g database (a huge one) in our company, and I provide employees with data upon their requests. My problem is, I save almost every SQL query I wrote, and now my list has grown too much. I want to organize and rename these .sql files so that I can find the one I want easily. At the moment, I'm using some folders named as Sales Dept, Field Team, Planning Dept, Special etc. and under those folders there are .sql files like Delivery_sales_1, Delivery_sales_2, ... Sent_sold_lostsales_endpoints, ... Sales_provinces_period, Returnrates_regions_bymonths, ... Jack_1, Steve_1, Steve_2, ... I try to name the files regarding their content but this makes file names longer and does not completely meet my needs. Sometimes someone comes and demands a special report, and I give the file his name, but this is also not so good. I know duplicates or very similar files are growing in time but I don't have control over them. Can you show me the right direction to rename all these files and folders and organize my queries for easy and better control? TIA.

  Read the article

< Previous Page | 420 421 422 423 424 425 426 427 428 429 430 431  | Next Page >