|
Hi,
I need to bring back records in a certain date range.
I need it to be from May 2007 to August 2007. I can pass through (2007 and 5) and (2007 and 8) but not sure how my WHERE will look like.
Please can some one help??
Regards
ma se
|
|
|
|
|
searching on datetime column for dates between May-2007 and Aug-2007
DECLARE @Mth1 varchar(5)
DECLARE @Yr1 varchar(5)
DECLARE @Mth2 varchar(5)
DECLARE @Yr2 varchar(5)
SET @Mth1 = '5'
SET @Yr1 = '2007'
SET @Mth2 = '8'
SET @Yr2 = '2007'
DtCol >= Convert(datetime, '01/' + @Mth1 + '/' + @yr1, 103)
AND DtCol < DateAdd(m, 1, Convert(datetime, '01/' + @Mth2 + '/' + @yr2, 103))
here the condition is starting from 1st of May-2007 to 31st of Aug-2007
i.e. deriving both dates initial date would be 1st of the month and ending date is less than 1st of required month + 1
Regards
KP
|
|
|
|
|
Dear All  ,
I am trying to parse a huge text files(size: 50kb to 1MB).
Currently I have developed a mechanism using regular expressions which
can group the values in each and every line, finally the values are
formatted into a XML string which is passed into a stored procedure
where the XML is loaded and again by using OPENXML statement I read
all the values and records are inserted into the tables. My problem
here is with the execution time of the Stored Procedure which is
taking nearly 45 secs to execute the XML string.
Row Count ranges from 100 to 2000.
Column count is 40.
Can anyone please suggest me an alternate method or the necessary step
which I can do with the front end and back end.
Thanks and Regards,
Valan.
|
|
|
|
|
|
I have a limitation in using the BULK INSERT or BCP,
It is a text file of unicode characters with txt extension.
Actual file information will start from
fifth line and it will end before the last second line.
Business logic has to be implemented like validating the
format of file and validating the values of the field,
due to these type of requirements I prefered to apply
regular expressions from front end and formatted values
are converted into XML strings.
I 'am using SQL Server 2000, and .NET 2.0
Can you please suggest me with better method to follow.
Thanks
Valan.
|
|
|
|
|
How about, instead of converting data to XML, changing the TXT file format (and performing all the necessary validation), so bcp/BULK INSERT can process it?
|
|
|
|
|
Mr. Chopeen,
Are you sure by using the BULK INSERT I will be able to reduce the
execution time, then I will try in all the possible ways to use
the BULK INSERT to complete my File Parsing.
Is that only the better and best way for inserting records into the
DataBase?
Please suggest me.
Thanks and Regards
Valan
|
|
|
|
|
A completely different idea just came to my mind - you are using some kind of .NET application to generate the XML file, aren't you? Can't you change it and generate INSERT statements? Then you will have to simply execute those INSERT-s.
|
|
|
|
|
Ok Fine thanks I will try to do with your suggestion.
But any Idea why it has to take a longer time for XML
execution.
Thanks and Regards
Valan
|
|
|
|
|
I don't have much experience with XML so I'm not sure I will be able to help, but if you post your stored procedure I'll take a look.
|
|
|
|
|
This is my mail ID tonic_valan@hotmail.com,
if you are willing I can mail my SP.
|
|
|
|
|
I have recently been using sql server 2005 and cannot find how you set the default directory to save your sql scripts. This was in the options and called "Query File Directory" in sql 2000.
Thanks, Jared
|
|
|
|
|
hi
i have developed some t-sql to backup/restore any database.
while restoring a database, if any session or connection be active on it i can not restore database and recieve exception.
How can i restore such databases?
How can i kill all sessions from database to solve my problem?
|
|
|
|
|
WDI wrote: How can i kill all sessions from database to solve my problem?
That would not be nice for your users. Imagine, you are working along and wham database session is killed and you lose work.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
I second that, those type of tasks are usually scheduled for when people are not working.
www.codewash.com
|
|
|
|
|
The only way I can think to end all the sessions would be by stopping the SQL service and then restarting it, your users might open their session again by the time you have started restoring though in which case it will still fail.
You could just run the command "NET STOP MSSQLSERVER" and then "NET START MSSQLSERVER" within your application.
There probably is a better way though but like the others said, it's probably not a good idea.
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
You can put the database into a mode where only a single connection is allowed at a time by using ALTER DATABASE db SET SINGLE_USER. Use the WITH ROLLBACK option to tell SQL Server when to rollback/abort any other existing connections. The connection you run ALTER DATABASE from remains connected.
You may also be able to restore the database if you take it OFFLINE.
|
|
|
|
|
I am trying to insert Data in SQL Database using C#, it's not giving any error but at the same time no rows are getting updated in my database.
private void Button1_Click(object sender, System.EventArgs e)
{
SqlConnection myConnection = new SqlConnection("server=sbs;database=bansal;Trusted_Connection=Yes");
SqlCommand myCommand = new SqlCommand("InsertCategory", myConnection);
myCommand.CommandText = "InsertCategory";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.NChar, 15));
myCommand.Parameters["@CategoryName"].Value = "Ambarish";
myCommand.Connection.Open();
try
{
myCommand.ExecuteNonQuery();
}
catch(SqlException e){}
myCommand.Connection.Close();
}
Following is the Stored Procedure:
CREATE PROCEDURE InsertCategory
(
@CategoryName nchar(15)
)
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
Table Name is Categories and has two Columns only:
1. CategoryName NChar 15
2. Identity Int 4 (Identity Seed is 1 starting from 1)
Sachin Jain
-- modified at 4:36 Saturday 4th August, 2007
|
|
|
|
|
You are adding a parameter to the command every single time you press the button. You now have several parameters - which one does the database use? It will use the first one with the correct name and ignore the others. You most likely have a parameters set up by the wizard that created your connection with the database.
This is the big problem with using these wizards - they hide what is really happening. Don't use them. They should never be used. Write your own database code, and write it in a DAL (Data Access Layer) class.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Dear Sir,
Thank you very much for your quick reply. I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
The problem that this code doesn't give any error, but at the same time data is not inserting in table.
Please help me in this regard.
Thanks,
Sachin Jain
|
|
|
|
|
sacheesach wrote: I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
Okay - it looked a bit like you were using wizard created code, but on looking again it doesn't seem to be the case anymore.
sacheesach wrote: The problem that this code doesn't give any error
It probably does but you are using the anti-pattern known at try-catch-ignore which is summed up by this line in your code:
catch(SqlException e){}
Comment out the try and catch statements to run the code inside on its own. Does the debugger point out an error?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Sir,
Thanks for your suggestions.
It was a silly mistake committed by my new programmer. In fact the Permissions (Select, Insert, Delete, Update etc.) on the Categories Table were unchecked and this was the reason the error was given.
Thank you again for your kind support.
Sachin Jain
|
|
|
|
|
Hi!
Hope every1 is doing fine. I am having an issue with database, i am using SQL Server 2000 and Asp.Net (VS.NET 2003) to develop my application. There is a scenario in my application where i need to compare rows of a table with each other and if they match then store its one copy in another table and delete both copies from first table.
Problem is that the table contains more than 50 fields (columns) and if i try to compare each column through query it will take hell of a time...i was wondering if there is any builtin funtion or some technique through which i can compare two rows without writing lengthy query.
Also i want to keep track of those fields of table which does not match and stotre their information in another table.
Any kind of help is appriciated...thanks in advance!
Regards,
Affan Ahmad Toor
..................
QUAIDIAN FOR ONCE, QUAIDIAN FOR EVER!
|
|
|
|
|
Affan Toor wrote: There is a scenario in my application where i need to compare rows of a table with each other and if they match then store its one copy in another table
How it should match ? All columns should match or only some columns. I presume that it should match all cloumns. Writing a single query that does all theses things seems impossible to me. You can do this using cursors.
Declare YourCursor Cursor For
Select Coulumn1,Column2,Column3..... from your table
Open YourCursor
FETCH NEXT FROM YourCursor into @Columnvar1,@Columnvar2...
WHILE @@FETCH_STATUS <> -1
BEGIN
--Select rows from table and insert to new table
Insert into newtable
Select columns fom table where column1=@Columnvar1, column2 = @Columnvar1
FETCH NEXT FROM YourCursor into @Columnvar1,@Columnvar2...
END
Close YourCursor
deallocate YourCursor
select * from newtable
This may insert duplicate rows into newtable. You need to refine the code to make it work for you.
|
|
|
|
|
HI!
thanks for reply, i will tryn this query and return to you if further help is required.
Regards,
Affan Ahmad Toor
..................
QUAIDIAN FOR ONCE, QUAIDIAN FOR EVER!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
