Suppose I have these two tables: Invoice ------- iInvoiceID int PK not null dtCompleted datetime null InvoiceItem ----------- iInvoiceItemID int PK not null iInvoiceID int FK (Invoice.iInvoiceID) not null dtCompleted datetime null Each InvoiceItem might be fulfilled by a different process (executable) that runs on a different machine...
I have a simplified case here: a table with 5 fields of startdate, starttime, enddate, endtime and totalduration, all as varchar(20) type. Date fields are in the format like '02/02/2009' and time format like '02:02:00'. There are no null values at all. There is no problem for the following query: select cast(startdate + ' ' + sta...
How can I stop a particular user from accessing the database for a period of time but at the same time not lose the permissions the user has on db objects. Basically when he comes back (ie when access is given back) he should have all the permissions he had before his access was cut off. If I use sp_revokedbaccess 'myuser' the user myu...
When I use SSMS to script a stored procedure, it wraps the script in a sp_executesql statement which bugs me. Is there a way to stop SSMS from doing this and use a straight CREATE PROCEDURE...? EDIT: I meant SSMS not SSIS ...
Hi guys I am looking at doing some automated unit testing and I was wondering if you know of any way that one can "Parse"/"Compile" a stored proc(s) from code (i.e. from within .net). The case I am trying to catch is if someone makes a change to a table (i.e. removes a column) and that breaks a stored proc somewhere that they don't kn...
Hi, in my current C# windows application password has been stored in plain text which is obviously not good. so i just want to know what is the best way to encrypt the password and stored into SQL Server. I have read that using hash+salt is better. but i feel "EncryptByPassPhrase","DecryptByPassPhrase" new feature in sql 2005 is better t...
I have a list of ISO2 country codes I want to use in a query. Something like this: select cou, 128,13, 1 from ('AD', 'AE', 'AF', 'AG', 'AI', 'AL', 'AM', [snip]) as cou But.. working. I remember doing such a thing in the past, but I can't find any doc about it anymore. It's a one shot query so I don't mind performance, coding practice...
How to set the Password for sql server 2005 MDF file. Becoz i want to give the trail package to the client,package including the MDF. After installing the package, the MDF will be placed in C drive, user data will store in MDF file through the application. but not allow to attach that MDF file using sql server in that system. ...
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other ...
Is it possible to get the list of Full Text Search noise/stop words from SQL Server 2005 by querying the database? I am aware that the noise words are in a text file ~/FTData/noiseEng.txt but this file is not accessible to our application. I've look at the sys.fulltext_* tables but these don't seem to have the words. ...
Hi , i have created a procedure create procedure testProcedure_One as DECLARE @Query nvarchar(4000) begin SET @Query = 'SELECT * into #temptest FROM Table1' Exec sp_Executesql @query SELECT * FROM #temptest drop table #temptest end When i run the procedure testProcedure_One i am getting "Invalid object name '#temp' " erro...
I have the following hierachy Category SubCategory sub-SubCategory I have two tables for this:- Category(CategoryID,CatName,CatDesc) CategoryID is primaryKey SubCategory(SubCategoryID,SubCategoryName,SubCategoryDesc,CategoryID,ParentSubCategoryID) SubCategoryID is primarykey. CategoryID is foreignKey to CategoryID of C...
I'm trying to pull results from a database (sql server 2005) which takes 4 tables: Subscriber S, Member M, ClaimLines L, ClaimHistoryLines H Query is as follows: select S.SBSB_ID, M.MEME_NAME, (CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_H...
I have a simple little table (just 8 fields) an hour ago I rebuilt one of the indexes on the table, which reset it to 0% fragmentation, but now it’s up to 38%. The table itself has 400k records in it, but only 158 new ones have been inserted since I rebuilt the index, there have been no updates to records but perhaps a couple of deletes...
In our server client software in vb6 with sql 2005, On server side one sql table is continously updated by data and we need to display data from this same table on client at running time. Pls help how to acceess this same table from server and client so that data is continously updated from server side and records in this table show on c...
I have a query that counts member's wedding dates in the database... Select Sum(NumberOfBrides) As [Wedding Count], DATEPART( wk, WeddingDate) as [Week Number], DATEPART( year, WeddingDate) as [Year] FROM MemberWeddingDates Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate) Order By Sum(NumberOfBrides) Desc How...
Hello, I'm new to XML world. I'm having a problem with XML data type in SQL Server 2005. I've a stored procedure that expects XML parameter. The value of the parameter as given below... DECLARE @productIds xml SET @productIds = '<ROOT> <AGENTLIST> <AGENT> <HOSTAPPLICATIONRECORDKEY>GRI</HOSTAPPLICATIONRECO...
I have SQL Server 2005 with several linked server defined. One of them is a connection to an Oracle server and another one is an ODBC bridge to another server on a remote machine (ODBC server). Recently I tried to use the linked server to Oracle to update data with two large size tables by using several joints. The update query took too...
I wrote a tsql procedure which inserts a string into a text file which means it requires all variables be converted to a string. Instead of using a case by case statement, is there an easier to do this which encompasses all cases and forces whatever type to a string type? thanks in advance ...
Guys, Quick Questions... Where are the values for SET OPTIONS stored in the database for a SP, Func, Trigger, etc? If they are different from the global settings? SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF I know t...