|
Looks like a bug in SQL Server. Apply the latest SP's and/or Service packs. If you still keep getting this, you may want to contact microsoft support.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
thanks
i have overcomed this by creating a Temp table.It is a Bug in SQL Server, i will take your advice.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
H,
I have a stored procedure in SQL server 2005 in a database.Right now i am unable to access my SQL Server and it needs reinstallation.But i need the stored procedures i have written previously in this.I know the name of teh database that contains the SP.I can see it in my programmeFiles as mdf and ldf.
Once the reinstallation of my new sql server 2005 is done,i want my new sql server to have this SP.I tried to open my mdf and ldf but its not opening.I tried to copy to some location but it says "another program is using this file"-This is not true.
So my question is how can i do this.I desperatly need ny SQL server after reinstallation to have these SP's since writting them again will be tedious.
Many Thanks
|
|
|
|
|
From the message - "another program is using this file" - it's clear that your DB is still attached to the SQL Server. To move it you need to detach it. But for that you need to connect to the server.
When you connect to your 'new' installation, can't you see the 'old' database in object explorer? You should actually have access to the complete database and not need to recreate anything...
|
|
|
|
|
What do you mean when you say you're unable to access SQL Server? Lost your password?, or is it some other error you're getting. Are you the owner of the montioned database?
You have two options here
1) Try solving your existing problem and backup the database.
2) Stop the SQL Server Service, copy the mdf and ldf to a different location and do your reinstall. Once you have the new instance up, you can try attaching the mdf, although I seriously doubt if it'll succeed. It's a gamble you've to take.
location but it says "another program is using this file"-This is not true
This actually might mean that your SQL Server instance is still running. Try figuring out what's wrong with your login or post the error you get here.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
2) Stop the SQL Server Service, copy the mdf and ldf to a different location and do your reinstall. Once you have the new instance up, you can try attaching the mdf, although I seriously doubt if it'll succeed. It's a gamble you've to take.
I did this and am going to reinstall my SQL server 2005.I hope while attaching it will work.Any way at this point i think we have to take that risk since we dont have any other options.
I will try this and will post the effects.Please be in this loop.
Many Thanks
|
|
|
|
|
I am trying to come up with a query to determine the structure of a table. So far I have come up with the following query:
SELECT SC.name, SC.length FROM syscolumns SC
inner join sysobjects SO on SO.id = SC.id
where SO.name = 'Customers'
The problem is that the "length" column is twice the size it should be.
Is there a flaw in my query or do I just have to divide the "length"
by two?
Thanks
James Johnson
|
|
|
|
|
Assuming you are using sql server 2000 or later try this:
select * from information_schema.columns<br />
where table_name = 'Customers'
It gives you all the information about the table structure.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What I ended up doing is creating a command to execute the following
Select top 1 * from Customers
Then
DataReader dr = cmd.ExecuteReader();
DataTable dt = dr.GetSchemaTable();
The resulting DataTable contains all of the information I need.
Thanks
James Johnson
|
|
|
|
|
Thats one way 
Even better would be
Select * from Customers where 1 = 2
Which will give you an empty table and use less resource on the server
Bob
Ashfield Consultants Ltd
|
|
|
|
|
This data is in SQL 2000 and the Field Name is raw_payload I want to write a query that seperates this field by the '+' as a delimiter into 4 other fields a1 a2 a3 a4 or whatever in the same database. Any help would be awsome.
raw_payload
S9000+
UV+100.0000+0.0000
UV+101.0000+101.0000
UV+102.0000+64.0000
UV+103.0000+0.0000
UV+104.0000+
UV+105.0000+0.0000
UV+106.0000+3.0000
UV+107.0000+0.0000
UV+108.0000+0.0000
UV+109.0000+0.0000
UV+110.0000+0.0000
UV+111.0000+0.0000
UV+112.0000+1494.0000
UV+113.0000+38.6623
UV+114.0000+66.0000
UV+115.0000+8.1340
UV+116.0000+0.0000
UV+117.0000+0.0000
UV+118.0000+0.0000
UV+119.0000+0.0000
UV+120.0000+0.0000
UV+121.0000+0.0000
UV+122.0000+0.0000
UV+123.0000+0.0000
UV+124.0000+0.0000
UV+125.0000+0.0000
UV+126.0000+0.0000
UV+127.0000+0.0000
UV+128.0000+0.0000
UV+129.0000+0.0000
UV+130.0000+0.0000
UV+131.0000+0.0000
UV+132.0000+0.0000
UV+133.0000+0.0000
UV+134.0000+0.0000
UV+135.0000+0.0000
UV+136.0000+0.0000
UV+137.0000+0.0000
UV+138.0000+0.0000
|
|
|
|
|
This is best handled outside of TSQL since arrays are not a native element in SQL. Therefore, nothing to split the string into.
You can cludge a query using CHARINDEX and SUBSTRING. You will need to add some CASE statements to deal with any missformed data.
|
|
|
|
|
Right now I am exporting from SQL 2000, importing this into Access 2003 exporting out to Excel 2003 parsing the data with macros and re importing to Access to run my reports wanted to reduce all the importing and exporting if possible to speed up the process....
|
|
|
|
|
It can be done - it will be quicker. The code will be look clumsy.
|
|
|
|
|
try this, I guess it will help you.
<br />
select *,left(raw_payload,charindex('+',raw_payload,1)-1),<br />
left(substring(raw_payload, 1-len(left(raw_payload,charindex('+',raw_payload,1)+1)), <br />
len(raw_payload)),charindex('+',raw_payload,1)* charindex('+',raw_payload,1)-1 ),substring(right(raw_payload,charindex('+',raw_payload,1)*3),charindex('+',right(raw_payload,charindex('+',raw_payload,1)*3),1)+1,len(raw_payload))<br />
from table2
I Love T-SQL
modified on Monday, April 21, 2008 5:52 PM
|
|
|
|
|
The left works all the way down the right starts to include the middle and the + sign delimiter,
The data as it goes down increases the characters and some times it has bad data or junk I would like it to delete if it does not match the standard. field one descrption=text field two usage=number and field three adjustables=numbers
Below are the want it and the way it is...
THE WAY I WANT IT
DESCRIPTION USAGE ADJUSTABLES
UV 195 0
UV 196 2
UV 197 0
UV 198 0
UVN 500 1
UVN 501 1
UVN 502 0
VN 509 -571
UVN 510 -377
UVN 511 0
UVN 512 -575
OTLWV 513 -296
OTLWV 514 -35
OTD/ROV 515 112.4570
OTD/ROV 516 12.7000
THE WAY IT IS IN SQL
"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 133.0000+ 0.0000"
"UV+ 134.0000+ 0.0000"
"UV+ 135.0000+ 0.0000"
"UV+ 136.0000+ 0.0000"
"UV+ 137.0000+ 0.0000"
"UV+ 138.0000+ 0.0000"
"UV+ 139.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 526.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
"OTD/ROV+2405.0000+ 0.0000"
"OTD/ROV+2406.0000+ 0.0000"
"OTD/ROV+2407.0000+ 0.0000"
"OTD/ROV+2408.0000+ 0.0000"
"OTD/ROV+2409.0000+ 0.0000"
"OTD/ROV+2410.0000+ 0.0000"
"OTD/ROV+2411.0000+ 0.0000"
"OTD/ROV+2412.0000+ 0.0000"
"OTD/ROV+2413.0000+ 0.0000"
"OTD/ROV+2414.0000+ 0.0000"
"OTD/ROV+2415.0000+ 0.0000"
"OTD/ROV+2416.0000+ 0.0000"
"OTD/ROV+2417.0000+ 12.7000"
"OTD/ROV+2418.0000+ 0.0000"
"OTD/ROV+2419.0000+ 0.0000"
"OTD/ROV+2420.0000+ 0.0000"
"OTD/ROV+2421.0000+ 17.0000"
"OTD/ROV+2422.0000+ 17.0000"
"OTD/ROV+2423.0000+ 17.0000"
"OTD/ROV+2424.0000+ 17.0000"
"OTD/ROV+2425.0000+ 0.0000"
"OTD/ROV+2426.0000+ 0.0000"
OTD/ROV+2427.0000+ 0.
"OTD/ROV+2428.0000+ 0.0000"
"OTD/ROV+2429.0000+ 0.0000"
"OTD/ROV+2430.0000+ 0.0000"
"OTD/ROV+2431.0000+ 0.0000"
"OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2433.0000+ 0.0000"
"OTD/ROV+2434.0000+ 0.0000"
"OTD/ROV+2435.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2437.0000+ 0.0000"
"OTD/ROV+2438.0000+ 0.0000"
"OTD/ROV+2439.0000+ 0.0000"
"OTD/ROV+2440.0000+ 0.0000"
"OTD/ROV+2441.0000+ 0.0000"
"OTD/ROV+2442.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5322.0000+- 112.4570"
"G59WO+5323.0000+- 619.6670"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5604.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"
|
|
|
|
|
I have written the query(Update,View,Delete) in three Stored Procedure such as SP_GetOfficeHourToday,SP_UpdateOfficeOpenCloseHours,SP_DeleteOfficeHour
How to write the multiple query( View,Update,Delete) in single store procedure using SqlServer2005.
USE [Myrefer]
GO
/****** Object: Table [dbo].[Office_Hours] Script Date: 04/21/2008 17:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Office_Hours](
[DayOfTheWeek] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OfficeOpenHours] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OfficeCloseHours] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
VIEW
*******
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GetOfficeHourToday]
@today varchar(10)
as
begin
select DayOfTheWeek,OfficeOpenHours,OfficeCloseHours from Office_Hours where DayOfTheWeek=@today
end
UPDATE
********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_UpdateOfficeOpenCloseHours]
@DayOfTheWeek varchar(10),
@OfficeOpenHours char(4),
@OfficeCloseHours char(4)
as
begin
update Office_Hours set OfficeOpenHours=@OfficeOpenHours,OfficeCloseHours=@OfficeCloseHours where DayOfTheWeek=@DayOfTheWeek
end
DELETE
*******
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_DeleteOfficeHour]
@today varchar(10)
as
begin
Delete from Office_Hours
end
modified on Monday, April 21, 2008 7:52 AM
|
|
|
|
|
bruze wrote: How to write the multiple query(like View,Insert,Update,Delete) in single store procedure using SqlServer2005.
I'm not sure how to answer this because you just write multiple queries in the stored procedure. If it makes you feel better you can put a semi-colon between each query.
|
|
|
|
|
You can do something like:
Create Proc up_Maintain (@mode char(1))<br />
if @mode = 'a'<br />
begin<br />
....code to add<br />
return<br />
end<br />
if @code = 'd'<br />
begin<br />
....code to delete<br />
return<br />
end<br />
begin<br />
....code to update<br />
return<br />
end
However this is bad practice as the optimiser will optimise for the first time the proc is run. An accepatable alternative is to have a proc like this, but keep the add/delete/update procs and just call thme from the controlling proc, but you gain nothing.
As far as I can see there is nothing to be gained from merging the procs together, but there are potential performance issues, so they should be kept independantly.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank You For Your Response.
|
|
|
|
|
We can use this procedure in c# application?
|
|
|
|
|
I have return the code in c# application using individual store procedue.
How to write the application code using Multiple query with single stored procedure?
public DataSet GetDayHours(string strSingleDay)
{
//Get the Day,OpenHours and CloseHours from Database
string GetConnection = Connection.GetConnection();
SqlConnection strConnectionString = new SqlConnection();
strConnectionString.ConnectionString = GetConnection;
strConnectionString.Open();
SqlCommand cmdsearch = new SqlCommand("SP_SelectOfficeOpenCloseHours", strConnectionString);
cmdsearch.CommandType = CommandType.StoredProcedure;
cmdsearch.Parameters.Add(new SqlParameter("@DayOfTheWeek", strSingleDay));
SqlDataAdapter daAdapter = new SqlDataAdapter(cmdsearch);
daAdapter.Fill(dsGetDayHours);
}
|
|
|
|
|
So you don't want to have multple actions in a single stored prcoedure? Please clarify what you do want to achieve.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi
how to get list of all tables that exist in my sql server instance ?
i'm using this scrpit :
select * from sys.tables
but this command return only tables that exist in Current Database.
thanks
|
|
|
|
|
Check my reply to you on C# forum.
I Love T-SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
