Cant sort a text field!

Hi
ive got a table and contains a surname text field. Why cant i do a select statement ORDER BY surname. I get an error saying i cant sort a text field! how do i go around it!

thanksYou do ANSI SQL ALTER Table and change text to Varchar 40 because text cannot be used for things like name that can be covered with Varchar 40 or 50 at most. Then do ORDER BY. Hope this helps.|||

Your surname filed is a text datatype field which cannot be sorted.

You need change this field to varchar(50) or nvarchar(50) data type to allow you sort on it.

Limno

CAN'T SOLVE SELECT GROUPING WITH MAX FUNCTION

Here is the scenario
ACCOUNT CONTACT DATE
account A CILXFA000HJ6 4/6/2005 16:05
account A C15467XXXXXX 7/27/2005 12:22
account B C20002XXXXXX 2/15/2005 18:25
account B C12225XXXXXX 12/14/2005 20:10
The RESULT should be this;
account A C15467XXXXXX 7/27/2005 12:22
account B C12225XXXXXX 12/14/2005 20:10
I tried using MAX function to get the LAST modified date display one
single record with account, contact and LAST modified date or last date
fields but the problem is that I can't use it to select out the right
contact because it is not designed to pick the last record? What are
other possible simple solutions. PS. The listing is already sorted by
date.
Rodselect account, contact, date
from yourtable t1
where date = (select max(date)
from yourtable
where account=t1.account
)
rlueneberg@.gmail.com wrote:
> Here is the scenario
> ACCOUNT CONTACT DATE
> account A CILXFA000HJ6 4/6/2005 16:05
> account A C15467XXXXXX 7/27/2005 12:22
> account B C20002XXXXXX 2/15/2005 18:25
> account B C12225XXXXXX 12/14/2005 20:10
> The RESULT should be this;
> account A C15467XXXXXX 7/27/2005 12:22
> account B C12225XXXXXX 12/14/2005 20:10
> I tried using MAX function to get the LAST modified date display one
> single record with account, contact and LAST modified date or last date
> fields but the problem is that I can't use it to select out the right
> contact because it is not designed to pick the last record? What are
> other possible simple solutions. PS. The listing is already sorted by
> date.
>
> Rod
>|||Alternatively:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.account=t2.account
"rlueneberg@.gmail.com" wrote:

> Here is the scenario
> ACCOUNT CONTACT DATE
> account A CILXFA000HJ6 4/6/2005 16:05
> account A C15467XXXXXX 7/27/2005 12:22
> account B C20002XXXXXX 2/15/2005 18:25
> account B C12225XXXXXX 12/14/2005 20:10
> The RESULT should be this;
> account A C15467XXXXXX 7/27/2005 12:22
> account B C12225XXXXXX 12/14/2005 20:10
> I tried using MAX function to get the LAST modified date display one
> single record with account, contact and LAST modified date or last date
> fields but the problem is that I can't use it to select out the right
> contact because it is not designed to pick the last record? What are
> other possible simple solutions. PS. The listing is already sorted by
> date.
>
> Rod
>|||Oops. This work better:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.date=t2.maxdate
"Mark Williams" wrote:
> Alternatively:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.account=t2.account
>
> --
>
> "rlueneberg@.gmail.com" wrote:
>|||Mark, you need to specify both columns in your join clause:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.account = t2.account and t1.date=t2.maxdate
Joe
"Mark Williams" wrote:
> Oops. This work better:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.date=t2.maxdate
>
> "Mark Williams" wrote:
>|||Thanks Joe. I thought about it for a few minutes, and realized that if two
accounts had the same max date value, my query would produce the wrong
results.
"Joe from WI" wrote:
> Mark, you need to specify both columns in your join clause:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.account = t2.account and t1.date=t2.maxdate
> Joe
> "Mark Williams" wrote:
>|||That was exactly what I was looking. It took a quite some time to run
though, but it does the job precisely. I will test the other
suggestions and see how it goes.
Thank you
Rod|||Using the suggested script I found that there are some contacts with
exact same create date and time, including seconds. This is because
they were eletronically imported. This leads to another scenario. I
need to assign at least one contact as a primary contact. In this case,
how can I pick the first one or the last one? If this is not possible,
then how can I randomly pick just one record of each grouped result. PS
There is no autoincrement or sequential column.
For example:
account C CILXFA000HJ6 4/6/2005 16:05
account C CILXFA000333 4/6/2005 16:05
account D C20002XXXXXX 2/15/2005 18:25
account D C50902XXXXXX 2/15/2005 18:25
account D C50008XXXXXX 2/15/2005 18:25
Thanks
Rod

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000
Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>
|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
--
Regards
Pawel Potasinski
U¿ytkownik <-> napisa³ w wiadomo¶ci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik <-> napisa³ w wiadomo¶ci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> Any ideas why I can't select anything from 1 table? If I try to execute a
>> select statement (even Top 1), the statement just hangs. We constantly
>> have to restart SQL Server to "free" up the problem, but it keeps coming
>> back.
>> Any ideas what's going on?
>> Thanks.
>> Sql 2000
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Cant select Date

hi, my DatePost field in the database formated as2/15/2006 11:40:46 AM .i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks

no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String

myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL ="SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()

Author = myReader("Author")

myReader.Close()
myConn.Close()
lblShow.Text = Subject

Hello Sebastian,

Try adding slashes to your date string like this:

no = "2/15/2006"

Even better would be to use a variable of DateTime type like this:

Dim no As DateTime
no = "2/15/2006"

This should solve your problem.

Regards, Maurits

|||

If you used no="20060215" then your query would be correct, but it still would not return any records, because the datetime "2006-15-02 11:40:46" <> "2006-15-02 00:00:00".

no = "20060215" -- assume string is in YYYYMMDD format so SQL Server can implicitly convert it to a datetime for comparision (OR any string recognized by SQL Server's current language/culture setting)
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String

myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL ="SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()

Author = myReader("Author")

myReader.Close()
myConn.Close()
lblShow.Text = Subject

I'm going to assume you know how to make no a datetime variable instead of a string, but assuming it is a datetime with a time portion being midnight, this should do what you want:

Dim myConn As new SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
myConn.open
Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)
myCmd.parameters.add("@.no",sqldbtype.datetime).value=no
lblShow.Text = myCmd.executeScalar
myConn.Close()

Actually, the above code will work even if no is string that contains a datetime in a format that is understood by the culture the ASP.NET thread is running too. (For example "2/15/2006" if the current culture is US-en, or "15-02-2006" if the current culture is FR-fr", etc.)

|||Two things to keep in mind when working with datetime column types.

1. Datetime type contains two components: date and time. So, when specifying WHERE clause, you have to consider the time component as well. By default, datetime's time component is "12:00:00 AM". So, if you're trying to filter for date of "02-20-2006', the something similar to following would be used:

... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'

(notice the less-than sign and the date of one more than the one intended in query)

or

... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'

2. As in the example above, you need to provide a specific format of date string. Your value of "2152006" is confusing to the translation engine, so transform it to something like "mm-dd-yyyy", "mm/dd/yyyy" or even "yyyymmdd" which is the ISO version.|||

WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'

is really what you should use, the statement

WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'

actually has two issues. First, if you use that with a datetime column, since the datetime field is accurate to approximately 1/300th of a second, there is really a 1-second gap that the query will not find. You could conceivably do something like:

WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59.997 PM'

but that assumes you know the exact highest time value that is storable in a datetime field, which I prefer not to use (assume). It's inviting problems on upgrades, conversion, or database migration to another platform. Secondly, if you tried to do that with a smalldatetime field, SQL Server will convert the strings to a smalldatetime, and since that is only accurate to 2 seconds, it will round your string to the nearest 2-second interval (Since it is half way between, it will round UP), giving you 02-21-2006 00:00:00, which will include any smalldatetimes that are part of midnight the next day, also very yucky.

|||

jcasp wrote:

Two things to keep in mind when working with datetime column types.

... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'

or

... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'

I'd definitely stick with that first WHERE clause. The second one is not taking milliseconds into consideration. And I prefer using the ISO version of the date '20060215' as it is unanmbiguous.

Motley wrote:

Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)

You want to stay away from this approach Don't use a function against a column like that; it really increases the overhead in running the query as the fuction has to be performed against every row. Stick with the approach suggested by jcasp.|||Correction noted. One should keep in mind that datetime column type does have higher precision than the second component (goes to milliseconds). Very easy to overlook, but can definitely come into play. Good catch.|||

tmorton wrote:

jcasp wrote:

Two things to keep in mind when working with datetime column types.

... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'

or

... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'

I'd definitely stick with that first WHERE clause. The second one is not taking milliseconds into consideration. And I prefer using the ISO version of the date '20060215' as it is unanmbiguous.

Motley wrote:

Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)

You want to stay away from this approach Don't use a function against a column like that; it really increases the overhead in running the query as the fuction has to be performed against every row. Stick with the approach suggested by jcasp.

Actually, what I wrote is the only method mentioned here that will actually work, assuming that the field "Date" is actually a varchar field. Since the original poster said his "PostDate" field had a format of "MM/DD/YYYY HH:MM:SS PM", and datetimes don't have a format, this was my assumption. Of course, if the original poster was incorrect, and the field is actually a datetime, then just drop the cast portion of my command. Since the DateAdd function is a deterministic function, and it's input is based on the parameters, SQL Server will only need to execute this once instead of every row.

|||ok, before i select the author record, i have insert a date record into the table. this is how i do.

Dim Date As Date = Now()

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("INSERT INTO Booktbl (Author, Date) VALUES('" & strAuthor & "', '" & Date & "')", myConnection)
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Catch ex As SqlException
lblMsg.Text = " Error"
End Try
End Sub

End insert section. the data type used to store date is datetime. the record look like this 2/12/2006 11:40:40 AM

when i try to retrive back the record base on the date it give me error. i also declare the no as date, datetime or string and put it "2/12/2006" or some other format it still not work. i have try one example to put the no = "2/12/2006 11:40:40 AM" then it work, but what i need is the date not the time.

anymore figure out what happen??|||Dim MyDate As Datetime = Now()

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("INSERT INTO Booktbl (Author, Date) VALUES(@.author,@.MyDate)", myConnection)
myCommand.parameters.add("@.author",sqdbtype.varchar).value=strAuthor
myCommand.parameters.add("@.MyDate",sqldbtype.datetime).value=MyDate
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Catch ex As SqlException
lblMsg.Text = " Error"
End Try
End Sub|||Motley, i have used your code to insert data to the table. now i want to select the info and display it. This is how i do it!

Dim no As DateTime
no = "2/22/2006"

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("SELECT Author FROM Booktbl WHERE Date = @.no", myConnection)
myCommand.Parameters.Add("@.no", SqlDbType.DateTime).Value = no
myConnection.Open()

Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
myReader.Read()

Author = myReader("Author")

myReader.Close()
myConnection.Close()
lblShow2.Text = Author

it give me this error message "Invalid attempt to read when no data is present."why? why i can't retrive the data? i been work on this few days ago, hope u can help me. thanks
|||

Because the datetimes aren't exactly the same.

SELECT Author FROM Booktbl WHERE dateadd(d,0,datediff(d,0,Date)) = dateadd(d,0,datediff(d,0,@.no))

is the select statement you want if you just want the date portions to match.

SELECT Author FROM Booktbl WHERE Date>=dateadd(d,0,datediff(d,0,@.no)) AND Date<dateadd(d,1,datediff(d,0,@.no))

is the same idea, but will execute much faster, it's looking for a datetime >= to the beginning of the day in @.no through (but not including) the beginning of the day after @.no. (Between 2/22/2006 @. midnight through 2/23/2006 @. midnight)

sql

Can't Select Data Mining Technique....Cause: Issue with Firewall Software

Colleagues:

I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.

All suggestions appreciated - very much looking forward to working with the technology.

Michael (michael.dataSense@.sympatico.ca)

We have Zone Alarm Pro installed on all our development boxes. When ZAP is shut down, the problem goes away. ZAP allows you to set access permissions on a machine for exe and dll files. As there are hundreds of interdependencies within the SQL Server system and the .NET Framework, I'm not sure exactly where the access issue is.

I'd be grateful for any suggestions, and I will follow up with Zone Labs.

Michael (michael.dataSense@.sympatico.ca)

|||

Hi Michael,

As far as I know, the firewalls track Internet access per process; my recommendation is to grant permission to the following EXEs: devenv.exe, sqlwb.exe and msmdsrv.exe.

On the server computer, for de default instance, you need to open TCP port 2383, unless you have customized that. If you have multiple instances, please take a look at a file named “msmdredir.ini” (most of the time under c:\Program Files\Microsoft SQL Server\90\Shared\ASConfig) – the section “Instances” enumerates what port is assigned to each instance. However, keep in mind that those assignments are random and may change if the service restarts.

Hope this helps,

--

Raymond [MSFT]

Can't Select Data Mining Technique....Cause: Issue with Firewall Software

Colleagues:

I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.

All suggestions appreciated - very much looking forward to working with the technology.

Michael (michael.dataSense@.sympatico.ca)

We have Zone Alarm Pro installed on all our development boxes. When ZAP is shut down, the problem goes away. ZAP allows you to set access permissions on a machine for exe and dll files. As there are hundreds of interdependencies within the SQL Server system and the .NET Framework, I'm not sure exactly where the access issue is.

I'd be grateful for any suggestions, and I will follow up with Zone Labs.

Michael (michael.dataSense@.sympatico.ca)

|||

Hi Michael,

As far as I know, the firewalls track Internet access per process; my recommendation is to grant permission to the following EXEs: devenv.exe, sqlwb.exe and msmdsrv.exe.

On the server computer, for de default instance, you need to open TCP port 2383, unless you have customized that. If you have multiple instances, please take a look at a file named “msmdredir.ini” (most of the time under c:\Program Files\Microsoft SQL Server\90\Shared\ASConfig) – the section “Instances” enumerates what port is assigned to each instance. However, keep in mind that those assignments are random and may change if the service restarts.

Hope this helps,

--

Raymond [MSFT]

Can't Select Data Mining Technique....

Colleagues:

I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.

All suggestions appreciated - very much looking forward to working with the technology.

Michael (michael.dataSense@.sympatico.ca)

We have Zone Alarm Pro installed on all our development boxes. When ZAP is shut down, the problem goes away. ZAP allows you to set access permissions on a machine for exe and dll files. As there are hundreds of interdependencies within the SQL Server system and the .NET Framework, I'm not sure exactly where the access issue is.

I'd be grateful for any suggestions, and I will follow up with Zone Labs.

Michael (michael.dataSense@.sympatico.ca)

|||

Hi Michael,

As far as I know, the firewalls track Internet access per process; my recommendation is to grant permission to the following EXEs: devenv.exe, sqlwb.exe and msmdsrv.exe.

On the server computer, for de default instance, you need to open TCP port 2383, unless you have customized that. If you have multiple instances, please take a look at a file named “msmdredir.ini” (most of the time under c:\Program Files\Microsoft SQL Server\90\Shared\ASConfig) – the section “Instances” enumerates what port is assigned to each instance. However, keep in mind that those assignments are random and may change if the service restarts.

Hope this helps,

--

Raymond [MSFT]

Can't select a datetime field in a query without time portion

Hi,
I am doing some project with SQL Server 2000 and C#. I am using a
SELECT statement to get a column and to bind it to a dropdown list. The
column is a datetime format. I don't need time portion of it. Before I
tried any CONVERT functions my query looked like this:
SELECT DISTINCT DateEntered FROM TBL_PTM_Report
I can get the data this way but only with time portion in it. I read
about CONVERT but can't get it working. I am getting all kinds of error
messages from Visual Studio.
I tried the lowwoling:
SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
It didn't work. I need data in the following format MM/DD/YYYY
Thank you,
Stan> SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
CONVERT.
AMB
"suslishe@.mail.ru" wrote:

> Hi,
> I am doing some project with SQL Server 2000 and C#. I am using a
> SELECT statement to get a column and to bind it to a dropdown list. The
> column is a datetime format. I don't need time portion of it. Before I
> tried any CONVERT functions my query looked like this:
> SELECT DISTINCT DateEntered FROM TBL_PTM_Report
> I can get the data this way but only with time portion in it. I read
> about CONVERT but can't get it working. I am getting all kinds of error
> messages from Visual Studio.
> I tried the lowwoling:
>
> SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
> It didn't work. I need data in the following format MM/DD/YYYY
> Thank you,
> Stan
>|||This was my typing mistake, sorry! The statement looks like this:
SELECT CONVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report|||I just ran this in query analyser and everything works! But Visual
Studio gives me an error.

Cant seem to make this query work

Hi,

I have a query thatI need to make into one query for the sake of an application reading only one cursor.

Here's the syntax:

select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

The error message i'm recieving is the following:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas? any suggestions would be great.

Thanks

This error came from the only subquery in your query (which produces the New column). You're trying to return a result set 'distinct(x.amount)' and then use it as an expression, which is not permitted. Because SQL can not predict how many rows will be returned by the subquery, and there is no relationship between the column New by the subquery and other columns in the whole query, thus SQL can't join theNew column to the whole result set as other columns. In this case, you should either make the subquery return a single value:

select (select max(distinct(x.amount)) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

or move the tables referenced by the subquery and find join conditions with other tables, then returned a column data which satisfies the join conditions, rather than using subquery.

|||

Hi when I run the query I get the following error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

Is there a substitute for the = operator I can use to get this to parse correctly?

Thanks,

|||

Strange What's the query you're using? Are you sure you add a MAX to make the subquery return single value?

selectmax(distinct(x.amount))

cant see server

Hi,
Twice this has happened....
WinXP Pro SP2. SQL server 2000
When I have used a third party app (.net tool) to select my sql box from the
"available list" it pauses for a while and the drop down list is empty.
I manually type the name of the server in and it goes ahead just fine.
The same happened when I just tried to create a DSN via the MS Access upsize
wizard.

Why can't 3rd parties see the server instance?

JayIf your are using TCP. Sounds like WINS is working but something is
blocking UDP port 1434.

Could be a router, firewall, XP SP2's built in firewall blocks this...

"Jay" <nospam@.*here.com> wrote in message
news:WqUsd.22789$9A.392629@.news.xtra.co.nz...
> Hi,
> Twice this has happened....
> WinXP Pro SP2. SQL server 2000
> When I have used a third party app (.net tool) to select my sql box from
> the "available list" it pauses for a while and the drop down list is
> empty.
> I manually type the name of the server in and it goes ahead just fine.
> The same happened when I just tried to create a DSN via the MS Access
> upsize wizard.
> Why can't 3rd parties see the server instance?
> Jay|||Hi Danny,
More info...

Looked at network config and it says that named pipes and tcp are the
enabled protocols.
I don't use the XP SP2 firewall. I use Kerio.
I've checked the kerio app settings and SQL is set to "ask" as opposed to
permit or deny (so I'd expect a Kerio dialogue if anything was trying?)
The server installation (in EM) is listed simply as (local).

New DSN and the server drop down box is empty but, get this!, if I type in a
period "." and click next it goes ahead no problem and the connection tests
ok!

Jay

"Danny" <istdrs@.flash.net> wrote in message
news:mzYsd.29107$Rf1.16651@.newssvr19.news.prodigy. com...
> If your are using TCP. Sounds like WINS is working but something is
> blocking UDP port 1434.
> Could be a router, firewall, XP SP2's built in firewall blocks this...
>
> "Jay" <nospam@.*here.com> wrote in message
> news:WqUsd.22789$9A.392629@.news.xtra.co.nz...
>> Hi,
>> Twice this has happened....
>> WinXP Pro SP2. SQL server 2000
>> When I have used a third party app (.net tool) to select my sql box from
>> the "available list" it pauses for a while and the drop down list is
>> empty.
>> I manually type the name of the server in and it goes ahead just fine.
>> The same happened when I just tried to create a DSN via the MS Access
>> upsize wizard.
>>
>> Why can't 3rd parties see the server instance?
>>
>> Jay
>>

can't resolve sortingconflict for equel to

Hi,
I'm trying create a simple select statment with a join in the tempdb.
However i'm getting errors which says that it can't resolve a
sortingconflict in the equal to line.
Here's my select statement, maybe someone can see the problem.
use tempdb
select p.name, ad.adress
from test.dbo.persons p
inner join adresses ad
on p.id = ad.id
----
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.comHi
The TempDb collation is different to the DB's one.
Look at the index keyword "collations, mixed collation environments" in BOL.
Has some great information there.
Regards
Mike
"Jason" wrote:

> Hi,
> I'm trying create a simple select statment with a join in the tempdb.
> However i'm getting errors which says that it can't resolve a
> sortingconflict in the equal to line.
> Here's my select statement, maybe someone can see the problem.
> use tempdb
> select p.name, ad.adress
> from test.dbo.persons p
> inner join adresses ad
> on p.id = ad.id
>
> --
>
> ----
> This mailbox protected from junk email by MailFrontier Desktop
> from MailFrontier, Inc. http://info.mailfrontier.com
>
>

Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END

cant login into lcoal when installing time tracker starter kit..

During the istall of the issue tracker starter kit, im prompted ti select the type of DB. If I select MSACCESS, all is good

I have " Microsoft SQL Server 2005 Express Edition with Advanced Services" installed

If I select Microsoft SQL Server/MSDE 2000 I get the following

1) Sql Server ( I select local)

2) connect using

a) Windows authentication - WhenI do this I get the error message "Login Failure: could not loginto server (local)"

b) SQL Server authtication - WhenI do this I get the error message "Login Failure: could not loginto server (local)"

Please Please help me. I have to get this going or I'm mud !!! actually , less than mud

The error message seems to be customized and you may need to find the original exception that came from .NET. However if you haven't enabled remote connections for the SQL Express instance, try to enable this option in SQL Server Surface Area Configuration.

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve
the HostName, Login and ProgramName from the results. This will give you vi
tal clues as to what the culprit is. eg A service account on a particular wo
rkstation or server running
say a .NET application or component. If you can't locate the "application"
in question take this information to one of your network administrators (who
should be able to solve this one for you).

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve the HostName, Login and ProgramName from the results. This will give you vital clues as to what the culprit is. eg A service account on a particular workstation or server running say a .NET application or component. If you can't locate the "application" in question take this information to one of your network administrators (who should be able to solve this one for you).

Cant insert and delete

Hi,

Can anyone help me? I am using MSDE 2000 with VS.NET. I can SELECT from the database but i cant insert and delete and update. Below are my code

public static int Add(string tmpSql, string connectionString)

{

oleConn = new OleDbConnection(connectionString);

oleConn.Open();

oleTran = oleConn.BeginTransaction();

try

{

oleCom = new OleDbCommand(tmpSql, oleConn);

oleCom.Transaction = oleTran;

int a =oleCom.ExecuteNonQuery();

return(0);

}

catch

{

oleTran.Rollback();

return(1);

}

finally

{

oleConn.Close();

}

}

my int a give a value of 1 but when i look at the database the data is not inserted? but my primary key of the table did increase by one. what went wrong? is it due to my configuration which dun allow my to insert and update? help. Thank you

Please some1 pls help me or send me any useful link and i will figure out my self thanks..

|||Is it possible that you need to explicitly commit your transaction inside your try block before returning? In the example code on MSDN at the link below the transaction is explicitly committed.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbconnectionclassbegintransactiontopic.asp

Hope this helps,
Vaughn|||oh ya how can i forget that.. thanks alot..

Can't get this select join to work...

I get an error 'Multi-part identifier '#tmp1' could not be bound.
select #tmp1.col1 as mycol,
other columns...
from #tmp1, realtable
left join table2 on table2.id = #tmp1.col1
...
where realtable.id = table2.realtable
If I reverse the order in the from statement everything is ok but my results
don't seem right.
This statement did work in MySql.
Thanks,
JoeNot a clue what you are trying to do, but you have to realize that this is
not really a good idea, even in MySQL. What version of SQL Server. I
execute the following batch:
drop table #tmp1
drop table table2
drop table realtable
go
create table #tmp1
(
col1 int
)
create table realtable
(
id int
)
create table table2
(
realtable int
)
go
select #tmp1.col1 as mycol
from #tmp1,
realtable
left join table2
on table2.id = #tmp1.col1
where realtable.id = table2.realtable
and get:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#tmp1' does not match with a table name or alias name
used in the query.
You are cross joining #tmp1 with the results of realtable left joined with
table2, but your join criteria references the table you are cross joining
to. I don't think this even should be allowed. Change the comma to a cross
join and it will compile, though I am not sure you will get the correct
results.
select #tmp1.col1 as mycol
from #tmp1 cross join
realtable
left join table2
on table2.id = #tmp1.col1
where realtable.id = table2.realtable
Can you explain why the other criteria is in the where clause? I would have
expected:
select #tmp1.col1 as mycol,
other columns...
from #tmp1,
inner join realtable
on realtable.id = table2.realtable:
left outer join table2
on table2.id = #tmp1.col1
Though this may not be what you want, and it may be exactly what you are
doing anyhow, base on where you are doing the different joins.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e9CVgK2AFHA.1452@.TK2MSFTNGP11.phx.gbl...
>I get an error 'Multi-part identifier '#tmp1' could not be bound.
> select #tmp1.col1 as mycol,
> other columns...
> from #tmp1, realtable
> left join table2 on table2.id = #tmp1.col1
> ...
> where realtable.id = table2.realtable
> If I reverse the order in the from statement everything is ok but my
> results
> don't seem right.
> This statement did work in MySql.
> Thanks,
> Joe
>

Cant get SProc to work

Hi

I can't seem to get this procedure to work correctly. Here's my code:

DECLARE @.PropertyDetails nvarchar (6)
Select @.PropertyDescription = PropertyDescription from Property where
ApplicationID = @.applicationid
If @.PropertyDescription is not null or @.PropertyDescription <> ''
Begin
If (select isnumeric(PropertyDescription) from Property where ApplicationID =
@.applicationid) = 1
INSERT INTO #errors VALUES (1410,@.ApplicationID, 0, 'Y')
ELSE
INSERT INTO #errors values (1410, @.ApplicationID, 0, 'N')
End

I am trying to bring up an error advising users not to capture alphabets in a
field called Property Description.
I need to bring up the error from the #ERRORS table with the rule number 1410.

My Syntax checks successful, but my error does not come up for the users. Am
I missing something?

Thanks for any help at all, guys.
Driesen Pillay

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200508/1If PropertyDescription should only have numbers, then why is it a
character data type? What do you consider "numeric" to mean - what
digits/characters are allowed? You'll probably have to give some more
details of what you're trying to do and what your data looks like to
get a good answer - can you post a short script which others can
actually execute themselves?

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Hi Simon

Sorry about that. Even though I only want numbers in this field, I don't want
to stop the user from inputing alphabets (management red tape). Eg: If the
user enters "ERF 1234". I need to raise the error "Remove aplhabets". We have
an HTML interface with SQL running the actual background programming, so
unfortunately I can't supply you with a script. I know I didn't give you much
to go on, so I might scrap this rule, because it doesn't really make sense to
have it there. What would you recomend? Is this a waste of time?

Thanks for the help.
Driesen

Simon Hayes wrote:
>If PropertyDescription should only have numbers, then why is it a
>character data type? What do you consider "numeric" to mean - what
>digits/characters are allowed? You'll probably have to give some more
>details of what you're trying to do and what your data looks like to
>get a good answer - can you post a short script which others can
>actually execute themselves?
>http://www.aspfaq.com/etiquette.asp?id=5006
>Simon

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200508/1|||In general, this type of input validation would belong in the front
end, not the database; the database would have an integer column, and
the front end would make sure that only integer data is passed to it.

The problem with your current solution is that you have to load the
data into the database, check it, populate an error table, present the
contents of the error table to the user, wait for the user to enter new
values, and then repeat the process until the user gets it right. This
seems to be a lot of work compared to a simple input mask in your user
interface. Assuming that by "HTML interface" you mean ASP, PHP or
something similar, then this should be fairly easy.

But I appreciate that I don't know all the details of your application
or your environment, so you might try something like the code below
(untested).

Simon

declare @.PropertyDescription nvarchar (6)

select @.PropertyDescription = PropertyDescription
from dbo.Property
where ApplicationID = @.Applicationid

if coalesce(@.PropertyDescription, '') <> ''
and exists (
select *
from dbo.Property
where ApplicationID = @.ApplicationID
-- find non-numeric characters
and rtrim(ltrim(PropertyDescription)) not like '%[^0-9]%'
)
INSERT INTO #errors VALUES (1410, @.ApplicationID, 0, 'N')
ELSE
INSERT INTO #errors values (1410, @.ApplicationID, 0, 'Y')
End|||Oops - I think I put 'N' and 'Y' the wrong way round, but you should
see that from your data.

Simon|||Thanks very much for the help, Simon. I will give that a try.
Thanks again.

Driesen

--
Message posted via http://www.sqlmonster.com