Search Results

Search found 4906 results on 197 pages for 'sqlexpress 2005'.

Page 16/197 | < Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >

• Weird SQL Server 2005 Collation difference between varchar() and nvarchar()

  - by richardtallent

  Can someone please explain this: SELECT CASE WHEN CAST('iX' AS nvarchar(20)) > CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END, CASE WHEN CAST('iX' AS varchar(20)) > CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END Results: 0 1 SELECT CASE WHEN CAST('i' AS nvarchar(20)) > CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END, CASE WHEN CAST('i' AS varchar(20)) > CAST('-' AS varchar(20)) THEN 1 ELSE 0 END Results: 1 1 On the first query, the nvarchar() result is not what I'm expecting, and yet removing the X make the nvarchar() sort happen as expected. (My original queries used the '' and N'' literal syntax to distinguish varchar() and nvarchar() rather than CAST() and got the same result.) Collation setting for the database is SQL_Latin1_General_CP1_CI_AS.

  Read the article

• Materialized Query Table in SQL Server 2005

  - by Azho KG

  In DB2 there is a support for Materialized Query Table (MQT). Basicly you write a query and create a MQT. But the difference from View is that the query is pre-executed and resulting data is stored in MQT and there are some options when to refresh/syncronize the MQT with base tables. I want same functionality in SQL Server. Is there a way to achieve same result? I've tables with millions of rows, and I want to show summary (like total # of members, total expense and etc) in dashboard. So I don't want to count every time user gets to dashboard, instead I want to store them in table and I want that table to be refresh each night. Any kind of hints, answers,suggestions and ideas are welcome. Thanks.

  Read the article

• Identity column SQL Server 2005 inserting same value twice

  - by DannykPowell

  I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value). We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, which is then updated back to the table. In some situations the stored procedure is called simultaneously by more than one process, resulting in "Violation of PRIMARY KEY constraint..." errors. This would seem to indicate that the identity column is allowing the same number to be inserted for more than one record. First question- how is this possible? Second question- how to stop it...there's no error handling currently so I'm going to add some try/ catch logic- but would like to understand the problem fully to deal with properly

  Read the article

• Exec problem in SQL Server 2005

  - by IordanTanev

  Hi, I have the situation where i have two databases with same structure. The first have some data in its data tables. I need to create a script that will transfer the data from the first database to the second. I have created this script. DECLARE @table_name nvarchar(MAX), @query nvarchar(MAX) DECLARE @table_cursor CURSOR SET @table_cursor = CURSOR FAST_FORWARD FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES OPEN @table_cursor FETCH NEXT FROM @table_cursor INTO @table_name WHILE @@FETCH_STATUS = 0 BEGIN SET @query = 'INSERT INTO ' + @table_name + ' SELECT * FROM MyDataBase.dbo.' + @table_name print @query exec @query FETCH NEXT FROM @table_cursor INTO @table_name END CLOSE @table_cursor DEALLOCATE @table_cursor The problem is that when I run the script the "print @query" statement prints statement like this INSERT INTO table SELECT * FROM MyDataBase.dbo.table When I copy this and run it from Management studio it works fine. But when the script tries to run it with exec I get this error Msg 911, Level 16, State 1, Line 21 Could not locate entry in sysdatabases for database 'INSERT INTO table SELECT * FROM MPDEV090314'. No entry found with that name. Make sure that the name is entered correctly. Hope someone can tell me whot is wront with this. Best Regards, Iordan Tanev

  Read the article

• Displaying tree path of record in SQL Server 2005

  - by jskiles1

  An example of my tree table is: ([id] is an identity) [id], [parent_id], [path] 1, NULL, 1 2, 1, 1-2 3, 1, 1-3 4, 3, 1-3-4 My goal is to query quickly for multiple rows of this table and view the full path of the node from its root, through its superiors, down to itself. The ultimate question is, should I generate this path on inserts and maintain it in its own column or generate this path on query to save disk space? I guess it depends if this table is write heavy or read heavy. I've been contemplating several approaches to using the "path" characteristic of this parent/child relationship and I just can't seem to settle on one. This "path" is simply for display purposes and serves absolutely no purpose other than that. Here is what I have done to implement this "path." AFTER INSERT TRIGGER - requires passing a NULL path to the insert and updating the path for the record at the inserted rows identity INSTEAD OF INSERT TRIGGER - does not require insert to have NULL path passed, but does require the trigger to insert with a NULL path and updating the path for the record at SCOPE_IDENTITY() STORED PROCEDURE - requiring all inserts into this table to be done through the stored procedure implementing the trigger logic VIEW - requires building the path in the view 1 and 2 seem annoying if massive amounts of data are entered at once. 3 seems annoying because all inserts must go through the procedure in order to have a valid path populated. 1, 2, and 3 require maintaining a path column on the table. 4 removes all the limitations of the above but require the view to perform the path logic and requires use of the view if a path is to be displayed. I have successfully implemented all of the above approaches and I'm mainly looking for some advice. Am I way off the mark here or are any of the above acceptable? Each has it's advantages and disadvantages.

  Read the article

• to_date in SQL Server 2005

  - by Chin

  Does any one know how I would have to change the following to work with ms sql? WHERE registrationDate between to_date ('2003/01/01', 'yyyy/mm/dd') AND to_date ('2003/12/31', 'yyyy/mm/dd'); What I have read implies I would have to construct it using DATEPART() which could become very long winded. Especially when the goal would be to compare on dates which I receive in the following format "2003-12-30 10:07:42". It would be nice to pass them off to the database as is. Any pointers appreciated.

  Read the article

• handling Concurrency in SQL SERVER 2005

  - by sameer

  Hi, I have one question for you, if you can answer and refer resource it will be great help. I have a scenario where i need to create a appointment slot and a serial no for each slot memberwise. ex: Member Id |App Slot # 1|1 1|2 2|1 2|2 1|3 what im doing is take the Max slot number,increamenting it and insert it memberwise. but the problem is concurrent user can create a slot when i take the max slot after that if any other user insert the slot the value that im working with is no more valid, how to over come this problem Thanks & Regards, Sameer

  Read the article

• Problem with nvarchar data with XML in SQL server 2005

  - by Paresh

  Create table testxml (xmldata xml) declare @var nvarchar(max) set @var = N'?????? ?????? ????' insert into testxml select @var After inserting , i am getting data from table like select * from testxml --------------------- ???????? Can you provide me the solution?

  Read the article

• TSQL Help (SQL Server 2005)

  - by Mick Walker

  I have been playing around with a quite complex SQL Statement for a few days, and have gotten most of it working correctly. I am having trouble with one last part, and was wondering if anyone could shed some light on the issue, as I have no idea why it isnt working: INSERT INTO ExistingClientsAccounts_IMPORT SELECT DISTINCT cca.AccountID, cca.SKBranch, cca.SKAccount, cca.SKName, cca.SKBase, cca.SyncStatus, cca.SKCCY, cca.ClientType, cca.GFCID, cca.GFPID, cca.SyncInput, cca.SyncUpdate, cca.LastUpdatedBy, cca.Deleted, cca.Branch_Account, cca.AccountTypeID FROM ClientsAccounts AS cca INNER JOIN (SELECT DISTINCT ClientAccount, SKAccount, SKDesc, SKBase, SKBranch, ClientType, SKStatus, GFCID, GFPID, Account_Open_Date, Account_Update FROM ClientsAccounts_IMPORT) AS ccai ON cca.Branch_Account = ccai.ClientAccount Table definitions follow: CREATE TABLE [dbo].[ExistingClientsAccounts_IMPORT]( [AccountID] [int] NOT NULL, [SKBranch] [varchar](2) NOT NULL, [SKAccount] [varchar](12) NOT NULL, [SKName] [varchar](255) NULL, [SKBase] [varchar](16) NULL, [SyncStatus] [varchar](50) NULL, [SKCCY] [varchar](5) NULL, [ClientType] [varchar](50) NULL, [GFCID] [varchar](10) NULL, [GFPID] [varchar](10) NULL, [SyncInput] [smalldatetime] NULL, [SyncUpdate] [smalldatetime] NULL, [LastUpdatedBy] [varchar](50) NOT NULL, [Deleted] [tinyint] NOT NULL, [Branch_Account] [varchar](16) NOT NULL, [AccountTypeID] [int] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[ClientsAccounts_IMPORT]( [NEWClientIndex] [bigint] NOT NULL, [ClientGroup] [varchar](255) NOT NULL, [ClientAccount] [varchar](255) NOT NULL, [SKAccount] [varchar](255) NOT NULL, [SKDesc] [varchar](255) NOT NULL, [SKBase] [varchar](10) NULL, [SKBranch] [varchar](2) NOT NULL, [ClientType] [varchar](255) NOT NULL, [SKStatus] [varchar](255) NOT NULL, [GFCID] [varchar](255) NULL, [GFPID] [varchar](255) NULL, [Account_Open_Date] [smalldatetime] NULL, [Account_Update] [smalldatetime] NULL, [SKType] [varchar](255) NOT NULL ) ON [PRIMARY] The error message I get is: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.

  Read the article

• SQL Server 2005 red herring error "The user does not have permission to perform this action"

  - by Sylvia

  We're getting the error "The user does not have permission to perform this action" during times of heavy load (regularly, when a trim job occurs). The error clears up when the service accessing sql server is restarted, so it's obviously not a real permissions problems. What kind of situations could cause an error like this, when it's not really a permissions problem? Any ideas of good places to start looking? thanks for any insights...

  Read the article

• SQL 2005: Select top N, group by ID with joins

  - by Suzy Fresh

  I'm having real difficulty with a query involving 3 tables. I need to get the 3 newest users per department grouped by department names. The groups should be sorted by the users.dateadded so the department with the newest activity is first. The users can exist in multiple departments so Im using a lookup table that just contains the userID and deptID. My tables are as follows. Department - depID|name Users - userID|name|dateadded DepUsers - depID|userID The output I need would be Receiving John Doe - 4/23/2010 Bill Smith - 4/22/2010 Accounting Steve Jones - 4/22/2010 John Doe - 4/21/2010 Auditing Steve Jones - 4/21/2010 Bill Smith - 4/21/2010

  Read the article

• SQL 2005 w/ C# optimal "Paging"

  - by David Murdoch

  When creating a record "grid" with custom paging what is the best/optimal way to query the total number of records as well as the records start-end using C#? SQL to return paged record set: SELECT Some, Columns, Here FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Column ASC) AS RowId, * FROM Records WHERE (...) ) AS tbl WHERE ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)) ) SQL to count total number of records: SELECT COUNT(*) FROM Records WHERE (...) Right now, I make two trips to the server: one for getting the records, and the other for counting the total number of records. What is/are the best way(s) to combine these queries to avoid multiple DB trips?

  Read the article

• SQL Server Agent 2005 job runs but no output

  - by alimack

  Essentially I have a job which runs in BIDS and as as a stand lone package and while it runs under the SQL Server Agent it doesn't complete properly (no error messages though). The job steps are: 1) Delete all rows from table; 2) Use For each loop to fill up table from Excel spreasheets; 3) Clean up table. I've tried this [MS page][1] (steps 1 & 2), didn't see any need to start changing from Server side security. Also SQLServerCentral.com for [this page][2], no resolution. How can I get error logging or a fix? Note I've reposted this from Server Fault as it's one of those questions that's not pure admin or programming. I have logged in as the proxy account I'm running this under, and the job runs stand alone but complains that the Excel tables are empty?

  Read the article

• exact full text search - sql server 2005

  - by csetzkorn

  Hi, Is it possible to do an 'exact full text search' with CONTAINS. I have removed all noise words etc. but the dbms still seems to manipulate the 'exact word' (e.g. 'j-blade - blade'). Can I disable this? Thanks. Christian PS: I would like to avoid like because it is too slow and with exact I mean that the text contains the exact word.

  Read the article

• Moving from a non-clustered PK to a clustered PK in SQL 2005

  - by adaptr

  HI all, I recently asked this question in another thread, and thought I would reproduce it here with my solution: What if I have an auto-increment INT as my non-clustered primary key, and there are about 15 foreign keys defined to it ? (snide comment about original designer being braindead in the original :) ) This is a 15M row table, on a live database, SQL Standard, so dropping indexes is out of the question. Even temporarily dropping the foreign key constraints will be difficult. I'm curious if anybody has a solution that causes minimal downtime. I tested this in our testing environment and finally found that the downtime wasn't as severe as I had originally feared. I ended up writing a script that drops all FK constraints, then drops the non-clustered key, re-creates the PK as a clustered index, and finally re-created all FKs WITH NOCHECK to avoid trawling through all FKs to check constraint compliance. Then I just enable the CHECK constraints to enable constraint checking from that point onwards, and all is dandy :) The most important thing to realize is that during the time the FKs are absent, there MUST NOT be any INSERTs or DELETEs on the parent table, as this may break the constraints and cause issues in the future. The total time taken for clustering a 15M row, 800MB index was ~4 minutes :)

  Read the article

• Convert XML to table in SQL Server 2005.

  - by Tamim Sadikali

  If I pass in an xml parameter to a stored proc which looks like this: <ClientKeys> <ck>3052</ck> <ck>3051</ck> <ck>3050</ck> <ck>3049</ck> ... </ClientKeys> ...and then convert the XML to a temp table like this: CREATE TABLE #ClientKeys ( ClientKey varchar(36) ) INSERT INTO #ClientKeys (ClientKey) SELECT ParamValues.ck.value('.','VARCHAR(36)') FROM @ClientKeys.nodes('/ClientKeys/ck') as ParamValues(ck) ...the temp tbl is populated and everything is good. However the time taken to populate said table is strictly proportionate to the number of 'ck' elements in the xml - which I wasn't expecting as there is no iterative step. And thus the time taken to populate the tbl soon becomes 'too long'. Is there a quicker way to achieve the above?

  Read the article

• Entity Framework How to specify paramter type in generated SQL (SQLServer 2005) Nvarchar vs Varchar

  - by Gratzy

  In entity framework I have an Entity 'Client' that was generated from a database. There is a property called 'Account' it is defined in the storage model as: <Property Name="Account" Type="char" Nullable="false" MaxLength="6" /> And in the Conceptual Model as: <Property Name="Account" Type="String" Nullable="false" /> When select statements are generated using a variable for Account i.e. where m.Account == myAccount... Entity Framework generates a paramaterized query with a paramater of type NVarchar(6). The problem is that the column in the table is data type of char(6). When this is executed there is a large performance hit because of the data type difference. Account is an index on the table and instead of using the index I believe an Index scan is done. Anyone know how to force EF to not use Unicode for the paramater and use Varchar(6) instead?

  Read the article

• SQL Server 2005 Import from Excel

  - by user327045

  I'd like to know what my best option would be to import data from an excel file on a weekly or monthly basis. At first, I thought I would use SSIS, but after much struggle with seemingly simple tasks, I'm starting to rethink my plan. Would it be better/easier to just write the SQL by hand or use the services of an SSIS package? The basic process will be as follows: A separate process will download an .xls file to a local fileshare. The xls file will have a filename like: 'myfilename MON YY'. I will need to read the month and year from the the filename, reformat it to a sql date and then query a DimDate table to find the corresponding date key. For each row (after the first 2 header rows), insert the data with the date key, unless the row is a total row, then ignore. Here are some of the issues I've been encountering with SSIS: I can parse the date string from a flat file datasource, but can't seem to do it with an excel data source. Also, once parsed, i cannot seem to convert the string to a date in order to perform the lookup for the date key. For example, I want to do something like this: select DateKey from DimDate where ActualDate = convert(datetime, '01-' + 'JAN-10', 120) but i don't think it is possible to use the 'convert' or 'datetime' keywords in an expression builder. I have been also unable to find where I can edit the SQL to ignore the first 2 rows of data. I'm very skeptical of using SSIS because it seems like a Kludgy way of doing something that can probably be accomplished more efficiently writing the SQL yourself, but I may be forced to use SSIS. Thoughts?

  Read the article

• How can I use a subquery as alias in SQL Server 2005

  - by Soham Dasgupta

  This is my code. Its giving me an error. select b.bill_no as 'Bill Number', (select descript from SALE_TERMS where STERMS_CODE='99')=b.[99] from BILLDET as b

  Read the article

• Other language string in SQL Server 2005

  - by balachandar

  I am trying you insert some string which is not in English (other language). when i fetch back they are not correct. They comes like "?????". But at the same time when I enter the string through the SQL Server UI (SSMS) to enter the string, it works OK. What could be the solution please?

  Read the article

• sql server 2005 - return single row when 2 records in right table

  - by Peanut

  Hi, I have two related sql server tables ... TableA and TableB. ***TableA - Columns*** TableA_ID INT VALUE VARCHAR(100) ***TableB - Columns*** TableB_ID INT TableA_ID INT VALUE VARCHAR(100) For every single record in TableA there are always 2 records in TableB. Therefore TableA has a one-to-many relationship with TableB. How could I write a single sql statement to join these tables and return a single row for each row in TableA that includes: a column for the VALUE column in the first related row in table B a column for the VALUE column in the second related row in table B? Thanks.

  Read the article

• Where can I find Microsoft Sql Profiler 2005?

  - by Kevin

  Is there a way to get the profiler without installing Microsoft Sql Server? If the answer is no, which edition do I need to get the profiler? Do I need the Developer Edition?

  Read the article

• SQL 2005 Merge / concatenate multiple rows to one column

  - by Dave

  Hi, We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F is there a way I can group these results within SQL to return as 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A B C D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E F Any ideas people? Many thanks Dave

  Read the article

• Copy 2 databases without detach in SQL 2005

  - by molgan

  Hello I have a server with live databases, and I have a test-server where I sometimes restore databases from the live one, to get fresh data. But this way I have to set up all the users again, I have about 4 different users with different permissions that need set again. Is there some way to do this a better way without using detach and attach, since I cant take the databases offline on the live-server? Prefered some thing I could run that "re-fills" the tables with fresh data. No need to redo stored procedures and rights. /M

  Read the article

• Data Access from single table in sql server 2005 is too slow

  - by Muhammad Kashif Nadeem

  Following is the script of table. Accessing data from this table is too slow. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Emails]( [id] [int] IDENTITY(1,1) NOT NULL, [datecreated] [datetime] NULL CONSTRAINT [DF_Emails_datecreated] DEFAULT (getdate()), [UID] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL, [From] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL, [To] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL, [Subject] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [Body] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [HTML] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [AttachmentCount] [int] NULL, [Dated] [datetime] NULL ) ON [PRIMARY] Following query takes 50 seconds to fetch data. select id, datecreated, UID, [From], [To], Subject, AttachmentCount, Dated from emails If I include Body and Html in select then time is event worse. indexes are on: id unique clustered From Non unique non clustered To Non unique non clustered Tabls has currently 180000+ records. There might be 100,000 records each month so this will become more slow as time will pass. Does splitting data into two table will solve the problem? What other indexes should be there?

  Read the article

< Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >