analysis services 2000 (data mining)

i create a model mining

CREATE MINING MODEL [a'S]
( [Cusid] LONG KEY ,
[Orderdetails] TABLE PREDICT ([Productid] LONG KEY )
) USING Microsoft_Decision_Trees

i can show for customer some products other when he choise a product.

ect: he choise A, i show he B, C, D ....

but for that, i must know his cusid.

now i want show B,C,D when one man choise A ( i don't know his cusid). how i do that.

( i'm using sql server and analysis service 2000)

You don't need to supply the customer id for prediction. The customer ID is only ysed to identify records and has no impact on the patterns in the model.|||

i can't do.

i use select query:

SELECT FLATTENED
[t1].[cusid],TopCount( Predict(.[Orderdetails], INCLUDE_STATISTICS, EXCLUSIVE), $ADJUSTEDPROBABILITY, 3)
FROM

PREDICTION JOIN
SHAPE
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid" FROM "customers" ORDER BY "cusid"'
)

}
APPEND
(
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid_1", "productid" AS "Productid" FROM "vwAllAgeSex" ORDER BY "cusid"'
)
}
RELATE [Cusid] TO [Cusid_1]
)
AS [Orderdetails]
AS [T1]
ON
.[Cusid] = [T1].[Cusid] AND
.[Orderdetails].[Productid] = [T1].[Orderdetails].[Productid]

the result:

cusid productid $support $adjustedprobability

1 12 119 0.92

1 320 40 0.34

1 300 23 0.31

3 12 54 0.76

3 19 54 0.76

.......

so when i select product with productid = 12. how i show other relation products

( examples: when i choose a t-shirt product, i want show relation products: shoes, jeans trousers.... )

sql

Analysis Services

How can you process multiple Analysis Services databases against the same SQL Server database without receiving table locks and the processing fails.How about one by one database using some program? There is a command line tool available to execute XML/A requests.|||

Dear,

We can create a DTS Package that will process all the Analysis Databases one by one.

HTH

from

sufian

Analysis Service: filter fact table ?

I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ...
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thankscreate view, and for fact table in AS point at the view.|||create view, and for fact table in AS point at the view.

analysis manager question - cube

Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to
be build.Create the views with the same level of data grain and build dimen
sions.
Once the dimensions and facts table are joined together, process the cube wi
th the "Full Process" option. For the new records to be appeared in the cube
, go for "Incremental update" or "Refresh Cube".
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

analysis manager question - cube

Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!
Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to be build.Create the views with the same level of data grain and build dimensions.
Once the dimensions and facts table are joined together, process the cube with the "Full Process" option. For the new records to be appeared in the cube, go for "Incremental update" or "Refresh Cube".
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

an unexpected error occured during this operation

Hi

when i right click table and click design table then error occured
(an unexpected error occured during this operation)
If any one knows please let let me know your help would be appreciated .

thanks
pardhi

--
Message posted via http://www.sqlmonster.comRight click using what tool? I am guessing EM, if so, then try closing
EM and getting back in. I have run into a couple of problems that went
away when I re-started EM.

Knowing what you had done just prior to this might also be helpful as
would the database version: 7, 2000.

HTH -- Mark D Powell --

an sql command that doest work in a page

Hello,

I have a sequense of sql commands in order to recursively update a table that has parents/childs

After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining

Here is the command:

Dim

InsertConnectionAs Data.SqlClient.SqlConnection =New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")Dim SqlInsertCommandAs Data.SqlClient.SqlCommand =New Data.SqlClient.SqlCommand("while @.@.rowcount > 0 " _

&

"begin INSERT INTO submenu" _

& uid &

" (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _

&

" i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _

& uid &

" i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _

&

"end", InsertConnection)

InsertConnection.Open()

SqlInsertCommand.ExecuteNonQuery()

InsertConnection.Close()

SqlInsertCommand =

Nothing

If I insert any other SQLcommand there it is excecuted normally.

The command I have is excecuted fine using sql server manager.

Is there any way that a command is excecuted in the SQL manager but not in a page...??

Any ideas would be great...

Thank you

Hello my friend,

I would not use @.@.rowcount outside of Enterprise Manager. Could you describe your database structure and what you are trying to insert. No need to send vb code, just the SQL or some comments on the steps and I can send you the correct SQL that will work from wherever it is used.

Kind regards

Scotty

|||

You use @.@.RowCount in first line of your query but this returns number of rows affected by last select statement in current SQL thread, but your thread is starting so it returns always 0 so your loop is never executed.

You should populate your temporary table in the same select statement to work correctly. The best solution is to create SQL stored procedure which will do all your work at one shot if you can do it.

Thanks

JPazgier

|||

Hi,

The software is a sitebuilder. The particular table holds the page stucrure of each site.

The table is this one:

----

pageid int identify

siteid int

pagename nvarchar(200)

parentid int

----

I need to update / delete all of the tree when the user wants to update or delete a top element. The number of levels is not limited.

I managed to do it, using a variable. The "problem" is that I set it to 1000 times. So if someone has more than 1000 pages under the parent, if will fail. And it's not right in the first place.

The thing is, that this worked fine when I was on an other server that used MS SQL 2000. I didn;t find any differences searching the web from 2000 to 2005

jpazgier, why does it work then when I excecute it using SQL manager..? Isn't this weird? I mean, if the rowcount is 0 from the beggining in the application, should't it be 0 in the SQL manager too?

Thank you

|||

Hello my friend,

I realize now what you are trying to do and I have the answer for you and this will work no matter how many levels you have (no 1000 limit). Run the following SQL, but change tblTree to the name of your table (I did not know what you have called it): -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

Now to get all child IDs of page 1 (either direct children of 1, and also children of ones that are children of 1, and so on) I run the following: -

select PageID from dbo.fnGetPages(1)

To delete the page and all of its children I run the following 2 commands: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(1))

DELETE FROM tblTree WHERE PageID = 1

Kind regards

Scotty

|||

Thanks Scotty,

The code to create the function is only run once right?

Then I just select update or do whatever I need to do using the function right?

Thanks

|||

Yes that is correct. You only run the function SQL once. You only need to run this again if you decide to use this functionality within a new database.

You just need to run the commands that use the function and you should be fine.

Kind regards

Scotty

an OpenRowset little doubt

Hi all.

Seeing that the old system table syslang is no longer updateable(Not even in DAC) i decided to take a look at the definition of it, just to see where the languages are stored.(out of curiosity)

Select Object_Definition(Object_Id('SysLanguages'))

and it returns

Select .......

FROM OpenRowset(TABLE SYSLANG)

The odd thing is I tried to run that query but it gives me an error telling an incorrect sintax near TABLE.

so, the question is if does anybody knows why i can't run the same query that the object_definition returned?

any comments are most apreciated

This syntax works only in mssqlsystemresource database context.|||

Thanks a lot! i copy and attached the mssqlsystemresource as an standar database and it worked out.

Thanks for your help!

An internal error occurred on the report server - subreport - tabl

Judging from the posts, it seems I'm not the only one having this problem. I
too get this error when I put my subreport in a particulart table grouping.
It seems to work fine in other places on the report (though I haven't tried
them all). NoRows doesn't work at all in 2005 from what I can tell and that
may be part of the problem, but even when I run the report where this is
always data in the subreport it crashes. Also, like some, it doesn't happen
for me until I try to navigate in the report. Even scrolling causes it. I
suppose that the error log doesn't exist from what I've read here. Has
anyone found anything on this issue? Any work-arounds?
-- Thanks in advance,
Mike Yeager
MikeYeager.comPS This is a client side report (rdlc) and it's not defined or published in
SQL Server.
--
Mike Yeager

An interesting Qn

Hi,
My table has the following structure
create table CompanyRights (
CompanyID int, RightID int )
Insert into CompanyRights select 1,1
Insert into CompanyRights select 1,2
Insert into CompanyRights select 1,3
Insert into CompanyRights select 2,1
Insert into CompanyRights select 2,1
and i want to choose all the companies having rights 1 and 2
select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
but this didn't work
Regards
LaraIs it possible without a join
With Join I have the answer
SELECT t1.CompanyID,T1.RightID
FROM CompanyRights t1
INNER join CompanyRights t2
ON t1.CompanyID =t2.CompanyID
WHERE t1.RightID = 1 AND t2.RightID = 2
regards Lara|||select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
The key RightID cant be 1 AND 2 at the same time, this must be
select * from CompanyRights where CompanyID = 1 And (RightID = 1 OR RightID
= 2)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:O$6pQLaSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Sure,
Select * From CompanyRights
Where RightID In (1,2)
-- which is same as
Select * From CompanyRights
Where RightID = 1 Or RightID = 2
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Try,
SELECT
CompanyID
FROM
CompanyRights
WHERE
RightID = 1
or RightID = 2
group by
CompanyID
having
count(distinct RightID) = 2;
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
AMB
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Select Distinct CompanyID
From CompanyRights R
Where Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 1)
And Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 2)
"Lara" wrote:

> Hi,
> My table has the following structure
> create table CompanyRights (
> CompanyID int, RightID int )
> Insert into CompanyRights select 1,1
> Insert into CompanyRights select 1,2
> Insert into CompanyRights select 1,3
> Insert into CompanyRights select 2,1
> Insert into CompanyRights select 2,1
> and i want to choose all the companies having rights 1 and 2
> select * from CompanyRights where CompanyID = 1 And RightID = 1 AND Right
ID
> = 2
> but this didn't work
> Regards
> Lara
>
>

an interesting problem

I manage a news database oriented website where there is a column named
'news' in a table 'news' storing huge HTML formatted artciles. I had to
replace a particular word 'india' by 'world' in all the records under this
column. Strange thing is the occurances of the word 'india' towards the end
of the articles are getting recognized by
select count(*) from news where charindex('india',news)>0
i.e. the above query results 0. Though there are thousands of records in
the column 'news' containing the word 'india'.
I think the issue is of max limit of charindex.
Please help at the earliest convenience of yours.
Regards,
John-I think the issue is of max limit of charindex.
No I don=B4t think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.|||John
Did you try LIKE operator?
"John" <netconsul@.gmail.com> wrote in message
news:ePx4t626FHA.1140@.tk2msftngp13.phx.gbl...
>I manage a news database oriented website where there is a column named
>'news' in a table 'news' storing huge HTML formatted artciles. I had to
>replace a particular word 'india' by 'world' in all the records under this
>column. Strange thing is the occurances of the word 'india' towards the end
>of the articles are getting recognized by
> select count(*) from news where charindex('india',news)>0
>
> i.e. the above query results 0. Though there are thousands of records in
> the column 'news' containing the word 'india'.
> I think the issue is of max limit of charindex.
> Please help at the earliest convenience of yours.
> Regards,
> John
>
>
>|||Thats what my problem is.
select news from news where news like '%india%'
results in 74000+ records while
select count(*) from news where charindex('india',news)>0
returns none.
I am using charindex to find the position of India in the record and use
'updatetext' to replace it by world.
because charindex('india',news) returns 0, updatetext cant replace india in
each record by world.
Any quick help will be appreciated.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132230042.794270.99970@.g43g2000cwa.googlegroups.com...
-I think the issue is of max limit of charindex.
No I don´t think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.|||Hi Everyone,
Solution to my problem lies here.
http://www.aspfaq.com/show.asp?id=2445
Please note that my fear about charindex is correct.
Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies. :-)
Thanks
John
"John" <netconsul@.gmail.com> wrote in message
news:eT7LVE66FHA.4076@.TK2MSFTNGP10.phx.gbl...
> Thats what my problem is.
> select news from news where news like '%india%'
> results in 74000+ records while
> select count(*) from news where charindex('india',news)>0
>
> returns none.
> I am using charindex to find the position of India in the record and use
> 'updatetext' to replace it by world.
> because charindex('india',news) returns 0, updatetext cant replace india
> in each record by world.
>
> Any quick help will be appreciated.
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1132230042.794270.99970@.g43g2000cwa.googlegroups.com...
> -I think the issue is of max limit of charindex.
> No I don´t think so, because Charindex returns an INT, so there should
> be a problem if the character would be found at the
> 2,147,483,648 +1. So are you searching on the right table and database
> ? That should return a hit, if the word india is present in there.
> HTH, jens Suessmeyer.
>

an interesting problem

I manage a news database oriented website where there is a column named
'news' in a table 'news' storing huge HTML formatted artciles. I had to
replace a particular word 'india' by 'world' in all the records under this
column. Strange thing is the occurances of the word 'india' towards the end
of the articles are getting recognized by
select count(*) from news where charindex('india',news)>0
i.e. the above query results 0. Though there are thousands of records in
the column 'news' containing the word 'india'.
I think the issue is of max limit of charindex.
Please help at the earliest convenience of yours.
Regards,
John
-I think the issue is of max limit of charindex.
No I don=B4t think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.
|||John
Did you try LIKE operator?
"John" <netconsul@.gmail.com> wrote in message
news:ePx4t626FHA.1140@.tk2msftngp13.phx.gbl...
>I manage a news database oriented website where there is a column named
>'news' in a table 'news' storing huge HTML formatted artciles. I had to
>replace a particular word 'india' by 'world' in all the records under this
>column. Strange thing is the occurances of the word 'india' towards the end
>of the articles are getting recognized by
> select count(*) from news where charindex('india',news)>0
>
> i.e. the above query results 0. Though there are thousands of records in
> the column 'news' containing the word 'india'.
> I think the issue is of max limit of charindex.
> Please help at the earliest convenience of yours.
> Regards,
> John
>
>
>
|||Thats what my problem is.
select news from news where news like '%india%'
results in 74000+ records while
select count(*) from news where charindex('india',news)>0
returns none.
I am using charindex to find the position of India in the record and use
'updatetext' to replace it by world.
because charindex('india',news) returns 0, updatetext cant replace india in
each record by world.
Any quick help will be appreciated.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132230042.794270.99970@.g43g2000cwa.googlegro ups.com...
-I think the issue is of max limit of charindex.
No I dont think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.
|||Hi Everyone,
Solution to my problem lies here.
http://www.aspfaq.com/show.asp?id=2445
Please note that my fear about charindex is correct.
Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies. :-)
Thanks
John
"John" <netconsul@.gmail.com> wrote in message
news:eT7LVE66FHA.4076@.TK2MSFTNGP10.phx.gbl...
> Thats what my problem is.
> select news from news where news like '%india%'
> results in 74000+ records while
> select count(*) from news where charindex('india',news)>0
>
> returns none.
> I am using charindex to find the position of India in the record and use
> 'updatetext' to replace it by world.
> because charindex('india',news) returns 0, updatetext cant replace india
> in each record by world.
>
> Any quick help will be appreciated.
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1132230042.794270.99970@.g43g2000cwa.googlegro ups.com...
> -I think the issue is of max limit of charindex.
> No I dont think so, because Charindex returns an INT, so there should
> be a problem if the character would be found at the
> 2,147,483,648 +1. So are you searching on the right table and database
> ? That should return a hit, if the word india is present in there.
> HTH, jens Suessmeyer.
>

an interesting problem

I manage a news database oriented website where there is a column named
'news' in a table 'news' storing huge HTML formatted artciles. I had to
replace a particular word 'india' by 'world' in all the records under this
column. Strange thing is the occurances of the word 'india' towards the end
of the articles are getting recognized by
select count(*) from news where charindex('india',news)>0
i.e. the above query results 0. Though there are thousands of records in
the column 'news' containing the word 'india'.
I think the issue is of max limit of charindex.
Please help at the earliest convenience of yours.
Regards,
John-I think the issue is of max limit of charindex.
No I don=B4t think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.|||John
Did you try LIKE operator?
"John" <netconsul@.gmail.com> wrote in message
news:ePx4t626FHA.1140@.tk2msftngp13.phx.gbl...
>I manage a news database oriented website where there is a column named
>'news' in a table 'news' storing huge HTML formatted artciles. I had to
>replace a particular word 'india' by 'world' in all the records under this
>column. Strange thing is the occurances of the word 'india' towards the end
>of the articles are getting recognized by
> select count(*) from news where charindex('india',news)>0
>
> i.e. the above query results 0. Though there are thousands of records in
> the column 'news' containing the word 'india'.
> I think the issue is of max limit of charindex.
> Please help at the earliest convenience of yours.
> Regards,
> John
>
>
>|||Thats what my problem is.
select news from news where news like '%india%'
results in 74000+ records while
select count(*) from news where charindex('india',news)>0
returns none.
I am using charindex to find the position of India in the record and use
'updatetext' to replace it by world.
because charindex('india',news) returns 0, updatetext cant replace india in
each record by world.
Any quick help will be appreciated.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132230042.794270.99970@.g43g2000cwa.googlegroups.com...
-I think the issue is of max limit of charindex.
No I dont think so, because Charindex returns an INT, so there should
be a problem if the character would be found at the
2,147,483,648 +1. So are you searching on the right table and database
? That should return a hit, if the word india is present in there.
HTH, jens Suessmeyer.|||Hi Everyone,
Solution to my problem lies here.
http://www.aspfaq.com/show.asp?id=2445
Please note that my fear about charindex is correct.
Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies. :-)
Thanks
John
"John" <netconsul@.gmail.com> wrote in message
news:eT7LVE66FHA.4076@.TK2MSFTNGP10.phx.gbl...
> Thats what my problem is.
> select news from news where news like '%india%'
> results in 74000+ records while
> select count(*) from news where charindex('india',news)>0
>
> returns none.
> I am using charindex to find the position of India in the record and use
> 'updatetext' to replace it by world.
> because charindex('india',news) returns 0, updatetext cant replace india
> in each record by world.
>
> Any quick help will be appreciated.
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1132230042.794270.99970@.g43g2000cwa.googlegroups.com...
> -I think the issue is of max limit of charindex.
> No I dont think so, because Charindex returns an INT, so there should
> be a problem if the character would be found at the
> 2,147,483,648 +1. So are you searching on the right table and database
> ? That should return a hit, if the word india is present in there.
> HTH, jens Suessmeyer.
>

An interesting issue with adding columns.

I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.

Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.

Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].

This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version

------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion

You'd have to post some code snipets though on what you are doing.

I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'

select colid,* from syscolumns where object_name(id)='wcsub' order by colid

Both will return the following in the results:

DOHFILING varchar 1 48
FORMTYPE varchar 2 53

(from sysobjects)
48 DOHFILING
53 FORMTYPE

Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion

I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?

EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.

I don't understand, I would have a script that would run all the sql and apply the changes.

What kind of development lifecycle do you use?

And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"

If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?

It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc

Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.

Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.

Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.

The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!

Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.

Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.

I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.

I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.

If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.

I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.

-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?

Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.

I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.

Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.

If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.

Plase help me to understand.

-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.

Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.

So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?

I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.

I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.

Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.

Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.

We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration

however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"

that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.

Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.

Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?

That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!

:)

an interesting bug for SSIS

I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!

How can this happen?

I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.

Thanks.

Please provide more details about the package and the ODBCsource. When you say 'Time' does that mean the date is no part it?|||Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.|||I have no idea what a D3 database is; in my case I had to deal with an old PErvasive DB that had a similar case. It had separate columns/data type for the date and the time. I solved it by within the SQL query I use to extract the data. There was a native sql function that allowed me to 'concatenate' the date and time columns into a single column; then the data reader component could see it as a dateTime data type. Conclussion: try to resolve it right on the query.|||

Thanks.

I don't want to "concatenate" the date and time column and that table doesn't has date column at all!

The problem is table created by that package is with wrong data type. But DTS package in SQL 2000 created table properly. This is not about setting the query to select the data.

Can you show me how the query looks like, please?

|||

Qiuwei wrote:

Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.

This is most likely the provider stack. Are you using a different provider to what you used in DTS?

-Jamie

|||In my SQL2000 DTS package, I use "Other(ODBC Data Source)"

Thanks.|||

Which ODBC provider?

It could be that the dotnet provider for ODBC doesn't play very well with whatever ODBC provider you have. You may want to look into getting a different ODBC driver.

-Jamie

|||

Thanks.

Sorry for the late response (toob busy these days).

To get around this problem, we make the source data (time) as varchar type and imported as nvarchar.

Question is that why SQL server doesn't have "Time" data type which is quite useful?

Cheers.

|||

Qiuwei wrote:

Question is that why SQL server doesn't have "Time" data type which is quite useful?

many many people ask the same question. Which is why it (plus some other new things around dates and times) will be coming to a version of SQL Server near you very soon.

-Jamie

|||

That will be great!

Thanks.

An Insert Trigger with CDOSYS generated email

Hello,
I've got an insert trigger defined on a table.
Everything seems to work perfectly excpet the body section of my e-mail
message is delivered empty.
Here the relavent code segment...
Begin
DECLARE @.CaseCounter varchar(50)
If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0
SELECT @.CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS varchar(50))
)
SELECT @.body = 'The Case Number is: ' + @.CaseCounter
Set @.vet_email = 'valid@.to.email.address'
exec dbo.sp_send_cdosysmail 'valid@.from.email.address, @.vet_email, 'An
Unlisted PI was submitted with this case', @.body
End
As you can tell from the my trigger code I'm trying to concatenate the
inserted Identity value onto a Character string. As I stated above, the
e-mail arrive fine but the Body section is blank. In case you're wondering,
the @.vet_mail variable is DECLARED earlier in the trigger.
I previously had all variables DECLARE at the top of the code but I still
got the same results.
All suggestions are welcomed!
Thanks
Application Engineer / DBA
UCLA SOM(a) please, please, please... do NOT send e-mail from a trigger!
(b) why don't you look at the base table instead of inserted. inserted is a
virtual table and I think you will find that IDENT_CURRENT() will return
NULL (which, when concatenated to your @.body value, makes the whole
parameter NULL). To prove it, try:
SET @.body = 'The Case Number is: ' + COALESCE(@.CaseCounter, 'NULL');
"Marcial" <no_spam@.antispammer.com> wrote in message
news:1B796F05-D8E3-4F0A-AA9F-A1859F94FE58@.microsoft.com...
> Hello,
> I've got an insert trigger defined on a table.
> Everything seems to work perfectly excpet the body section of my e-mail
> message is delivered empty.
> Here the relavent code segment...
> Begin
> DECLARE @.CaseCounter varchar(50)
> If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0
> SELECT @.CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS
> varchar(50)))
> SELECT @.body = 'The Case Number is: ' + @.CaseCounter
> Set @.vet_email = 'valid@.to.email.address'
> exec dbo.sp_send_cdosysmail 'valid@.from.email.address, @.vet_email, 'An
> Unlisted PI was submitted with this case', @.body
> End
>
> As you can tell from the my trigger code I'm trying to concatenate the
> inserted Identity value onto a Character string. As I stated above, the
> e-mail arrive fine but the Body section is blank. In case you're
> wondering,
> the @.vet_mail variable is DECLARED earlier in the trigger.
> I previously had all variables DECLARE at the top of the code but I still
> got the same results.
> All suggestions are welcomed!
> Thanks
> --
> Application Engineer / DBA
> UCLA SOM|||Hi Marcial,
just to add to Aarons post:
Its REALLY=B2 NOT=B3 recommended to send Emails in a trigger...
WHY
=3D=3D=3D=3D
1=2E Triggers behave synchronously, that means the trigger will block the
current transaction till the whole code in it was executed. So in any
cases that your mail server is unreachable, taking long for
communcation etc. the transaction will be blocked the data / pages /
tables (depending on your locking level) will be blocked and your
application or frontend or whatever another transaction wants to
manipulate the data will be on hold.
2=2ETriggers can cause the transcation to rollback due to a non-business
error. Although when the data and the transaction which is executed is
valid and should be commited to the database, if the sending EMail
procedure will bring back an error and you don=B4t handle it, or it is
of a certain severity which causes the transaction to rollback, your
whole BUSINESS is on hold, only because of sending an email !!!
You don=B4t want that, erh ?
I would suggest (as this is not time critical) to write the data in a
table which is regulary checked for content to be sent.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--|||Thanks Very Much Jens for the expanded explanation. And thanks to Aarron fo
r
the initial Alert anbd Reply.
...You may consider me anevangelized user who has been convinced to find
another way beside triggers to send e-mail. Toward that end might anyone
have an example of Stored Proc code that searches a table and sends e-mail
accordingly.
Cheers~
--
Application Engineer / DBA
UCLA SOM
"Jens" wrote:

> Hi Marcial,
> just to add to Aarons post:
> Its REALLY2 NOT3 recommended to send Emails in a trigger...
> WHY
> ====
> 1. Triggers behave synchronously, that means the trigger will block the
> current transaction till the whole code in it was executed. So in any
> cases that your mail server is unreachable, taking long for
> communcation etc. the transaction will be blocked the data / pages /
> tables (depending on your locking level) will be blocked and your
> application or frontend or whatever another transaction wants to
> manipulate the data will be on hold.
> 2.Triggers can cause the transcation to rollback due to a non-business
> error. Although when the data and the transaction which is executed is
> valid and should be commited to the database, if the sending EMail
> procedure will bring back an error and you don′t handle it, or it is
> of a certain severity which causes the transaction to rollback, your
> whole BUSINESS is on hold, only because of sending an email !!!
> You don′t want that, erh ?
> I would suggest (as this is not time critical) to write the data in a
> table which is regulary checked for content to be sent.
>
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
>|||Hi Marcial,
I thinkk I will write one for you that take as a template. If I don=B4t
come back to the thread please send me a reminder that I will keep
track of that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

An INSERT EXEC statement cannot be nested.

i wanted to store the output of my store proc in a temp table and i wsa doin
d
this:
INSERT #temp EXEC sproc
and it turned out i cannot do this if my sproc has another insert...exec
thing going on within it.
Is there any way i can store the output of my sproc somehow ?
thanks in advanceAbhishek Pandey (AbhishekPandey@.discussions.microsoft.com) writes:
> i wanted to store the output of my store proc in a temp table and i wsa
> doind this: >
> INSERT #temp EXEC sproc
> and it turned out i cannot do this if my sproc has another insert...exec
> thing going on within it.
> Is there any way i can store the output of my sproc somehow ?
Answered in comp.databases.ms-sqlserver. Please to do not post to multiple
newsgroups independently.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

An Full-Text Catalog Item Count of 0?

Hi,
I've enabled a full-text index on a table having 77 thousand records on a
varchar(50) column. And yet when I when I populate the catalog:
exec sp_fulltext_catalog 'OrdersCatalog', 'start_full'
I get an item count,
SELECT fulltextcatalogproperty('OrdersCatalog', 'ItemCount'),
of 0.
This column has data 95% of the time. What am I missing?
Thank you.
--
Mark Holahan
Check the event log and the gatherer logs for messages:
It is likely you are running into one of the errors in these kb articles
http://support.microsoft.com/default...b;en-us;295034
http://support.microsoft.com/default...b;en-us;317746
http://support.microsoft.com/default...b;en-us;295772
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:Ogx2wObHFHA.2860@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've enabled a full-text index on a table having 77 thousand records on a
> varchar(50) column. And yet when I when I populate the catalog:
> exec sp_fulltext_catalog 'OrdersCatalog', 'start_full'
> I get an item count,
> SELECT fulltextcatalogproperty('OrdersCatalog', 'ItemCount'),
> of 0.
> This column has data 95% of the time. What am I missing?
> Thank you.
> --
> --
> Mark Holahan
>
|||Bull's eye! Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ek2TowbHFHA.3880@.TK2MSFTNGP10.phx.gbl...
> Check the event log and the gatherer logs for messages:
> It is likely you are running into one of the errors in these kb articles
> http://support.microsoft.com/default...b;en-us;295034
> http://support.microsoft.com/default...b;en-us;317746
> http://support.microsoft.com/default...b;en-us;295772
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
> news:Ogx2wObHFHA.2860@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Hilary,
I have had a similar problem with the Full-text Catalog Item Count of 0,
after I enable some Full-text Indexing from 4 tables. I can view the
table names on the Full-text Catalog, but when double-clicked the
Catalog, the propereties page showed an Item Count of 0.
Because we are developing a back-end database for a SharePoint Services
server, based on the information from the SharePoint Administration
guide, we needed to use Domain Account, instead of the local System
account, after we saw the Item Count of 0 for our first try.
The big question is after we set up the domain account, the Item Count
was still zero.
Is something wrong with the Full-text Search configuration, or on the
SharePoint Service server?
Thanks,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Is MSSearch running under the local system account? It must be for this to
work.
Are there any error or warning messages in the application log from MSSearch
or MSSCi?
You might want to consult these kb article for more information on resolving
the typical problems you get this MSSearch and SQL Server.
http://support.microsoft.com/default...b;en-us;295034
http://support.microsoft.com/default...b;en-us;317746
http://support.microsoft.com/default...b;en-us;295772
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:ug43i2TIFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi Hilary,
> I have had a similar problem with the Full-text Catalog Item Count of 0,
> after I enable some Full-text Indexing from 4 tables. I can view the
> table names on the Full-text Catalog, but when double-clicked the
> Catalog, the propereties page showed an Item Count of 0.
> Because we are developing a back-end database for a SharePoint Services
> server, based on the information from the SharePoint Administration
> guide, we needed to use Domain Account, instead of the local System
> account, after we saw the Item Count of 0 for our first try.
> The big question is after we set up the domain account, the Item Count
> was still zero.
> Is something wrong with the Full-text Search configuration, or on the
> SharePoint Service server?
> Thanks,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

An explicit value for the identity column in table

for some unknow reasons.. my store proc stop working.. and i got an error..
i have installaed the latest SP4 for SQL server 2000 and still have the problem !
any ideas why ??
Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."
CREATE procedure LCMS_Modules_Add

@.PageID int,
@.ModuleDefID int,
@.Panename nvarchar(32),
@.Title nvarchar(128),
@.Admins nvarchar(256)

as

insert into LCMS_Modules
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)
GO

Have you reordered the columns in your LCMS_Modules table? I amguessing that you did, and that the identity column had previously beenat the end and is now in the beginning or somewhere in the middle.
Specify your column names in your INSERT statement. This is a best practice anyway and should take care of your problem.
insert into LCMS_Modules (column1, column2, column3, ...etc..., column15),
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)

An existing connection was forcibly closed by the remote host

Hi,

I am using ASP.NET 2.0 application to fetch data from a SQL server 2005 using stroed proc.

Stored proc is also using one table from Linked Server (SQL 2000). Linked Server is configured to use login's current security context.

I am getting the following error message

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/15/2006 4:26:30 PM
Event time (UTC): 6/15/2006 8:26:30 PM
Event ID: 988964e6fa5249e38b1bc6c9a5ecd1e4
Event sequence: 850
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/MyApp-1-127948732130920720
Trust level: Full
Application Virtual Path: /MyApp
Application Path: C:\Inetpub\wwwroot\MyApp\
Machine name: MyServer
Process information:
Process ID: 352
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://MyServer/MyApp/Page1.aspx
Request path: /MyServer/MyApp/Page1.aspx
User host address: XX.XX.XXX.XX
User: DomainName/UserName

Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Please Guide

Thanks

Is it intermittent or consistent?

1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This looks like a consistent issue. It normally means that you need to configuration IIS account to access SQL Server. You can take a look at this link, http://support.microsoft.com/kb/247931/en-us.

2. TCP Provider: An existing connection was forcibly closed by the remote host.

This normally is an intermittent issue. It happens when you enable connection pooling and server closes the connection for reason like connection clean up, restart, network failure, the ASP.NET( sqlclient ) use dead connection from the pool to write data and thus get the failure you see. Re-retry connection should solve the issue.

|||

Hi Nan,

Re: 1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

IIS is configured properly to access data from SQL Server and it is working fine when IIS & SQL are on the same box. But the above message comes only when the stored proc tries to access data from a 2nd SQL Server (linked server which is not on the same box as IIS). Somehow it is not passing the credential to the 2nd SQL Server. 2nd SQL server is linked using login's current security context. Also ASP.NET 2 web.config file says <identity impersonate="true" />. I am using NT domain right now. Is this issue of "Double hop"?

Re: 2. TCP Provider: An existing connection was forcibly closed by the remote host.

Is connection pooling is by default? Can we change so that it should not use it?

Thanks

Shafiq