Hello, I'm building an application that connects to SQL Server 2005. It currently uses Windows authentication, but I'd like to switch to SQL Authentication (I believe it is also sometimes called Mixed Authentication). My current connection string is: "Data Source=LOCALHOST;Initial Catalog={0};Integrated Security=SSPI" That's for Windo...
I have to update value based on values from another table: update OracleOb..NS.myTable set name = (select name from myTable1 where id = 1) where id = 1 here the SQL has some problem. How can I get value from myTable1 and set it to myTable? I am using MS SQL 2005. Sorry I have to edit this question again. The table myTable is a li...
How do I remove the last character in a string in T-SQL? I.E. 'TEST STRING' to return: 'TEST STRIN' thanks Dave ...
update: changed one time to show that the times per shipment may not be in sequential order always. here is my input create table test ( shipment_id int, stop_seq tinyint, time datetime ) insert into test values (1,1,'2009-8-10 8:00:00') insert into test values (1,2,'2009-8-10 9:00:00') insert into test values (1,3,'2009-8-10 10:00...
I'm working on a stored procedure for a calendar application. Each event in the calendar can have several dates. This information is stored in two different tables. Rather than write two stored procedures and call the second one multiple times to save the dates, I'd rather just pass them in using XML. The trouble is that I want to conver...
This seems like an easy thing, but I'm drawing a blank. Select * from .... inner join ( select JobsID, Value from Jobs where Value **is the highest** ) as MaxJob on MaxJob.CustID = A.CustID inner join ( select other information based upon MaxJob.JobID ) as OtherStuff Is there a nice way to have that first subquery gi...
The objective is below the list of tables. Tables: Table: Job JobID CustomerID Value Year Table: Customer CustomerID CustName Table: Invoice SaleAmount CustomerID The Objective Part 1: (easy) I need to select all invoice records and sort by Customer (To place nice w/ Crystal Reports) Select * from Invoice as A inner join...
Hi all, I need to improve some existing stored procedures in my project for better transaction handling. I understand I can use the SET XACT_Abort ON statement in my procedure so that transaction will be automatically rolled back in case of errors. I can also use Try/Catch block for error handling and roll back the transaction in the Cat...
Hi, I have parent / child tables: parent table: id | description 1 | Alexandra 2 | Natalia child table: id | id_parent | description 1 | 1 | Programmer 2 | 1 | Surgery 3 | 2 | Programmer 4 | 2 | IT How to return set of record according filters, like if we want to get all records with "Programm...
With SQL Server 2005 Express (obeserved on XP and Server 2003), I get sometimes huge Error logs files in production: The file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG grows to fill the disk (file size becomes more than 15 GB). This file is not the transaction log, just the error log : a text log for SQL Server. ...
Hi, I want to insert the data in my table tblSubscriptions and I only want to use one insert statement. I am going to insert data for every userId in User Table. The following SQL doesnot work. Insert tblSubscriptions (UserID, ProductID, isACtive, SubscriptionDays, Price, MonthlyPrice, ProductType, CurrencyID) Values ((Select userID Fr...
Hi All, I am using declare @insertsql nvarchar(MAX) --above @insertsql for sp_executesql takes only nvarchar as input set @insertsql='--i am giving More than 10000 characters here -----' EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT', @inXMLRequest OUTPUT how to insert morethan 10000 charecters in NVARCHAR(MAX) in sq...
Hi All, I am using declare @insertsql nvarchar(MAX) --above @insertsql for sp_executesql takes only nvarchar as input set @insertsql='--i am giving More than 10000 characters here -----' EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT', @inXMLRequest OUTPUT how to insert morethan 10000 charecters in NVARCHAR(MAX) in sql...
I have an expensive scalar UDF that I need to include in a select statement and use that value to narrow the results in the where clause. The UDF takes parameters from the current row so I can't just store it in a var and select from that. Running the UDF twice per row just feels wrong: Select someField, someOtherField, ...
I have a scheduled job with a SP running on daily basis (SQL Server 2005). Recently I frequently encounter deadlock problem for this SP. Here is the error message: Message Executed as user: dbo. Transaction (Process ID 56) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock...
I need to make a query on multiple databases. I got the part for building the query for every database but now i need to put the result in something for each query then i can return it. @requete is a query ALTER PROCEDURE [dbo].[RequeteMultiBd] @requete varchar(max) AS BEGIN --get server + database name select dbo.trim(Ser...
I have a table in a SQL 2005 database which is brand new. As part of our application deployment we load the table with about 2.6M rows. Once that is done, the indexes on the table are all rebuilt. Then the users are let into the system and queries against that table time out. I can then rebuild the indexes (using the same exact script th...
I added an additional, new DataSet to my report and have been getting this cryptic error ever since. ...
I am learning SQL and am trying to learn JOINs this week. I have gotten to the level where I can do three table joins, similar to a lot of examples I've seen. I'm still trying to figure out the tiny details of how things work. All the examples I've seen of three table joins use INNER JOINS only. What about LEFT and RIGHT JOINs? Do ...
CREATE FUNCTION GetPayCodeList ( -- Add the parameters for the function here @PC varchar(50) ) RETURNS TABLE AS RETURN ( IF @PC = '*' SELECT DISTINCT ID, Code, Description FROM tbl ELSE SELECT DISTINCT ID, Code, Description FROM t...