|
Hi,
I am using SQL Server 2005. When I create an index on a foreign key there is an option to check the "Use Page Locks when Accessing the Index" option. I do not know when to check this and when not to?
Can someone please help clear this for me? If checked will it reduce performance, or is it standard procedure to always have it checked?
Thanks
Brendan
|
|
|
|
|
i want to spilit the date on format dd/mm/yyyy and insert the substring as primary key field on another table
i tried the following but it failed
declare @exitDate smalldatetime
set @exitdate=(select Exit_date from invoice_details)
select substring( cast (@exitDate As varchar) As exitDateText,5,4)
from invoice_details
help please
regards
|
|
|
|
|
I'm guessing, because you didn't explain (you just supplied some broken code and said it didn't work) that you want to extract the year. Is that correct?
If so, why not just use the YEAR(@someDate) function? You can then cast that into a varchar.
|
|
|
|
|
Hi,
I'm using SQL Server 2000 with VB.NET 2005.
I'm trying to access the database which is in remote machine thru internet.
Can you help me with the required details & connection string...
Thanks & Regards.
Hariram
|
|
|
|
|
The connection string will be as normal, but with the IP address or full domain name as the server name.
|
|
|
|
|
Thanks Colin,
The IP Address should be a STATIC IP right...?
Regards,
Hariram
|
|
|
|
|
The IP address of the SQL Server must be static
|
|
|
|
|
hi,
This is code i am using I've picked it from some where and I've changed it according to my requirements but its not inserting a new record. it gives error like attempt failed or record set is read only. I don't know i am using dynaset as an opening mode but still i get the error. kindly any one knows where i am going wrong.
CDatabase *mydb=new CDatabase;
ASSERT(mydb);
mydb->OpenEx("DSN=ff");
if(!mydb->IsOpen())
{
MessageBox("Unable to connect to Database. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
return;
}
CRecordset*myrec=new CRecordset(mydb);
ASSERT(myrec);
myrec->Open(CRecordset::dynaset,_T("SELECT s,p FROM e"),CRecordset::noDirtyFieldCheck);
if(!myrec->CanUpdate())
{
MessageBox("Database reports unable to update.", "Database Error", MB_ICONEXCLAMATION);
myrec->Close();
return;
}
if(!set.Open(CRecordset::dynaset,"SELECT * From e",CRecordset::noDirtyFieldCheck))
{
MessageBox("Unable to open database record. (SELECT * e)", "Database Error", MB_ICONEXCLAMATION);
set.Close();
return;
}
/*if (!set.CanAppend())
{
MessageBox("Database does not support adding records. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
set.Close();
}*/
set.MoveFirst();
while( !set.IsEOF() )
{
set.MoveNext();
}
int lRecCnt = set.GetRecordCount();
int newID=lRecCnt+2;
m_g=lRecCnt;
UpdateData(FALSE);
// Prepare for a new record addition.
try
{
set.MoveLast();
set.AddNew();
set.m_s = "Becky Dugan";
set.m_p = "Becky Dugan";
set.Requery();
set.SetFieldDirty(NULL);
set.Update();
}
catch(CException* pE)
{
char buf[256];
pE->GetErrorMessage(buf, 256, NULL);
AfxMessageBox(buf);
pE->Delete();
}
// Close Recordset
set.Close();
// Close Database
mydb->Close();
|
|
|
|
|
I have a similar problem, but I think you should not use Requery.
I tryied it in my code and it wiped out the data added to record created by AddNew.
Vaclav
|
|
|
|
|
hey thr...its not working even if i eliminate the Requery statment...anything else ????
|
|
|
|
|
hi friends
what is this error?
i have not sqlserver 2005, i just install sqlserver 2000 but in the error text you see a text about failding to connect to sqlserver 2005?????
what happen for my server or program?
is there anybody to tell me what happen?
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
Hello,
When i run the report through my application, at that time system perfomance get down(memory usgae get down).
Please help me to sort out this issue.
-Bhushan
Bhushan Kukade
|
|
|
|
|
I am writing this query
select r.csno,CM.course_name,CTM.cat_name+','+r.sex+','+
(case when r.subcat_code='02'
then SCTM.subcat_name + '-' + c.ESM_Priority else SCTM.subcat_name end) as category,
max(r.rank) as maxrank
from registration r,
institute_master IM,course_master CM,category_master CTM,subcategory_master SCTM ,
candidate c
where r.inst_code!='000' and im.inst_code=r.inst_code
and c.roll_no=r.roll_no and c.subcat_code=r.subcat_code
and cm.course_code=r.course_code and ctm.cat_code=r.cat_code and
sctm.subcat_code=r.subcat_code
--and r.csno=@csno and r.inst_code=@inst_code
group by r.csno,r.inst_code,r.course_code,r.cat_code,r.subcat_code,r.sex,
CM.course_name,CTM.cat_name,SCTM.subcat_name,c.ESM_Priority
order by r.course_code,r.cat_code,r.subcat_code,r.sex
and the result of this query is
something like this
01 Computer Science & Engg. AIC,F,Others 33039.0
01 Computer Science & Engg. AIC,F,Others 11302.0
01 Computer Science & Engg. AIC,F,Others 35305.0
01 Computer Science & Engg. AIC,F,Others 7679.0
01 Computer Science & Engg. AIC,F,Others 63768.0
01 Computer Science & Engg. AIC,F,Others 41363.0
01 Computer Science & Engg. AIC,F,Others 58615.0
01 Computer Science & Engg. AIC,F,Others 56984.0
01 Computer Science & Engg. AIC,F,Others 66286.0
01 Computer Science & Engg. AIC,F,Others 22362.0
01 Computer Science & Engg. AIC,F,Others 43684.0
01 Computer Science & Engg. AIC,F,Others 88502.0
01 Computer Science & Engg. AIC,F,Others 75349.0
01 Computer Science & Engg. AIC,F,Others 79841.0
01 Computer Science & Engg. AIC,F,Others 78248.0
01 Computer Science & Engg. AIC,F,Others 53999.0
01 Computer Science & Engg. AIC,F,Others 17456.0
01 Computer Science & Engg. AIC,F,Others 100834.0
01 Computer Science & Engg. AIC,F,Others 93685.0
01 Computer Science & Engg. AIC,F,Others 31434.0
01 Computer Science & Engg. AIC,F,Others 30982.0
01 Computer Science & Engg. AIC,F,Others 64501.0
01 Computer Science & Engg. AIC,F,Others 38245.0
01 Computer Science & Engg. AIC,F,Others 44901.0
01 Computer Science & Engg. AIC,F,Others 75336.0
01 Computer Science & Engg. AIC,F,Others 95223.0
01 Computer Science & Engg. AIC,F,Others 79918.0
01 Computer Science & Engg. AIC,F,Others 22798.0
01 Computer Science & Engg. AIC,F,Others 59257.0
01 Computer Science & Engg. AIC,F,Others 69944.0
01 Computer Science & Engg. AIC,F,Others 31593.0
01 Computer Science & Engg. AIC,F,Others 89065.0
01 Computer Science & Engg. AIC,F,Others 41946.0
01 Computer Science & Engg. AIC,F,Others 55956.0
01 Computer Science & Engg. AIC,F,Others 62812.0
01 Computer Science & Engg. AIC,F,Others 105264.0
01 Computer Science & Engg. AIC,F,Others 58567.0
01 Computer Science & Engg. AIC,F,Others 39512.0
01 Computer Science & Engg. AIC,F,Others 88404.0
01 Computer Science & Engg. AIC,F,Others 29413.0
01 Computer Science & Engg. AIC,F,Others 110726.0
01 Computer Science & Engg. AIC,F,Others 29939.0
01 Computer Science & Engg. AIC,F,Others 101460.0
01 Computer Science & Engg. AIC,F,Others 63403.0
01 Computer Science & Engg. AIC,F,Others 79171.0
01 Computer Science & Engg. AIC,F,Others 120370.0
01 Computer Science & Engg. AIC,F,Others 106321.0
01 Computer Science & Engg. AIC,F,Others 100908.0
01 Computer Science & Engg. AIC,F,Others 131467.0
01 Computer Science & Engg. AIC,F,Others 115509.0
01 Computer Science & Engg. AIC,F,Others 103949.0
01 Computer Science & Engg. AIC,F,Others 95519.0
01 Computer Science & Engg. AIC,F,Others 123022.0
01 Computer Science & Engg. AIC,F,Others 136771.0
01 Computer Science & Engg. AIC,F,Others 112522.0
01 Computer Science & Engg. AIC,F,Others 211598.0
02 Computer Science & Engg. AIC,F,Others 50449.0
02 Computer Science & Engg. AIC,F,Others 18078.0
02 Computer Science & Engg. AIC,F,Others 74740.0
02 Computer Science & Engg. AIC,F,Others 12867.0
02 Computer Science & Engg. AIC,F,Others 54947.0
02 Computer Science & Engg. AIC,F,Others 124523.0
02 Computer Science & Engg. AIC,F,Others 86040.0
02 Computer Science & Engg. AIC,F,Others 81094.0
02 Computer Science & Engg. AIC,F,Others 32750.0
02 Computer Science & Engg. AIC,F,Others 46014.0
02 Computer Science & Engg. AIC,F,Others 135872.0
02 Computer Science & Engg. AIC,F,Others 104810.0
02 Computer Science & Engg. AIC,F,Others 134683.0
02 Computer Science & Engg. AIC,F,Others 125076.0
02 Computer Science & Engg. AIC,F,Others 85418.0
02 Computer Science & Engg. AIC,F,Others 20628.0
02 Computer Science & Engg. AIC,F,Others 136908.0
02 Computer Science & Engg. AIC,F,Others 157406.0
02 Computer Science & Engg. AIC,F,Others 32246.0
02 Computer Science & Engg. AIC,F,Others 47138.0
02 Computer Science & Engg. AIC,F,Others 74428.0
02 Computer Science & Engg. AIC,F,Others 42476.0
02 Computer Science & Engg. AIC,F,Others 71440.0
02 Computer Science & Engg. AIC,F,Others 121619.0
02 Computer Science & Engg. AIC,F,Others 161855.0
02 Computer Science & Engg. AIC,F,Others 133762.0
02 Computer Science & Engg. AIC,F,Others 34453.0
02 Computer Science & Engg. AIC,F,Others 67582.0
02 Computer Science & Engg. AIC,F,Others 106875.0
02 Computer Science & Engg. AIC,F,Others 99424.0
02 Computer Science & Engg. AIC,F,Others 67169.0
02 Computer Science & Engg. AIC,F,Others 72990.0
02 Computer Science & Engg. AIC,F,Others 121548.0
02 Computer Science & Engg. AIC,F,Others 134975.0
02 Computer Science & Engg. AIC,F,Others 81303.0
02 Computer Science & Engg. AIC,F,Others 82506.0
02 Computer Science & Engg. AIC,F,Others 116105.0
02 Computer Science & Engg. AIC,F,Others 40175.0
02 Computer Science & Engg. AIC,F,Others 145820.0
02 Computer Science & Engg. AIC,F,Others 85772.0
02 Computer Science & Engg. AIC,F,Others 127614.0
02 Computer Science & Engg. AIC,F,Others 95483.0
02 Computer Science & Engg. AIC,F,Others 77450.0
02 Computer Science & Engg. AIC,F,Others 159026.0
02 Computer Science & Engg. AIC,F,Others 130038.0
02 Computer Science & Engg. AIC,F,Others 118117.0
02 Computer Science & Engg. AIC,F,Others 140501.0
02 Computer Science & Engg. AIC,F,Others 138995.0
02 Computer Science & Engg. AIC,F,Others 119339.0
02 Computer Science & Engg. AIC,F,Others 137108.0
02 Computer Science & Engg. AIC,F,Others 152161.0
02 Computer Science & Engg. AIC,F,Others 160658.0
02 Computer Science & Engg. AIC,F,Others 139247.0
02 Computer Science & Engg. AIC,F,Others 151151.0
01 Computer Science & Engg. AIC,M,Others 26444.0
Now i want to get rid of course name.Like for 01, Computer Science & Engg. is showing multiple times.I want to show Computer Science & Engg. only one time for 01.
Kindly help me as early as possible.
|
|
|
|
|
Wrong tool. An sql query will return you the data, not format it for consumption.
You need to manage the result set in your presentation layer rather than attempt to make the sql query do the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, thanks for looking
I am upgrading my app from 2000 to 2005
Some SPs that im using dont return the rows in the expected sort anymore
I have SPs calling views, the sorting is applied in the view and the sp just filters the view
When i was using sql2000 the sorting was working right (as set in the view), but now in sql2005 rows are not returned in the correct sort
Do i have to specify an order by clause in each SP Or is there any setting that i must set??
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
When adding the order by clause to the view you will get the following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.<br />
So I would figure all your sps will require an order by in their select. BTW I would not use top 100% just to get the order by into the view.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your answer
What a huge change
I wonder what other surprises ill find on sql2005 and later in sql2008
I cant beleive that even visual studio server explorer added a wrong workaround, something that as i just read was never supposed to be used inside a view
Alexei Rodriguez
|
|
|
|
|
I guess i just found the biggest problem with sql2005
When generating the scripts, with doesnt care about dependencies and when executing them, you know what happens
Do you know any way to generate the scripts in the correct order?
Thnans in advance
Alexei Rodriguez
|
|
|
|
|
I use sql compare (empty database) it does a good job. I have never tried between different versions.
Why not split it logically to table/views/procs then only the procs can be out of sequence.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply
How im i supposed to use that?? where is that option?
Alexei Rodriguez
|
|
|
|
|
Sorry SQL Compare is a Red-Gate product not part of SQL Server.
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a database which is used to pass information about certain entities between two programs. Each entity will have a record for each program, containing, in simplified form:
The ID of the entity
The identity (0 or 1) of the program that created the record
Poll Time (used mainly to confirm that both programs are checking the database in timely fashion--not used by the synchronization operations themselves)
Data for the other program
A timestamp/rowversion for that data
A timestamp/rowversion for the other program's data (copied from the other program's record when it's observed)
The first two fields would form a primary key. Each program would check its own timestamp/rowversion against the other programs' to see whether any updates were required.
Two related questions:
-1- What should I use for the timestamp/rowversion? Possibilities I can see:
-a- Using a date/time record for the timestamp would probably work, but it could break if a record gets updated twice quickly. The risk is probably more theoretical than actual, but I'd prefer to avoid something that is structurally not sound.
-b- A 'timestamp' field would seem almost ideal, except that I would want to be able to update the 'other program' timestamp field without bumping my own.
-c- A GUID would work, but GUIDs are bulky and ugly.
-d- In an earlier SQL Server I could have used a bigint and had the update store @@DBTS (a 64-bit integer that was bumped every time a database access occurred). Unfortunately, in newer versions @@DBTS doesn't get bumped unless a row containing a timestamp/rowversion field is updated.
-e- I could have each row contain an ignored timestamp/rowversion field and store @@DBTS in a field I'm actually interested in. That would work, but having the unused field just to force a @@DBTS update seems goofy.
-f- I could use another single-column table as a 'ticket dispenser'. Each time I want a number, do an insert, select SCOPE_IDENTITY(), and empty out the table. That would probably work, but again seems goofy.
What's the right approach?
-2- Is there any good way to find pairs of records, such that the entity ID's are equal, the record types are 0 and 1, and the "My Timestamp" field of the first does not math the "Other Timestamp" field of the second or vice versa, or is the data format described above really not a good design?
|
|
|
|
|
Hi.
In a system that has SQL Server 2005, how can I know the instance names of the SQL Server?
Best wishes
|
|
|
|
|
If there is only 1 server installed, it has no instances - but only a default instance which does not need to be called with a name.
If there is more, then I think you need to talk to the guys who've set up multiple SQL Server instances.
|
|
|
|
|
goto ths path
control pannel->administrative tools->computer management->
now you have to expand the services and application and in subtree expand the microsoft sql server
just it.
the instance name of your sql server is there.
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|