i was just working with cursor in SQL 2008 but i face some problems. the first is that with the line i declared the cursor every time i execute the query it gives me an error. which says the cursor with such a name already exists. i want to offer me a way that if the cursor exists do not create it and if it is not defined yet sql creates...
Hello, I'm trying to increase the time between dates by a set amount. For example, I want to add two months to the time between date 1,2 and 3. I'm having trouble incrementing my 'date counter'... DECLARE @monthDiff int SET @monthDiff = 1; UPDATE [Table1] SET [Date] = DATEADD(MONTH, (SET @monthDiff = @monthDiff + 1), [Date]) WHERE ...
HI As a daily check I have to check the SQL connection to all our servers. At the moment this is done by manually logging on through SQL server managment studio. I was just wondering if this can be coded in C# so that I can run it first thing in a morning and its checks each server and the instance for a valid SQL connection and reports...
Hi people, please help me with this one, i want to convert a value from Bigint to datetime. For example im reading HISTORY table of teamcity server, on the field *build_start_time_server* i have this value on one record 1283174502729. How can i convert to datetime value??? Thanks ...
Hello all, me again! I have the following sp that uses a condtional where clause - ish! @MemberId varchar(7), @LocationId varchar(8), @UUF1 varchar(150), @UUF2 varchar(50), @UUF4 varchar(50), @IDDesc varchar(255), @OwnBrand nvarchar(50), @WeightStatus varchar(50), @MaterialLevel varchar(10), @BaseMaterialName varchar(15), @ExtendedMa...
Hi, I have a custom query which eventually returns a list of objects. I need the function to return the actual objects but I don't want to hit the database twice for every query since it's already an expensive query. How can i return a model instance without hitting the db? NB: I presume doing something like the following will actually...
I have 100 columns in a table, i want to list 99 columns except a particular one, how to exclude that columns name? ...
Is it a good practice to create an index to every foreing key in my database? ...
Hi , I have a job say somthing like X. If i want to give permissions to execute the X job to a user . what role I have to assign to the user. But the user should not be able to edit or delete or modify the job and this job is in share environment. no other should not prevlge to run the job X. ...
I recently read somewhere that one of ways of tuning sql query is - If it has too many joins then do one join with fewer tables and cache the results in a temporary table. Then do the rest of the query joining on that table. My question - How it will improve the performance, as you are joining same numbers of tables. (Only not together)...
Dear All, I have a stored procedure in SQL Server for generating transaction numbers. Can anyone help me with how to call the Stored Procedure from VB.NET and how will i get the value that is returned from the procedure into the front end. Regards, George ...
How do I select all rows for a table, their isn't part of any transaction that hasn't committed yet? Example: Let's say, Table T has 10 rows. User A is doing a transaction with some queries: INSERT INTO T (...) SELECT ... FROM T // doing other queries Now, here comes the tricky part: What if User B, in the time between User A in...
hi , i want to select top 500 records later records (i.e from 501 records) to till another 100 records. ...
I have a stored procedure (see below) which inserts data into a physical table and then joins information with sys.databases. I was thinking that would it be better to not have a physical table for data insertion? Would it be better to fetch these results into a table variable within this procedure? If so, how to do that? CREATE PROCEDU...
I am creating tables from java on the fly as they are needed. Now to define constraints and indexes on these tables we need to come up with unique names. I am planning to query the system views to get the constraint and index system views to get a list of all existing names and then come up with a random name and check against these val...
Hi everyone. I'm using SQL Server (2005). Today i have some tables that everytime the data inside them changes i get DB trigger (implemented via the sql server trigger mechanism) and handles it. the problem is that the trigger mechanism has bad performance and handling with it is not exaclly afun thing to do. now to my question. is anyb...
I have a master detail relationship between a person and its friends: person id name -- ------ 1 Jones 2 Smith 3 Norris friends id personId friendName -- -------- ---------- 1 1 Alice 2 1 Bob 3 1 Charly 4 1 Deirdre 5 2 Elenor A person can have as many friends as he wants. I want to create...
I have the following user-defined function: create function [dbo].[FullNameLastFirst] ( @IsPerson bit, @LastName nvarchar(100), @FirstName nvarchar(100) ) returns nvarchar(201) as begin declare @Result nvarchar(201) set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName el...
Hello, in our company's product, we attach a database to SQL Server, and we need to add credentials to some accounts to the catalog. Currently we're using SMO to do this, but this gives us problems (x86 vs x64, SQL Server 2005 vs 2008, etc) Is there's anyway of adding a login (to the server) and a user (to the catalog) using SQL? This...
I have a mysql table and data entries in it: CREATE TABLE `invoices`.`invoices` ( `InvoiceID` bigint(20) unsigned NOT NULL auto_increment, `UserID` int(10) unsigned NOT NULL, `Value` decimal(10,3) NOT NULL, `Description` varchar(4048) NOT NULL, `DateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURR...