|
Hi
friends
I want to select two date from sys.
1st date is 1-April-2007
where Day 1st= is fixed
Month April is also fixed
But Year 2007 is the current year-1
and the 2nd date is 31-Dec-2007
where day 31st/30th/28th/29th is the last day of the previous month.means current month -1
and Year 2007=Current year-1 for the month of January and for the other months its fixed that is 2008 menas
the current year.
well i am using SQL SERVER 2005.pLZ help me out.
|
|
|
|
|
I think this will do it - it looks a bit horrendous and I'm sure there must be an easier way 
declare @start datetime, @end datetime, @month varchar(10)<br />
<br />
set @month = 'feb'<br />
<br />
select @start = convert(datetime, '01-Apr-' + convert(varchar,datepart(year,dateadd(year,-1,getdate()))))<br />
select @start<br />
<br />
select @end = convert(datetime,dateadd(day,-1,dateadd(year,1,convert(datetime, '01-' + @month + convert(varchar,datepart(year,dateadd(year,-1,getdate())))))))<br />
select @end
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If we delete or update a row in a table; the no of changed row shows as n rows affect.
Here is the example.
Ex: update TableName set Column1='N' where Column2=2
Output: 4 rows affected
In this i need to get the value 4. How to fetch that value.
Can anyone help me out 
Regards,
LEE
|
|
|
|
|
In SQL Server you can get this value by using the global variable @@rowcount. The variable contains data about the number of rows affected by a sql statement and is updated each time you run a statement.
|
|
|
|
|
Hi i have a doubt,.
I have a view with two colums from table1, two columns from table2,
if i changed the data in view then updation occurs in both tables are not?
Another doubt..
If i combine two tables data in to one view...
how much space it takes.. suppose table1 takes 2kb, table2 takes 4kb
then view takes 6b or less ?
G. Satish
|
|
|
|
|
Satish - Developer wrote: if i changed the data in view then updation occurs in both tables are not?
Nope. To do that, you need to have an 'instead of' trigger on your view.
Satish - Developer wrote: If i combine two tables data in to one view...
how much space it takes
Theoretically, a view does not occupy storage space, except for the view definition stored in one of the system tables. When you query your view, it'll run the select query present in it's definition and return the result set.
SG
Cause is effect concealed. Effect is cause revealed.
modified on Thursday, January 31, 2008 7:44:10 AM
|
|
|
|
|
hi everybody
i am using sql 2000 server and facing syntax error.
plz correct and reply.
query is below but that is script of sql 2005 and i am using this on sql 2000
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductCategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductCategory](
[ProductCategoryID] [int] IDENTITY(1,1) NOT NULL,
[ProductCategoryName] [text] NOT NULL,
CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED
(
[ProductCategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
rajeev kumar sharma
vri software pvt. ltd.
new delhi India
modified on Thursday, January 31, 2008 2:06:42 AM
|
|
|
|
|
RajeevKumarSharma wrote: i am using sql 2000 server and facing syntax error.
What is the error? Did you check what the error message means before posting?
If you can't be bothered to do a little investigation yourself, I can't be bothered to help you 
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i tryed to solve that but i am unable to solve that
problem is
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.
Rajeev Kr. Sharma
VRI Software Pvt.Ltd.
New Delhi India
HumOnline.com
Stay Connected
|
|
|
|
|
Hi Friends,
I was running following command in SQL Server 2005.But,it was not worked. The command is,
"ALTER DATABASE [mydatabase name]
SET AUTO_SHRINK ON WITH NO_WAIT;"
Is this correct , i was using in sql server? Please suggest me. The following syntax using for auto shrink database log.
ALTER DATABASE database
SET optionspec [ ,...n ] [ WITH termination ] [;]
optionspec:
AUTO_SHRINK {ON | OFF}
Termination:
ROLLBACK AFTER integer [SECONDS]
ROLLBACK IMMEDIATE
NO_WAIT
My problem is, i have shrink my database transaction log every time by manually. So, i was using above auto shrink command.
Thanks in advance,
Rameshkumar Thirumalaisamy
modified on Thursday, January 31, 2008 7:31:20 AM
|
|
|
|
|
Does anyone know hot to import data from a spreadsheet into sql server table.
Many thanks
|
|
|
|
|
solarthur01 wrote: Does anyone know hot to import data from a spreadsheet into sql server table.
Yes.
Did you try searching for a solution yourself before you posted? A quick search on Google will give you plenty of information.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
Hello
Does anyone know how to get child rows of a row as a string?
I mean, lets say that i have 3 tables:
Students, Courses, and Student_Courses to track the courses that each student is taking
Each student can take many courses at the same time
What i want to retrive is:
Column 1 Column2
Alex 'Course1, Course2, Course3, Course6'
Alexei 'Course3, Course6'
Alexa 'Course2, Course3, Course6'
I dont want to use cursors and i would prefer to do this in SQL server
Im only wondering if SQL has a intruction to acomplish this or if anyone knows an easy way to do this
Thanks for your time
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: Does anyone know how to get child rows of a row as a string?
Check out this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Try this:
<br />
create table student (StudentID int, Name varchar(50))<br />
create table student_course (StudentID int, CourseID int)<br />
create table course (CourseID int, Name varchar(50))<br />
<br />
insert into student select 1,'Fred'<br />
insert into student select 2,'Joe'<br />
insert into student select 3,'Mary'<br />
<br />
insert into course select 1,'Maths'<br />
insert into course select 2,'Cooking'<br />
insert into course select 3,'Languages'<br />
<br />
insert into student_course select 1,1<br />
insert into student_course select 1,3<br />
insert into student_course select 2,1<br />
insert into student_course select 3,1<br />
insert into student_course select 3,2<br />
insert into student_course select 3,3<br />
<br />
go<br />
<br />
<br />
CREATE FUNCTION dbo.udf_GetStudentCourses(@StudentID int)<br />
RETURNS VARCHAR(1000) AS<br />
<br />
BEGIN<br />
DECLARE @CourseList varchar(1000)<br />
<br />
SELECT @CourseList = COALESCE(@CourseList + ', ', '') + c.Name<br />
FROM Student_Course s, Course c<br />
WHERE s.StudentID = @StudentID<br />
and c.CourseID = s.CourseID<br />
<br />
RETURN @CourseList<br />
END<br />
go<br />
<br />
<br />
select Name,dbo.udf_GetStudentCourses(StudentID)<br />
from Student
Results:
Fred Maths, Languages
Joe Maths
Mary Maths, Cooking, Languages
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks both for your answer
Ill take the function approach suggested by Ashfield, it works great
Thanks again
Alexei Rodriguez
|
|
|
|
|
SITUATION:
An external process (web server) writes one row into a table (WipTable) in my SQL 2005 server, and it takes too much time for my 'INSERT' trigger to execute in once, since the data process and checking is big and time consuming.
This 'INSERT' trigger to this 'WipTable' need to end quickly, but, the trigger takes too much time to finish the transaction. The only step required at this stage is updating a 'confirmation status' field into the table.
My goal is simple:  avoiding the big delay before COMMITTING the first stage of the transaction, and instead split the task in 2 parts (like an Interrupt Service Routine would react):
1) first, do the simple things that place some data (to put a 'CONFIRMATION' status) and write one 'row task' into another table (RequestTable). Then, the RequestTable's TRIGGER would do the rest of the work in ANOTHER transaction.
2) The second table that would then be triggered would take more time to execute but
QUESTION:
How to 'force' the real and effective completion of the transaction, so that another (longer) transaction could begin just after it (being in fact 'triggered' by the first transaction because of an INSERT into a second table possessing an 'INSERT' trigger) ? 
|
|
|
|
|
I'm pretty sure a trigger won't accomplish that.
You could deposit your data in a table and another process (a Windows Service perhaps) could process it into the other tables.
|
|
|
|
|
Thanks for trying to help me.
I finally resorted the problem in using a scheduled job to do the "very" long part.
In summary:
-----------
Part 1) The trigger validates and gets the basics infos for the part 2 as a future work, and places all required entries into a 'REQUEST' table, and QUIT quickly.
Part 2) When the job is activated (could be 1-5 minutes later, for now), the rest of the long searches and inserts are done. Also, the 'request' entries are deletes after work completion.
In the future, I may consider creating a job 'on-the-fly' just before quitting the trigger, we'll see...
Thanks again.
(by the way, it is my first post into this forum, I'm glad to see that this community is really active, it encourages me to contribute more eventually...)
|
|
|
|
|
Hello all
Im trying to figure out an easy way to retrieve records using a simple query like:
SELECT * FROM Products WHERE ProductID IN (3,5,7,9)
This works, but what i need is to store the numbers (3,5,7,9) or any other combination in another table field and execute the select statement with something like the following:
SELECT * FROM Products WHERE ProductID IN (SELECT NumbersField FROM MyTable WHERE Field=@MyParam)
This gives me a syntax error:
Syntax error converting the varchar value '3,5,7,9' to a column of data type int.
Ill appeciate any help
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
To get this to work, you would need to construct a dynamic SQL statement in a (n)varchar variable and execute it using the EXECUTE command.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks for your help, this will save a lot of work 
This is how it looks:
<br />
declare @string varchar(1000)<br />
select @string=movieids from test where X = 1<br />
EXECUTE ('SELECT * FROM movies WHERE movie_id IN ('+ @string +')')<br />
I wonder what the difference is between EXE and EXECUTE??
Both of them work
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: I wonder what the difference is between EXE and EXECUTE??
There's no command called EXE. I assume you mean EXEC. If you had read the documentation, you would know that EXEC is the shorthand syntax for EXECUTE.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am using the Add Connection wizard in VS 2005.
Data Source: Oracle Database (OLE DB)
Database, username, and password are correct. Click Test Connection.
Login Timeout expired.
An error has occured while establishing a connection to the server. While connecting to SQL Server 2005  , this failure may be caused by the fact that under the settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server (53).
"Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit..."
"There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain..."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
