Hi guys,
I have a simple field in a table in my sql server DB. All i need to do
is update a count on it, from 5 to 6, from 6 to 7, so on. A simple counter.
Do I have to SELECT the count field once, get the value, do the adding
in my program, then do another command to update the count field? Or
does SQL syntax allow a simple increment function?
Thanks!
Buck
My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.
e.g. TABLE2:
e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);
--^Doesn't work
so my TABLE1 should be:
e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0
(The .. is just spacing to show the table here)
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc
All help is greatly appreciated.
Hello All,
I have an application running off an Access database. Im trying to convert the application to a server-client architecture and thus moving everything to SQL Server 2000.
I have read alot of articles about ways to accomplish that but I've still yet to decide on which approach is the best (best as in robust and scalable).
I pretty much eliminated the ODBC route due to all the layers of translation a request has to go through to reach SQL Server, although this route seems to be the quickes to accomplish.
Mind you I have plenty of time on hand and am willing to re-write the whole thing from scratch if it means a better app.
Now should I go the ADP route and keep Access as the user interface or should I rebuild the whole front End in pure Visual Basic that interacts with SQL Server? Im leaning towards the latter solution.
I haven't read any articles talking about rebuilding the whole app. using VB and SQL Server instead of just using ADP. Why so and which solution do you think is a better solution for a client-server architecture??
Thanks in advance for any replies to my questions.
Hi all,
I would like to simply link two tables that are in two different databases in the SAME server.
I know that I could use the replication method (snapshot or merge) but I need a simpler method like the Access link table method.
Any suggestions?
Thanks
Alessandro
Hello all,
I need some help in simplyfying the following update statement -
update table <table_a> set <col_1> = NULL where <col_1> = 'N/A'
update table <table_a> set <col_2> = NULL where <col_2> = 'N/A'
update table <table_a> set <col_3> = NULL where <col_3> = 'N/A'
update table <table_a> set <col_4> = NULL where <col_4> = 'N/A'
update table <table_a> set <col_5> = NULL where <col_5> = 'N/A'
update table <table_a> set <col_6> = NULL where <col_6> = 'N/A'
update table <table_a> set <col_7> = NULL where <col_7> = 'N/A'
....and there are 73 columns
Anyway I can create a loop or array and store the column name as a parameter and then pass it to the update statement?
Thanks in advance,
Saurav
I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP
Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong.
Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test")
Dim cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "cssp_family"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cn.closed
Hi.
What I want to know is what the issues/scenarios are of only using copies of mdf/ldf files as backups.
TIA.
I'm just getting started with SSIS and need pointing in the right direction with my first attempt to create a simple transform lookup.
The objective is to copy some columns from a table in one SQL Server 2005 db into another table in a different db on the same server. There are some simple column transforms involved but one column in the target table needs populating from a lookup as a result of a SQL Query.
So far, I have one OLE DB Source object linked to one OLE DB Destination in 'Data Flow'. I've configured the column transforms and these appear ok. However, the problem is that I can't see how to setup the Lookup. I have added a lookup transform object to the Data Flow space, linked from the appropriate OLE DB Source. The simple sql query returns the correct value when previewed. If I try to connect the output of this lookup object with the same OLE DB Destination that is the output from the first column transform, I get a warning message : -
"Cannot create connector. The destination component does not have any available inputs for use in creating a path"
There is column in the destination table available but I don't know how to direct the lookup output to it. I can see this available column by looking at the OLE Destination object properties and clicking 'Mappings'. The other columns are showing the links in the transformation with the destination column intended for the lookup output is not showing any links.
How do I linkup the output from the lookup object to the OLE Destination object? Is it ok to have two inputs into the OLE Destination object (ie. 1. the output from the OLE DB Source; and 2. The output from the lookup object)?
Thanks in advance,
Clive
I am developing a asp.net app and I have a long, very long list of data fields.
The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So...
I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand.
I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array.
Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do.
So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ???
Thank you Marc
Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName
(
column_name data_type
)
But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey?
Hope really for help, because this is a importen thing, and I cant find the answer? :S
Hi.
A newbie question. I am tearing my hair out trying to work out how in Sql Server 2005 to get a printout (or even better a file I can save that i could incroporate in a wrod document), or both, which shows the structure of all the tables in my database.
I want to list all tables (or selected tables perhaps) , and all columns in those tables, with the attributes of each column (nvarchar(2) etc or decimal(18,5) etc). Just a simple listing of all tables and their columns and the attributes of those columns.
Surely this must be possible with a simple one click operation in Sql Server 2005. I have created a database diagram which gives me part of what I want, but that just shows the tables, relationships, and column names, not the attributes of each column which is what I need as well.
I don't want to have to start installing third party products to do this, and I have no great script writing capabilities. Surely such a basic function is easily acheivable with one or two clicks in Sql Server 2005 from a menu somewhere in sql server mangaement studio?
Thanks in Advance for your help.
Chris M
Hi
I have one SSIS pkg when I executed this pkg I got following error message
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "column58" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "column58" (250)" failed because truncation occurred, and the truncation row disposition on "output column "column58" (250)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "D:ok_filesmusercm.txt" on data row 1.
(SQL Server Import and Export Wizard)
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I am using Flat file as a source & SQL 2005 as Destination.my flat file have "Tab" as a column delimiter and "{CR}-{LF}" as row delimiter and TEXT Qualifier is ["].
Advanced setting options for flatfiles were tried, but didn't work.
For solving this problem i am also using Script Component for column 58.
I am also geting more then 60 warnings of truncation
Thanks in Advance
Sachin
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies View RelatedThe following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies View RelatedI would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
I am trying to make a stored procedure in my website for updating an address:1 CREATE PROCEDURE dbo.UPDATE
2 (
3 @add NVarchar(50),
4 @cit NVarchar(50),
5 @state NVarchar(50),
6 @zip NVarchar(50),
7 @CNum int
8 )
9
10 UPDATE table_name
11 AppAdd = @add, AppCity = @cit, AppState = @state, AppZip = @zip
12 WHERE CertNum = @CNum When I try to save it it give me an error: Incorrect syntax near keyword 'UPDATE'Must declare scalar variable '@add'
Hi, I'm new to ASP.Net quite new to C# (My first attempt at a database website) and am trying to get a button to add "1" to "int" value called "Comments" a each time its pressed basically counting each time a comment is added. I also only wnat it to affect the row where "ModID" in my database is equal to the query string "ModID" I'm using on the page. I cannot find any tutorials so this is my best guess so far. This is probably a Noobie type stupid question but I'm stuck. This is the code I have so far for my Button_Click event: protected void Button_Click(object sender, EventArgs e){ SqlDataSource CommentCountDataSource = new SqlDataSource(); CommentCountDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["Main_Site_DatabaseConnectionString1"].ToString(); CommentCountDataSource.UpdateCommandType = SqlDataSourceCommandType.Text; CommentCountDataSource.SelectCommand = "SELECT (ModID, DateTimeLastComment, Comments) FROM Mods"; CommentCountDataSource.UpdateCommand = "UPDATE Mods SET (DateTimeLastComment=@DateTimeLastComment, Comments=@Comments) WHERE ModID=@ModID"; //How do i get the Where to use the query string info? CommentCountDataSource.UpdateParameters.Add("DateTimeLastComment", DateTime.Now.ToString()); CommentCountDataSource.UpdateParameters.Add("Comments", "10"); //"10" is just a value to test I'll change this to add "1" once I figure how. CommentCountDataSource.Update();} Sorry if I'm using the wrong lingo but as I say I'm new. If my code is a mile off then please can you send me in the right direction of some code that works.Thanks in advance if anyone can help me. Cheers,Alan
View 5 Replies View Relatedguys can anyone know whats wrong with my syntax? I always get this error: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.This is the syntax: Protected Sub btnupd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnupd.Click Dim pwing, pblood, pcolor, plcolor, pmark, pbdate, pstat, premark, ppefr, pstyle As Object Dim sql As String Dim y As New Object y = GVh.SelectedDataKey(0) pwing = txtwingb.Text pblood = txtblood.Text pcolor = txtcolor.Text plcolor = txtlcolor.Text pmark = txtmark.Text pbdate = ddday.SelectedValue & "-" & Ddmonth.SelectedValue & "-" & ddyear.SelectedValue pstat = txtstat.Text premark = txtremark.Text sql = "UPDATE [broodhen]" sql = sql & " SET bwing = '" & pwing & "'," sql = sql & " bloodlines = '" & pblood & "'," sql = sql & " color = '" & pcolor & "'," sql = sql & " lcolor = '" & plcolor & "'," sql = sql & " mark = '" & pmark & "'," sql = sql & " bday = '" & pbdate & "'," sql = sql & " status = '" & pstat & "'," sql = sql & " remarks = '" & premark & "'" sql = sql & " WHERE(bwing = '@wwing')" DShen.UpdateCommand = sql DShen.Update() MView.SetActiveView(GView) End SubThat is from my aspx.vb file and for the .aspx my datasource:<asp:SqlDataSource ID="DShen" runat="server" ConnectionString="<%$ ConnectionStrings:CSD %>" ProviderName="<%$ ConnectionStrings:CSD.ProviderName %>" SelectCommand="SELECT * FROM [broodhen]" > </asp:SqlDataSource> Note: I select records from a gridview to edit. i can delete, insert, select but i cant update. please help... tnx.
View 3 Replies View RelatedI am trying to update a SQL Server DB via my ASP.NET application with the following SqlCommand:
UPDATE StudentCourses
SET SC_Active = 0
WHERE (UT_ID IN (@UT_IDs)) AND (CO_ID = @CO_ID)
@UT_IDs in populated from a StringBuilder object with the following:
354,284,305,281,308,351,439,355,306,282
When I run the application to update the DB, I get the error:
System.FormatException: Input string was not in a correct format.
If I change @UT_IDs to:
354
if works.
Can anyone explain this behaviour?
Cheers,
ocann
I am a DBA who is moving in the direction of minor database design. I have gone through the steps to get my tables normalized, and I am ready to get them set up so they will update from table to table. How do I set this up? I know SQL does not cascade like Access, so how is this done? From what I have read (Robert Vieira's book) I should use triggers? Any help would be appreciated. Dallas
View 1 Replies View RelatedI have a query that updates
strSQL = "UPDATE customers SET "
strSQL &= "entryid = '" & strtheEntryid & " ' "
strSQL &= "WHERE id =1 "
right now in table id is my primary key field it increments by 1, I want to say get first record instead of saying id = 1, how to do that?
I have some code to hack in c#..
I have a database and when a form (C#) is loaded a copy of the table is made in a DataTable object. The form has a list box showing just the one column of info from the local DataTable.
On the form it is possible to change a record, delete a record and add a record. These are all done in the local table.
The question, how do i send these changes back to the database. Currently it deletes the lot and copies all the local table values in. How do only do the affected row, UPDATE, INSERT etc
Hey
I have an ASP application that uses a stored procedure and ADO.net to update a sql server data file. The problem is I know the code is working, I don't have any errors with the ADO, no exceptions are caught. I can use the same basic code to insert a record using a different procedure. It is the update procedure that does not carry through.
So, I know I have a connection, the procedure works using the query builder directly so the procedure works, but when I run the code, I get no errors and no update to the datafile. I am not even sure how to trouble shoot this since I don't have an error to look up.
C# Code:-------------
private void UpdateIssue()
{
DateTime date = new DateTime();
date = Convert.ToDateTime(this.txtDate.Text);
//edit record in HelpDeskIssuesTbl here.
SqlConnection con = new SqlConnection("Data Source...");
SqlCommand comUpdateTicket = new SqlCommand("sp_UpdateHelpDeskIssues", con);
comUpdateTicket.CommandType = CommandType.StoredProcedure;
comUpdateTicket.Parameters.Add("@IssueID", this.GridView1.SelectedIndex.ToString());
comUpdateTicket.Parameters.Add("@EmpID", this.ddlEmployee.SelectedValue.ToString());
comUpdateTicket.Parameters.Add("@Date", date.ToShortDateString());
comUpdateTicket.Parameters.Add("@StatusID", this.ddlStatus.SelectedValue.ToString());
try
{
con.Open();
comUpdateTicket.ExecuteNonQuery();
}
catch (Exception ex)
{
this.lblMessage.Text = "Data save error: " + ex.Message.ToString();
this.pnlMessage.Visible = true;
}
finally
{
con.Close();
}
}
----------------------------------------------------
dbo.sp_UpdateHelpDeskIssues
(
@IssueID int,
@EmpID int,
@Date datetime,
@StatusID int
)
AS
UPDATE HelpDeskIssuesTbl
SET EmployeeID = @EmpID, IssueDate = @Date, IssueStatusID = @StatusID
WHERE (IssueID = @IssueID)
RETURN
Like I said the Stored Procedure does work when I run it directly in Visual Studio. I have double checked all the params and they all match up unless I am missing something.
Please send help! Thanks.
Hi gurus,
My feeling with the SCD component is not that very solid. I have the feeling that the behavior of the insert/update strategy is not always correct an working.
I will describe two problems that i encounter.
1. My destination table contains records with the value ''. Cause i don't want '' ( 2 single quotes) in our DWH i update the view that is the source with a case statement that changes the '' to NULL. But when i run the packages the '' values are not update with the NULL values. When i delete the destination table and run the package, the records are inserted with the NULL value as expected. Anyone who has experienced this problem?
2. When i create a new table and run the package so the destination table gets filled with records the SCD will insert alle records (for example 100). When i start the run directly after the first run, all records are updated instead of doing nothing what it should do cause all records exists.
Anyone who can give me some feedback?
with regards, Arthur