|
how to write in sql storeprocedure
ForExample:3 rows like
flag1 flag2 flag3
1 malini salini
2 devi rani
3 vani vijaya
here flags are fields i need to swap complete 1 row to other row
like this,
flag1 flag2 flag3
1 malini salini
3 vani vijaya
2 devi rani
Priyanka
|
|
|
|
|
i wanna to make data visualisation for selected numerical fields in star coordinates(1 star/row)
 please help me as soon as possible THANKS
-- modified at 11:57 Tuesday 28th November, 2006
|
|
|
|
|
Is this homework ? as we can't see figure below ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hello,
I want to update a database. the code i have below gets all the values from a table called roles, and populates a dataset.
I then have a datatable that i want to add some new rows too. I then add this rows to the data table and add the data table to the dataset.
I then want to update the database by calling the dataAdapter update command. The code below as a problem with the update.
I have been told that the insertcommand would be better, but not sure, as i have never used that below. Can anyone give any advice on this.
Many thanks in advance,
Steve
using VB 2005 and SQL Server 2005
<br />
Dim i As Integer<br />
Dim dt As New DataTable()<br />
Dim dr As DataRow<br />
Dim userID As Integer<br />
Dim ds As New DataSet()<br />
Dim da As New SqlDataAdapter()<br />
Dim cmd As New SqlCommand()<br />
Dim cnn As New SqlConnection<br />
Dim cb As New SqlCommandBuilder()<br />
<br />
dt.Columns.Add("UserID")<br />
dt.Columns.Add("GroupID")<br />
Try<br />
cnn.ConnectionString = "server=.; database=serviceMaster; integrated security = true"<br />
cnn.Open()<br />
cmd.Connection = cnn<br />
cb.DataAdapter = da<br />
cmd.CommandType = CommandType.Text<br />
cmd.CommandText = "SELECT * FROM Roles"<br />
<br />
da.SelectCommand = cmd<br />
da.Fill(ds)<br />
dt = ds.Tables(0)<br />
<br />
Catch ex As Exception<br />
<br />
End Try<br />
<br />
userID = 24<br />
Dim chklstIndexes As CheckedListBox.CheckedIndexCollection = Me.chkLstSecurityRoles.CheckedIndices<br />
<br />
For i = 0 To Me.chkLstSecurityRoles.CheckedItems.Count - 1<br />
dr = dt.NewRow()<br />
dr("userID") = userID<br />
dr("GroupID") = chklstIndexes.Item(i)<br />
dt.Rows.Add(dr)<br />
Next<br />
<br />
'Add to the dataset and use the dataAdapter to update the database<br />
Try<br />
ds.Tables.Add(dt)<br />
cb.GetUpdateCommand()<br />
da.Update(ds)<br />
Catch ex As Exception<br />
<br />
End Try<br />
|
|
|
|
|
You have a few choices
1) Set your datadapter to write back to the database using update, insert commands configured for your dataAdapter
2) Use SQL commands or storedprocedures to update your data
3) Create an object class with a save method to save data back to the database.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
How do i do a funtion o procedure to add or Sum two values from differents columns.
La Light
|
|
|
|
|
Select (tbl1.value + tbl2.value) as ColSum from tbl1,tbl2
Keep Smiling !!! Tomorrow Never Die
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
I need to write a stored procedure that meets the following specification
Tables Involved:
TblCustomer - CustNo,CustName
TblInvoice - InvoiceNo,IssuedDate,CustNo
TblProduct - ProductCode,ProdDesc,ProductFamily,GroupKeyID,FRB,SourceID,MarketSegmtID,ConvFactor
TblInvoiceDetailProduct - InvoiceNo,ProductCode,Quantity,UnitPrice
TblInvoiceDetailGL - InvoiceNo,GLCode,Quantity,UnitPrice
TblInvoiceDetailNonProduct - InvoiceNo,
TblInvoiceDetailProductNonInventory - InvoiceNo,ProductCode,Quantity,UnitPrice
Fields to be selected - CustName,ProductDescription,ProductCode,SourceID,FRB,MktSegmtID,Quantity( Sum up for the Invoices' of the particular period),Value - Summation of all the Invoices' quantity * convfactor in ProductTable
Using the CustNo from TblInvoice and TblCustomer,CustName should be selected
If the ProductCode of ProductTable matches with the following tables
InVoiceDetailProduct
InVoiceDetailProductNonInventory
InVoiceDetailGL
InVoiceDetailNonProduct,
We have to retrieve the corresponding fields : ProductDesc,SourceId,FRB, MktSegmentID From ProductTable
Quantity - For the particular period,summing up of all the Invoices' Quantity
Can anyone let me know how I can write a stored procedure that meets the specification?That would be a great help!
Thank you!
Sincerely,
Deepa
|
|
|
|
|
Hi,
I dont think that asking others to write stored procedure for you will be good for you. You can search for syntax and understanding of stored procedures on google.com. Then if you have any specific problem, everybody here is sitting for solving each others problems. I am sure someone will help you.
As far as the syntax of stored procedures you can visit - http://www.microsoft.com/sql/techinfo/books.mspx[^]
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hi,
I came up with the following stored procedure.But I couldn't any data.Can anyone let me know the issue behind?
Create Procedure dbo.procMARRPTMonthlySalesByCustomer_Final
@FromDate smalldatetime='',
@ToDate smalldatetime=''
As
Set NoCount On
if @FromDate =''
Begin
Select @FromDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @FromDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
if @ToDate =''
Begin
Select @ToDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @ToDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDp.Quantity) As "Billing Quantity",
Sum(InvDp.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDp.Quantity) * InvDp.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProduct InvDp
inner join
dbo.MARTblInvoice Inv
on InvDp.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDp.ProductCode = P.ProductCode and InvDp.SourceID = P.SourceID and InvDp.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDp.UnitPrice,
InvDp.Quantity
union
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDpNonInv.Quantity) As "Billing Quantity",
Sum(InvDpNonInv.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDpNonInv.Quantity) * InvDpNonInv.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProductNonInventory InvDpNonInv
inner join
dbo.MARTblInvoice Inv
on InvDpNonInv.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDpNonInv.ProductCode = P.ProductCode and InvDpNonInv.SourceID = P.SourceID and InvDpNonInv.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDpNonInv.Quantity,
InvDpNonInv.UnitPrice
Select Cust.CustName,
GLC.GLDesc As "ProductDesc",
InvDetGL.GLCode As "ProductCode",
'' As SourceID,
'' As FRB,
'' As MarketSegmtID,
Sum(InvDetGL.Quantity) As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDetGL.Quantity) * InvDetGL.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailGL InvDetGL
inner join
dbo.MARTblInvoice Inv
on InvDetGL.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblGLCode GLC
on InvDetGL.GLCode = GLC.GLCode
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDetGL.Quantity,
InvDetGL.UnitPrice,
GLC.GLDesc,
InvDetGL.GLCode
union
Select Cust.CustName,
InvDNonP.ProductName As "ProductDesc",
'' As "ProductCode",
'' As "SourceID",
'' As "FRB",
'' As "MarketSegmtID",
0 As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDNonP.Amount) As "Value"
From
dbo.MARTblInvoiceDetailNonProduct InvDNonP
inner join
dbo.MARTblInvoice Inv
on InvDNonP.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDNonP.ProductName,
InvDNonP.Amount
Thank you,
Sincerely,
Deepa
|
|
|
|
|
Hi there,
I am passing a LocationCode to a Stored Proc in my SQL report, and then the report is build around that parameter I passed.
The problem is, now I need to display the Location Name in the report footer for each page. I can currently display the LocationCode using the following code:
<br />
"Parameters!LocationCode.Value"
How can I get the LocationName for that LocationCode, without using "ReportingItems!txtLocationName.Value" - if I use this, I can only display the Name on the first page in a PDF doc.
|
|
|
|
|
Are you using Reporting Services ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hello,
I'm working on a project which carrying a large scale of database (over 5 millions records).
I'm looking for a way to improve the communication speed with the SQL server in any way that i can find useful.
The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes and this amount of time is unreasonable.
can anyone please advise how to do a different process method?
thanks,
Leo S
|
|
|
|
|
Hello,
Are you using Store Procedure for insert and update ?
Use set nocount on/off in store procedure.
Regards,
S Kumar
|
|
|
|
|
Lior S wrote: The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes
What is being updated? What else is the SQL Server doing at the time of the update?
It may be a poor data model, or it may be that there are so many transactions taking place at the same time that the SQL Server cannot cope.
Tweaking the data model may help. So might the creation of staging tables. (Where you queue changes to the data and then have a process that implements the changes once in a while).
But, you really haven't provided enough information. The reason for the slow down may be due to a million and one possible things.
|
|
|
|
|
basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update.
i have used the c# SqlBulkCopy which provided me a good method to insert data quickly but still i don't have any solution for the update.
|
|
|
|
|
Lior S wrote: basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update.
Sounds like the search component isn't very efficient. Are you searching on the primary key, or an index? If not make an index from the columns on which you are searching.
|
|
|
|
|
HI,
I need to know what is the best way to create a database on SQLSERVER 2000 for a holding and its subsidiaries.
Same server will be used.
Same database structure will be applied for all subsidiaries which have common data(in tables like country, city, agency, client, product), but at the same time, transactional data specific to each one on transactional tables.
Is it better to create a database for all common tables and another one for the other tables (with creating triggers , so that we have same data on all databases) OR Creating one database for all subsidiaries? OR is there another way?
Advantages and disadvantages ??
Thanks in advance,
Regards,
|
|
|
|
|
Myra,
This depends a lot on security, and how much data you need to proccess and what schema your data will be organised in.
I suggest you model your data then make a choice from there.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Thanks Frank,
I'll try to give you more details, maybe you would clarify the situation to me.
I have until now 4 subsidiaries for this holding, each one having to book spots for different TV channels.Each subsidiary have one or more channels.A user should be related to one subsisiary and can only view and work with the booking orders of this subsisiary. However, he can have access to product , client, agency tables which are shared by all users in the holding. cause u know that, for instance, the agencies and the products are the same.
as an example, one subsidiary could have booked around 300 000 spots per year, for 5 channels.Another one , having one channel, 60 000 records for booking Order per year
I'm a little bit lost
Thanks in advance
|
|
|
|
|
Basically I cannot connect (in certain circumstances to SQL Server 2005 (Express) on my laptop (from my laptop)).
Configuration:
Window XP SP2 (not part of domain)
SQL Server 2005 Express
Visual Studio .NET 2005 Standard
SQL Management Studio Express
The service is running as is the SQL Browser Service
I can connect to the server using SSME (Management Studio) but not using SqlConnection nor osql nor sqlcmd nor VS2005 Server Explorer.
Shared Memory, Named Pipes and TCP/IP connections are enabled and I've been trying to log in through Windows Authentication (work requires it although not essential for development).
Does anybody have any ideas on what might be causing this lack of connectivity, I've run through the articles in the MS KB on configuring the XP firewall, even tried disabling it with no luck, still same error message:
<span name="intelliTxt" id="intelliTxt" style="margin-top: -15px;"></span>An error has occurred while establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection
to SQL Server). Which reminds me remote connections are enabled in SSME.
Don't know what's going on since it was running under Win2K just fine (BTW removing SP2 is unfortunately not an option).
|
|
|
|
|
What is the status of windows firewall? Is port 1433 open for SQL server TCPIP connections?
What does your connection string look like?
|
|
|
|
|
Firewall is enabled, added the default instance of sqlservr.exe (only one running) to the exceptions list and opened port 1433 as well but to no avail.
Note I have also tried disabling the firewall as well but that doesn't seem to do anything either, what's bugging me is that SSME can connect but nothing else, not even VS.
Connection strings tried:
Data Source=(local);Initial Catalog=MIDAS;Integrated Security=true;
Data Source=seafire;Initial Catalog=MIDAS;Integrated Security=true;
Data Source=<ipaddress>;Initial Catalog=MIDAS;Integrated Security=true; Also tried variations using an SQL authentication but that doesn't seem to work.
On a different note another possibility would be if I could get the following scenario to work.
SQL 2005 on Win2K with ZoneAlarm as firewall (at least this is easy to configure).
Accessing from WinXP SP2 from laptop.
Both can be (and are I think) part of the same workgroup but there is no domain involved. IP address of desktop with Win2K is fixed, laptop is assigned dynamically from the router through which we access our broadband.
Either is a possibility, but using SQL on laptop is preferable (but only slightly to accessing it from the desktop).
Many thanks
|
|
|
|
|
Fixed it (I think), changed the connection string to use Data Source=SEAFIRE\SQLEXPRESS. At least I got a different error which showed that the connection was thrown out by SQL because of permissions so I'm correcting these and hopefully it should work.
Looks like it's not a default instance as I thought it was.
|
|
|
|
|
I have created a database table with 5 columns in VS 2005 and I want the first column to autoincrement. I have set the Is Identity property of the column to yes and set the Identity Increment and Identity Seed properties to 1. In my codebehind, when I make the call this.TableAdapter.Insert(1, 1, DateTime.Now, 0, 0) and run it, I get this error message: Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF. What do I need to pass in as the first argument into the Insert function to get the auto increment to work? Any help would be appreciated, thanks.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
