I would like to have two tables. One I call SystemPropertyTypeTable = which=20 contains the defaults and the other UserPropertyTypeTable. Each has 3=20 fields. PropertyType, Description, Status. The idea here is to allow a user to change his/her defaults or to add a = new=20 Property Type...more >>

Hi. My database app stores URLs in the database. I noticed that if I use nvarchar I wind up with leading spaces. I read around the forum and realize I should use varchar. The only problem is that when I serve the pages, the URLs have extra characters: e.g. webpage/page.html%20%20. ...more >>

Hi, How is it possible to convert date without seconds ? dd mon yyyy hh:mi(24h) Cant find that convert() has an option for this. Thanks Totto ...more >>

I have an xls file that is about 358 kb and contains about 1569 rows. I am using the following code: Code: Use master DECLARE @reult varchar(200) EXEC @result = xp_cmdshell '\\ITITPAFS02\pds\voicenet\isiti\winzip\wzzip.exe \ \ititpafs01\pds\inetpub\ftproot\accumen\political \at0123.zip \\...more >>

CREATE TABLE Limiter ( ref_code CHAR(1) NOT NULL PRIMARY KEY ,limit_it CHAR(1) NOT NULL CHECK (limit_it IN ('T', 'F')) DEFAULT 'F' ); -- Limiter will have at most one entry in it at a time INSERT INTO Limter VALUES ('A', 'T'); CREATE TABLE Stuff ( item_code CHAR(1) NOT NULL P...more >>

Is it possible to restore deleted rows? Thanks ...more >>

Hi, Is there a way to get the input-filename in a variable inside the script file without using -v switch or environment variables? I have a requirement to store the inputfilename in a database table when the script is run successfully. So, I am expecting to use the following command in m...more >>

Hi all, Trying to select rows in a group by and, works great when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because there are different set of numbers for all rows. I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned from each group? SELECT COUNT(*)AS Total, SA...more >>

Hi All, I am using the new [sp_send_cdosysmail] from MS (http://support.microsoft.com/kb/312839/en-us). I need to add an attachment, so I added the old fix... EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @Attachment (in this case @Attachment = 'C:\Text.txt') As well, I added the...more >>

Hi, when I select from the SYSCOLUMNS system table, I see various numeric values in column XTYPE that I believe refer to the data type of the column. What do these values refer to? Do I need to join to another system table somewhere to see their actual (string) values?...more >>

select count(*) from t1 inner join t2 on t1.c_id = t2.c_id inner join t3 on t3.U_ID = t1.u_id and t3.L_ID = t1.L_ID and t3.D_ID = t1.D_ID and t3.P_ID = t1.P_ID inner join t4 on t4.cr = t3.cr_id t1 and t3 are big table with 10M and 30M rows. My tempDB...more >>

I was innocently looking through my database for events that happened during office hours -- I had to add the time zone and daylight savings time values to the date value, as they happened all over the world throughout the year. And I kept getting this error. Server: Msg 517, Level 16, Stat...more >>

Assume I have the stored proc shown below. I have a 'With Execute As' included. Doesn't this mean that whoever attempts to execute this stored proc, even if it is executed from a remote server, will get authenticated as if he were the user were the "sa" user? With all the permissions "sa" has?...more >>

ok, so I'm workin on a customer & leads part of this application. I need to setup a way to set a territory location status. We have two territories - LA and NY... LA Territory - California Customers & Leads. NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts, New Hampshire,...more >>

I'm trying to set an attribute on the root element in my slect for xml path statement. Here's my query: WITH XMLNAMESPACES('http://www.foo.com/' as n0) SELECT 'john' as name, '222 mystreet' as address for xml PATH ('MyPerson') ,root('n0:Root') which returns this: <n0:Root xmlns:n0="http...more >>

Hi guys n gals, Im on hunt for an escape code or command for my TSQL stored proc.. If Something = SomethingElse <Exit the Stored Proc> I feel a little dumb asking this but I Could not find any thing relating to termination aborting Exiting Ending etc... Regards Michael ...more >>

Ok... I give up... how do these 2 queries differ? SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) WHERE (OtherField<>6) SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) AND (OtherField<>6) (The "WHERE" is replaced with "AND") Th...more >>

Hello, In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A coupl...more >>

The locations of vehicles are received and stored in a database as a lat/lon value, and accompanied by the datetime timestamp of when the position was taken. Because of the technology, sometimes vehicles will submit their position multiple times within a minute, sometimes they are unable to r...more >>

Question: I have some 2000 databases that are in FULL mode and get log backups. The get a full backup in the early evening and then backup with a few log backups throughout the day. They are growing to a size that is uncomfortably large (for the drive) and so I'd like to shrink them after...more >>

Hi. I am trying to add a column to my table which should be a big varchar, therefor I tried this: ALTER TABLE table_name ADD column_name VARCHAR(MAX) The error I got was somewhat surprising: Incorrect syntax near 'MAX'. What do you think this is? Every tip and advice are appreciated. Tha...more >>

Hello All, I am having trouble getting the following to work. What is the syntax for multiple WHERE's, i need to use both fastener_name and num_action, eg fastener_name and num_action not in select fastener_name,num_action from #QA_Fastener_Check_Action where fastener_name and num_action no...more >>

Hi All I am using SQL Server 2005 I have a table CREATE TABLE dbo.testrun_reports ( RunID INT IDENTITY(1,1) PRIMARY KEY, RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(), db2dbreport VARCHAR(MAX), incexcreport VARCHAR(MAX) ); and stored procedure alter PROCEDURE dbo.R...more >>

Hello I am using SQL Server 2005 When trying to update xml field that contains xml without schema I got the following error: XQuery: Only nillable elements or text nodes can be updated with empty sequence The code I was using: Update interface set messages.modify( 'replace v...more >>

Would anyone know how I could update a data type from nvarchar to varchar for an entire database of tables? I don't want to have to manually open each table and replace the field data types. Please advise. Amytdev...more >>

Hi All, I have a field in a table which is varchar data type. Some records in that field of the table starts with number such as 2AFX. When I try to retreive records for that field I do not get any records ( select * from table where field1='2AFX'). But I see records are there. I feel...more >>

Assuming that a SQL Server 2005 DB table has a column named ID & one of the ID records is 25, if the following DELETE query is executed, the ID=25 record will get deleted from the DB table: DELETE FROM Users WHERE ID=25 Now if the above DELETE query is executed again, then SQL Server just g...more >>

I have these records and I want to retrieve the most current date of all the unique IDs: ID Date 001 1/1/2001 001 1/1/2002 001 1/1/2004 -- Returned 002 9/3/2002 002 9/5/2006 -- Returned 003 7/8/2004 003 8/5/2005 -- Ret...more >>

Ok, here's the skinny. We survey surfaces for radiation. Our equipment scans hundreds of meters in a single run, each meter containing 40 individual data acquisitions, each acquisiton containing 36 or so data values. Needless to say, this results in a bunch of data, and my current looping simply...more >>

String concatenation aggregate UDF or a query that is not a CLR based function. I have a customer table customerid int, customername varchar and a contacts table contactid int, customerid int, emailaddress varchar, includeinlist bit for each customer i want to return a list of emailaddresse...more >>

I've got a couple tables with identical structure... I would like to create an exception report indicating any differences in the content of any of the columns (even in the case where one value may be null and its counterpart a space)... Any ideas on how to build such a query ? ...more >>

Hello, I am new to SQL Server 2005 and I have 2 questions. 1) I am wondering how to set unique constraint on 'username' field in SQL Server Management Studio in Visual Way but not using Query Window. Here is the users table. would u please give me a step by step approach to achieve it? 2) T...more >>

I have two similar tables and Table1 is superset of Table2, e.g. Table1 ===== ModelCode ReferenceType ReferenceId Data1 Data2 Data3 1111 Type1 Value1 d1 d2 d3 1232 Type2 Value2 e1 ...more >>

Hi, Can someone pls help me on the following problem. How can I take only 8 decimal places on a numeric number? e.g turn 123456.12345678912 into 123456.12345679 turn 1234.123456789123 into 1234.12345679? Thanks in advance. ...more >>

HI All, I have a triger, tat inserts tree-hierchy of related data based on the initial row inserted. What I would like to do is return all the PK of those inserted rows. So A users inserts row PK1 THe trigger then isnerts related rows from a tree structured table INSERT INTO policysection_d...more >>

Hi I'm trying to insert a number of records into a linked server. One of the columns I'm trying to insert into, is an IDENTITY field so I need to run SET IDENTITY INSERT ON for this. How do I do this for a linked server? The SET IDENTITY INSERT ON statement only accepts the database and t...more >>