I use SQl server management studio on sql server 2005.
when I rightclick a table in the object explorer and choose "open table" I get the current data in the table....
HOWEVER, if I want to refresh the data I have to close the current tab and THEN open the table AGAIN!
There has to be some way to refresh it.....right?
I'd love to hear it!
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedI have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
return DataCollection;
}
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies View RelatedI have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies View RelatedHi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies View RelatedProduction and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedI wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
Greetings,
I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)
Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination?
Thanks,
Black Cat Bone
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
Hi,
I have a stored procedure which uses:
DECLARE @Tmp TABLE ( Row_ID INT IDENTITY(1,1), Property_ID INT)
And I'd like to reuse the table in the stored procedure and have attempted to empty the table by calling
TRUNCATE TABLE @Tmp
But this doesn't work. How else can I do this? I don't want to use
DELETE FROM @Tmp
Because this doesn't reset the Row_ID, and I'm using the Row_ID in a WHILE loop. Any ideas?
I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!Thanks in advance,Chris
View 1 Replies View RelatedIf you are doing an insert into a table with 10 columns, but you only have values for 2, is it better to insert 2 and let the table default the others or is it better to insert all 10 using default values for the 8 you don't know? Do you know of any benefits one way or the other? Thanks in advance.
View 1 Replies View RelatedHi SQL Server Experts,I really need some help ASAP.I thank you in advance.We have an ASP/MS SQL Server Shopping cart application,developed by anindependent developer.We really have a problem where the Order_detailsare not getting stored completely.The concerned Tables are:Table1 Order_InfoStores Order_ID,customer data,Total_Amount,Shipping details etc.(One Record per Order.)Table2 Order_detailsStores Order_ID,Product_Id,Price and Quantity.(Multiple Records per Order.Basically stores the shopping cart items,one by one.)Table3 Payment_InfoStores Order_ID and Transaction Details(One Record per Order.)Now after a customer checks out and the payment transaction has beenapproved the order details are stored this way.Step 1: First a new record is added to Table1 (Order_Info).Step 2: A 'For' loop adds new records - one for each item in the cart-to Table2(Order_details).Step 3: A new record is added to the Table3 (Payment_Info).Recently, we have been having problems with Step 2, where only apartial list of the item details are stored in the Database and Step 3is not executed at all.And we don't have the problem all the time.What could be the problem with Step 2 where a series of record areadded to a table continuosly? does this make SQL Server "too busy" sothat subsequent Add operations are not done?Can someone see an apparent problem?Any solution is greatly appeciated.Please HELP ME!Thanks.Sriram
View 6 Replies View RelatedI have SQL server 2005 in which I have two Server (1 & 2).
There is a table, userLookUp in a database of Server 1, with huge data.
I created a table, called userLookUp on server 2 with same number of colums and same dataType.Now I want to get the data from the table in the server1 to the table I have created in the server2.
How ?????
I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".
Isn't there a cleaner way of doing this?
hi
I want to read data from XML file and insert that data from XML file into the Database Table From ASP.NET page.plz give me the code to do this using DataAdapter.Update(ds)
Hi,I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...Thanks for your helpJohann
View 3 Replies View RelatedHi,
I am using DTS (Data Pump Task) to fetch selective data from AS/400. The Selection is based on a table which is on SQL Server.
I can not fetch the whole data as data is huge & only want for particular items. How can I achieve this taks
Thanks
Shafiq
:confused:
Hi,
Can anybody give me some information on Table data types of SQL2K.
How and where can it be used. Does it make the queries faster, in case there are many users using ?
Please could anybody give me these details, to make me decide whether I can use it or not ?
Thanx and Regards,
Sushma
Does any one have any examples of how to declare and use these properly?
MSDN doesn't seem to give any example for a beginner.
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
I have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from db1 to db2, My table contains
employee_no INT,
date_of_attendance datetime,
present varchar(20),
shift_type VARCHAR(20),
marked_by VARCHAR(50)
I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.
create table dbo.Scott_TEST
(
YearMonth char(9),
Gender char(1),
NumOfFemale int,
NumOfMale int
[Code] ....
MyTable has the following data (Just a sample)
parent | NAme | Checked | contactmethod|Check2 | Other
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face | true | skills
974224 | Other | true | Contact | true | skills
I'd like to pivot on "parent"
In a perfect world I'd like to see output like
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face, Collateral Contact | true | skills
If there are more than one name or contactmethod for the same parent then they would be strung along with commas
I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
This value is 668 so, the row should look like
c010A92668
create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)
[Code] .....
Hi,
I need to import data from more than 10 excels having the same format in to a single sql server table.
I tried to use
INSERT INTO MyTempTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 11.0;Database=C:Book1.xls', [Sheet1$])
but got the below error
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
If DTS package is used then I am not sure how I can place 10 excels at a time so that they can be picked one by one and data is imported in to table.
Please help.
Thanks,
Vimalraj
Hi,Can anybody tell me if it is possible to return a table data type (asopposed to a temporary table) from a stored procedure.I am currently studying for 70-229 using the Thomas Moore book but itdoesn't really make it clear, as the text suggests you can't returnthem but some of his sample questions suggest you can.I can't make it work in Query Analyser so I am assuming that you can'treturn them. Is that correct?Thanks in advance for any help,Simon.
View 1 Replies View Related