Most Efficient Way Of Returning Data

Feb 22, 2007

I have a number of tables, and I need to create a summary of the data.
Now I have two choices. I could create a stored procedure that creates a temp table with the filters applied. This would require 12 selects for each year (we have 3 years of data so far). This means 36 selects so far that fill a temp table.

The second choice is to create a table with the required columns and rows. As I am esspecially cross-joining 4 tables, the table would have about 10 x 10 x 12 x A rows, where A is a variable that will grow quickly. I can then keep this table up-to-date using triggers for each insert. Then all I need to do is use an aggregate funtion on the relevant filter.
My question is, which is more efficient. Creating a stored procedure that creates the table dynamically, or have a table with thousands of rows and using an aggregate function on these.
PS I am using SQL Server 2000
Jag

View 1 Replies

Efficient Way Of Backing Up Data

Jan 24, 2008

We are writing an in-house backup tools. It's written in C# and the program allows me to select which records we want to archive and remove from existing database. Functionally, we can acheive what we want to do but I feel that we did not do it right. Can someone show me the right way to go? Here is what we are doing in the backup process.

1) Create an archive database (with the file name, say archive_101.mdf)
2) Move the data from orginal database to archive database using the following SQL statement
INSERT INTO [archive_database].dbo.records SELECT a, b, c, date FROM [original_database] WHERE date < CONVERT(DATETIME, '2007-10-10', 120)
3) Delete the moved data from original database.
4) Of course, step 2 and step3 are done in a transaction
5) Then, we detach the archive database and store the file "archive_101.mdf somewhere.

I felt it's wrong because the performance is very bad. If I run the select statement, it takes me 1 min to dump all the data to console. If I run the same SELECT statement together with INSERT INTO, like what I wrote in step 2, it takes me more than 1 hour to write to another database. I checked the tempdb and its size does grow a lot when I am doing step 2. I know the data I am selecting is about 500MB large but still, it should not take that long. Can somebody give me any hints?

Thanks in advance.

View 5 Replies View Related

Efficient Way To Store Game Data: Help?

Jun 22, 2007

Hello everyone!

So I am trying to wrap my head around the most efficient way to store some game data in sql server 2005.

I wont list all of my tables or columns as there would just be too many, but will try to post as exact as I can.

Basically lets say I have 3000 monsters in my game, and over 8000 items some of which are monster loot.

Each monster could have 1 drop item or they could have 200 different item drops all depending on the monster.

So I am just trying to find the best method of creating a loot table.

So I have the following in mind.

[MonsterTable]

monsterID;

monsterName;

etc...

[ItemTable]

itemID;

itemName;

etc..

[LootTable]

lootTableID;

refMonsterID;

refItemID;

So I'm not worried about how I would get all of the data in there, I'm just wondering if this simple method of using Ref. ID's between tables would provide the best method for extracting the data when needed.

Any ideas or thoughts are more then welcome, any questions just ask, and thanks for any guidance or information you can provide on a more efficient manner.

View 1 Replies View Related

Are Data Sets Memory Efficient?

Feb 8, 2008

Hi,

I have designed a contact manager with Data Grid Control bound to a Data Set.

When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.

Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?

Please advise

View 3 Replies View Related

Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files

Apr 13, 2006

Hi,

I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.

I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.

I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:

SQL SERVER (I don't have permission for this)

or

FILE SYSTEM (did this one)

I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.

Hope somebody can point me in the right direction.

Regards,

Patrick Briggs,
Pasadena, CA

View 7 Replies View Related

Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files

Apr 18, 2006

I just spent some time working out how to do a seemingly simple task. I��m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.

My original question posed on several SQL newsgroups was based on this goal:

I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables. I want to do this without having to redo all the layout criteria 4 additional times.

Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):

Create the Package (data import)

1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file. At the CHOOSE DATA SOURCE window browse for your file.
2) Under the Advanced tab, you can set your Column attributes (��output column width�? or ��data type�? to name a few). I highlighted all the columns and selected ��string [DT_STR]�? for data type. To avoid truncation errors, I selected 255 for output column width. You can name the columns whose data you are most concerned with (I did import all the available fields).
3) After choosing a server destination you will have a ��SELECT SOURCE TABLES AND VIEWS�? window pop up. Under the ��Mapping�? column you can choose to tweak your mapping further editing in SQL (see Edit SQL button). I didn��t.
4) The ��SAVE AND EXECUTE PACKAGE�? will pop up. The ��Execute Immediately�? box should be checked and you should check the ��Save SSIS Package�? (SQL Server Integration Services). When you do, select ��File System�? for where to save this import-file-package to.
5) Click OKAY for the Package Protection Level and the ��SAVE SSIS PACKAGE�? window will appear. Browse for a path on your local computer to save to.

Modify Package (data import) for Next Use

6) In SQL Server Management Studio, browse for the Package and open it.

Preparation for SQL Task �� box

7) You should see a screen that shows two boxes (��Preparation for SQL Task�?) and (��Data Flow Task�?).
8) Right click on the former and select ��Edit�?.
9) On the ��SQL Statement�? row, click into the right column and select the ��? box
10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.
11) Click OK for the ��SQL Task Editor�?

Data Flow Task - box

12) Right click on the ��Data Flow Task�? box and select ��Edit�?.
13) Three boxes will appear ��SourceConnectionFlatFile�?, ��Data Conversion 1�?, and ��Destination - <whatever table name your original data import went to>�?. Below them is a section that displays ��Connection Managers�?

SourceConnectionFlatFile - editing

14) The first thing you will want to do is change the import source to a new flat file. You do this by going below the boxes under the ��Connection Managers�? window and right clicking on ��SourceConnectionFlatFile�? and then selecting ��Edit�?
15) Browse for the new ��File Name�? and select it.
16) A ��Microsoft SQL Server Management Studio�? window will pop up asking you if you want to ��keep or reset the existing metadata�?. The metadata is just your column definitions and choosing ��YES�? to keep this makes sense if you are doing data imports on files with the same file layout.
17) Still in the ��Flat File Connection Manager Editor�? window, change the ��Connection Manager Name�? to something meaningful (I add <_> at the end and then the name of the table the flat file is going to) and click OK.

SourceConnectionFlatFile �� box (editing)

18) Right click on the ��SourceConnectionFlatFile�? box and select ��Edit�?.
19) Your newly named ��Flat File Connection Manager�? should appear in select box.
20) Click OK, right click again on the ��SourceConnectionFlatFile�? box and select ��Show Advanced Editor�?.
21) Under the ��Connections Manager�? tab, your newly named ��Flat File Connection�? should appear (the prior step is necessary for the advanced editor to recognize your change).
22) Under the ��Component Properties�? tab, on the ��Name�? row, click into the right column and rename to something meaningful (notice the ��Identification String�? row description changes too once you click out of the ��Name�? row)
23) Under the ��Column Mappings�? tab, just confirm you are mapping your flat file fields (��Available External Columns�?) to a destination table��s fields (��Available Output Columns�?).
24) Under the ��Input and Output Properties�? tab you can check in ��Flat File Source Output�? to make modifications to either your ��External Columns�? or your ��Output Columns�? �� you shouldn��t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the ��Connection Manager Window�? for the ��SourceConnectionFlatFile�? as well as the ��Data Conversion 1�? box under the ��Data Flow Tasks�? window, so exercise caution
25) NOTE: This process has worked for me by making my source columns all ��string [DT_STR]�? data type and the output columns all ��Unicode String [DT_WSTR]�? data type.

Data Conversion 1 �� box (editing)

26) There is nothing you need to do here. By right clicking on the ��Data Conversion 1�? box and selecting ��Edit�?, you can see and change the data type of the output columns (the ones in the table your importing the flat file to). There are probably more edits one can do but they��re beyond what I��ve learned.

Destination - <whatever table name your original data import went to> �� box (editing)

27) Right click on the ��Destination - <whatever table name your original data import went to>�? box and select ��Show Advanced Editor�?.
28) Select the ��Component Properties�? tab.
29) Select the right column at the ��Name�? row and change the name to something meaningful (ie. related to the source file name or the table name you��re importing to).
30) Select the right column at the ��Identification String�? row and it will update to this change.
31) Select the right column at the ��OpenRowSet�? and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).
32) Click OK
33) Select FILE and select ��Save As��? and then give your package a new name that��s meaningful (this will be helpful if you have to rerun the import of the flat file later).

Run (execute) the Revised Package (data import)

34) Go back to SQL Server Management Studio and open the Object Explorer
35) Connect to an ��Integration Services�? component. This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).
36) In ��Object Explorer�? go down to your ��Integration Services�? object and expand it.
37) Expand ��Stored Packages�?
38) Right click on ��File System�? and select ��Import Package�? and an ��IMPORT PACKAGE�? window will appear
39) For ��Package Location�? choose ��File System�? and then browse for the ��Package Path�?
40) Click into the ��Package Name�? and it defaults to your Package��s file name.
41) Click OK and the Package is imported.
42) Right click on the newly imported Package and select ��Run Package�?
43) An ��Execute Package Utility�? window appears
44) Select ��Execute�? and the package runs.

View 1 Replies View Related

SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data

May 7, 2014

I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.

So here is the DDL, sample data, and the ideal return rows

CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,

[Code] ....

I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.

SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1

One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.

View 9 Replies View Related

Sql Not Returning Any Data

Feb 20, 2007

Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.

View 5 Replies View Related

Returning Column Name(s) That Contain Data

Jun 25, 2007

say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this: eg: ID.... vote1..... vote2.... vote3 1 0 0 1 2 1 0 1for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null ) Cheers!!!

View 1 Replies View Related

Query Not Returning Data!

Oct 21, 2007

Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

View 4 Replies View Related

READTEXT Not Returning All Data

Apr 12, 2004

SELECT @@TEXTSIZE
SET TEXTSIZE 4096
SELECT @@TEXTSIZE
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @ptrval 0 2000
GO

Thats my code. and it never returns the entire email template. why?

thx in adv

View 2 Replies View Related

Problem Returning Data

Apr 2, 2006

I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:
ALTER PROCEDURE dbo.usp_CalcDeliveryCharge
@mintDistance int,
@mintCustomer_ID int = 0,
@mintRate int = 0 OUTPUT,
@mstrZone nchar(10) = null OUTPUT
AS

/* SET NOCOUNT ON */

SELECT @mintRate=RATE,
@mstrZone=ZONE
FROM tblRates
WHERE Customer_ID=@mintCustomer_ID
AND Mile_Range_Min <= @mintDistance
AND Mile_Range_Max >= @mintDistance

And this is my code:
sql_Command.CommandText = "usp_CalcDeliveryCharge"
sql_Command.CommandType = CommandType.StoredProcedure
sql_Command.Parameters.Clear()
sql_Command.Parameters.AddWithValue("@mintDistance", intApproxMiles)
sql_Command.Parameters.AddWithValue("@mintCustomer_ID", Profile.CompanyID)
sql_Conn.Open()
sql_Reader = sql_Command.ExecuteReader()
While (sql_Reader.Read())
Me.lblZone.Text = sql_Reader.Item(0).ToString
End While
sql_Conn.Close()
sql_Reader.Close()
sql_Command.Dispose()

View 1 Replies View Related

2 Querys Not Returning The Same Data

Apr 17, 2008

I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:

SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis

View 4 Replies View Related

Query Not Returning Data

May 29, 2008

Hello everyone,

I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:

SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)

The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.

Any suggestion would be great.

Thanks

View 4 Replies View Related

Problem Returning SQL Data

Apr 9, 2007

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceCompanyCompany.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

 
<% Response.Write RS ("GuestName") %> <% Response.Write RS("GuestDescription") %> 
Related Links: 
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>



<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?

View 1 Replies View Related

Store Procedure For Returning Data

Apr 19, 2006

i want to return certain number of columns frm table using store procedure .and i hav to load those returned data into text boxes which i created in asp.net page.is there a way

View 2 Replies View Related

Returning Data From Webservice To SSIS

Jul 14, 2007

Hello,

I am trying to use the data returned by a webservice that our application team has built.

What method should they use to return the data to the caller ?

If they try to retun a data set the SSIS is saying that it is not a well formated XML.

The webservice is using an API call to HIS, can I make this call using C# or some other mechanisam directly from SSIS?

View 3 Replies View Related

4 Part Name Join Returning Too Much Data

Jan 12, 2008

Hello,

I recently created a linked server to db2. When I do a join on the linked server it is returning to much data.
select * from table1 left join [linkedServer]..Schema.Table as tb1 on table1.id=tb1.id

For now, I had to first get the ids I wanted from table1 and then use a subquery to join table1 on the a result set produced from a query to the linked server that only specified those ids. Below is an example

select * from table1 left join (select col1 from [linkedServer]..Schema.table where id=1 or id=2) as tb1 on table1.id=tb1.id

I'm guessing I have some setting wrong. One of the biggest reasons for me to use the four part name is so I can do joins like the first example.

Please let me know if you have any ideas.

Thanks

View 4 Replies View Related

My Report Not Returning Data For Only One Field

Jan 18, 2008

Hi all,

I have a simple query like this:

SELECT crop, ItemNo, LotNo, Site, Process, IntID, ObsDetail, ObsValue,

(CASE WHEN (rtrim(crop) = '010' OR
rtrim(crop) = '010W' OR
rtrim(crop) = '019' OR
rtrim(crop) = '019W' OR
rtrim(crop) = '018') THEN 'CORN' WHEN (rtrim(crop) = '0150' OR
rtrim(crop) = '0159' OR
rtrim(crop) = '0150W' OR
rtrim(crop) = '159W' OR
rtrim(crop) = '158') THEN 'RICE' ELSE '' END) AS Product, TestStartDate, TestEndDate, ObsString
FROM V_LAB
WHERE (LotNo >= @lotStart) AND (LotNo <= @lotEnd)

If I run this query and filled the lotStart and lotEnd parameter with: G0424MK
Then, below's the data returned. (I run the query in management studio)

010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCT MOIST

10.35

CORN

NULL

010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCT GOOD

97.5

CORN

NULL

010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCENTMEAN

97.75

CORN

NULL

I'd only going to retrive 3 data that i'd like to show on the report:
1. Moisture Content, with formula:
=IIf(RTRIM(Fields!ObsDetail.Value) = "PRCT MOIST", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK is: 10.35 (this is correct)

2. Physical Damage, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCT GOOD", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 97.50 (this is correct)

3. Warm Test, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCENTMEAN", Fields!ObsValue.Value, cdec(0.00))

the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 0.00 (this is wrong) because it should be shown 97.75

anybody knows which part i made my mistake?

Thanks!
Addin

View 6 Replies View Related

SQL Stored Proc Not Returning Any Data In The Web Page

Feb 26, 2007

Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.

If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then

Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()

Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)

Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)

Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)

'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output

'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)

Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1

If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If

End Try

Else
ErrorLbl.Text = "Please enter you username and password"

End If

View 5 Replies View Related

Query Analyzer Returning Messed Up Data

Jul 23, 2005

I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?

View 3 Replies View Related

Stored Procedure - Returning Data Using Parameters

Feb 12, 2008

Hello,

I am trying to get records related to a Device_ID with the following conditions:

IF LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate) OUTPUT
ELSE OUTPUT ALL.

I want records between the range of date selected, or all the records if no date entered. Can somebody help me ASAP. Here is what I have so far. I just don't know where to put the IF...THEN ELSE.

SELECT Device_ID,
Action,
Username,
LastModified_Date,
FROM ActivityMonitorActionLogs
WHERE Device_ID = ISNULL (@p_int_Device_ID, AL.Device_ID)
AND LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate)

Thanks a lot!!!
Mylene

View 3 Replies View Related

Error Returning OLAP Data In RS 2005

Mar 28, 2006

I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error:

An error has occurred during report processing.

Query execution failed for data set 'Three_Month_Funding_Trend'.
Unable to recognize the requested property ID 'ReturnCellProperties'.

Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine.

Thanks for your assistance

View 11 Replies View Related

Stored Procedure Returning An Empty Data Table

Nov 20, 2007

I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC

View 7 Replies View Related

SELECT * View Returning Wrong Column Data

Oct 16, 2007

SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW

View 12 Replies View Related

SQL 2012 :: Entity Framework Returning Cached Data

Mar 23, 2014

I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:

// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}

When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.

View 0 Replies View Related

Derived Column Returning No Data On GetDate() Function

Aug 1, 2006

Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?

View 5 Replies View Related

Transact SQL :: Returning Different Data When Part Of Query Is Separate?

Aug 3, 2015

I am writing a stored procedure to prepare some reports.

The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).

When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?

alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

[code]...

View 5 Replies View Related

SQL XML :: Importing Data From XML Into Database And NULL Returning Results

Sep 16, 2015

I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.

<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>

[code]....

View 4 Replies View Related

SQL Server 2012 :: DB2 Store Procedure Returning Two Data Sets

Oct 13, 2014

A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?

View 1 Replies View Related

SQL Server 2008 :: Spatial Data Not Returning Correct Distance

Apr 29, 2015

I have the two following locations.

They're both towns in Australia , State of Victoria

Fitzroy,-37.798701, 144.978687
Footscray,-37.799736, 144.899734

After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:

Fitzroy, 0xE6100000010C292499D53BE642C0A7406667511F6240
Footscray, 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240

In my SQL Query, I have the following which works out the distance between both towns. Geo being my Geography column

DECLARE @s geography = 0xE6100000010C292499D53BE642C0A7406667511F6240 -- Fitzroy
DECLARE @t geography = 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240 -- Footscray
SELECT @s.STDistance(@t)

The result I get is

6954.44911927616

I then looked at formatting this as in Australia we go by KM so after some searching I found two solutions one for Miles and the other KM

So I changed Select statement to look like this

select @s.STDistance(@t)/1000 -- format to KM

My result is then

6.95444911927616

When I go to google maps and do a direction request between the locations provided above it says 10.2km (depending on traffic)

Now I'm new to this spatial data within SQL, why would I get a different result from google maps?

Also I would like to round this number so its easier to use within my where statement so I'm using Ceiling as shown here:

SELECT CEILING(@s.STDistance(@t)/1000)

Is ceiling the correct way to go?

Reason I need to round this is because we are allowing the end user to search by radius so if they pass in 50km I will then say

Where CEILING(@s.STDistance(@t)/1000) < 50

View 2 Replies View Related

SSRS 2005 Report Stopped Returning AS 2000 Data

May 2, 2008

Hello:

Here is the scenario: I have a report in SSRS 2005 that uses an OLE DB connection to send a MDX query to an AS 2000 cube. The report has been working fine for weeks. When the report ran today, it returned the row and column metadata, but not the cell values.

I ran the MDX query separately in the SSRS query editor pane, and it returns all values properly. I was also able to perform the query using ProClarity, so it appears that the issue is between the result set and SSRS.

What would cause this to happen?

Thanks for your help!

Tim

View 4 Replies View Related

Flat File Data Flow Source Returning Limited Rows

Jan 14, 2008

All,
I'm having an issue with the Flat File Data Flow Source returning only a limited set of the rows that are in the flat file. Basically, I connect to the flat file fine, it goes to retrieve the data (tab delimited file) and only returns 190 of 392 rows. Is there a limitation on the # of rows this data flow source can retrieve or something? I've look all through the settings and properties of the task as well as the connection manager and nothing is obvious as to what is causing this. Hopefully someone ou tthere has run into this before and can help me retrieve all rows. Thanks in advance!

bakerz

View 4 Replies View Related