Search Results

Search found 36773 results on 1471 pages for 'sql statement syntax'.

Page 55/1471 | < Previous Page | 51 52 53 54 55 56 57 58 59 60 61 62  | Next Page >

• SQL server 2005 - user rights

  - by Paresh

  I have created one user named "tuser" with create database rights in SQL server 2005. and given the 'db_owner' database role of master and msdb database to "tuser". From this user login when I run the script for create database then it will create new database. But "tuser" don't have access that newly created database generated from script. Any one have any idea?, I want to write the script so "tuser" have access that new created database after creation and can have add user permission of newly created database. I want to give 'db_owner' database roles to "tuser" on that newly created database in the same script which create new database. The script run under 'tuser'.

  Read the article

• send dbmail on @@error from sql server 2005

  - by Ved

  Hi, I am trying to send database mail when error occurs inside the transaction.My setup for dbo.sp_send_dbmail is correct , when I execute the proc I do get an email within 1 min. However when I try to use dbo.sp_send_dbmail inside another proc within transactions than I do not get the email. Sql server does show in the result window that "Mail queued" but I never receive it. BEGIN TRANSACTION DECLARE @err int DECLARE @test nvarchar(max) RAISERROR('This is a test', 16, 1) SELECT @err = @@ERROR IF @err < 0 BEGIN SET @test = error_message() EXEC msdb.dbo.sp_send_dbmail @recipients= '[email protected]', @body = 'test inside', @subject = 'Error with proc', @body_format = 'HTML', @append_query_error = 1, @profile_name ='Database Mail Profile'; ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION And I get result as Msg 50000, Level 16, State 1, Line 7 This is a test Mail queued.

  Read the article

• How to make awkward pivot of sql table in MS SQL Server 2005?

  - by Oliver

  I have to rotate a given table from an sql server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format? Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time. Given Data ID | Label | Numerator | Denominator | Ratio ---+-----------------+-------------+---------------+-------- 1 | LabelNameOne | 41 | 10 | 4,1 1 | LabelNameTwo | 0 | 0 | 0 1 | LabelNameThree | 21 | 10 | 2,1 1 | LabelNameFour | 15 | 10 | 1,5 2 | LabelNameOne | 19 | 19 | 1 2 | LabelNameTwo | 0 | 0 | 0 2 | LabelNameThree | 15 | 16 | 0,9375 2 | LabelNameFive | 19 | 19 | 1 2 | LabelNameSix | 17 | 17 | 1 3 | LabelNameOne | 12 | 12 | 1 3 | LabelNameTwo | 0 | 0 | 0 3 | LabelNameThree | 11 | 12 | 0,9167 3 | LabelNameFour | 12 | 12 | 1 3 | LabelNameSix | 0 | 1 | 0 Wanted result ID | ValueType | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix ---+-------------+--------------+--------------+----------------+---------------+---------------+-------------- 1 | Numerator | 41 | 0 | 21 | 15 | | 1 | Denominator | 10 | 0 | 10 | 10 | | 1 | Ratio | 4,1 | 0 | 2,1 | 1,5 | | 2 | Numerator | 19 | 0 | 15 | | 19 | 17 2 | Denominator | 19 | 0 | 16 | | 19 | 17 2 | Ratio | 1 | 0 | 0,9375 | | 1 | 1 3 | Numerator | 12 | 0 | 11 | 12 | | 0 3 | Denominator | 12 | 0 | 12 | 12 | | 1 3 | Ratio | 1 | 0 | 0,9167 | 1 | | 0

  Read the article

• SQL syntax Newbie student

  - by sammysmall

  Describe the output of the following SQL query: select custId, name from customer where region = "New York" UNION select cust.custId, cust.name from customer cust where cust.custId IN (select cust_order.custId from customer_order cust_order, company_employee comp_emp where cust_order.salesEmpId = comp_emp.empId AND comp_emp.name = 'DANIEL'); My question is: at line from customer cust is cust referring to a column in the customer table... This is a homework question I have identified the components leading up to this line and I think that cust is a column in the customer table... I am not asking for an overall solution just a bit of encouragement if I am on the right track...

  Read the article

• SQL CHECK constraint issues

  - by blahblah

  I'm using SQL Server 2008 and I have a table with three columns: Length, StartTime and EndTime. I want to make a CHECK constraint on this table which says that: if Length == NULL then StartTime <> NULL and EndTime <> NULL else StartTime == NULL and EndTime == NULL I've begun to try things like this: Length == NULL AND StartTime <> NULL AND EndTime <> NULL Obviously this is not enough, but even this simple expression will not validate. I get the error: "Error validating 'CK_Test_Length_Or_Time'. Do you want to edit the constraint?" Any ideas on how to go about doing this?

  Read the article

• How to display all the dates between two given dates in SQL

  - by Gopal

  Using SQL server 2000. If the Start date is 06/23/2008 and End date is 06/30/2008 Then I need the Output of query as 06/23/2008 06/24/2008 06/25/2008 . . . 06/30/2008 I Created a Table names as Integer which has 1 Column, column values are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 then I used the below mentioned query Tried Query SELECT DATEADD(d, H.i * 100 + T .i * 10 + U.i, '" & dtpfrom.Value & "') AS Dates FROM integers H CROSS JOIN integers T CROSS JOIN integers U order by dates The above query is displaying 999 Dates only. 999 Dates means (365 + 365 + 269) Dates Only. Suppose I want to select more than 3 Years (01/01/2003 to 01/01/2008). The above query should not suitable. How to modify my query? Or any other query is available for the above condition. Please kindly provide me the Query.

  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

• How to make awkward pivot of sql table in SQL Server 2005?

  - by Oliver

  I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format? Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time. Given Data ID | Label | Numerator | Denominator | Ratio ---+-----------------+-------------+---------------+-------- 1 | LabelNameOne | 41 | 10 | 4,1 1 | LabelNameTwo | 0 | 0 | 0 1 | LabelNameThree | 21 | 10 | 2,1 1 | LabelNameFour | 15 | 10 | 1,5 2 | LabelNameOne | 19 | 19 | 1 2 | LabelNameTwo | 0 | 0 | 0 2 | LabelNameThree | 15 | 16 | 0,9375 2 | LabelNameFive | 19 | 19 | 1 2 | LabelNameSix | 17 | 17 | 1 3 | LabelNameOne | 12 | 12 | 1 3 | LabelNameTwo | 0 | 0 | 0 3 | LabelNameThree | 11 | 12 | 0,9167 3 | LabelNameFour | 12 | 12 | 1 3 | LabelNameSix | 0 | 1 | 0 Wanted result ID | ValueType | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix ---+-------------+--------------+--------------+----------------+---------------+---------------+-------------- 1 | Numerator | 41 | 0 | 21 | 15 | | 1 | Denominator | 10 | 0 | 10 | 10 | | 1 | Ratio | 4,1 | 0 | 2,1 | 1,5 | | 2 | Numerator | 19 | 0 | 15 | | 19 | 17 2 | Denominator | 19 | 0 | 16 | | 19 | 17 2 | Ratio | 1 | 0 | 0,9375 | | 1 | 1 3 | Numerator | 12 | 0 | 11 | 12 | | 0 3 | Denominator | 12 | 0 | 12 | 12 | | 1 3 | Ratio | 1 | 0 | 0,9167 | 1 | | 0

  Read the article

• SQL - Two foreign keys that have a dependency between them

  - by Brian

  The current structure is as follows: Table RowType: RowTypeID Table RowSubType: RowSubTypeID FK_RowTypeID Table ColumnDef: FK_RowTypeID FK_RowSubTypeID (nullable) In short, I'm mapping column definitions to rows. In some cases, those rows have subtype(s), which will have column definitions specific to them. Alternatively, I could hang those column definitions that are specific to subtypes off their own table, or I could combine the data in RowType and RowSubType into one table and work with a single ID, but I'm not sure either is a better solution (if anything, I'd lean towards the latter, as we mostly end up pulling ColumnDefs for a given RowType/RowSubType). Is the current design SQL Blasphemy? If I keep the current structuree, how do I maintain that if RowSubTypeID is specified in ColumnDef, that it must correspond to the RowType specified by RowTypeID? Should I try to enforce this with a trigger or am I missing a simple redesign that would solve the problem?

  Read the article

• MS-SQL statement to replace/delete sub-strings

  - by StefanE

  Hi, I have a table with 6 columns containing HTML content with some markups in it and now when moving to a new designed site most of this HTML code has to be deleted. More or less all tags except and . Is there a nice way of doing this, identify all tags end delete them within the data? I'm sure there are no < symbols in the test so a regular expression would maybe work? My alternative is to fecth every row, process it and update the database but I'm guessing this is possible to do in SQL directly. Thanks, Stefan

  Read the article

• Problems when going from SQL 2005 to SQL 2008

  - by Nezdet

  Hi! I did go over from SQL server 2005 to 2008. Doing that gave me some problems with the fulltext search. This site is based on Fulltext search. It occurs more deadlocks, the search is slower and sometimes it return empty lists, don't know why. A lot of people has been writning about they having this problem with 2008. But I haven'tgot any solutions why 2005 worked better for my program.. PLS help me out!

  Read the article

• SQL Server union selects built dynamically from list of words

  - by Adam Tuttle

  I need to count occurrence of a list of words across all records in a given table. If I only had 1 word, I could do this: select count(id) as NumRecs where essay like '%word%' But my list could be hundreds or thousands of words, and I don't want to create hundreds or thousands of sql requests serially; that seems silly. I had a thought that I might be able to create a stored procedure that would accept a comma-delimited list of words, and for each word, it would run the above query, and then union them all together, and return one huge dataset. (Sounds reasonable, right? But I'm not sure where to start with that approach...) Short of some weird thing with union, I might try to do something with a temp table -- inserting a row for each word and record count, and then returning select * from that temp table. If it's possible with a union, how? And does one approach have advantages (performance or otherwise) over the other?

  Read the article

• SQL Server: query database user roles for all databases in server

  - by atricapilla

  I would like to make a query for database user roles for all databases in my sql server instance. I modified a query from sp_helpuser: select u.name ,case when (r.principal_id is null) then 'public' else r.name end ,l.default_database_name ,u.default_schema_name ,u.principal_id from sys.database_principals u left join (sys.database_role_members m join sys.database_principals r on m.role_principal_id = r.principal_id) on m.member_principal_id = u.principal_id left join sys.server_principals l on u.sid = l.sid where u.type <> 'R' How can I modify this to query from all databases? What is the link between sys.databases and sys.database_principals?

  Read the article

• Export view data programmatically in Access/SQL Server

  - by andy

  We have an Access application front-end connected to a SQL Server 2000 database. We would like to be able to programmatically export the results of some views to whatever format we can (ideally Excel, but CSV / tab delimited is fine). Up until now we've just hit F11, opened up the view, and hit File-Save As, but we're starting to get results with more than 16,000 results, which can't be exported. I'd like some sort of server side stored procedure we can trigger that will do this. I'm aware of the sp_makewebtask procedure that does this, however it requires administrative rights on the server, and for obvious reasons we can't give that to everyone. Any ideas?

  Read the article

• SQL Server 2008 GUID column is all 0's

  - by Andy Evans

  I'm hoping this is a simple goof I did on my end ... I have a table in my database set up like so: column name: widget_guid data type: uniqueidentifier allow nulls: false default value: newid() identity: false row guid: true When records are created (via LINQ to SQL) that the values in this field are formatted as a GUID but contain all 0's My assumption was that when a new record was created, that a guid would be autogenerated for that column, much like an auto-incrementing row id. Is this not true? Any help would be greatly appreciated. Thanks.

  Read the article

• Add version control to existing SQL Server database

  - by ederbf

  I am part of a development team currently working with a database that does not have any kind of source control. We work with SQL Server 2008 R2 and have always managed the DB directly with SSMS. It now has ~340 tables and ~1600 stored procedures, plus a few triggers and views, so it is not a small DB. My goal is to have the DB under version control, so I have been reading articles, like Scott Allen's series (http://bitly.com/9cJmGR) and many old SO related questions. But I am still unable to decide on how to proceed. What I'm thinking of is to script the database schema in one file, then procedures, triggers and views in one file each. Then keep everything versioned under Mercurial. But of course, every member of the team can access SSMS and directly change the schema and procedures, with the possibility that any of us can forget to replicate those changes in the versioned files. What better options are there? And, did I forget any element worth having source control of?

  Read the article

• Group SQL tables in Microsoft SQL Server Management Studio object explorer

  - by MainMa

  I have a table which has approximately sixty tables, and other tables are added constantly. Each table is a part of a schema. A such quantity of tables makes it difficult to use Microsoft SQL Server Management Studio 2008. For example, I must scroll up in object explorer to access database related functions, or scroll down each time I need to access Views or Security features. Is it possible to group several tables to be able to expand or collapse them in Object Explorer? Maybe a folder may be displayed for each schema, letting collapse the folders I don't need to use?

  Read the article

• Storing SQL queries in Table in sql server

  - by Rohit

  We have multiple jobs in our system.These jobs are listed in a grid. We have 3 different user types (usertypeid 1,2,3). For each user listing is different and he can filter listing by selecting view from a dropdown. ViewName in the below table is the view which needs to be displayed. To achieve this functionality, a fellow developer has created the following table structure and stored sql fragments in SQLExpression in the below table. According to me the query should not be stored in database. What are the pros and cons of this approach and what are the available alternatives? JobListingViewID ViewName SQLExpression UserTypeID 3 All Jobs 1 = 1 3 4 Error Jobs JobStatusID IN ( 2 ) 1 5 Error Jobs JobStatusID IN ( 2 ) 2 6 Error Jobs JobStatusID IN ( 2 ) 3 7 Speech JobStatusID IN ( 1, 3, 8 ) 1

  Read the article

• Finding efficient overlapped entries in a SQL table

  - by Laoneo

  What is the most efficient way to find all entries which do overlap with others in the same table? Every entry has a start and end date. For example I have the following database setup: CREATE TABLE DEMO ( DEMO_ID int IDENTITY , START date NOT NULL , END date NOT NULL }; INSERT INTO DEMO (DEMO_ID, START, END) VALUES (1, '20100201', '20100205'); INSERT INTO DEMO (DEMO_ID, START, END) VALUES (2, '20100202', '20100204'); INSERT INTO DEMO (DEMO_ID, START, END) VALUES (3, '20100204', '20100208'); INSERT INTO DEMO (DEMO_ID, START, END) VALUES (4, '20100206', '20100211'); My query looks as follow: SELECT DISTINCT * FROM DEMO A, DEMO B WHERE A.DEMO_ID != B.DEMO_ID AND A.START < B.END AND B.START < A.END The problem is when my demo table has for example 20'000 rows the query takes too long. My environment is MS SQL Server 2008. Thanks for any more efficient solution

  Read the article

• SQL aggregation query, grouping by entries in junction table

  - by cm007

  I have TableA in a many-to-many relationship with TableC via TableB. That is, TableA TableB TableC id | val fkeyA | fkeyC id | data I wish the do select sum(val) on TableA, grouping by the relationship(s) to TableC. Every entry in TableA has at least one relationship with TableC. For example, TableA 1 | 25 2 | 30 3 | 50 TableB 1 | 1 1 | 2 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 should output 75 30 since rows 1 and 3 in Table have the same relationships to TableC, but row 2 in TableA has a different relationship to TableC. How can I write a SQL query for this?

  Read the article

• SQL 2008 GUID column is all 0's

  - by Andy Evans

  I'm hoping this is a simple goof I did on my end ... I have a table in my database set up like so: column name: widget_guid data type: uniqueidentifier allow nulls: false default value: newid() identity: false row guid: true When records are created (via LINQ to SQL) that the values in this field are formatted as a GUID but contain all 0's My assumption was that when a new record was created, that a guid would be autogenerated for that column, much like an auto-incrementing row id. Is this not true? Any help would be greatly appreciated. Thanks.

  Read the article

• Disable Primary Key and Re-Enable After SQL Bulk Insert

  - by Jon

  I am about to run a massive data insert into my DB. I have managed to work out how to enable and rebuild non-clustered indexes on my tables but I also want to disable/enable primary keys. You can't disable the clustered index for the primary key as the table is inaccessible when that is done and my attempt to do a ALTER TABLE for constraints does not work as I think that is only for foreign keys. Do you know of a way to Disable the Primary Key and Re-Enable After SQL Bulk Insert. NOTE: This is over numerous tables and so I don't know the exact primary key specifications eg/name etc

  Read the article

• sql query where parameters null not null

  - by Laziale

  I am trying to do a sql query and to build the where condition dynamically depending if the parameters are null or no. I have something like this: SELECT tblOrder.ProdOrder, tblOrder.Customer FROM tblOrder CASE WHEN @OrderId IS NOT NULL THEN WHERE tblOrder.OrderId = @OrderId ELSE END CASE WHEN @OrderCustomer IS NOT NULL THEN AND tblOrder.OrderCustomer = @OrderCustomer ELSE END END This doesn't work, but this is just a small prototype how to assemble the query, so if the orderid is not null include in the where clause, or if the ordercustomer is not null include in the where clause. But I see problem here, for example if the ordercustomer is not null but the orderid is null, there will be error because the where keyword is not included. Any advice how I can tackle this problem. Thanks in advance, Laziale

  Read the article

• Linq to SQl Stored Procedure Problem( it can't figure out the return type)

  - by chobo2

  Hi I have this SP USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[UsersInsert](@UpdatedProdData XML) AS INSERT INTO dbo.UserTable(UserId,UserName,LicenseId,Password,PasswordSalt,Email,IsApproved,IsLockedOut,CreateDate, LastLoginDate,LastLockOutDate,FailedPasswordAttempts,RoleId) SELECT @UpdatedProdData.value('(/ArrayOfUsers/Users/UserId)[1]', 'uniqueidentifier'), @UpdatedProdData.value('(/ArrayOfUsers/Users/UserName)[1]', 'varchar(20)'), @UpdatedProdData.value('(/ArrayOfUsers/Users/LicenseId)[1]', 'varchar(50)'), @UpdatedProdData.value('(/ArrayOfUsers/Users/Password)[1]', 'varchar(128)'), @UpdatedProdData.value('(/ArrayOfUsers/Users/PasswordSalt)[1]', 'varchar(128)'), @UpdatedProdData.value('(/ArrayOfUsers/Users/Email)[1]', 'varchar(50)'), @UpdatedProdData.value('(/ArrayOfUsers/Users/IsApproved)[1]', 'bit'), @UpdatedProdData.value('(/ArrayOfUsers/Users/IsLockedOut)[1]', 'bit'), @UpdatedProdData.value('(/ArrayOfUsers/Users/CreateDate)[1]', 'datetime'), @UpdatedProdData.value('(/ArrayOfUsers/Users/LastLoginDate)[1]', 'datetime'), @UpdatedProdData.value('(/ArrayOfUsers/Users/LastLockOutDate)[1]', 'datetime'), @UpdatedProdData.value('(/ArrayOfUsers/Users/FailedPasswordAttempts)[1]', 'int'), @UpdatedProdData.value('(/ArrayOfUsers/Users/RoleId)[1]', 'int') Now this SP creates just fine. It's when I go to VS2010 and try to drag this SP in my method panel of my linq to sql file in design view. It tells me that it can't figure out the return type. I try to go to the properties but it does not have "none" as a choice and I can't type it in. It should be "none" so how do I set it to "none"?

  Read the article

• T-SQL stored procedure to return google style "suggested" search results

  - by stephen776

  Ok, using SQL Server 2008. On my web page I have a textbox with jQuery-UI AutoComplete hooked up. Now I need a stored procedure to search across all columns of a single table(or multiple joined tables I suppose) for a search string coming from the textbox/autocomplete AJAX call, and return "suggested" search strings. I am using the AdventureWorks db for testing(Products table) So for example, the product table has columns for product name and product number(among others) and I want to return suggested search strings based on user input where they may enter a product name and/or a product number. I have it working across a single column which was simple. Any ideas?

  Read the article

< Previous Page | 51 52 53 54 55 56 57 58 59 60 61 62  | Next Page >