Reporting On Delimited Fields.... Best Practice? Suggestions?

Feb 3, 2004

Hi guys... I need to do some statistical reporting on the answers to registration questions. The answers are stored in pipe-delimited varchar fields to allow for one-or-many answers to a single question... here's an example:





Q: What are your fav colors?


A: [checkbox list of colors]





Now the data is stored like this:





Reg001


-----------


red|blue|green|yellow





Now I need to determine how many people selected each individual answer, so like, how many people picked red, how many people picked yellow.... to report like this:





Red - 12


Blue - 23


Green - 15


Yellow - 9





What is the best, or suggested way, to go about splitting this data up and reporting on it? Can it be done in T-SQL, or will it need to be a combination of T-SQL and .Net language?





Thanks!





-e

View 1 Replies


ADVERTISEMENT

User-defined Functions - Best Practice Suggestions.

Jun 20, 2006

I've got a couple guys on my team who seem to think user defined functions are a "risky" feature in SQL 2000. I've been using them for years without problems. Yeah, this one of those loaded prove me right or flame me questions ... whatever. I promise not to consider posts on MSDN forums authoritative answers to this practice question. I'm just trying to see where people stand. What I'm interested in knowing is:

Do you consider UDF's in SQL advanced, hard to figure out, hard to get right or risky?

Any cases where they've caused you more problems than other MS SQL features?

Random hints on best-practice for UDF's - where do they spead things up, slow things down?

Reasons that they are better than sliced bread / cheese spread?

View 5 Replies View Related

Best Practice For Ragged Delimited Text Import

Aug 9, 2007

I have tab delimited text files which may have optional fields (meaning they can be not present at all) to the right of the required fields that I care about. It would appear that using a Flat File Connection with Delimited Format (tab) set will choke if it is initially configured with a file that has something like:

data data data


and it then encounters

data data data optionaldata


It chokes. I know this could be parsed line by line, but that seems silly. It seems like there should be a way to ignore columns beyond a certain point (e.g. Format "Delimited Ragged Right").

Is there some way to do this with a directly with a flat file connection?

Thanks,

--Andrew

View 1 Replies View Related

Suggestions On Maintaining Audit Fields

Apr 14, 2008

Currently all of our tables in several databases have the following columns:

user_added (this is nvarchar)
host_added (this is nvarchar)
date_added (this is datetime)
user_modified (this is nvarchar)
host_modified (this is nvarchar)
date_modified (this is datetime)

Right now our policy is that (a) the _added columns use defaults to populate the data on INSERTS and triggers are generated to update the _modified fields upon an UPDATE of the table.

Our practice has been (a) to manually create these fields in our scripts as we create new tables in our system and (b) create triggers to perform the update anytime we create a new table.

This practice has been fine until recently where we have been outsourcing some of our development and not all of our standards have been adhered to, including this one. I'd like to look at alternatives for somehow maintaining these concepts outside of our development workflows.

The first thing I'd like to inquire about is regarding options to eliminate having developers include these columns in the CREATE TABLE statements. Is it possible in SQL Server 2005 to capture when an CREATE TABLE statement is executed and override/append to the initial CREATE TABLE statement?

The second thing I'd like to inquire about is regarding options to eliminate having developers write the initial trigger that maintains the _modified fields. I guess if there are options to capture when a CREATE TABLE statement is executed, we could possibly generate a CREATE TRIGGER statement against that object as well?

Another idea I would like thoughts on are using some sort of 'table inheritence' to store this information for all objects in our database? This idea come up when I saw this article - http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server. Do you think the situation I explained here would fall into this concept?

I'm also open to any other thoughts and/suggestions.

View 2 Replies View Related

Best Practice For Storing Long Text Fields

Sep 12, 2006

As we all know, there is a 8060 bytes size limit on SQL Server rows. Ihave a table which requires a number of text fields (5 or 6). Each ofthese text fields should support a max of 4000 characters. We currentlystore the data in varchar columns, which worked fine untill ourappetite for text fields increased to the current requirement of 5, 6fields of 4000 characters size. I am given to review a design, whichesentially suggests moving the text columns to a separate TextFieldstable. The TextFields table will have two columns - a unique referenceand a VARCHAR (4000) column, thus allowing us to crossreference withthe original record. My first impresion is that I'd rather use the SQLServer 'text' DB type instead, which would allow me the samefunctionality with much less effort and possibly better performance.Can anyone advise on advantages and disadvantages of the two optionsand what the best practice in this case would be.Any advise will be well appreciated.Tzanko

View 7 Replies View Related

Best Practice For Add, Edit Records Into Database With Lots Of Fields ?

Feb 7, 2006

What's the best practice for adding / editing a record into a database with lots of fields ?I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.
Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.
But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand,  with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...
What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).
With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.
Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.
So come on guys (and gals)... let's hear your thoughts on how you would handle the insert  / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)
 

View 1 Replies View Related

Suggestions For A Reporting System

Jul 23, 2005

Hi,We will be developing a management reporting software for a bank. Theuser will see reports that get updated in near real time( e.g. every 5min), with data regarding transaction amounts, etc. across a number ofdimensions such as day, time, region, etc.The dimensions will be hierarchical in nature. So a zone will havestates and a state will have cities.The raw data has to be pulled from several different databases, such asa DB for ATM transactions, another for home loan applications, etc.It should be easy to add customized reports if a different view of thedata is desired.Our clients suggest that we use a tool called Clementine developed bySPSS. But we have the liberty to choose a different tool if that servesour purpose.Clementine seems to allow a data flow to be defined which might be ofuse to pur project. Does anyone have any idea how this could bedifferent from SQL server's Data Transformation Services?Any other thoughts regarding the approach to be taken, will beappreciated.ThanksYash

View 3 Replies View Related

Web Based Reporting - Suggestions Needed

Jul 20, 2005

I want to create a web browser based MIS suite to use our data held onSQL 7. We have Dreamweaver and Crystal Reports available but I want toknow if these are suitable or if there are any recommendations forsomething I can pick up quickly.It will be simple reports based on complex queries (I can handle mostof this in SQL 7), don't need fancy graphs, just plain boringfinancial reports are what I need to produce.Obvious things I need to create are combo boxes and list boxes forselections. My background is more Delphi/VB and SQL than web basedstuff, so please let me know any recommendations or good places tostart looking.ThanksRyan

View 2 Replies View Related

Best Practice For Locating Reporting Services

Jan 4, 2008



I have been asked to look into the best (read practical, efficient, easy to maintain) scenario for setting up a Reporting Service environment.

Ideally what we are looking at is a SQL Server (maybe more than one) for Production data, this will support SharePoint and most other data sources including linked Oracle sources. Data will be distributed to both internal and external applications from this server, possibly located in the DMZ.

A second server would have Reporting Services on it. The thinking here is that this would possibly help take a load off of the production SQL Server and would also isolate Reporting Services from the Production Data box.

A third server would be a SQL Server with test databases and a place for Reporting Services development and testing.

Does this all make sense?

Are we over looking something?

Will we be causing any problems with SharePoint or anything else by adding the extra "hop" because Reporting Services is on a different server than the database?

Are we gaining anything splitting data and Reporting Services?

Thanks in advance
Terry

View 3 Replies View Related

Reporting Services :: Concatenate 24 Columns With Semicolon Delimited Into Single Column

Sep 17, 2015

How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....

View 2 Replies View Related

Reporting Services And Numeric Fields

Aug 31, 2007

Hi

I've generated a report using Reporting Services, and some of my fields are numeric with the value zero. On generating the report this is shown as 0.00 and not 0. Is this something that is easily fixed?

View 1 Replies View Related

Reporting Services :: Find Row Sum On Calculated Fields?

Jul 20, 2015

I have a simple report with a row grouping on teh Date and record_id from an sql query. I would like to find the row total.

The expression I have on the row is:

=IIF(Max(Fields!closing.Value) > 0, Microsoft.VisualBasic.Interaction.IIF(Max(Fields!opening.Value) > 0, IIF(Max(Fields!closing.Value) > Max(Fields!opening.Value), Max(Fields!closing.Value) - Max(Fields!opening.Value), Nothing), Nothing), Nothing)

View 2 Replies View Related

Reporting Services :: Dynamic Fields In SSRS

May 21, 2015

I have a SP like this:

ALTER PROCEDURE [dbo].[ReportGateWay]
(
@ISO bigint= 0,
@Gateway bigint= 0
)
AS
BEGIN
DECLARE @SQL nvarchar(max)

[Code] ....

I need to invoke this in SSRS. The problem is that when on creating dataset for this, I get an error which read:

You must have atleast one field for your dataset

What can be done in this case?

Here is the SQL Fiddle: [URL] ....

View 16 Replies View Related

Reporting Services :: SSRS - Using Fields That Are Already A Parameter

Jun 15, 2015

Currently i am setting up a report. The data source comes from pre-defined custom datasets where i've pre-entered the parameters for both Month and Year.

I already have one table up and working, but now i need a new table below it with a % column for each month so it gives visibility for the trend in recent months. As the interactive parameters are already in place; when i try to create the table it comes through with the specific month that the parameters are set too, and change when the parameters are altered.Is there a way to ignore the parameters for this table; or get around it?

View 2 Replies View Related

Reporting Services - Fields Cannot Be Used In Page Headers Or Footers

Nov 11, 2006

Hello all,

I am creating a report in SSRS and the data is obtained through a stored procedure. One of the parameters is Begin_Date. This is simply a date field. I need to have it on the Page Header section. However, when I placed it there and ran the report, I received the following error -

Error2[rsFieldInPageSectionExpression] The Value expression for the textbox ‘begindate’ refers to a field. Fields cannot be used in page headers or footers.

Is there a way around? I have never faced such a problem when I created reports in Crystal or ActiveReports. Please let me know whether I am missing anything.

Thanks in advance,
Saurav

View 2 Replies View Related

Reporting Services :: Could Not Update List Of Fields For Query

Oct 7, 2015

I have the following store procedure which execute perfectly fine Under SSMS as it is :

-- Insert statements for procedure here
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode

[Code] ....

But then when I try to create a dataset using ReportBuilder and pointing to that StoreProcedure, I get the following error message box :

"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. An  item with same key already been added" ...

View 3 Replies View Related

Reporting Services :: Tablix Causing Other Fields To Move

Apr 22, 2015

I've got a report consisting of nested list items and a tablix inside the last list.

As seen below - the tablix seem to shift the  "Brand No:" textbox details to the right when it becomes bigger.  How do I prevent this? Is there some anchor property of the text boxes that link them to the tablix?

View 2 Replies View Related

Reporting Services Formatting Fields From Whole Minutes To Hours

Jun 6, 2007

Dear All,

I have a problem formatting a field in Reporting Services (minutes to hours).

I have a field called duration which stores time in whole minutes only. I can format this into hours within mssql using the following:

cast(sn.duration/60 as varchar(5)) + ':' + RIGHT('0' + cast(sn.duration%60 as varchar(2)), 2)

But I need to have totals and average columns in my report, which means that the data must come through to RS in the minutes format so I can perform the calculations there.

I have the first part (I think!!):

=string.format("{0:0}",Fields!SalesTime.Value / 60) + ":"

But I cannot get the minutes part working!



Any help would be gratefully received.



Dan

View 3 Replies View Related

Reporting Services :: Add Two Different Custom Calculated Fields From Two Different Datasets In Matrix

May 22, 2015

I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.

So it reports for example how much we billed in May 2015, 2014 and 2013.

I have got the table to display this when reporting on one dataset by grouping by year

The datasets both look similar to this 

SELECT
  Practice.ibvSalesByJob.JobBilledExVAT
  ,Practice.ibvSalesByJob.[Year]
FROM
  Practice.ibvSalesByJob
  INNER JOIN Practice.idvJobType

[Code] ...

The calculated field is 

=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")
The field I am looking to add together is below
=Sum(Fields!JobBilledExVAT.Value)

If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.

I tried this

=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")

However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.

I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.

View 5 Replies View Related

Reporting Services :: Lookup Matching Fields In Rows Of Data

Jul 16, 2015

Our division has approximately 300 employees.  We have an annual shift bid where seniority is calculated using Date of Hire.  If 2 or more employees share the same Date of Hire then we fall back to Date of Application.  Currently the SSRS report does a very simple query and shows all the employees in order of their Date of Hire.  If they match then it sorts the matching Date of Hire entries alphabetically by the employee name.  It then becomes the task of the scheduler to locatethe entries with the same Date of Hire and manually look up the employees' dates of applications then sort them accordingly and re-write the report. 

Goal:
Convert the manual process into an automatic process by modifying the current SSRS report.
Data:
The dataset is "DivDir" which contains the following fields:  "EmpName", "DofHire", & "AppDate". 

EmpName    DofHire         AppDate
Adam ......   12/2/1996      11/15/1996
Bob ..... .... 1/16/1997       12/27/1996
Charlie ....... 1/16/1997      12/12/1996
Dan ......   ... 4/11/2001     3/22/2001

In the above example I want the SSRS report to list the employees in this order:  Adam, Charlie, Bob, Dan.How do I do this programmatically using SSRS?

View 7 Replies View Related

Reporting Services :: Using Join Function For Fields In SSRS 2008

Jun 30, 2009

we need the list suppliers in a text box,if it were a parameter, we can do it by =join(parameters!Supplier_ID.Value,",")but if the Supplier Id is a field , join (Fields!Supplier_ID.Value,",") doesnt work!how to achieve this.

View 9 Replies View Related

Reporting Services :: Power View - Text Wrap For One Of Fields

May 20, 2015

When I create a Power View on the SQL server after loading up my data model, how do I turn on Text wrap for one of the fields?

View 2 Replies View Related

Reporting Services :: Bullet Chart In SSRS - Set Maximum Value In Linear Scale Properties To Highest Value Of 4 Fields

Nov 24, 2015

I'm using a bullet chart in a SSRS report and I want to set the Maximum value in the Linear Scale properties to highest value of the following 4 fields. Is there any way to do this??  This will make all charts line up properly.

NC_LAST_YEAR
NC_LINKED
NC_CURRENT
NC_PLAN

View 5 Replies View Related

Retrieve Fields List From A Published Report Data Model Published On The Reporting Server.

Jul 17, 2007

Can someone please tell me how to retrieve/query the list of fields from an entity of a report data model that has been published on the reporting server programmatically ?

I am trying to upload a report data model to the reporting server and planning to use that model as the data source and consume it through our existing web application?

Thank you ,

Rashid A. Khan

View 1 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


I have just started using SQL Server reporting services and am stuck with creating subreports.

I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.

For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.

When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.

Am I missing something here? Any help is appreciated.

Thanks,
Sirisha

View 3 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Public Overridable ReadOnly Default Property Fields() As ADODB.Fields

Jan 26, 2008

sir

I have got this error message to establish connction with recordset vb .net, Can you please rectify this

Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'

my code like this


rs = New ADODB.Recordset

rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)


If txtUserName.Text = rs.Fields.Append(userid) Then


MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")

End If


thanks

View 1 Replies View Related

Need Help...any Suggestions??

Jul 2, 2007

here is my schema...












Board of Zoning Appeals

Parcel#
BZACase#
ApplicantID
OwnerID
DateFiled
Size
Zoning











VU (Variance of Use)




BZACase#
ProposedUse
Comments











VDS (Variance of Developmental Standard)


BZACase#
OrdinanceReq
RequestedDim
ProposedUse
Comments
















SE (Special Exception)

BZACase#
CurrentUse
ProposedUse
OrdinanceReq
RequestedDim
Comments











Applicant

ApplicantID
FirstName
LastName
CompanyName
Line1
Line2
City


State
Zip
PhoneNum















Owner

OwnerID
FirstName
LastName
CompanyName
Line1
Line2
City


State
Zip
PhoneNum



Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is
1.   how do i get the last three numbers to increment each time a new application is created?
2.  how do i retrieve the last record in the table???
3.   Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???

View 11 Replies View Related

Suggestions Please

Mar 26, 2003

I am requesting suggestions to solve my problem.

Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.

Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).

I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.

This info in this table should:

Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)

Be updated: if the customer order has been updated (i.e. the commission was changed)

Be inserted: if the order exists but the order number is not in the new table.

That is it in a nutshell.

Thanks,
Brian

View 1 Replies View Related

Need Suggestions

Sep 25, 2006

hi
i have written a procedure for stock report.
its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks

Note: User is required to execute this procedure daily.
i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spUpdateStock
@strReturn varchar(70) output
AS
BEGIN
declare @maxDt smalld atetime
if exists(Select * from Stock_Dump where Txn_Date=
Convert(varchar,Getdate(),101))
BEGIN
set @strReturn='Stock Table already generated
for the day. cannot generate it again'
END

ELSE
BEGIN
TRUNCATE TABLE Stock_Dump_Temp
select @maxDt=max(Txn_Date) from Stock_Dump
/* insert (Opening stock) Closing stock for all
all the products from last max Date*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,
Closing_Stock as Opening_Stock ,
0,0,0,0,0,0,0 from Stock_Dump Where
Txn_Date=Convert(varchar,@maxDt,101)
/* Issues*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,
Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details
Where Issue_No IN(Select Issue_No from Issue_Hdr
Where Issue_Date > Convert(varchar,@maxDt,101) and
Issue_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Goods receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details
Where Dlv_Note_No IN(Select Dlv_Note_No from
Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and
Dlv_Note_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Rejection after receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,Sum(Qty) as Rejected,0,0,0,0 from
Rejection_Details Where Rejection_No IN
(Select Rejection_No from Rejection_Hdr Where
Rejection_Date > Convert(varchar,@maxDt,101) and
Rejection_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Issues returns*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,Sum(Qty) As Issue_Returns,0,0,0 from
Issue_Return_Details Where Issue_R_No
IN(Select Issue_R_No from Issue_Return_Hdr
Where Return_Date > Convert(varchar,@maxDt,101) and
Return_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical Stock + */
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from
Physical_Details Where Var_Qty>0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical -*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from
Physical_Details Where Var_Qty<0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/
INSERT INTO Stock_Dump Select Product_code,Txn_Date,
Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as
Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as
Rejected,Sum(Issue_Returns) as Issue_returns,
Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as
Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp
Group By ProducT_Code,Txn_Date
/* update closing stock*/
UPDATE Stock_Dump Set
Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M))
Where Txn_Date=Convert(varchar,Getdate(),101)
/* delete unwanted records */
DELETE From Stock_Dump Where Opening_Stock=0 and
Issue_Qty=0 and Purchase=0 and Rejected=0
and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0

set @strReturn='Stock Table Update Successfully'
return
END

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO








suji

View 1 Replies View Related

Any Suggestions??

Nov 21, 2006

I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..

View 2 Replies View Related

Suggestions

Dec 8, 2006



I want to transform textfiles to sql server set based and not row based.what would be the best way to transfer.

let me know.

View 10 Replies View Related

Need Some Suggestions.

May 4, 2008



Hello all!

I have this simple sp.

SELECT VisName
FROM tblVis
WHERE (VisID = 1)

Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code




Code Snippet
Dim constr As New SqlConnection(PVDBConn)
Try
'Variable to hold the results
Dim results As String = String.Empty
cmdUpd = New SqlCommand("SelVis1Name", constr)
cmdUpd.CommandType = CommandType.StoredProcedure
constr.Open()
'Set results to the value returned from ExecuteScalar()
results = CType(cmdUpd.ExecuteScalar(), String)
constr.Close()
'Set our buttons text to that value
Button1.Text = results
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try






At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?

And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?

TIA!

Rudy

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved