|
from : http://p2p.wrox.com/topic.asp?TOPIC_ID=24417
Hi,
Yes, you can use sp_rename for renaming a table or column in SQL Server. The only difference is that if you want to rename a column witbin a table you need to provide proper namespace.
for e.g.
for renaming a table
EXEC sp_rename 'Old_TableName', 'New_TableName'
for renaming a column
EXEC sp_rename 'TableName.[Old_ColumnName]', 'TableName.[New_ColumnName]', 'COLUMN'
Hope this helps,
Lalit,
Life Means More...
|
|
|
|
|
Hi all,
I am not fully aware of DTS, I have created a DTS package its executing from the .Net code also, but I have been struck up with 2-things. They are
1. Here I am using a .csv file as source file, this file has to be given at runtime as user selects it.
2. In the destination table, the DTS has to check whether the same recipt number exitsting, if it exists it should fail running of the DTS. And error message must be passed to a table.
Can anybody please advice me how to do it. I would be thankfull for them pls.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Hi all,
I have created a DTS which is running fine, now I would like to give its source file at runtime, how can I do it. Pls. help me its urgent. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Is there any restriction on the name of the stored procedure. I mean on the length of the sp.What is the max length that a stored procedure can have.
Mohinder Singh
|
|
|
|
|
From SQL Server Books On Line:
the NAME of a stored procedure cannot exceed 128 characters.
The size of a Transact SQL Stored procedure cannot exceed 128 MB.
|
|
|
|
|
what is different between SqlDataAdapter and SqlDataReader?.these 2 r different from dataadapter and datareader.
|
|
|
|
|
No these two are same.Dataadapter is used when u want to do insert or update or delete. And if u want the records for reading purpose then it is better to use datareader. Datareader is fast in this case. Datareader directly interacts with the Database. Dataadapter works with the dataset. And dataadapter interacts with the database. and fills the records in the dataset.Dataadapter is used only when you use dataset.And dataaset uses disconnected architecture and datareader does not.This reason makes dataset more useful.
Mohinder Singh
|
|
|
|
|
SqlDataAdapter is a version of the DataAdapter that is specific for use with Sql Server. For other databases an OleDbDataAdapter or OdbcDataAtapter would be appropriate. Both of these classes inherit from the DataAdapter class, which providesthe abstract model for both. Similarly, SqlDataReader is an implementation of (inherits from) DataReader, one which is specifically for use with SQL Server databases.
DataAdatpters are disigned to serve as the bridge between a database and the in memory representastion classes DataSet and DataTable. The provide mechanisms for populating Datasets with the data, metadata and relationships form a database, and allow one to work with this in memory data while disconnected from the database. Mechanisms fro reconnecting and updating the database are also provided.
DataReaders are designed for rapid reading of data from a database. The data is provided as a set of records which must be read in order and can only be read once without closing and restarting the datareader.
All of this information (in much more detail) is available from MSDN[^]
|
|
|
|
|
Hi,
ADO.Net implements disconnected architecture with sqlDataAdapter and Dataset,SqlDataAdapter is disconnected from datasorce it establishes connection when required once data processing is finished it disconnect from DB and data is dumped into Dataset.
SqlDataAdappter is used for all DML command by defaults it is selectcommand mode.
SqlDataReader is connection oriented it reads the records on at a time mean while it reads all records. we can use DataReader only for SelectCommand. we cann't instantiate DataReader with the help of executeReader we can
SqldataReader used mainly for populate data to the controls, when we need to gothough all the records in data
I hope this will clear u r doubt for some extend
"Winning is not our dream,It our habit"
|
|
|
|
|
This is a question about the best way to construct a query.
Each person is associated with a team, and each team is associated with an office. This is represented in three tables:
a table of users (tblUsers) that have an id (userID) and a team (userTeamID)
a table of teams (tblTeams) that have an id (teamID) and an office (teamOfficeID)
a table of offices (tblOffices) that have an id (officeID).
How can I (with a single query) retrieve a list of all of the people (in tblUsers) who are in a particular office (officeID)?
Initially I tried this (in this example, I'm finding all the people in the office with officeID of XYZ):
SELECT userID
FROM tblUsers,tblTeams,tblOffices
WHERE
officeID='XYZ'
teamOfficeID=officeID
userTeamID=teamID
But that doesn't seem to be working for me.
Any thoughts?
---Greg
|
|
|
|
|
Isn't it :
SELECT userID FROM tblUsers,tblTeams,tblOffices WHERE officeID='XYZ' AND teamOfficeID=officeID AND userTeamID=teamID
?
|
|
|
|
|
Hi,
Many ways to write the query :
1- using "IN" :
select userid from tblusers where userTeamId in (select teamId from tblTeams where teamOfficeId in (select officeID from tblOffices where officeId= 'XYZ'))
2- Using Join (more recommended) :
SELECT tblUsers.userID
FROM tblUsers INNER JOIN
tblTeams ON tblUsers.userTeamId = tblTeams.teamId INNER JOIN
tblOffices ON tblTeams.teamOfficeId = tblOffices.officeId
where tblOffices.OfficeId = 'XYZ'
HTH.
Hayder Marzouk
|
|
|
|
|
Always use ANSCII standards for writing queries this means even joins, instead of where clause condition first join the table and put where clause for specific requirement.
Regards,
Jaiprakash M Bankolli
jaiprakash.bankolli@gmail.com
|
|
|
|
|
Hi, my database takes data from the user, but doesn't save it. i have made a search and found that there is a property in the database.mdf file called "copy to output directory" should be set from "copy always" to "do not copy", but this resulted in an error saying "specified file can't be opened or a databse with the same name exists".
So can anybody help me in that...
|
|
|
|
|
nadinekamal wrote: there is a property in the database.mdf file called "copy to output directory" should be set from "copy always" to "do not copy", but this resulted in an error saying "specified file can't be opened or a databse with the same name exists".
That sounds like a visual studio property for a file in a project. If you have the database being copied each time you build the project the database in the output directory will be overwritten each time, so you reset the database back to what is stored in the project. When you specify "do not copy" the database isn't copied at all from the project to the output directory so the application cannot find it.
|
|
|
|
|
So what the problem could be?? how can i make the database updated?
|
|
|
|
|
nadinekamal wrote: So what the problem could be??
I told you what I suspect the problem to be: You are constantly copying over the database that you make the updates to with a previous version.
As to how to proceed... That depends on what you want to happen. That is something you have not explained.
|
|
|
|
|
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted.
During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again.
When I investigated further (using ‘sp_spaceused’) I found the following:
07.80 Gb Data
01.70 Gb Index
27.10 Gb Unused
36.60 Gb Reserved
01.00 Gb Unallocated
38.60 Gb Database Size
It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database).
At this point I'm stymied. Can anyone help me?
Ian Dennis
Business Analyst, Assets
HANSEN
|
|
|
|
|
What recovery model (simple, full or bulk-logged) is your database using?
|
|
|
|
|
I'm using a simple recovery model (I've just discovered!) but I need to state that it is the data file (mdf) that is growing out of control, not the log file (ldf).
|
|
|
|
|
1. The sp_spaceused procedure has an @updateusage parameter - try to run EXEC sp_spaceused @updateusage = N'TRUE' and check if the results will not change.
2. If you have time and some free disk space, make a full backup of the database and check its size - is it around 10GB?
|
|
|
|
|
I've used @updateusage = 'TRUE' and it doesn't make any difference.
I've totally killed the database, reloaded it, and started testing my program again. It's growing already ...
Reload database - DbSize:06,033, Unalloc ,515, Reserved:05,341, Data:3,786, Index:1,493, Unused ,061
Run conversion. - DbSize:18,558, Unalloc:3,511, Reserved:14,869, Data:5,049, Index:1,815, Unused:8,006
(sizes in Mb)
|
|
|
|
|
When you used DBCC SHRINKFILE, did you specify target_size parameter to force pages reorganization before releasing unused space?
From BOL[^]:
A. Shrinking a data file to a specified target size
The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.
USE UserDB;<br />
GO<br />
DBCC SHRINKFILE (DataFile1, 7);<br />
GO
|
|
|
|
|
I don't remember now. I've got some more info which I'll post in a new message in this same thread so, please view that. Thanks
|
|
|
|
|
I've been running a series of further tests. My first thought is that my problem was connected with deleting records, as that is something I'm doing that is, perhaps, unusual. For instance, I delete 25,000 sewer records out of a table and insert 25,000 new sewer records, then I delete 27,000 water records out of the same table and insert 27,000 new water records, etc.
But I'm checking the progress of my program with sp_spaceused @updateusage='true' and it shows the unused area of my database growing when I am inserting records only ... i.e., no deleting is taking place!!!
Basically, what I am seeing is the unallocated size diminishing at the same speed as the unused area grows, but when the unallocated size gets to zero, it just increases the datafile size (at which point, the unallocated space gets big again). It never releases any of the unused area, nor can I claw any of that back from any of the maintenance routines I've described above.
The only thing to add is the application is written in VB.Net. I've recently learnt that a colleague wrote another VB.Net conversion program which exhibited the same symptoms, even although his was operating on an Oracle database, not a SQL Server one. Does that make any lightbulbs go off in anyone's brain?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
