need help about call sp sql server in vb.net

need help about call sp sql server in vb.net
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Hello
Take a look into the below URL:-
http://www.codeguru.com/vb/gen/vb_database/sqlserver/article.php/c8671__2/
Thanks
Hari
<jorge> wrote in message news:200734145147jorgepachu@.yahoo.com.ar...
> need help about call sp sql server in vb.net
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

need help about call sp sql server in vb.net

need help about call sp sql server in vb.net
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comHello
Take a look into the below URL:-
http://www.codeguru.com/vb/gen/vb_d...e.php/c8671__2/
Thanks
Hari
<jorge> wrote in message news:200734145147jorgepachu@.yahoo.com.ar...
> need help about call sp sql server in vb.net
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Need Help - Quick simple implementation of code

Hi,
I am looking for a very simple and straighforward way of calling an already
deployed report from ASP.net. I simply need a hyperlink that displays the
report on screen if that is possible.
Any help would be tremendously appreciated.Hi Dan.
You can download this ASP.NET demo, it would help you rendering the report.
http://www.rdlcomponents.com/ASPExamples/default.aspx
Thanks
Jerry
"Dan Colgan via SQLMonster.com" wrote:
> Hi,
> I am looking for a very simple and straighforward way of calling an already
> deployed report from ASP.net. I simply need a hyperlink that displays the
> report on screen if that is possible.
> Any help would be tremendously appreciated.
>sql

Need help - Converting OLEDB connections to SQL connections in asp.net

Hi there,

Here we have got aasp.net application that was developed when database wassitting on SQL server 6.5. Now client has moved all of their databasesto SQL server 2000. When the database was on 6.5 the previousdevelopment team has used oledb connections all over. As the databaseshave been moved to SQL server 2000 now i am in process of changing thedatabase connection part. As part of the process i have a loginauthorization code.

PrivateFunction Authenticate(ByVal usernameAsString,ByVal passwordAsString,ByRef resultsAs NorisSetupLib.AuthorizationResult)AsBoolean

Dim connAs IDbConnection = GetConnection()

Try

Dim cmdAs IDbCommand = conn.CreateCommand()

Dim sqlAsString = "EDSConfirmUpdate"'"EDSConfirmUpdate""PswdConfirmation"

'Dim cmd As SqlCommand = New SqlCommand("sql", conn)

cmd.CommandText = sql

cmd.CommandType = CommandType.StoredProcedure

NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)

NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)

conn.Open()

'Get string for return values

Dim ReturnValueAsString = cmd.ExecuteScalar.ToString

'Split string into array

Dim Values()AsString = ReturnValue.Split(";~".ToCharArray)

'If the return code is CONTINUE, all is well. Otherwise, collect the

'reason why the result failed and let the user know

If Values(0) = "CONTINUE"Then

ReturnTrue

Else

results.Result = Values(0)

'Make sure there is a message being returned

If Values.Length > 1Then

results.Message = Values(2)

EndIf

ReturnFalse

EndIf

Catch exAs Exception

Throw ex

Finally

If (Not connIsNothingAndAlso conn.State = ConnectionState.Open)Then

conn.Close()

EndIf

EndTry

EndFunction

''' ------------------------

''' <summary>

''' Getting the Connection from the config file

''' </summary>

''' <returns>A connection object</returns>

''' <remarks>

''' This is the same for all of the data classes.

''' Reads a specificconnection string from the web.config file for the service, creates aconnection object and returns it as an IDbConnection.

''' </remarks>

''' ------------------------

PrivateFunction GetConnection()As IDbConnection

'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection

Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection

conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)

Return conn

EndFunction

in the above GetConnection() method ihave commented out the .net dataprovider for oledb and changed it to.net dataprovider for SQLconnection. this function works fine. But inthe authenticate method above at the line

Dim ReturnValueAsString = cmd.ExecuteScalar.ToString

for some reason its throwing the below error.

Run-time exception thrown : System.Data.SqlClient.SqlException - @.password is not a parameter for procedure EDSConfirmUpdate.

If i comment out the

Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection

and uncomment the .net oledb provider,

Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection

then it works fine.

I also have changed the webconfig file as below.

<!--<addkey="Common" value='User ID=**secret**;pwd=**secret**;DataSource="ESMALLDB2K";Initial Catalog=cj_common;AutoTranslate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'/>-->

<addkey="Common"value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;'/>

Please help. Thanks in advance.

what are the parameters your stored proc is defined with ?
NEVERgive out your userid/pwd in the connection string when pasting code over on a public forum.Mask your pwd.
|||

Thanks for the quick response Dinakar.

Here are the parameters the stored proc is expecting

*******************************

CREATE PROCEDURE EDSConfirmUpdate
@.logon char(10), @.curpswdclr varchar(255), @.newpswdclr varchar(255) = null
AS

***********************

But in the it is sending the parameter

NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)

NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)

obviously @.password is not a parameter in the sotred proc. But why it works when i use oledb... i am not sure about it.

Hope this will help you to understand further.

Thanks and i just pasted it like that. I won't do it again.

-D

|||well I dont know how it worked in OLEDB. But for SQL you need todeclare the same parameters that you used in the stored procedure.
|||

yeah you are right. i couldn't figure out why it was working with oledb before with a wrong parameter. I have changed it to the right parameter now its working.

I have one more problem with the tree control that is defined. The client main concern is that they are not able to use their tree view control. before move it was rendering well but after the databases to 2000 its got messed up. I have changed the connections to SQL server and thought it would render automatically(off course there is not connection between rendering a treeveiw and the type of the provider thats being used ) but i thought some thing messed up with the move and the tree view was unable to find the data needed to build it. But no luck yet! i have posted it at

http://forums.asp.net/930668/ShowPost.aspx

Please take a look at it if you can think of any thing.

Thank you

-D

Need Help

I have a web app in asp.net which has 8 textboxes

the user searches the database and the search result is shown in a datagrid

the user has the option to select any records displayed using a checkbox

and they can enter values into the textboxes (or leave it empty) and when they say edit

the app updates the database the selected rows with values from the 8 textboxes id any of the text boxes are empty or "" then the existing value in that column has to be retained if the text box is not empty then then the new value is updated

the way i am doing it now is putting the update statement in a for loop and checking if the text box is not null and then updating the DB for that row

the problem is that if the user selects 5000 rows then the app has to do 5000 updates.

and it is a big performance problem

I want to do this update in the SQL 2000 by using a Stored Proc or do I need to use a Cursor

can somebody help me with small code snippet ..

Thanks

PKGCan you show us some code? Like what the update looks like?

In your case though, I might use a work table...and do the updates on the server...|||Originally posted by Brett Kaiser
Can you show us some code? Like what the update looks like?

In your case though, I might use a work table...and do the updates on the server...

here is a sample update statement

UPDATE BIRECIN SET GLDEBIT = '789945562.524', GLCREDIT ='74859612.586' WHERE EntryClass = 'AADM' and AccCode ='MDH' and RevArea='CCU '

then..


UPDATE BIRECIN SET GLDEBIT = '789945562.524', GLCREDIT ='74859612.586' WHERE EntryClass = 'AAFM' and AccCode ='MDH' and RevArea='CCI '

this update statement is inside a for loop and gets executed for n number of rows|||Use the isnull function

Update YourTable
Set YourExistingValue = isnull(@.YourNewValue, YourExistingValue)
From YourTable
Where Keyfield = @.KeyField

If @.YourNewValue is null, the old value is retained.

If you can't submit null values, but have to submit zero-length strings instead, this should work:
Set YourExistingValue = isnull(null(@.YourNewValue, ''), YourExistingValue)

blindman|||Yeah, ok, but he wants to update an entire record set back to the database...

How would you do that...

He says he's looping like 5,000 times...

How do you get the data?|||I get it. The table he is updating with the check boxes isn't in SQL server. It's a dataset in his asp page?

Criminy. That's an asp problem, and out of my scope.

blindman|||Is there some way in asp to write the records (or just the checked ones) back into a temporary table that he could then join to his raw data tables?

blindman|||Yeah, that's what I was thinking, so he could do a set based op instead..

like bcp the data or is there an asp method...

There's got to be asp forums someplace...

We got the db section covered|||How about ...

Here (http://www.dbforums.com/forumdisplay.php?forumid=192)

Guys .. dont you ever look outside
dBforums Database Server Software Microsoft SQL Server|||Originally posted by Enigma
How about ...

Here (http://www.dbforums.com/forumdisplay.php?forumid=192)

Guys .. dont you ever look outside
dBforums Database Server Software Microsoft SQL Server

Sure do:

http://www.sqlteam.com/default.asp

Front end stuff...No thanks...see what a vb developer is making?

Enough to muddle through...not enough to be tapped to code...

The horror...the sheer horror...

Besides...I make all of the developers call stored procedures...they just present what I give them..|||The way the world should be. We cook the food. They can arrange it on the plate. Bon apetit', and my complements to the chef.

blindman|||Originally posted by blindman
The way the world should be. We cook the food. They can arrange it on the plate. Bon apetit', and my complements to the chef.

blindman

That's a GREAT analogy...

Need for Multi-Threaded Visual Basic.NET to SQL Server Data Access Example Code

Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.

I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.

If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.

The more examples the better.

Thanks in advance.

Sounds like you need to make your calls Async.

http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx

|||

I've never seen any database programming books that cover this specific topic. You may want to start by looking for .NET books that cover multi-threaded programming in .NET. From my past experience, I remember it is a bit tricky.

|||

Async ADO.NET2.0- Summary of Best Search Results

The best discussions re async commands (transactions) using SQL Server 2005 that I have found are:

Books:

WROX?’s ‘Professional ADO.NET 2’

Web Articles :

http://msdn2.microsoft.com/en-us/vstudio/aa718334.aspx (Asych Query Samples from Data Access Samples) with source (this is the sample I chose)

www.codeproject.com/dotnet/asynchronousdataaccess.asp (with source)

http://msdn2.microsoft.com/en-us/library/ms379553.aspx (with source), but note warning at:

http://blogs.msdn.com/angelsb/archieve/2004/09/02/224964.aspx

http://nayyeri.net/archieve/2006/08/29/Asynchronous-command-execution-in-.NET-2.0.aspx

http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconAsynchronousExecution.asp

http://msdn2.microsoft.com/en-us/library/ms228972(vs.80,d=printer).aspx

http://msdn2.microsoft.com/en-us/library/ms228975(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/wewwczdw(VS.80).aspx

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

|||

Most books talk about how to do basic async execution from a button click this is fine but the trouble begins when you want to populate the UI with the results of an async db command. The .NET UI is NOT thread safe and you have to serialize everything to the UI thread when doing things like updating a datagrid control for example. If you stick with the data driven events you are OK but if you want to do things more complex it can get troublesome.

I remember doing this back in the old days (.NET 1.0 and 1.1 days) and I would end up with a dead datagrid control with a big red X on it when the grid control got out of sync. So what I finally did after much pain and suffering was write my own custom message passing system that would pass messages to background threads and queue responses back to UI thread, then final bit of data manipulation had to be done on the UI thread.

I remember even creating/manipulating a new datarow on a secondary thread would break the datagrid/dataset (note I would just call NewRow on background thread and then serialize the actual adding of the new row on primary UI thread). This stuff might be fixed in .NET 2.0, so maybe I am being overly cautious here.

Need Dynamic SQL Select turotrial

Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.

Thank you

Do you mean full text searching? (Searching for a particular word within a lot of text)

|||

I have a database column 'Description' varchar(600) that contains product descriptions. I need to search it looking for any of the keywords contained in a Keyword string.

Example Keyword string - sail boat hardware

I need it to find all rows WHERE 'sail' or 'boat' or 'hardware' is found.

Thank you

|||

That form of search is more a full text search rather than a dynamic search. The latter is searching on one to many columns as selected at runtime.

|||

I am using a shared SQL2000 server. What I want to do is find a way in ASP.Net to take the keyword string entered and convert it into a Sql2000 select statement like below.

Keywords entered: sail boat hardware

SELECT Company, Phone, Description
FROM tblCompanyInfo
WHERE (tbl.CompanyInfo.Description LIKE '%' + "sail" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "boat" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "hardware" + '%')

|||

Look athttp://msdn2.microsoft.com/en-gb/sql/aa336289.aspx

|||

I am actualy looking for an ASP routine that splits the Keyword string and creates the OR elements for a Sql2000 compatible Sql SELECT command. I have looked at the many CONTAINS options discussed in the article but did not find any that offer a split like solution. As for a full text search, that option is not available at least not in the shared SQL environment that I am using.

Thank you

|||

In which case you need to roll your own text search solutions.

Create a Noise words table to contain words that you do not want indexed.

|||

Looks like someone should create a new ASP.Net SearchControl. A bunch of us out here could sure use it.

Thank you for the follow up.

sql

Need Coding for inserting data into SQL Server Using stored proecdure.

Hi to all..

I am new to ASP.Net, I want coding for inserting data in to SQL server 2005 that code contain the following criteria.

1.ASP.Net using C#.Net.

2.Using Stored Procdure.

3.the connetion string ,command , Data reader is in the same class. this class will be reusable for other page also without modifying any thing in it.

Please Help me.

Advance thanks.

Balaji,

I have an article in VB.NET which explains how to insert images to SQL server. Apart from images, i am using other columns to insert into the table.

Here is the article link:http://aspalliance.com/138_Inserting_Images_to_SqlServer_in_ASP_NET

And here is a link which will convert any VB.net code to c#http://labs.developerfusion.co.uk/convert/vb-to-csharp.aspx

Also, here is another link from ASP.NET quick starts which explains your scenario.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/databases.aspx#insert

Hope this helps.

|||

See if below thread can be useful.

http://forums.asp.net/t/1165758.aspx

Need code examples of how to call SSRS reports from winforms app

I'm writing a vb.net winforms app and need to call up reports from SSRS. I
also need to pass in secure parameters into the report and I don't want to
pass the credentials if I don't have to. I was hoping I could call my own
web service where I can pass encrypted data to my web service which would
contain the parameters. then a business object on the server could generate
the credential needed by SSRS and pass both the credentials and decrypted
parameters into the report. Problem is I don't know how to get the report
back to the client. One idea was to render the report on the server side
and then convert its html into a byte stream and pass the byte stream as a
return value for my web service. Then my winform client could convert the
byte stream into an html document. However, I have no idea if this is
possible and need examples for the various parts.
can someone please give me advise (and preferable links to code examples) on
how to solve this problem?
Thanks.
--
moondaddy@.nospam.nospamHello,
The following link might be helpful for you:
http://www.codeproject.com/useritems/SQLRSViewer.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "moondaddy" <moondaddy@.nospam.nospam>
| Subject: Need code examples of how to call SSRS reports from winforms app
| Date: Fri, 29 Apr 2005 00:29:02 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <ujCUetHTFHA.2756@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: adsl-70-240-196-183.dsl.hstntx.swbell.net
70.240.196.183
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42254
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm writing a vb.net winforms app and need to call up reports from SSRS.
I
| also need to pass in secure parameters into the report and I don't want
to
| pass the credentials if I don't have to. I was hoping I could call my
own
| web service where I can pass encrypted data to my web service which would
| contain the parameters. then a business object on the server could
generate
| the credential needed by SSRS and pass both the credentials and decrypted
| parameters into the report. Problem is I don't know how to get the
report
| back to the client. One idea was to render the report on the server side
| and then convert its html into a byte stream and pass the byte stream as
a
| return value for my web service. Then my winform client could convert the
| byte stream into an html document. However, I have no idea if this is
| possible and need examples for the various parts.
|
| can someone please give me advise (and preferable links to code examples)
on
| how to solve this problem?
|
| Thanks.
|
| --
| moondaddy@.nospam.nospam
|
|
|sql

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need advice on SQL Server account to use for my ASP.NET application

Hi,

I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.

I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?

If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?Yes look at the Microsoft Patterns and Practices Site..."Building Secure Microsoft ASP.NET Applications"

Need advice on a Data Import Routine

Hello,
I have a system that I am writing to automaticly import Credit Transaction
data into a SQL Server 2000 Database. I am using a VB.Net application to
detect when the file arives and prep it for parsing. The file is
aproximately 10Mb of relatively complex hierarchal data that is defined by 2
character tokens at the begining of each data line. The structure breaks ou
t
into aproximately 6 parent-child related tables with numerous lookup tables
on each primary table.
The previous aproach loaded each line of raw data into an Import table and
used a Stored Procedure to break down the information into the tables. The
Stored Procedure is large and very complex. It also has the problem that th
e
Parsing takes so long I need to implement a very long timeout on my
connection or I get errors. It is also difficult to track progress through
the Import for the user to see.
The options I see at this point are;
1. Use the current approach of loading the raw data into an Import table and
Parsing with a Stored Procedure.
2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data into
the apropriate tables.
3. Use a mixed aproach of breaking the massive Stored Procedure into smaller
procedures and managing the steps with VB.Net.
I am looking for any tips and advice that the guru's out there are willing
to give. Especially a comparision of the effiency of doing ADO.NET bulk
inserts vs. Stored Procedure parsing.
Thanks for any help given.
Doug
PS - Sorry for the cross posting in dotnet.general but I am hoping to get a
balanced set of views on this issue.Doug,
I have heard that sql server is not good for parsing routines, so this can
lead you to do it in your client app.
What about using XML to represent this complex hierarchal data?
AMB
"Doug R" wrote:

> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup table
s
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables. Th
e
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress throug
h
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table a
nd
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data in
to
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into small
er
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||I have done the same type of thing in the recent past. The methodology I
have used is to execute a BCP statement into a properly structured table.
BCP is native SQL Server, much faster than a Bulk Insert statement, deals
with security in-line (you specify user.password in the command) and if the
data is consistent (the type of data appears in the same position in each
line) the BCP command will import into the properly formatted table without
any parsing required. The syntax for command is listed in Books On-line.
You will need to create a format file for the import to use. This is simply
executing a BCP FORMAT command against the desired SQL Server destination
table. Once the format file is built, you use that to define for the BCP IN
command the data structure you are importing. It is an extra step to create
the format file, but once you have it you place it in a location where your
process can access it will do the heavy lifting for you.
The advantages to this process are several. First, because you are
pre-defining a data structure to the import process, no additional parsing
is needed. Second, because BCP runs in a DOS environment it is very fast.
Third, because user ID and password are contained in the command line,
connection strings and other security items are unnecessary.
There is one additional caveat to using BCP in a procedure. You will have
to run it in a DOS environment using the xp_cmdshell extended procedure.
This allows SQL Server to run DOS commands from within a stored procedure.
The user ID that runs this will need privileges assigned to the
master.dbo.xp_cmdshell proc in SQL Server Enterprise Manager.
My suggestion for implementation would be to write a simple stored procedure
that builds the BCP statement as a Varchar variable and passes it to
master.dbo.xp_cmdshell to run. Call this stored procedure using the execute
nonquery option of the SQLServer.SQLCommand object in VB.Net. Doing this
will allow for full automation of the import process.
If you need any further assistance you can contact me at
groutme_alternate@.sbcglobal.net.
Good Luck:
groutme in SO Cal.
"Doug R" <DougR@.discussions.microsoft.com> wrote in message
news:D7FEFA89-E6BD-49C9-A0E5-5464130B3440@.microsoft.com...
> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup
tables
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables.
The
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress
through
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table
and
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data
into
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into
smaller
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||Thanks for the reply. Regretably I have no control over the form of the
input data or I would be using XML. The data is being sent to us from an
outside business.
Are you familiar with ADO.NET for doing bulk inserts? I would be very
interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
An alternative would possibly be something that parses the import data file
into XML before the insert. Any ideas on how this could be done efficiently
?
"Alejandro Mesa" wrote:
> Doug,
> I have heard that sql server is not good for parsing routines, so this can
> lead you to do it in your client app.
> What about using XML to represent this complex hierarchal data?
>
> AMB
> "Doug R" wrote:
>|||If your stored procedure uses cursors or some other method to processes the
data line-by-line then probably you can rewrite some or all of it as
set-based SQL code for a significant performance improvement.

> Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing
Bulk Insert is equally possible from TSQL or ADO.NET code so this doesn't
seem to me like a meaningful comparison. The real question is whether TSQL o
r
client code can transform the data efficiently. Since this is fundamentally
about data manipulation my money would be on TSQL ... but that's without
seeing the detail of the actual problem.
David Portas
SQL Server MVP
--|||Doug,
If you are subcribed to SQL Server Magazine, there is a good article about
this theme.
Bulk-Insert Options for ADO.NET
http://www.windowsitpro.com/Article...>
oad_7pv0.asp
ADO.NET 2.0 will include this feature.
http://www.windowsitpro.com/SQLServ...62.html

AMB
"Doug R" wrote:
> Thanks for the reply. Regretably I have no control over the form of the
> input data or I would be using XML. The data is being sent to us from an
> outside business.
> Are you familiar with ADO.NET for doing bulk inserts? I would be very
> interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
> An alternative would possibly be something that parses the import data fil
e
> into XML before the insert. Any ideas on how this could be done efficient
ly?
>
> "Alejandro Mesa" wrote:
>

Need A writen Help about writen application with RS.

I need to write VB.NET or C# App. that have an input
boxes that tansfer their content to RS parameters
and then renders the report.
thanks.You can take a look at the ReportViewer sample provided
with the RS installation.
>--Original Message--
> I need to write VB.NET or C# App. that have an input
>boxes that tansfer their content to RS parameters
>and then renders the report.
>
>thanks.
>
>.
>

Need "commercial grade" FileWatcher task

Does anyone know of such a task than runs under both x86 and x64? (I know a FileWatcher add-in but it was built using Beta verisons of VS.NET)

TIA,

barkingdog

I would suggest using the WMI Event Watcher task - see http://msdn2.microsoft.com/en-us/library/ms141130(SQL.90).aspx

Donald

nedd some help with datetime

Hi,
I am passing datetime from my vb.net app for storage in sql server like this
Dim DT As DateTime = DateTime.Now
Dim str As String
str = DT.ToString("MMddyyyyHHmmss")
...insert to database str (example datetime 10312005135802)
The table column datatype is char(15). How can I convert this to datetime in
sql server? I have an sp that need to look at both the date and time. I was
trying this
declare @.dateandtime datetime
select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea where
id = 1234
select @.dateandtime
I am getting the error "syntax error converting datetime from character
string".
Or should I change the format I am sending the datetime from my vb.net app?
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01A96FD0-238C-42A8-AEF5-3BE330EC9EDC@.microsoft.com...
> Hi,
> I am passing datetime from my vb.net app for storage in sql server like
> this
> Dim DT As DateTime = DateTime.Now
> Dim str As String
> str = DT.ToString("MMddyyyyHHmmss")
> ...insert to database str (example datetime 10312005135802)
> The table column datatype is char(15). How can I convert this to datetime
> in
> sql server? I have an sp that need to look at both the date and time. I
> was
> trying this
> declare @.dateandtime datetime
> select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea
> where
> id = 1234
> select @.dateandtime
> I am getting the error "syntax error converting datetime from character
> string".
> Or should I change the format I am sending the datetime from my vb.net
> app?
> Thanks
Any reason why you appear to be using dynamic SQL to do this? Pass the date
as an adDBTimeStamp type using the ADO parameters collection. That way the
conversion to SQL DATETIME is implicit. Also, you should typically call a
proc for a simple INSERT rather than constuct a dynamic SQL string in code.
See the Using Parameters topic in the ADO section of Books Online.
David Portas
SQL Server MVP
--|||> The table column datatype is char(15).
WHY? Do you put ice cream in your medicine cabinet too?
Pass it as a proper date time value, store it as a proper date time value,
and you will be amazed how many of these silly string conversion problems
magically disappear.|||Honestly, I was thinking the same thing, however, I have noticed in a few
articles a while ago where data is stored as strings and taught I was wrong
all along to use datetime type in my database when I need to pass and store
date and time form my front end app.
Thanks for rebooting that section.
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHY? Do you put ice cream in your medicine cabinet too?
> Pass it as a proper date time value, store it as a proper date time value,
> and you will be amazed how many of these silly string conversion problems
> magically disappear.
>
>

Navigation not working after clicking once

We open reports in new window from an asp.net page..using rs.render method.
Recently we have new reports with graphs...when clicked on detail for graph
I have to open another report detail in a new window. I have created a
link(naviagtion URL property for textbox ) in the main graph report to open
the detail report link as
="javascript:void(document.open('/ReportViewer/ReportFrame.aspx?rptPath=REPORTPATH&args="
& REPORTPARAMETERES &
"','_blank','left=50,top=50,width=800,height=580,menubar=no,resizable=yes,scrollbars=Yes,status=yes,toolbar=0'))"
Issue 1 :
The link is working fine for the first time. When I click the link for the
first time...the new window opens fine with the detail report...but when i
close the new window(detail report) and click the link nothing happens,no
report open..why. Also if i keep the new window(of detail report) as is
without closing and then click on the link again in the graph report...one
more new window opens up with the detail report...
Why does the link not work when i close the new window and re-click the link
Issue 2 :
I am trying this...
The window we open the report in contains frames..and we open the report in
the lower frame...how can i get the new detail report to open in the same
frame.
I tried using '_self'...but with this the new detail report is open on the
main IE page where our actual application is been open.
Any suggestions to get it working as I wish it tooo...would help.
Thanks.
SKI am still stuck on this...
I am doubting this is some kind of setting issue or what
I changed the URL link to simple
= "javascript:void(window.open('http://www.google.com','_blank'))"
even this ...when i open it for the first time it works fine...but when i
close the new window the link does not work anymore...
fyi : we are deploying the reports in PDF format...any issue with adobe !!!
I get a security warning saying document is trying to open a link...but
nothing opens after the firsttime '
any ideas....
"Sunny" wrote:
> We open reports in new window from an asp.net page..using rs.render method.
> Recently we have new reports with graphs...when clicked on detail for graph
> I have to open another report detail in a new window. I have created a
> link(naviagtion URL property for textbox ) in the main graph report to open
> the detail report link as
> ="javascript:void(document.open('/ReportViewer/ReportFrame.aspx?rptPath=REPORTPATH&args="
> & REPORTPARAMETERES &
> "','_blank','left=50,top=50,width=800,height=580,menubar=no,resizable=yes,scrollbars=Yes,status=yes,toolbar=0'))"
> Issue 1 :
> The link is working fine for the first time. When I click the link for the
> first time...the new window opens fine with the detail report...but when i
> close the new window(detail report) and click the link nothing happens,no
> report open..why. Also if i keep the new window(of detail report) as is
> without closing and then click on the link again in the graph report...one
> more new window opens up with the detail report...
> Why does the link not work when i close the new window and re-click the link
> Issue 2 :
> I am trying this...
> The window we open the report in contains frames..and we open the report in
> the lower frame...how can i get the new detail report to open in the same
> frame.
> I tried using '_self'...but with this the new detail report is open on the
> main IE page where our actual application is been open.
> Any suggestions to get it working as I wish it tooo...would help.
> Thanks.
> SK|||On Mar 28, 2:02 am, Sunny <S...@.discussions.microsoft.com> wrote:
> I am still stuck on this...
> I am doubting this is some kind of setting issue or what
> I changed the URL link to simple
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> even this ...when i open it for the first time it works fine...but when i
> close the new window the link does not work anymore...
> fyi : we are deploying the reports in PDF format...any issue with adobe !!!
> I get a security warning saying document is trying to open a link...but
> nothing opens after the firsttime '
> any ideas....
> "Sunny" wrote:
> > We open reports in new window from an asp.net page..using rs.render method.
> > Recently we have new reports with graphs...when clicked on detail for graph
> > I have to open another report detail in a new window. I have created a
> > link(naviagtion URL property for textbox ) in the main graph report to open
> > the detail report link as
> > ="javascript:void(document.open('/ReportViewer/ReportFrame.aspx?rptPath=REPORTPATH&args="
> > & REPORTPARAMETERES &
> > "','_blank','left=50,top=50,width=800,height=580,menubar=no,resizable=yes,scrollbars=Yes,status=yes,toolbar=0'))"
> > Issue 1 :
> > The link is working fine for the first time. When I click the link for the
> > first time...the new window opens fine with the detail report...but when i
> > close the new window(detail report) and click the link nothing happens,no
> > report open..why. Also if i keep the new window(of detail report) as is
> > without closing and then click on the link again in the graph report...one
> > more new window opens up with the detail report...
> > Why does the link not work when i close the new window and re-click the link
> > Issue 2 :
> > I am trying this...
> > The window we open the report in contains frames..and we open the report in
> > the lower frame...how can i get the new detail report to open in the same
> > frame.
> > I tried using '_self'...but with this the new detail report is open on the
> > main IE page where our actual application is been open.
> > Any suggestions to get it working as I wish it tooo...would help.
> > Thanks.
> > SK
It sounds kind-of-like your browser settings are not allowing pop-
ups.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks EMartinez for the reply...
But no I have tried that...by allowing any popups...
It seems really strange...I think it has something to do with adobe
reader...no idea what?
The link opens fine for the first time...the second time i click ..nothing
happens except i get the security errror from adobe reader saying the
document is trying to open the link bla bla. The link works fine when the
window opened the first time is kept open!Whyyyyy
"EMartinez" wrote:
> On Mar 28, 2:02 am, Sunny <S...@.discussions.microsoft.com> wrote:
> > I am still stuck on this...
> > I am doubting this is some kind of setting issue or what
> >
> > I changed the URL link to simple
> > = "javascript:void(window.open('http://www.google.com','_blank'))"
> >
> > even this ...when i open it for the first time it works fine...but when i
> > close the new window the link does not work anymore...
> >
> > fyi : we are deploying the reports in PDF format...any issue with adobe !!!
> > I get a security warning saying document is trying to open a link...but
> > nothing opens after the firsttime '
> >
> > any ideas....
> >
> > "Sunny" wrote:
> > > We open reports in new window from an asp.net page..using rs.render method.
> > > Recently we have new reports with graphs...when clicked on detail for graph
> > > I have to open another report detail in a new window. I have created a
> > > link(naviagtion URL property for textbox ) in the main graph report to open
> > > the detail report link as
> >
> > > ="javascript:void(document.open('/ReportViewer/ReportFrame.aspx?rptPath=REPORTPATH&args="
> > > & REPORTPARAMETERES &
> > > "','_blank','left=50,top=50,width=800,height=580,menubar=no,resizable=yes,scrollbars=Yes,status=yes,toolbar=0'))"
> >
> > > Issue 1 :
> >
> > > The link is working fine for the first time. When I click the link for the
> > > first time...the new window opens fine with the detail report...but when i
> > > close the new window(detail report) and click the link nothing happens,no
> > > report open..why. Also if i keep the new window(of detail report) as is
> > > without closing and then click on the link again in the graph report...one
> > > more new window opens up with the detail report...
> > > Why does the link not work when i close the new window and re-click the link
> >
> > > Issue 2 :
> > > I am trying this...
> > > The window we open the report in contains frames..and we open the report in
> > > the lower frame...how can i get the new detail report to open in the same
> > > frame.
> > > I tried using '_self'...but with this the new detail report is open on the
> > > main IE page where our actual application is been open.
> >
> > > Any suggestions to get it working as I wish it tooo...would help.
> >
> > > Thanks.
> >
> > > SK
> It sounds kind-of-like your browser settings are not allowing pop-
> ups.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>