I have a table in the one of our dbs that gets updated through out the day. I believe there are quite a bit of columns that can be moved into a separate table to help with some db contention. Is there some way to audit or view which columns are getting updated on a daily basis?
thanks
Hi All,
I am facing a problem to update the columns.
The issue is ..I am having 340 tables in SQL database with a total of 8500 columns.
The descriptions for all the columns in an excel sheet.
Is there any means by which we can run a query that which loads the descriptions to all the columns instead of doing that manually?
Good morning,
I want to update two columns in the same time by making a select from another table. Some think like the following;
Update TBL1 o set (o.COL1,o.COL2) =
(select f.COL1,f.COL2 from TBL2 f where f.PKY = o.PKY)
where o.COL3 = 100;
Thinks to helping me .
I have a large table with many columns that have rows with N/A or null values, which are gotten from a bulk insert.
In order to format the data properly, we need to convert the data from N/A or null to 0, as the original values come from the supplier.
In my code the best way to achieve this is running multiple queries as
update csv_testing set testing5 = 0 where testing5 like'%N/A%'
update csv_testing set testing6 = 0 where testing6 like'%N/A%'
update csv_testing set testing7 = 0 where testing7 like'%N/A%'
update csv_testing set testing9 = 0 where testing9 like'%N/A%'
etc for about 12 columns
is there a better way of doing this ?
Hi guys.........i'm tryign to update 2 tables in one stored procedure. However i'm getting errors. heres the code i have:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoUpdate
@Reference int,
@CanTravel int,
@SEEmployee varchar(100),
@SE_EMPLOYEE_FROM datetime,
@SE_EMPLOYEE_TO datetime,
@WorkHours Varchar(100),
@DrivingLicence varchar(100),
@CriminalConvictions bit,
@CriminalConvictionsDetails1 text,
@CriminalConvictionsDate1 datetime,
@CriminalConvictionsDetails2 text,
@CriminalConvictionsDate2 datetime,
@CriminalConvictionsDetails3 text,
@CriminalConvictionsDate3 datetime,
@VacancyMonitoring Varchar(100),
@VacancyMonitoringDetails Varchar(50),
AS
UPDATE Account
SET CanTravel= @CanTravel,
SEEmployee = @SEEmployee,
WorkHours= @WorkHours,
DrivingLicence = @DrivingLicence,
CriminalConvictions = @CriminalConvictions,
CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
CriminalConvictionsDate1 = @CriminalConvictionsDate1,
CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
CriminalConvictionsDate2 = @CriminalConvictionsDate2,
CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
CriminalConvictionsDate3 = @CriminalConvictionsDate3
WHERE Account.Reference = @Reference
UPDATE Application
SETVacancyMonitoring = @VacancyMonitoring,
VacancyMonitoringDetails = @VacancyMonitoringDetails,
WHERE Application.Reference = @Reference
GO
and here are the errors i'm getting:
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 19
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 37
Incorrect syntax near the keyword 'WHERE'.
thanks again guys!
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
-Jeremy
___________________________
Geek At Large
I am attempting to write a conversion of our product for Compact Edition; we already provide it based on SQL Server. The database interface uses ADO through a Python-win32com adaptor, and has worked fine so far. (Note: *not* ADO.net, just plain old COM)
Now, a curious thing happens. When inserting new data through a Recordset, everything works fine - except for columns defined as bigint. There are no exceptions thrown, but when you read the columns back they contain nothing but zeroes. Do the same to any other column type - I've tried integer, numeric, float, nvarchar and ntext so far, and they all seem to work just fine. It does not seem to be conversion-related either, since I've tested the exact same data to various column types. And using bigint on regular SQL Server works just fine.
The code involved is quite unspectacular, and simply switching the column types to integer would solve the immediate problem, but causes potential future issues since we normally store internal IDs in bigint columns, and the values may grow quite large.
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
I have a web interface where i have listing of several data and check box for inserting data into SQL server 2005 database table,
so I am able to inset data to sql tables using stored procedure. Now the question is i want to update these inserted records(agency approval column inserted as 1) in same table and assign value 1 fot the checked data to column finace approval as 1.
Here is how ia have webclas library where i script for getting the insert parameterspublic void Process_Payment(ref DataTable TableWithPayments, string Payment)
{SqlCommand InsertCommand = new SqlCommand();
SqlConnection AccessDatabase = new SqlConnection(FinanceSourceWrite.ConnectionString);int PaymentID = 0;
AccessDatabase.Open();
InsertCommand.Connection = AccessDatabase;
//DataTable TemporaryTable = new DataTable();
//TemporaryTable = TableWithPayments;SqlTransaction TransactionProcess = null;
SqlParameter InsertParameters;foreach (DataRow DataCommentInfo in TableWithPayments.Rows)
{InsertCommand.CommandText = "InsertPaymentList"; //THIS IS my stored procedureInsertCommand.CommandType = CommandType.StoredProcedure;
TransactionProcess = AccessDatabase.BeginTransaction();
// SET ALL THE VALUES FOR THE PARAMETERSInsertParameters = InsertCommand.Parameters.Add("@JC_ID", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["JC_ID"];
InsertParameters = InsertCommand.Parameters.Add("@Payment_Type", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["Payment_Type_ID"];
InsertParameters = InsertCommand.Parameters.Add("@Agency_approval", SqlDbType.Int);InsertParameters.Direction = ParameterDirection.Input;
InsertParameters.Value = DataCommentInfo["Agency_approval"];
Now my stored procedure
ALTER PROCEDURE [dbo].[InsertPaymentList](
@JC_ID int ,
@Payment_Type int,
@Payment_Group int,
@AGENCY_ID int,
@Agency_approval int,
@Agency_approval_date datetime,
@Program_ID nvarchar(50),
@Status bit,
@Jobsite_code_ID int,
@Date_Stamp datetime,
@Provider nvarchar(50),
@UserName nvarchar(256),
@Activity_ID int,
@Subproject_ID int,
@Payment_Support_Retention_List_ID int,
@WPR_ID int,
@Placement_ID int,
@Enrollment_ID int,
@Satisfaction_ID int,
@Enrollment_Bonus_ID int,
@Re_Placement_Bonus_ID int
)AS
INSERT INTO Payment_LIST_AIMS
(JC_ID, Payment_Type, Payment_Group, AGENCY_ID, Agency_approval,
Agency_approval_date, Program_ID, status,
Jobsite_code_ID, Provider, UserName, Placement_ACTV_ID, Placement_Sub_ID, Support_Retention_ID, WPR_ID, Placement_ID,Enrollment_ID,Satisfaction_ID,Enrollment_Bonus_ID,Re_Placement_Bonus_ID)
VALUES @JC_ID, @Payment_Type, @Payment_Group, @AGENCY_ID, @Agency_approval,@Agency_approval_date, @Program_ID, @Status,
@Jobsite_code_ID, @Provider, @UserName, @Activity_ID,
@Subproject_ID, @Payment_Support_Retention_List_ID, @WPR_ID, @Placement_ID,@Enrollment_ID,@Satisfaction_ID,@Enrollment_Bonus_ID,@Re_Placement_Bonus_ID)
SELECT CAST(scope_identity() as int)
Here like you see agency approval column in SQL server table gets value assigned as 1 when Agency user clicks the confirm payment button and so all the values as above....
Now another user Finance user process the same records from the web UI and clicks the process payment button at this stage ..i need to update Finance approval column as 1 agains that particular record existing th the SQL table, there are two three coulmc to be updated , Finance approval(this is where i need help) , Finance approval date , and user
Being a newbie please help me whith how i can fix this
Thanks
Santosh
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.
I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
Hi Gnite everyone, i once again need help with a T-SQL syntax for Auto Correction for insert and update when client enters the wrong format, i;e, creating a SSN Data type and a User Defined Procedure that auto corrects input format i;e,
user inserts into table authors of the pubs DB 525-477845 column au_id that executes auto correction so user doesn't have to put in dashes for SSN format. Please help me with this syntax .
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
Thanks in advance,
Dimeji
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
I have a question  in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
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?
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
Can someone cure my headache?
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
€¦
};
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
// dump out to raw file destination
}
I understand there are some pieces left out but that is the basic idea. It seems like I€™m going to have write custom pieces for each step.
It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?
I could just write a specific piece for each table but the only difference is the table name.
I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.
My table structure...
enddt datetime(8)
status varchar(20)
when enddt time equal to server time.
I want to change status field to expired.
What is the easy way to do this?.
enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live
I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
so it would kind of be like this:
=IIF(Fields!DayOfWeek.Value=2,"Mon",
ELSE(Fields!DayOfWeek.Value=3,"Tues",
Else(Fields!DayOfWeek.Value=4,"Wed",
etc etc
Am i doing this right, or am i off??
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
View 3 Replies View Related
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))
Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
What is the equivalent of Double for SQL Server?
View 1 Replies View RelatedOk - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies View Relatedscenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
View 3 Replies View RelatedI am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)
Thanks,
Matt