xp cmdshell: application

Showing posts with label application. Show all posts

Friday, March 30, 2012

problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Hi,

I have developed a stand alone application that get data from excel and dumps it in local database (sql server database primary data file) in visual studio .net 2005. I had no issues while developing the application. When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

connection string i am using is

Dim objLocalDB As System.Data.SqlClient.SqlConnection

objLocalDB = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & System.AppDomain.CurrentDomain.BaseDirectory & "LFDB.mdf;Integrated Security=True;User Instance=True")

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

hi,

Manyam wrote:

When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error
provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

I can't understand if you installed SQLExpress as well, on the target computer... did you?

regards

|||SQL Server Express is a backend database although the GUI tools are more lightweight than the ones of the more bigger editions. If you want to use user instances as mentioned in the connection string, you will need to have a SQL Server Express Service in place to attach the database to the Service. SQL Server Express can be deployed within your application (even through clickonce deplyoment but at the end need to be installed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Manyam wrote:
I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)
Please help me how can i get through this problem.

SQL Express is not part of the .NET Framework, it is a completely separate product that must be installed if you are going to use it. You can add SQL Express as a prerequisite to you application installation automatically using either ClickOnce or standard Setup Projects in VS 2005, in your projects properties, click the Prerequisite button and make sure SQL Express is checked in the list. This will automatically include the SQL Express installer in your deployment and install it on the target computer if it is needed.

Mike

|||

Hi,

Thanks for your suggestion. So we cannot run vb.net application that is developed using sql server management studio without SQL EXPRESS and only .net framework.

I tried to open the link provided by you. Its not opening. I am getting page cannot be displayed.

|||

Hi,

Thanks for your valueable suggestion. So i cannot run a vb application developed using sql server management studio without SQL EXPRESS.I tried installing SQL EXPRESS in standalone system, but its giving issues.

Process followed -->

Uninstalled existing framework.

Installed .NET framework 2.0Dowloaded SQLEXPRESS and istalled in system.

Its throwing a error that .net should require some updates... What updates do we need to install to get this working.

Thanks in advance.

|||What's your OS? If WIndows XP Pro, this needs SP2 and updated security patches

Problem running full text search - it's broken!

I have a 2-tier application
The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
This works fine for ALL database calls, except when it requires a full text
query. The error message received is:
--
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The catalog does not exist or is
currently unavailable. Please retry the action again later and if this
symptom persists, contact the system administrator.
--
However...I know that the full text catalogue DOES exist on the data tier
(I've rebuilt & repopulated it).
I also have an "identical" pair of machines and it works fine on that
one... By identical, I mean all the ASP code running under IIS is the
same.
No idea what I should be checking. The ADODB connection is identical on
both machines (uses a SQL Server account with identical access permissions)
and SQLServer is running under the ADMINISTRATOR account on both servers.
Thanks
GriffAre there any pertinent messages from MSSearch or MSSCI in the event log?
--
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
"Griff" <Howling@.The.Moon> wrote in message
news:e9f62cw3FHA.3628@.TK2MSFTNGP12.phx.gbl...
> I have a 2-tier application
> The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
> This works fine for ALL database calls, except when it requires a full
text
> query. The error message received is:
> --
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The catalog does not exist or
is
> currently unavailable. Please retry the action again later and if this
> symptom persists, contact the system administrator.
> --
> However...I know that the full text catalogue DOES exist on the data tier
> (I've rebuilt & repopulated it).
> I also have an "identical" pair of machines and it works fine on that
> one... By identical, I mean all the ASP code running under IIS is the
> same.
> No idea what I should be checking. The ADODB connection is identical on
> both machines (uses a SQL Server account with identical access
permissions)
> and SQLServer is running under the ADMINISTRATOR account on both servers.
> Thanks
> Griff
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hi Hilary
I'm afraid not. The only messages state that the catalogues were built
successfully, e.g.:
Master merge has completed on
d:\mssql\ftdata\sql0000700005\build\indexer\cifiles.
The only warning mentioned is:
SuperSocket info: (SpnRegister) : Error 1355.
Griff|||Griff,
I think we need additional information from the server to understand why
this error OLE DB '80040e14' is being raised and that you have confirmed
that the full text catalogue DOES exist on this server. Could you set trace
flag 7300 on, you can get extended OLEDB error info?
dbcc traceon (3604, 7300)
Could you also post the exact CONTAINS* or FREETEXT* query that you're using
along with the search word or phrase, and re-run the query and post the
extended OLEDB error info?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Griff" <Howling@.The.Moon> wrote in message
news:uUpfG%2343FHA.3628@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Are there any pertinent messages from MSSearch or MSSCI in the event log?
> Hi Hilary
> I'm afraid not. The only messages state that the catalogues were built
> successfully, e.g.:
> Master merge has completed on
> d:\mssql\ftdata\sql0000700005\build\indexer\cifiles.
> The only warning mentioned is:
> SuperSocket info: (SpnRegister) : Error 1355.
> Griff
>

Wednesday, March 28, 2012

Problem restoring database to a compressed drive.

I am learning how to use SMO and I have a test application that allows me to restore a database backup to a new database. On my system I have one physical drive but two logical drives on separate partitions. Drive C: is on a partition that is a FAT32 partition and drive D: is on a partition that is a NTFS partition and is a compressed partition. The database backup file is located on drive D:. When I try to restore the backup to a new database and provide a path on the D: drive in the RelocateFile object I get the following error message.

"The file 'D:\NPS_Test.mdf' is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

When I do a restore and indicate that the new database file will be located on the C: drive the restore works. I assume the problem has to do with the fact that drive D: is set up as a compressed drive, but I am not sure.

Does this mean that I cannot store a database file on a compressed drive or is there something I need to do to make it work?

Hi,

http://blogs.msdn.com/sqlblog/archive/2006/10/02/SQL-Server-databases-are-not-supported-on-compressed-volumes.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem Report Viewer Control

I am having an authentication problem using the Report Viewer Control. The
situation is that I have developed a simple ASP.NET application that will
used as an interface for Report Viewing. First, I have a TreeView Control
located on the default page that is being populated with the current reports
that are published on the Report Server. Second, I have a aspx page called
ReportViewer that holds the Report Viewer Control. Third, when a user clicks
on one of the reports in the TreeView control the report is generated in an
IFrame located in a table cell on the default page.
My Problem is that everytime I click to generate a report I am asked for
windows credentials. I am passing the full network credentials(testing
environment going to use an application pool when live and pass default
credentials) example System.Net.NetworkCredential("username", "password",
"domain") in the load event of both aspx pages and IIS directory security is
setup with anonymous access & integrated windows authentication(I have tried
about everything in IIS Directory Security). I have tried about everything
and for the life of me cannot get around this issue any assistance would be
greatly appreciated.
Thanks,
--
Andrew
MCSA,MCDBAIf your asp app is on a different machine then I think you are hitting the
double hop issue. You need to be using Kerberos to support double hop. This
is an IIS issue. Here is a good link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
I notice that you have enable anonymous on IIS where RS is located. This is
a bad idea. It will cause difficulties with managing Report Server. Either
you need to setup your security using Kerberos or you will have to implement
forms based authentication. If there is another way around it I am not sure
what it would be.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
>I am having an authentication problem using the Report Viewer Control. The
> situation is that I have developed a simple ASP.NET application that will
> used as an interface for Report Viewing. First, I have a TreeView Control
> located on the default page that is being populated with the current
> reports
> that are published on the Report Server. Second, I have a aspx page called
> ReportViewer that holds the Report Viewer Control. Third, when a user
> clicks
> on one of the reports in the TreeView control the report is generated in
> an
> IFrame located in a table cell on the default page.
> My Problem is that everytime I click to generate a report I am asked for
> windows credentials. I am passing the full network credentials(testing
> environment going to use an application pool when live and pass default
> credentials) example System.Net.NetworkCredential("username", "password",
> "domain") in the load event of both aspx pages and IIS directory security
> is
> setup with anonymous access & integrated windows authentication(I have
> tried
> about everything in IIS Directory Security). I have tried about everything
> and for the life of me cannot get around this issue any assistance would
> be
> greatly appreciated.
> Thanks,
> --
> Andrew
> MCSA,MCDBA|||Bruce,
Thanks for the response and I am just using windows authentication on the
live report server. I enabled the anonymous access in a testing environment
just to try to get around the authentication problems I was encountering. My
issue was that I am developing in a different domain and I am accessing the
report server by IP address. Well when I implemented the application on the
production domain I never changed the report server path to the actual report
server machine name and this caused my authentication problem. Just one of
those little slip ups that seem to cause the biggest problems. Thanks,
--
Andrew
MCSA,MCDBA
--
Andrew
MCSA,MCDBA
"Bruce L-C [MVP]" wrote:
> If your asp app is on a different machine then I think you are hitting the
> double hop issue. You need to be using Kerberos to support double hop. This
> is an IIS issue. Here is a good link:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
> I notice that you have enable anonymous on IIS where RS is located. This is
> a bad idea. It will cause difficulties with managing Report Server. Either
> you need to setup your security using Kerberos or you will have to implement
> forms based authentication. If there is another way around it I am not sure
> what it would be.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
> >I am having an authentication problem using the Report Viewer Control. The
> > situation is that I have developed a simple ASP.NET application that will
> > used as an interface for Report Viewing. First, I have a TreeView Control
> > located on the default page that is being populated with the current
> > reports
> > that are published on the Report Server. Second, I have a aspx page called
> > ReportViewer that holds the Report Viewer Control. Third, when a user
> > clicks
> > on one of the reports in the TreeView control the report is generated in
> > an
> > IFrame located in a table cell on the default page.
> >
> > My Problem is that everytime I click to generate a report I am asked for
> > windows credentials. I am passing the full network credentials(testing
> > environment going to use an application pool when live and pass default
> > credentials) example System.Net.NetworkCredential("username", "password",
> > "domain") in the load event of both aspx pages and IIS directory security
> > is
> > setup with anonymous access & integrated windows authentication(I have
> > tried
> > about everything in IIS Directory Security). I have tried about everything
> > and for the life of me cannot get around this issue any assistance would
> > be
> > greatly appreciated.
> >
> > Thanks,
> >
> > --
> > Andrew
> > MCSA,MCDBA
>
>|||Ahh, same symptom as double hop but different reason. Glad you got it figure
out.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:28AC4137-F5A4-4F09-8B00-80126775DBB6@.microsoft.com...
> Bruce,
> Thanks for the response and I am just using windows authentication on the
> live report server. I enabled the anonymous access in a testing
> environment
> just to try to get around the authentication problems I was encountering.
> My
> issue was that I am developing in a different domain and I am accessing
> the
> report server by IP address. Well when I implemented the application on
> the
> production domain I never changed the report server path to the actual
> report
> server machine name and this caused my authentication problem. Just one
> of
> those little slip ups that seem to cause the biggest problems. Thanks,
> --
> Andrew
> MCSA,MCDBA
> --
> Andrew
> MCSA,MCDBA
>
> "Bruce L-C [MVP]" wrote:
>> If your asp app is on a different machine then I think you are hitting
>> the
>> double hop issue. You need to be using Kerberos to support double hop.
>> This
>> is an IIS issue. Here is a good link:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
>> I notice that you have enable anonymous on IIS where RS is located. This
>> is
>> a bad idea. It will cause difficulties with managing Report Server.
>> Either
>> you need to setup your security using Kerberos or you will have to
>> implement
>> forms based authentication. If there is another way around it I am not
>> sure
>> what it would be.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
>> news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
>> >I am having an authentication problem using the Report Viewer Control.
>> >The
>> > situation is that I have developed a simple ASP.NET application that
>> > will
>> > used as an interface for Report Viewing. First, I have a TreeView
>> > Control
>> > located on the default page that is being populated with the current
>> > reports
>> > that are published on the Report Server. Second, I have a aspx page
>> > called
>> > ReportViewer that holds the Report Viewer Control. Third, when a user
>> > clicks
>> > on one of the reports in the TreeView control the report is generated
>> > in
>> > an
>> > IFrame located in a table cell on the default page.
>> >
>> > My Problem is that everytime I click to generate a report I am asked
>> > for
>> > windows credentials. I am passing the full network credentials(testing
>> > environment going to use an application pool when live and pass default
>> > credentials) example System.Net.NetworkCredential("username",
>> > "password",
>> > "domain") in the load event of both aspx pages and IIS directory
>> > security
>> > is
>> > setup with anonymous access & integrated windows authentication(I have
>> > tried
>> > about everything in IIS Directory Security). I have tried about
>> > everything
>> > and for the life of me cannot get around this issue any assistance
>> > would
>> > be
>> > greatly appreciated.
>> >
>> > Thanks,
>> >
>> > --
>> > Andrew
>> > MCSA,MCDBA
>>|||Andrew wrote:
> Bruce,
> Thanks for the response and I am just using windows authentication on the
> live report server. I enabled the anonymous access in a testing environment
> just to try to get around the authentication problems I was encountering. My
> issue was that I am developing in a different domain and I am accessing the
> report server by IP address. Well when I implemented the application on the
> production domain I never changed the report server path to the actual report
> server machine name and this caused my authentication problem. Just one of
> those little slip ups that seem to cause the biggest problems. Thanks,
Andrew,
you will not be authenticated with IIS if you have anonymous access
enabled.Even if you pass credentials with
System.Net.NetworkCredential(user,pass,domain).

Problem replication complex database

I have been have real trouble over the last few weeks trying to get an
application database to replicate. The database is relatively complex in
structure - but not ridiculously so.
It doesnt seem to matter what replication technique that is used we always
get the same problem.
Line516: Incorrect syntax near '-'.
(Source: SERVER2 (Data source); Error number: 50007)
xp_execresultset: unable to execute result set
{Source: SERVER2 (Data source); Error number: 50007)
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
However we found an article
http://support.microsoft.com/default.aspx?scid=kb;en-us;299439
We have followed this and tried moving the GUID columns to the end but now a
new error is seen...
The schema script '\\SERVER1\d$\sql
data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
could not be propagated to the subscriber.
The schema script '\\SERVER1\d$\sql
data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Operand type clash: int is incompatible with uniqueidentifier
(Source: SERVER2(Data source); Error number: 206)
-----
Any ideas would be very welcome!For what it is worth, we ran into similar '-' problems with our server names
in a recent re-engineering. I don't remember the error number(s), but we did
two things: 1- qualified the server names in source code with [server-name]
2- renamed several servers or changed dns entries to remove the
'-'
Just a thought.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mark Broadbent" wrote:
> I have been have real trouble over the last few weeks trying to get an
> application database to replicate. The database is relatively complex in
> structure - but not ridiculously so.
> It doesnt seem to matter what replication technique that is used we always
> get the same problem.
> Line516: Incorrect syntax near '-'.
> (Source: SERVER2 (Data source); Error number: 50007)
> xp_execresultset: unable to execute result set
> {Source: SERVER2 (Data source); Error number: 50007)
> The process could not deliver the snapshot to the Subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> However we found an article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;299439
> We have followed this and tried moving the GUID columns to the end but now a
> new error is seen...
> The schema script '\\SERVER1\d$\sql
> data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
> could not be propagated to the subscriber.
> The schema script '\\SERVER1\d$\sql
> data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> Operand type clash: int is incompatible with uniqueidentifier
> (Source: SERVER2(Data source); Error number: 206)
> -----
> Any ideas would be very welcome!
>
>|||Guys, thanks for your suggestions. It appears (I think) that the new problem
is down to a stored procedure inserting data into a table not using named
columns! Since the ordering of the tables has changed due to the GUID
columns being moved, this caused a problem. The developers have changed the
script.
br,
mark.
"Mark Broadbent" <nospam@.nospam.co.uk> wrote in message
news:uCwDBuI4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have been have real trouble over the last few weeks trying to get an
>application database to replicate. The database is relatively complex in
>structure - but not ridiculously so.
> It doesnt seem to matter what replication technique that is used we always
> get the same problem.
> Line516: Incorrect syntax near '-'.
> (Source: SERVER2 (Data source); Error number: 50007)
> xp_execresultset: unable to execute result set
> {Source: SERVER2 (Data source); Error number: 50007)
> The process could not deliver the snapshot to the Subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> However we found an article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;299439
> We have followed this and tried moving the GUID columns to the end but now
> a new error is seen...
> The schema script '\\SERVER1\d$\sql
> data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
> could not be propagated to the subscriber.
> The schema script '\\SERVER1\d$\sql
> data\MSSQL\ReplData\unc\SERVER1_DB1_DB1\20051103135903\spGetCriticalSectionToken_32.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> Operand type clash: int is incompatible with uniqueidentifier
> (Source: SERVER2(Data source); Error number: 206)
> -----
> Any ideas would be very welcome!
>

Monday, March 26, 2012

problem rendering a logo (external static image)

Hi,
I am having a problem rendering a logo, which is inside my asp.net
application, but first let me clear out what is exactly what I want to do and
what I have been able to do:
I am using soap access to render reports in our asp.net application, and it
works well (aside from a performance issue which I have not yet looked at).
The reports contain a logo, and it gets displayed pretty well. So basically,
everything is working.
The problem resides in that we wont be giving external access to Reporting
Services and the url of the images being displayed are pointing to reporting
services. I know there is a work around, since I could configure it as an
embedded image, and then use the render stream method of the web service. I
want to avoid doing so, since it is just a simple static image, which is
already rendered on the asp.net application. If I do so, I will have to deal
with creating temporary copies of it, and then deleting them (which I dont
know when to ... but that is another story).
So, the question is: is there a simple way to render the report in such a
way the image url the browser receive is that of the static file saved in our
asp.net application? if so, will exporting to other formats keep working
normally?I have been digging about the issue, and I dont seem to find any way to do
what I wanna do, in fact it seems that not being able to show external images
directly
from their source is by design ...
so, I am stuck with the normal routes ... I did find around in the boards,
how I can do it without ever writting the images to disk ... here, the post
before the last one
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=uzmGLaeAEHA.3316%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3Daspx%2Bimage%2Bgroup:*.reportingsvcs%2Bauthor:teo%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3DuzmGLaeAEHA.3316%2540TK2MSFTNGP11.phx.gbl%26rnum%3D1
In normal use of images it sounds pretty great, but I am concerned in my
case this might unnecesarily degrade performance (since I would be calling
the web service per image served, even if it is an already server image to
that client ... and it is a logo that appears on every page ... and the
report is showing all pages), even more than saving a copy of the static
image per client to the server, and then figuring out when to delete them
(and the way it is showed on the thread I posted above, doesnt help either
... because it assumes the image will get served once, and mine gets served
several times ...)
I would highly appreciate any lights in this, since I am in the dark ...
a couple comments:
to be honest, altough I undestand security implications I am not sure if it
solves the problem more than making devs lifes harder (I usually dont make
this type of comment :P :(:( ) ...
(*) One can render the report trough Soap access and get the images from rs,
then one is the server, in fact as long as u use the same generated id that u
get from the render call, u can even serve the image from a different
location (and setting the streamroot appropiately) ... in fact when using it
trough Soap access u can more easily mess with the reports itself (mess as
modify :P) ... this is a pretty good reason why restricting truly external
images when rendering in HTML trough soap access doesnt make any sense, it
isnt adding any degree of security ... in my opinion ...

PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE

I am WORKING IN AN APPLICATION USING SQL SERVER 2000 AND VB6

I'VE A PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE

I WILL EXPLAIN WITH AN EXAMPLE

TABLE STRUCTURE

AccAccounts

Accid(Numeric) AccName(Varchar)

1 Cash A/c

2 Students A/c

3 HDFC Bank A/c

my Application will pass the "Accid" as a string format to Stored Procedure

STORED PROCEDURE

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select * from Accaccount where accid in (@.Accid)

when i run this SP

declare @.Accid Varchar(100)
set @.Accid ='1,2'

exec GetAccName @.Accid

i get the following error

Server: Msg 8114, Level 16, State 5, Procedure GetAccName, Line 4
Error converting data type varchar to numeric.

please "ANY ONE" help me!!.

The above example is only an example

REGARDS

JAMES

Hi,

You can not use the @.Variable which holds the multiple accountids in Static SQL statement.. you need to use dynamic sql..

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Declare @.cmd varchar(4000)

Set @.Cmd = 'Select * from Accaccount where accid in (' + @.Accid')'

exec (@.cmd)

But would suggest you to go through the following link to see the advantages and disadvantages..

http://www.sommarskog.se/dynamic_sql.html

Regards,

|||

You can also do this without dynamic sql.

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select *
from AccAccounts
where charindex(','+convert(varchar,accid)+',',','+@.Accid+',')>0

|||

THANK U VERY MUCH

|||

Hi James

You shouldn't pass comma separated values to the stored procedure, like: '1,2,3' . Because when you call the sproc it is trying to convert your value(because accid is numeric in your table) to varchar implicitly and due to the ',' in your data the conversion is going to fail and throws an error. You need to write some other logic to get it done.

Thanks & Regards,

Kiran.Y

|||

THX FOR REPLY

WHAT IN CASE IF I NEED A QRY

Select * from AccAccounts where Accid Not in (1,2)

|||

>0 means a match is found

=0 means no match.

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select *
from AccAccounts
where charindex(','+convert(varchar,accid)+',',','+@.Accid+',') = 0

|||

Thx

Friday, March 23, 2012

Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks.. Idea

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks.. Idea

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

Problem reaching sqlserver2000 database with asp.net 2.0 application when deploying

I have developped an ASP.NET 2.0 webb application localy on my workstation and have the MSSQL2000 database on a
server.There has been no problem accessing the database during development.

Im now finnished and put the application on a web server.

No when i try to access the application i get the following problem as it tries to reach the database:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I still use the same database so remote connections are allowed and there are lot of application against that sqlserver already running.

I have anonumouse access and use a domain service account for the application and that very same account is added
as a login to the sqlserver and a domain\user in the database.

I have Forms authentication in my web.config file.

the connectionstring i use look like this:

Can somebody please tell me what is wrong and how to fix it. I dont change any configuration betweeen my workstation and the server in web.config

/ in pain!!

Hi Vader,

Use IP Address of your server to describe Data Source in your config settings like 192.192.xx.xx

I hope this will work.. Yes

Wednesday, March 21, 2012

Problem Opening Databases In ASP.NET

I Have A Problem With Opening A Database With "SQLOLEDB" Provider In Web Application Projects!
The Same Database Opens In Windows Application Projects But When I Try TO Open It (Manually Or During A DataAdapter.Fill Method) In Web APPS I Get An Error Message : "Login failed for user 'NT AUTHORITY\NETWORK SERVICE'."
I Use Windows NT Authentication In Connections,
I Have SQL Server 2000 Enterprise Edition (With Default Installation) , Windows .Net Server 2003 Enterprise Edition (IIS 6) , And VS.NET 2003 Enterprise Edition (.Net Framework 1.1)
Can Anyone Tell Me What's Wrong?You need to give the ASPNET user (or the user under which the worker process is running) the appropriate rights to your database.|||Thanks For Your Advice
I Solved That Problem By Adding A New User And Settting Permissions For It
By The Way , IT Came To My Mind That I Can Use ASPNET Account , But I Don't Know The Password , It Is'nt Blank And I Did'nt Set It Myself , n I Don't Know If I Change The Password Using Computer Management / Local Users , Would Be Anything Affected?
OR ASPNET Account Is Only For This Particular Use?

Note from moderator SomeNewKid:
Please refrain from capitalizing every word. Not only does it make your post hard to read,
it also means we cannot distinguish Class names and members.|||Typically, the ASPNET user has a system generated account and is local to the machine that the website is on. At work, we use a domain-level account for our web farm and that user then has rights to our app server tier for our file share for uploading/downloading files.

I was thinking of giving your ASPNET windows user rights to the database and using integrated security...

Your database user configuration is more portable and should work just as well.

Tuesday, March 20, 2012

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

Mike

|||

Thanks Mike!

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Monday, March 12, 2012

Problem of Parent-Child package with ASP.Net

I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)

I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.

Could somebody explain why this is happening? any suggestions/ similar experiences?

Regards

Saurabh

One more observation: if i run the same parent package as a stand-alone. (i.e. not through the asp.net application) it always executes fine. it calls all the child packages very well always. what is different in the asp.net context?

Problem of optimization in complex stored procedure

Hello,

I have a procedure stored in my application which takes a very very long time to execute.

It deals with the relations of compositon of a product (a computer is made up of a screen, a keyboard and electronic components).

The subject is about component/ sub component relations (article or product breakdown) also called non hierachical relationships.

The products have a code composed of 22 characters (ARTICLE_CODE) but the links in the breakdown between articles are builted on the first 12 characters (ROOT_ARTICLE_CODE).

So in the relationship table we can find an article (V_ROOT_PART_NUMBER_CODE) associated with several articles (N_ARTICLE_FATHER_ID) because they share the 12 first characters

Example

N_ARTICLE_ID;N_FATHER_ID;N_DATASOURCE_ID;N_SOURCE_ID;V_ROOT_PART_NUMBER_CODE;V_NODE_LABEL;B_ACTIVE;V_FATHER_ROOT_PART_NUMBER_CODE;V_FATHER_NODE_LABEL;B_FATHER_ACTIVE

1005;20637;2;14;5M4K85638000;5M4K85638000 - TEST7;1;TK18J1066002;TK18J1066002 - TEST1;1

1005;20638;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67000;A2HG31T67000 - TEST2;1

1005;20639;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67002;A2HG31T67002 - TEST3;1

1005;23946;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD231000;T5D8FD231000 - TEST4;1

1005;24034;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109862;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109876;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109877;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

9560;706;2;12;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;706;2;12;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;706;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;707;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9560;707;2;-6;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9554;707;2;-6;5F4RE5132202;5F4RE5132202 - TEST11;1;5F4RE5145002;5F4RE5145002 - TEST6;1

705;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST12;1;5F4RE5145002;5F4RE5145002 - TEST6;1

18405;707;2;-6;5F4RE5145220;5F4RE5145220 - TEST13;1;5F4RE5145002;5F4RE5145002 - TEST6;1

But sometimes links are missing between identifiers (I have already an existing link between two articles. I have just added an article (new N_ARTICLE_ID) common on the first 12 characters with one of the previous elements.

I must thus create a new link father/ child because of this common element.

I can create several links if the article is linked with seral articles at a component level (the same keyboard can be used in several models of computers)

I make the same procedure for the father (by adding the links with existing child for an article common at 12 characters).

The problem is that it is a very very long procedure.

I have 75000 articles and it is necessary at least 48H for me to manage them.

Could you help me to optimize the code of this stored procedure?

Thank you by advance

Code Snippet

CREATE TABLE [ARTICLE] (
[N_ARTICLE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[V_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[V_PART_NUMBER_LABEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_ARTICLE_CODE_UNIQUE] UNIQUE NONCLUSTERED
(
[V_CODE]
) ON [PRIMARY]
GO

CREATE TABLE [ARTICLE_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL ,
[N_ARTICLE_FATHER_ID] [bigint] NOT NULL ,
[N_DATASOURCE_ID] [bigint] NOT NULL ,
[N_SOURCE_ID] [bigint] NOT NULL ,
[V_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_ACTIVE] [bit] NULL ,
[V_FATHER_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_FATHER_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_FATHER_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID],
[N_ARTICLE_FATHER_ID],
[N_DATASOURCE_ID],
[N_SOURCE_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE] FOREIGN KEY
(
[N_ARTICLE_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE1] FOREIGN KEY
(
[N_ARTICLE_FATHER_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_SOURCE] FOREIGN KEY
(
[N_SOURCE_ID]
) REFERENCES [SOURCE] (
[N_SOURCE_ID]
)
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Code Snippet

CREATE PROCEDURE [dbo].[LOAD_ARTICLE_BREAKDOWN_PACKAGE]
@.ARTICLE_ID AS BIGINT= NULL,@.ARTICLE_FATHER_ID AS BIGINT= NULL, @.DATASOURCE AS BIGINT= NULL, @.SOURCE AS BIGINT= NULL
AS
BEGIN

SET NOCOUNT ON;
If @.ARTICLE_ID IS NOT NULL AND @.ARTICLE_FATHER_ID IS NOT NULL AND @.DATASOURCE IS NOT NULL AND @.SOURCE IS NOT NULL
BEGIN
/*Table pour mettre tous les pn fils
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

/*Table pour mettre tous les pn père
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

Declare @.LABEL1 as CHAR(50)
Declare @.SPARE1 as BIT
Declare @.LABEL2 as CHAR(50)
Declare @.SPARE2 as BIT
Declare @.ARTICLEC as BIGINT
Declare @.ARTICLEF as BIGINT
/*information sur le premier PN*/

SET @.LABEL1 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE1 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))))

/*information sur le second PN*/

SET @.LABEL2 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE2 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))))
/*recherche les articles communs a 12 dans la première boucle pour le fils*/
/*recherche les articles communs a 12 dans la seconde boucle pour le père*/
DELETE FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=-6) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=@.SOURCE) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, @.SOURCE AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLE_FATHER_ID) AND (PN1.N_ARTICLE_ID = @.ARTICLE_ID);
END
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
BEGIN
SET @.ARTICLEC = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
BEGIN
SET @.ARTICLEF = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_ARTICLE_ID = @.ARTICLEC) AND (N_ARTICLE_FATHER_ID = @.ARTICLEF)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, -6 AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLEF) AND (PN1.N_ARTICLE_ID = @.ARTICLEC);
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEF
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEC
END
END

/*if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Comment ca va Rabiwan?

Bon, je ne suis pas sur de pouvoir t'aider facilement. Continuons ca en english.

I can try to give a couple clues.

You use quite a few sub-queries. They are slow

You use quite a few DELETE statement, they are slow

Indexing the temp tables may or may not help, it takes time to index them and it is not sure that query plan will use these indexes.

I guess you should have a second look at the entire solution and see if you could have any use of recursive CTE's

Philippe

|||

Bonjour philippe,

ca va, ca va merci mais mieux quand ce problème sera arrang.

ok for the sub queries I will find a solution for that with an denormalization of the column V_CODE for have a new column at 12 characters.

For the delete I can set a new field in my tempory table and do an update when the data is processing.

The update is less consuming?

I use SQL SERVER 2000 SP4, So I think there is not CTE.

And for finish the index is only on primary key. But when I delete an element in the tempory table, the index is reload?

Rabbiwan