Anyone has any idea on how to insert 2 strings into a row with 2 lines created?

<p>Hi ,

I would like to know anyway to insert two strings into a row with 2 lines created?
My codes are as below

If NodeName = "subProductPrice" Then
If xmlrder.NodeType = Xml.XmlNodeType.Text Then
SubPrPriceList.Add(xmlrder.Value)

For i = 0 To SubPrPriceList.Count - 1
SubPrPrice = CStr(SubPrPriceList.Item(i))
PriceBrkDownStr &= SubPrPrice

Next

PricePerDay = SvcDate & PriceBrkDownStr
dr("dailyPrice") = PricePerDay
End If
End If

Although both the SvcDate and PriceBrkDownStr are inserted into the same row but they are displayed into one line such as below:

<u>dailyPrice </u>
02/03/2007 03/03/2007 120 230

Any idea how to make the date and price separate into two rows in the same table grid row? thanks =)
</p>

store <br> between the lines and let me know if it worked...

|||

Hi, sorry I dont get what you mean. Insert <br> between 2 lines? I am inserting the string into the table grid row. How can I insert the tag <br> ? =P

|||

I mean store them as a single line with <br> between them and when you will diplay them they will be shown like two lines... i understood thats what you want to do... if it is not the case then my mistake :)

Anyone else have problems with Sort/Merge in For Each loop?

Just wondering if I am an isolated case or just doing stuff other people don't. Ive created around a dozen packages for importing various types of data into a datawarehouse and some of the packages have random crashes. Over time I have been trying to tie together what the crashing ones have in common and now believe I have completely isolated it: Sort + Merge inside a dataflow in a for each loop. The more sorts and merges in the flow the more likely the problem is to occur.

This week I created 2 identical packages to import some XML... one used some gnarly complicated substrings and such to extract some data and add it as derived columns while the other used the XML correctly that produced 2 streams (one being meta data - other being detail data) and sorted + merged them. The gnarly substring package could run all night (processes about a file every 2 or 3 seconds so you can do the math) while the merged data flows crashes somewhere between 1 and 4 hrs of running - usually right around 3 hrs (also processes about 1 file every 2 seconds). The file it crashes on wil be completly random and not related to file size - I've had crashes on files with as little as 1 row of data and 1 row of metadata and as big as 1 row of metadata and 100,000+ rows of data. Another more complicated package I created has 4 merges (and thus 8 sorts) and it will crash anywhere between 5 minutes and 20 minutes of running.

The crash is usually one where the whole process just shuts down with a memory dump but occasionally I have gotten errors about out of memory or warnings about threads leaking buffers.

I am trying to do some "research" to see if others have this problem. I've spent 1.5 months writing all ETL jobs in SSIS and its gotten to the point management has asked me to explore other platforms. I do have a case open with Microsoft on the package with 4 merges - but we've spent weeks now just trying to get debugging tools to get them some useful info when the process crashes. I am running SP1 and kb91822.

Anyone else?

FYI to anyone that comes across this thread - msft identified a bug in the sort object (stack heap corruption) and now has a hotfix. If you run into this same situation please contact them (until the patch is released to the public)

Any way to have a cursor variable reference an *existing* cursor?

(SQL Server 2000, SP3a)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor, but I
can't seem to "set" my cursor variable from any of this information. Something like (once
I retrieve the cursor_handle or something):
set @.MyCursor = cursor @.cursor_handle
Does anyone know of any mechanism I can use to accomplish this?
Thanks!
John PetersonI had hoped that something like this would work:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But, alas, I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
I'd appreciate any other suggestions!
Regards,
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uxudcE99DHA.3488@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I have an open global cursor that is created dynamically by stored procedure A. I'd
like
> to reference this cursor from stored procedure B. I know the dynamic name of the
cursor,
> but I know of no way to get a "handle" of this cursor so that I can use it from stored
> procedure B in a cursor variable.
> I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor, but
I
> can't seem to "set" my cursor variable from any of this information. Something like
(once
> I retrieve the cursor_handle or something):
> set @.MyCursor = cursor @.cursor_handle
> Does anyone know of any mechanism I can use to accomplish this?
> Thanks!
> John Peterson
>

Any way to have a cursor variable reference an *existing* cursor?

(SQL Server 2000, SP3a)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
I had hoped that maybe I could somehow divine the "handle" from sp_describe_
cursor, but I
can't seem to "set" my cursor variable from any of this information. Someth
ing like (once
I retrieve the cursor_handle or something):
set @.MyCursor = cursor @.cursor_handle
Does anyone know of any mechanism I can use to accomplish this?
Thanks!
John PetersonI had hoped that something like this would work:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But, alas, I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
I'd appreciate any other suggestions!
Regards,
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uxudcE99DHA.3488@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I have an open global cursor that is created dynamically by stored procedure A. I
'd
like
> to reference this cursor from stored procedure B. I know the dynamic name of the[
/color]
cursor,
> but I know of no way to get a "handle" of this cursor so that I can use it
from stored
> procedure B in a cursor variable.
> I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor
, but
I
> can't seem to "set" my cursor variable from any of this information. Something li
ke
(once
> I retrieve the cursor_handle or something):
> set @.MyCursor = cursor @.cursor_handle
> Does anyone know of any mechanism I can use to accomplish this?
> Thanks!
> John Peterson
>

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?
Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:
>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

Any way logging or tracking method for dropped tables

Most of my tables is dropped after they are created. I cannot tell when this
happen. This include stored procedures as well. I checked the maintenance job
and none are dropping tables or stored procedures.
The funny part is my tables and stored procedure which is published out as
articles remain and not dropped as others. Why?
Is there any logs or tracking that can be put in place to find out who
deleted my tables?
Paul,
I'm using SQL Server 2000 so I guess I would need to use the log explorer
tool. Are there any recommendation which I can try first?
Another question which hopefully can narrow down my suspect here is..Do you
think replication dropped the tables?
Cheers,
Philip
"Paul Ibison" wrote:

> If you're on SQL Server 2005 you could set up ddl triggers to log who/what
> is performing the drops. Alternatively a 3rd party log explorer tool will
> have the info in it.
> HTH,
> Paul Ibison
>
>
|||Replication by default will drop tables on the subscriber, but never on the
publisher so i'd look elsewhere. The reason replicated objects haven't been
dropped is probably because once they're published, they have to be removed
from the publication to be allowed to be dropped, so that distinguishes them
from your other objects. (this is a bit of guesswork, but it makes sense).
Lumigent Log Explorer will audit DDL commands to help you figure out who did
the drop (http://lumigent.com/products/le_sql_faq.html#_I_do_not). If it
happens regularly then you could use profiler yourself to monitor for these
drops.
HTH,
Paul Ibison

Any suggestions?

I have a database which contains more than 20000 stored procedures
which were created with
ansi nulls off. This i found out using the query
SELECT name,AnsiNullsOn FROM
(
SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0

Is there any way that i can set this property to 1 for all the stored
procedures i have??

I know the alternate method is to drop the procedure and execute the
scripts again with AnsiNullsOn = 1.

Is there any other simple ways?? It will be very helpful for me..I believe this will do the job, though I suggest some small-scale
tests first.

1) Script out all the procs as CREATEs, but do not include the DROP
option.

2) Edit the script.

- Change CREATE PROC to ALTER PROC for all procedures.

- Change all the SET ANSI_NULLS ( and possibly the SET
QUOTED_IDENTIFIER) commands as you want them.

3) Run the script.

Roy Harvey
Beacon Falls, CT

On 21 Nov 2006 14:17:38 -0800, "balaji" <mailbalajijagan@.gmail.com>
wrote:

Quote:

Originally Posted by

>I have a database which contains more than 20000 stored procedures
>which were created with
>ansi nulls off. This i found out using the query
>SELECT name,AnsiNullsOn FROM
>(
>SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
>FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0
>
>Is there any way that i can set this property to 1 for all the stored
>procedures i have??
>
>I know the alternate method is to drop the procedure and execute the
>scripts again with AnsiNullsOn = 1.
>
>Is there any other simple ways?? It will be very helpful for me..

|||balaji wrote:

Quote:

Originally Posted by

I have a database which contains more than 20000 stored procedures
which were created with
ansi nulls off. This i found out using the query
SELECT name,AnsiNullsOn FROM
(
SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0
>
Is there any way that i can set this property to 1 for all the stored
procedures i have??
>
I know the alternate method is to drop the procedure and execute the
scripts again with AnsiNullsOn = 1.
>
Is there any other simple ways?? It will be very helpful for me..

Just to state the obvious. Since there are potential behaviour changes
associated with changing the setting it seems like the bigger task
could be testing the procs rather than making the change.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Any special way to create an index

Hi All,
I am not very experienced in creating indexes. Is there a preferred way
one should follow. I created come indexes using EM. When I do that, and
hit save on the table, does the SQL server at that point generate the
indexes?
Thanks for your comments/help.
*** Sent via Developersdex http://www.examnotes.net ***yes.
Or you can run a script from the query analyzer.
CREATE INDEX MyNewIndex ON TableName (ColumnName)
this is a very simple example. Look up "Create Index" in books on line (BOL)
for more detailed explanation
Greg Jackson
PDX, Oregon|||For what it's worth, within the Enterprise Manager's table designer, after
adding entries for your indexes, you can click the "save change script"
button instead of the "save" button, and it will display a dialog with the
SQL scripts that would be applied. You may want to save off the script, if
you are going to re-apply the indexes on other occasions.
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:Oq6yebVXFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am not very experienced in creating indexes. Is there a preferred way
> one should follow. I created come indexes using EM. When I do that, and
> hit save on the table, does the SQL server at that point generate the
> indexes?
> Thanks for your comments/help.
> *** Sent via Developersdex http://www.examnotes.net ***

Any significant differences between these two date CAST statements

I need to find records that have a date stamp of yesterday. I have created two where clauses that use CAST and DATETIME. Both seem to return the same results. Is either one better?

CAST(FLOOR(CAST(cc.Date AS float)) AS datetime) = CAST(FLOOR(CAST((getdate()-1) AS float)) AS datetime)

-- OR --

cast (round(cast(cc.Date as float),0,1) as datetime) = cast (round(cast((getdate()-1) as float),0,1) as datetime)

BTW: Oracle handles this easily as trunc(mydate)

Thanks in advance

Oh Using SQLServer 2005

Doug

www.cooltimbers.com

It looks to me like they will both work:

declare @.morningDt datetime set @.morningDt = '3/13/7 0:05'
declare @.afternoonDt datetime set @.afternoonDt = '3/13/7 23:59'

select cast(round(cast(@.morningDt as float),0,1) as datetime),
cast(round(cast(@.afternoonDt as float),0,1) as datetime),
round(cast(@.morningDt as float),0,1) ,
round(cast(@.afternoonDt as float),0,1)

-- -- - -
2007-03-13 00:00:00.000 2007-03-13 00:00:00.000 39152.0 39152.0

I am more used to seeing the FLOOR version.

|||

Thanks Kent for the prompt reply... and for your help in general!

I sure do wish Microsoft implements something similar to Oracles "TRUNC" function that nicely truncates to a day.

Any help on this SMO request?

My evaluation copy ran-out and I uninstlaled the previous and installed anothrer evaluation edition. The installation was successful. I created a SSIS package importing a tale from Oracle 10G XE to the SQL 2005 on my machine. I saved the package on the server. The SSIS service is working but when I try to refresh the node, I get this message. The help linl leads to no where:

--

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
TCP Provider: No connection could be made because the target machine actively refused it. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
TCP Provider: No connection could be made because the target machine actively refused it. (Microsoft SQL Native Client)

My efforts so far:

I have verified the items in the Surface area screen, everythign looks good.

SSIS service has started.

Both teh Network Server TCP/IP dynmaic port and the SQL Client TCP/IP dynamic port are the same.

I have no problem logging in to any of the services. But I get this message.

I tried to create a package from Visual Studio, but that also did not succeed.

In my previous set up, I created perhaps 1/ 2 dozen different packages.

I am puzzzled.

I appreciate if some one could throw some light.

If you could get the function call stack when the error occurs, that would help diagnose the problem. At the bottom of the exception message box, there is a little icon with a tooltip like "technical details" which displays this information.

Also, if you could provide more specific steps to reproduce the problem, that would help too. Are you using SSIS when you get this problem, or some Management Studio UI that uses SSIS, like the Copy Database Wizard? You can get more specific SSIS configuration help in the SSIS forum here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

Thanks,
Steve

Any help on sp_trace_getdata

Hi
I have created a trace and then I tried reading trace through
sp_trace_getdata by specifying the correct trace id.
But query analyser become busy in continously executing sp_trace_getdata and
does return any result for long time.
Can any one suggest how should I call sp_trace_getdata to get the result
asynchronously, of the trace as when it is generated.
Thanks
Pushkar
Are you trying to consume the trace rowset? If you are using SQL Query
Analyzer, make sure you are not sending it to the grid, but to the text
window.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:%23pJuSu3ZFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi
> I have created a trace and then I tried reading trace through
> sp_trace_getdata by specifying the correct trace id.
> But query analyser become busy in continously executing sp_trace_getdata
> and
> does return any result for long time.
> Can any one suggest how should I call sp_trace_getdata to get the result
> asynchronously, of the trace as when it is generated.
> Thanks
> Pushkar
>
|||Thanks a lot Gert.
When sending the output to the text it worked. But why it was not working
while sending the output to a grid?
How can I write a application or extended stored procedure that can consume
the trace rowset and update some table?
Please help me out.
Thanks a lot.
Pushkar
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:uiN6eVBaFHA.464@.TK2MSFTNGP15.phx.gbl...
> Are you trying to consume the trace rowset? If you are using SQL Query
> Analyzer, make sure you are not sending it to the grid, but to the text
> window.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:%23pJuSu3ZFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
|||Because the grid will consume rows until it gets end of rowset, which in
case of SQL Trace will only happen when you stop the trace.
This is a behavior of the grid in Query Analyzer, OSQL and when using the
text window will pull rows directly and display them, like profiler does,
you can do exactly the same thing.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u1oS9BCaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot Gert.
> When sending the output to the text it worked. But why it was not working
> while sending the output to a grid?
> How can I write a application or extended stored procedure that can
> consume
> the trace rowset and update some table?
> Please help me out.
> Thanks a lot.
> Pushkar
>
>
>
> "Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
> news:uiN6eVBaFHA.464@.TK2MSFTNGP15.phx.gbl...
> rights.
>
|||Thanks
Pushkar
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eSPrx6HaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Because the grid will consume rows until it gets end of rowset, which in
> case of SQL Trace will only happen when you stop the trace.
> This is a behavior of the grid in Query Analyzer, OSQL and when using the
> text window will pull rows directly and display them, like profiler does,
> you can do exactly the same thing.
> --
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:u1oS9BCaFHA.3864@.TK2MSFTNGP10.phx.gbl...
working
>

Any harm in adding a primary key...

after my table has been created and populated with data?
I have an identity column (named 'CID') on my customers table. This
value is often used in my queries and I'd like to take advantage of a
clustered
index (since none currently exist on the table).
Will making this column a primary key have any adverse effects? There are
currently 3,000 rows in the table and it's 10 columns wide."Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
> after my table has been created and populated with data?
> I have an identity column (named 'CID') on my customers table. This
> value is often used in my queries and I'd like to take advantage of a
> clustered
> index (since none currently exist on the table).
> Will making this column a primary key have any adverse effects? There are
> currently 3,000 rows in the table and it's 10 columns wide.
1. Why wasn't the table created with a PK in place already.
2. Just because you make it a primary key, doesn't make it a clustered
index. When you create a Primary Key in SQL Server it will choose Clustered
by default, but it could be non-clustered if you tell it to.
So to answer your question, you could just as easily create a clustered
index on that CID column as create a Primary key on it. Should it be the
Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say.
Since it is not a PK, I will assume that you are not using FK constraints
either. This could be a very serious issue in your database. The PK and FK
constraints are there to ensure the relational integrity of your data. If
you have your customers table as described above, and an Orders table.
Without these constraints, there is really nothing stopping someone from
adding Orders to your database that don't map to any Customer, or delete
Customers who may have orders pending for shipment.
You should really finish modelling your system and include the PK and FK
constraints, and any other constraints necessary to ensure the various
integreties of your system (entity integrity, relational integrity and
domain integrity).
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||1) I have no idea as I did not design the db.
2) I wanted to take advantage of using a clustered index.
I understand the importance of using the PK/FK for integrity. We are
planning a db redesign in the near future, but for the time being, I just
wanted to add the clustered index via a PK for performance purposes.
"Rick Sawtell" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
>
> 1. Why wasn't the table created with a PK in place already.
> 2. Just because you make it a primary key, doesn't make it a clustered
> index. When you create a Primary Key in SQL Server it will choose Cluster
ed
> by default, but it could be non-clustered if you tell it to.
>
> So to answer your question, you could just as easily create a clustered
> index on that CID column as create a Primary key on it. Should it be the
> Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say
.
> Since it is not a PK, I will assume that you are not using FK constraints
> either. This could be a very serious issue in your database. The PK and
FK
> constraints are there to ensure the relational integrity of your data. I
f
> you have your customers table as described above, and an Orders table.
> Without these constraints, there is really nothing stopping someone from
> adding Orders to your database that don't map to any Customer, or delete
> Customers who may have orders pending for shipment.
> You should really finish modelling your system and include the PK and FK
> constraints, and any other constraints necessary to ensure the various
> integreties of your system (entity integrity, relational integrity and
> domain integrity).
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:7585798D-DD21-4D84-9135-A21EB8F786DA@.microsoft.com...
> 1) I have no idea as I did not design the db.
> 2) I wanted to take advantage of using a clustered index.
> I understand the importance of using the PK/FK for integrity. We are
> planning a db redesign in the near future, but for the time being, I just
> wanted to add the clustered index via a PK for performance purposes.
> "Rick Sawtell" wrote:
>
Then by all means create a PK and specify Clustered.
ALTER TABLE dbo.Customers
ADD CONSTRAINT PK_Customers_CID
PRIMARY KEY CLUSTERED (CID)
To get even better performance, you should also create indexes on what will
eventually be your FK columns, so if you had an Orders table, you might do
the following.
CREATE NONCLUSTERED INDEX IX_Orders_CID ON dbo.Customers (CID)
Rick Sawtell
MCT, MCSD, MCDBA|||Eric wrote:
> 1) I have no idea as I did not design the db.
> 2) I wanted to take advantage of using a clustered index.
> I understand the importance of using the PK/FK for integrity. We are
> planning a db redesign in the near future, but for the time being, I just
> wanted to add the clustered index via a PK for performance purposes.
> "Rick Sawtell" wrote:
>
Your question is whether adding the cluster key could do any harm. If
your database is this weakly designed then I wouldn't take anything for
granted. Maybe they left out an ORDER BY on a cursor somewhere and
adding an extra index would cause some logic to fail. Test, test, test.
David Portas
SQL Server MVP
--|||David:
If you could please clarify the effect a cursor may have w/an omitted 'order
by' cluase. I'm using a cursor in only one instance on my db. I would like
to add a clustered index to one of my tables (Company.ID) but this table is
in fact included in my cursor statement. Appreciate your input.
"David Portas" wrote:

> Eric wrote:
> Your question is whether adding the cluster key could do any harm. If
> your database is this weakly designed then I wouldn't take anything for
> granted. Maybe they left out an ORDER BY on a cursor somewhere and
> adding an extra index would cause some logic to fail. Test, test, test.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not David, but:
Some developer seems to think that the order of rows returned from a SELECT
statement can be
determined by having a certain index in place, or some other physical attrib
ute of the table. That
is not true. The only way to guarantee that the result from a SELECT stateme
nt is ordered in any
certain way is to have ORDER BY. This is not exclusive to cursor operations,
btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:29804387-5EF8-434A-8BFA-D05A1303CBAE@.microsoft.com...
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>|||Eric wrote:
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>
If you write cursor code without specifying ORDER BY in the DECLARE
statement and if that cursor uses logic that assumes a particular
ordering to the data then it may break at some point if the execution
plan changes. This is made more likely if you create or drop indexes -
i.e. the assumed order of the cursor may change. Always specify ORDER
BY if the ordering is important to you.
If you avoid cursors and stick to set-based code you'll avoid this
potential problem. Most cursors are unnecessary and are written by
people who don't know SQL well enough to do any better.
David Portas
SQL Server MVP
--|||If CID is unique (and it must be unique since it is an identity column), and
there are no natural key candidates, then do make it the primary key. Every
table needs a primary key of some type.
http://www.aspfaq.com/show.asp?id=2504
Also, 3000 rows x 10 columns is practically nothing, and if it's being hit
frequently, SQL Server is probably keeping a complete in the buffer cache
anyway and reducing the need to read it from disk. It would not benefit from
a clustered index (although it wouldn't hurt).
http://msdn.microsoft.com/library/d...br />
1zu4.asp
http://msdn.microsoft.com/library/d...br />
3339.asp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
> after my table has been created and populated with data?
> I have an identity column (named 'CID') on my customers table. This
> value is often used in my queries and I'd like to take advantage of a
> clustered
> index (since none currently exist on the table).
> Will making this column a primary key have any adverse effects? There are
> currently 3,000 rows in the table and it's 10 columns wide.|||On Wed, 28 Dec 2005 08:25:05 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>after my table has been created and populated with data?
No harm.

>I have an identity column (named 'CID') on my customers table. This
>value is often used in my queries and I'd like to take advantage of a
>clustered index (since none currently exist on the table).
PK is one thing, clustering is an option.
Alternatively clustering is one thing, PK is another.
But if you want to do both at once, go ahead.
Are there any other existing indexes? Creating a clustered index does
mean rewriting the table and rebuilding the other indexes.

>Will making this column a primary key have any adverse effects?
Only that it will then enforce uniqueness, can't have null values,
just the standard behaviors of a PK.

>There are
>currently 3,000 rows in the table and it's 10 columns wide.
Extremely modest by modern standards.
J.

Ansi_Padding Question

Hi all,
How do you determine if ansi_padding was set to on or off when a table
was created? Aside from inserting a record with trailing blanks, I
can't seem to find an answer.
Thanks,
Terryif (@.@.OPTIONS & 16 = 16)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry|||@.@.options only helps you figure out the current value of ANSI_PADDING.
To check the value in effect when a column was created, use COLUMNPROPERTY
SELECT COLUMNPROPERTY(table_id, column_name, 'UsesANSITrim')
Please see Books Online for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry

ANSI_NULLS options successfully changed.But Error occured.

Hi,

Accoding to coding the SP was successfully created.But Once its going to be

executed through vb ADO cording, its giving following error message.

MSDTC on server "NISHANTHA" unavailable.

NISHANTHA is a working server.Particuler SP is exists in that server which is

accessing another server.

Please send me why it is and How to solve.Please attach sample cording.

Thanks,

Pubudu

This error suggests that you will need to start the 'Distributed Transaction Coordinator' NT Service on your NISHANTHA server.

Chris

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
SkSELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of[vbcol=seagreen]
>
>.
>

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
Sk
SELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk
|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of
>
>.
>

ANSI Nulls

I've mentioned this before, but now it's got me stuck. I have a
table-valued function that was created using QA in SQL 2000. I can't
edit it using 2005 Mgt Studio, because the property AnsiNullsStatus
isn't set (apparently SQL 2000 forgot to set it when creating the
function). Now I find I really want to edit it in SQL 2005 MS without
going to SQL 2000 QA on my laptop. So, using SQL 2005 MS, I dropped
and re-created the function, having obtained the code from SQL 2000 QA.
That should fix this, right? When SQL 2005 re-creates the function, it
will specify whatever it needs. But no, I still can't edit it! The
property is still not set!

Can anyone help? Please?

Thanks,
Jim GeissmanHello, Jim

It's a known bug. See:
http://lab.msdn.microsoft.com/Produ...ackid=FDBK38845

Microsoft tried to fix it in SQL Server 2005 SP1, but did not fully
succeed. However, on SP1 you can script the function in Management
Studio, by choosing "Script function as Create" (but it still doesn't
work with "Modify" or "Script function as Alter").

Razvan|||Thank you, Razvan.