Search Results

Search found 27337 results on 1094 pages for 't sql'.

Page 170/1094 | < Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177  | Next Page >

• SQL query for getting data in two fields from one column.

  - by AmiT

  I have a table [Tbl1] containing two fields. ID as int And TextValue as nvarchar(max) Suppose there are 7 records. I need a resultset that has two columns Text1 and Text2. The Text1 should have first 4 records and Text2 should have remaining 3 records. [Tbl1] ID | TextValue 1. | Apple 2. | Mango 3. | Orange 4. | Pineapple 5. | Banana 6. | Grapes 7. | Sapota Now, the result-set should have Text1 | Text2 Apple | Banana Mango | Grapes Orange | Sapota Pineapple |

  Read the article

• Is it safe to convert varchar and char into nvarchar and nchar in SQL Server?

  - by Svish

  We currently have a number of columns in the database which are of type varchar. The application that uses them are in C# and it uses Linq2Sql for the communication (or what to call it). We would like to support unicode characters, which means we would have to convert the varchar columns into nvarchar. Is this a safe operation? Is it just a matter of changing the column type and updating the dbml file, or are there more stuff that needs to be done? Any changes in the C# code? Do I need to somehow convert the text that already exist in the database manually, or is it handled for me?

  Read the article

• linq: SQL performance on high loaded web applications

  - by Alex

  I started working with linq to SQL several weeks ago. I got really tired of working with SQL server directly through the SQL queries (sqldatareader, sqlcommand and all this good stuff).  After hearing about linq to SQL and mvc I quickly moved all my projects to these technologies. I expected linq to SQL work slower but it suprisongly turned out to be pretty fast, primarily because I always forgot to close my connections when using datareaders. Now I don't have to worry about it. But there's one problem that really bothers me. There's one page that's requested thousands of times a day. The system gets data in the beginning, works with it and updates it. Primarily the updates are ++ @ -- (increase and decrease values). I used to do it like this UPDATE table SET value=value+1 WHERE ID=@I'd It worked with no problems obviously. But with linq to SQL the data is taken in the beginning, moved to the class, changed and then saved. Stats.registeredusers++; Db.submitchanges(); Let's say there were 100 000 users. Linq will say "let it be 100 001" instead of "let it be increased by 1". But if there value of users has already been increased (that happens in my site all the time) then linq will be like oops, this value is already 100 001. Whatever I'll throw an exception" You can change this behavior so that it won't throw an exception but it still will not set the value to 100 002. Like I said, it happened with me all the time. The stas value was increased twice a second on average. I simply had to rewrite this chunk of code with classic ado net. So my question is how can you solve the problem with linq

  Read the article

• Is there a way to only backup a SQL 2005 database structure fully, but only the data in a certain se

  - by TheSoftwareJedi

  I have several schemas in my database, and the largest one ("large" meaning disk space consumed) is my "web" schema which is a denormalized copy of data in the operational schemas. This denormalized data is able to be reconstructed at anytime, and is merely there for extremely fast read purposes. Since the data is redundant, and VERY large - I'd like to exclude it from being backed up. I already have stored procedures that can regenerate all of the data in that schema in a couple of hours - for use in the event of a failure. I assume I can split the tables in this schema out to another data file or such (ideally even on another drive for faster reads), but is there a way to never have that data file backup, yet still in the event of a failure its structure could be restored (and other DDL stuff like procs, views, etc)? Somewhat related, can I also have these tables not do transaction logging, if I go to "Full" backup mode for the rest of the database?

  Read the article

• Unattended Install of SQL Server 2005 Express with LOCAL Server InstanceName

  - by Jeff

  I'm creating an install package using InnoSetup and installing SQL Server 2005 Express. Here's the code below that appears in my RUN section: Filename: "{app}\SQL Server 2005 Express\SQLEXPR.exe" ; Parameters: "-q /norebootchk /qn reboot=ReallySuppress addlocal=all INSTANCENAME=(LOCAL) SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=2 SQLAUTOSTART=1 SAPWD=passwordhere SECURITYMODE=SQL"; WorkingDir: {app}\SQL Server 2005 Express; StatusMsg: Installing Microsoft SQL Server 2005 Express... Please Wait...;Check:SQLVerifyInstall What I'm trying to accomplish is have the SQL Server package install but only have the instance name itself reference the name of the machine name and nothing more. What I'm receiving instead is a named instance instead of local such as MachineName\SQLEXPRESS which is not what I want to receive. I need a local instance instead of a named instance due to the way my code is written to be able to install and talk with the databases in question. I would change it, trust me, were it not the fact that this install package is a replacement to a previous package that used the MSDE installer. I have to be able to support both through code. Any suggestions are welcome but a clear and concise method to get the installer to quietly install using only the machine name is my main goal. Thanks for the help and support!

  Read the article

• SQL Source Control with Vault Support Officially Released

  - by Ajarn Mark Caldwell

  HOORAY!  It is officially here!  Today, Red-Gate officially released SQL Source Control version 2.1 with support for Vault. While we have been happily and successfully running the beta version (a.k.a. the Early Access release) of Red-Gate SQL Source Control with support for Vault for quite a while, it is good to have the official RTM (or GOLD, or PROD, or whatever you call your “no-longer-in-beta”) release of the product. As a courtesy to those who have not already read the series, allow me to provide you with these links to my previous posts about this fantastic tool. Using SQL Source Control with Fortress or Vault – Part 1 – Introduction and initial thoughts about the tool and source controlling SQL code in general. Using SQL Source Control with Fortress or Vault – Part 2 – Additional details about included features and a few warnings. Source Control and SQL Development – Part 3 – How we did it in the good ol’ days before this product came along. Using SQL Source Control and Vault Professional Part 4 – A few closing thoughts.

  Read the article

• How Do I Escape Apostrophes in Field Valued in SQL Server?

  - by Mikecancook

  I asked a question a couple days ago about creating INSERTs by running a SELECT to move data to another server. That worked great until I ran into a table that has full on HTML and apostrophes in it. What's the best way to deal with this? Lucking there aren't too many rows so it is feasible as a last resort to 'copy and paste'. But, eventually I will need to do this and the table by that time will probably be way too big to copy and paste these HTML fields. This is what I have now: select 'Insert into userwidget ([Type],[UserName],[Title],[Description],[Data],[HtmlOutput],[DisplayOrder],[RealTime],[SubDisplayOrder]) VALUES (' + ISNULL('N'''+Convert(varchar(8000),Type)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Username)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Title)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Description)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),Data)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),HTMLOutput)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),DisplayOrder)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),RealTime)+'''','NULL') + ',' + ISNULL('N'''+Convert(varchar(8000),SubDisplayOrder)+'''','NULL') + ')' from userwidget Which is works fine except those pesky apostrophes in the HTMLOutput field. Can I escape them by having the query double up on the apostrophes or is there a way of encoding the field result so it won't matter?

  Read the article

• SQL Server 2008 full-text search doesn't find word in words?

  - by Martijn

  In the database I have a field with a .mht file. I want to use FTS to search in this document. I got this working, but I'm not satisfied with the result. For example (sorry it's in dutch, but I think you get my point) I will use 2 words: zieken and ziekenhuis. As you can see, the phrase 'zieken' is in the word 'ziekenhuis'. When I search on 'ziekenhuis' I get about 20 results. When I search on 'zieken' I get 7 results. How is this possible? I mean, why doesn't the FTS resturn the minimal results which I get from 'ziekenhuis'? Here's the query I use: SELECT DISTINCT d.DocID 'Id', d.Titel, (SELECT afbeeldinglokatie FROM tbl_Afbeelding WHERE soort = 'beleid') as Pic, 'belDoc' as DocType FROM docs d JOIN kpl_Document_Lokatie dl ON d.DocID = dl.DocID JOIN HandboekLokaties hb ON dl.LokatieID = hb.LokatieID WHERE hb.InstellingID = @instellingId AND ( FREETEXT(d.Doel, @searchstring) OR FREETEXT(d.Toepassingsgebied, @searchstring) OR FREETEXT(d.HtmlDocument, @searchstring) OR FREETEXT (d.extraTabblad, @searchstring) ) AND d.StatusID NOT IN( 1, 5)

  Read the article

• How can I optimize retrieving lowest edit distance from a large table in SQL?

  - by Matt

  Hey, I'm having troubles optimizing this Levenshtein Distance calculation I'm doing. I need to do the following: Get the record with the minimum distance for the source string as well as a trimmed version of the source string Pick the record with the minimum distance If the min distances are equal (original vs trimmed), choose the trimmed one with the lowest distance If there are still multiple records that fall under the above two categories, pick the one with the highest frequency Here's my working version: DECLARE @Results TABLE ( ID int, [Name] nvarchar(200), Distance int, Frequency int, Trimmed bit ) INSERT INTO @Results SELECT ID, [Name], (dbo.Levenshtein(@Source, [Name])) As Distance, Frequency, 'False' As Trimmed FROM MyTable INSERT INTO @Results SELECT ID, [Name], (dbo.Levenshtein(@SourceTrimmed, [Name])) As Distance, Frequency, 'True' As Trimmed FROM MyTable SET @ResultID = (SELECT TOP 1 ID FROM @Results ORDER BY Distance, Trimmed, Frequency) SET @Result = (SELECT TOP 1 [Name] FROM @Results ORDER BY Distance, Trimmed, Frequency) SET @ResultDist = (SELECT TOP 1 Distance FROM @Results ORDER BY Distance, Trimmed, Frequency) SET @ResultTrimmed = (SELECT TOP 1 Trimmed FROM @Results ORDER BY Distance, Trimmed, Frequency) I believe what I need to do here is to.. Not dumb the results to a temporary table Do only 1 select from `MyTable` Setting the results right in the select from the initial select statement. (Since select will set variables and you can set multiple variables in one select statement) I know there has to be a good implementation to this but I can't figure it out... this is as far as I got: SELECT top 1 @ResultID = ID, @Result = [Name], (dbo.Levenshtein(@Source, [Name])) As distOrig, (dbo.Levenshtein(@SourceTrimmed, [Name])) As distTrimmed, Frequency FROM MyTable WHERE /* ... yeah I'm lost */ ORDER BY distOrig, distTrimmed, Frequency Any ideas?

  Read the article

• My SQL query is only returning I need the parent aswell

  - by sico87

  My sql query is only returning the children of the parent I need it to return the parent as well, public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, parent, url, description, image FROM p_cat WHERE deleted = 0 AND parent is NULL ORDER BY position;"; $query = $this->db->query($sql) or die($this->db->error); }else{ //die($cat); $sql = "SET @parent = (SELECT c.itemID FROM p_cat c WHERE url = '".$this->sql($cat)."' AND deleted = 0); SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image, (SELECT c2.url FROM p_cat c2 WHERE c2.itemID = c1.parent LIMIT 1) as parentUrl FROM p_cat c1 WHERE c1.deleted = 0 AND c1.parent = @parent ORDER BY c1.position;"; $query = $this->db->multi_query($sql) or die($this->db->error); $this->db->store_result(); $this->db->next_result(); $query = $this->db->store_result(); } return $query; }

  Read the article

• Can't see *all* databases in a remote SQL Server instance

  - by George

  Yesterday I posted a related question on StackOverflow. This problem involved not being able to see a SQL Server 2008 instance on another PC. I am not sure why adding the port number enabled me to see a SQL Server that I could not otherwise see, since the port number that I specified was, after all, the default port. Now I notice that I have another problem. While I can connect to the remote SQL 2008 Server instance, I cannot see all the databases in the instance. I am trying to connect to the 2008 instance from another PC using SQL Server 2008 Mgt Studio. I am connecting from a Windows 7 Ultimate PC to a Windows XP Pro PC. I suspect that my problem has something to do with not all database in the remote instance having the same version. For example, I "upgraded" a a SL 2005 database to 2008 by doing a backup frm 2005 and importing it into 2008. When I realized that this was not one of the database that I could see from my other PC, I noticed that the compatability level of the imported was still 2005, so I changed it to 2008. Still I could not see the database. I am sure that this is relevant: I just noticed that on my remote server, the sql node instance node, named "sql2008" says "version 10" when I am on the remote serfver, but when I connect to the sql2008 remote instance fron my local PC, the connection is shown locally as being a "SQL Servr version 8.0" instance. I suspect that locally, I am only being shown databases that are somehow in the remote 2008 instance but have not been upgraded. I guess I don't know what constitutes an upgraded database and I don not know who to connect to see all the databases, even if this requires multiple connections from the source PC.

  Read the article

• SQL Server Command Timeouts Timeout expired. The timeout period elapsed prior to completion of the o

  - by user104295

  Hi there, we've had a ASP.NET web application deployed for a number of years now.... last week we migrated to a slightly slower server to save some money. Now we're frequently getting command timeouts: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding This is understandable in that a slower server will take longer to produce results; take longer time and produce a timeout. Is there any system-wide way to get SqlClient to set a longer timeout? We cannot change the code, as it's everywhere... we're using multiple data access technologies as well. Maybe there's a default command timeout setting on a connection string? We just need to increase it by 30 seconds; we're happy to wait a bit longer for queries to return. thanks

  Read the article

• How to create select SQL statement that would produce "merged" dataset from two tables(Oracle DBMS)?

  - by Roman Kagan

  Here's my original question: merging two data sets Unfortunately I omitted some intircacies, that I'd like to elaborate here. So I have two tables events_source_1 and events_source_2 tables. I have to produce the data set from those tables into resultant dataset (that I'd be able to insert into third table, but that's irrelevant). events_source_1 contain historic event data and I have to do get the most recent event (for such I'm doing the following: select event_type,b,c,max(event_date),null next_event_date from events_source_1 group by event_type,b,c,event_date,null events_source_2 contain the future event data and I have to do the following: select event_type,b,c,null event_date, next_event_date from events_source_2 where b>sysdate; How to put outer join statement to fill the void (i.e. when same event_type,b,c found from event_source_2 then next_event_date will be filled with the first date found GREATLY APPRECIATE FOR YOUR HELP IN ADVANCE.

  Read the article

• Turning on multiple result sets in an ODBC connection to SQL Server

  - by David Griffiths

  I have an application that originally needed to connect to Sybase (via ODBC), but I've needed to add the ability to connect to SQL Server as well. As ODBC should be able to handle both, I thought I was in a good position. Unfort, SQL Server will not let me, by default, nest ODBC commands and ODBCDataReaders - it complains the connection is busy. I know that I had to specify that multiple active result sets (MARS) were allowed in similar circumstances when connecting to SQL Server via a native driver, so I thought it wouldn't be an issue. The DSN wizard has no entr y when creating a SystemDSN. Some people have provided registry hacks to get around this, but this did not work (add a MARS_Connection with a value of Yes to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\system-dsn-name). Another suggestion was to create a file-dsn, and add "MARS_Connection=YES" to that. Didn't work. Finally, a DSN-less connection string. I've tried this one (using MultipleActiveResultSets - same variable as a Sql Server connection would use), "Driver={SQL Native Client};Server=xxx.xxx.xxx.xxx;Database=someDB;Uid=u;Pwd=p;MultipleActiveResultSets=True;" and this one: "Driver={SQL Native Client};Server=192.168.75.33\\ARIA;Database=Aria;Uid=sa;Pwd=service;MARS_Connection=YES;" I have checked the various connection-string sites - they all suggest what I've already tried.

  Read the article

• How can I drop a table if it exists in SQL Server 2000?

  - by Keng

  I have a DTS package that drops a table then creates it and populates it but sometimes something happens and the package fails after the drop table. If it's rerun it fails cuz the table hasn't been created yet. Is there something like "if exists" for SQLServer 2000 like in MySQL? thanks. Edit Thanks everyone! I went with TrickyNixon because his was quick and easier to initiate. But, I would like everyone to eval that statement and make sure it is best-practices because if it is, I'm TOTALLY going to dump that into EVERY drop/create DTS package I see!!! Nice work TrickyNixon and everyone who threw in on this.

  Read the article

• Where does a Server trigger save in SQL Server?

  - by Mostafa

  A couple of days ago , I was practicing and I wrote some triggers like these : create trigger trg_preventionDrop on all server for drop_database as print 'Can not Drop Database' rollback tran go create trigger trg_preventDeleteTable on database for drop_table as print 'you can not delete any table' rollback tran But the problem is I don't know where it has saved and How can I delete or edit those. Thank you

  Read the article

• Can I add a delay to sql server transactional replication ?

  - by Brann

  I've got transactional replication configured from a database called DBProd to another database called DBWarehouse ; everything works fine, and transaction are usually replicated instantaneously to the warehouse .... which is my problem. I'd like to add a slight delay to the replication (something like 10 minutes), so that the replicated database can be used to access a previous version of the database (in case a bug occurs for example) Is there a simple way to achieve this ?

  Read the article

• SQL Server: How do I convert all varchar(max) columns to text type?

  - by bakerjr

  How do I convert all varchar(max) columns to text type? I know varchar(max) is recommended but I still want to use type text because CakePHP doesn't automagically convert varchar(max) to textarea (HTML form). How do I do it? Thanks in advance

  Read the article

• Possible to get PrimayKey IDs back after a SQL BulkCopy?

  - by chobo2

  Hi I am using C# and using SqlBulkCopy. I have a problem though. I need to do a mass insert into one table then another mass insert into another table. These 2 have a PK/FK relationship. Table A Field1 -PK auto incrementing (easy to do SqlBulkCopy as straight forward) Table B Field1 -PK/FK - This field makes the relationship and is also the PK of this table. It is not auto incrementing and needs to have the same id as to the row in Table A. So these tables have a one to one relationship but I am unsure how to get back all those PK Id that the mass insert made since I need them for Table B.

  Read the article

• SQL exclude a column using SELECT * [except columnA] FROM tableA?

  - by uu?????s

  We all know that to select all columns from a table, we can use SELECT * FROM tableA Is there a way to exclude column(s) from a table without specifying all the columns? SELECT * [except columnA] FROM tableA The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns. thanks!

  Read the article

• SQL: without a cursor, how to select records making a unique integer id (identity like) for dups?

  - by Dr. Zim

  If you have the select statement below where the PK is the primary key: select distinct dbo.DateAsInt( dateEntered) * 100 as PK, recordDescription as Data from MyTable and the output is something like this (the first numbers are spaced for clarity): PK Data 2010 01 01 00 New Years Day 2010 01 01 00 Make Resolutions 2010 01 01 00 Return Gifts 2010 02 14 00 Valentines day 2010 02 14 00 Buy flowers and you want to output something like this: PK Data 2010 01 01 01 New Years Day 2010 01 01 02 Make Resolutions 2010 01 01 03 Return Gifts 2010 02 14 01 Valentines day 2010 02 14 02 Buy flowers Is it possible to make the "00" in the PK have an "identity" number effect within a single select? Otherwise, how could you increment the number by 1 for each found activity for that date? I am already thinking as I type to try something like Sum(case when ?? then 1 end) with a group by.

  Read the article

• SQL Server Error: maximum number of prefixes. The maximum is 3.

  - by Ian Boyd

  Trying to run a cross-server update: UPDATE cmslive.CMSFintrac.dbo.lsipos SET PostHistorySequencenNmber = ( SELECT TransactionNumber FROM Transactions WHERE Transactions.TransactionDate = cmslive.CMSFintrac.dbo.lsipos.TransactionDate) Gives the error: Server: Msg 117, Level 15, State 2, Line 5 The number name 'cmslive.CMSFintrac.dbo.lsipos' contains more than the maximum number of prefixes. The maximum is 3. What gives? Note: Rearranging the query into a less readable join form: UPDATE cmslive.CMSFintrac.dbo.lsipos SET PostHistorySequenceNumber = B.TransactionNumber FROM cmslive.CMSFintrac.dbo.lsipos A INNER JOIN Transactions B ON A.TransactionDate = B.TransactionDate does not give an error.

  Read the article

• SQL Reporting 2008: easy way to share look and feel of reports?

  - by Gerrie Schenck

  What is the way to go to share a look and feel of a report among other reports in the same project? I want to share the layout, colors, etc. Thanks

  Read the article

• .NET app - Should we use SQL Server and duplicate some reference data from an external Oracle DB? Or use Oracle and have a DB link?

  - by Daventry

  We're looking to migrate some existing Excel/Access processes into a new system which will provide the users with a Silverlight frontend to run and view the reports instead of using MS Access. The initial idea was to have SQL Server 2008 as RDBMS. The problem is that we've got some static data such as country codes, counterparties, etc which live in an existing Oracle DB. Since we do not want to duplicate that data (if possible), we were thinking of having a DB link between SQL Server and Oracle, but our firm does not allow that. So the options are either duplicate the data or use Oracle as RDBMS - surprise, the firm does allow DB links between Oracle databases. The initial idea was also to use WCF RIA Services, Entity Framework, etc which we're not sure they play well with Oracle, that's why it was decided to go with SQL Server in the first place. Would you advise to go for Oracle so that we can just link the static data? Or use SQL Server 2008 and replicate it because it's "safer" to stay within the Microsoft land? To use or not to use Entity Framework and WCF RIA Services at all? Regards. UPDATE: Thanks everyone for your answers. Nothing is set in stone yet. We'll try to import the data instead of linking, as if the other DB goes down, our system can still carry on. We're likely to use SQL Server just because most developers are more experienced with it. Even if we used RIA Services, we can swap out the Data Access Layer and use other frameworks such those mentioned below.

  Read the article

• Migrate Data and Schema from MySQL to SQL Server

  - by colithium

  Are there any free solutions for automatically migrating a database from MySQL to SQL Server Server that "just works"? I've been attempting this simple (at least I thought so) task all day now. I've tried: SQL Server Management Studio's Import Data feature Create an empty database Tasks - Import Data... .NET Framework Data Provider for Odbc Valid DSN (verified it connects) Copy data from one or more tables or views Check 1 VERY simple table Click Preview Get Error: The preview data could not be retrieved. ADDITIONAL INFORMATION: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"table_name"' at line 1 (myodbc5.dll) A similar error occurs if I go through the rest of the wizard and perform the operation. The failed step is "Setting Source Connection" the error refers to retrieving column information and then lists the above error. It can retrieve column information just fine when I modify column mappings so I really don't know what the issue is. I've also tried getting various MySql tools to output ddl statements that SQL Server understand but haven't succeeded. I've tried with MySQL v5.1.11 to SQL Server 2005 and with MySQL v5.1.45 to SQL Server 2008 (with ODBC drivers 3.51.27.00 and 5.01.06.00 respectively)

  Read the article

< Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177  | Next Page >