Hi
i have 2 sql servers ie solomon(acounting package) and production server.
so if i'll change the data ie customer info in solomon then it'll be update the data(related tables) in production server automatically.how it'll update the customer info from one server to another server automatically after making changes.
thanx in advance
reddy
Hello:
I'd like to create a view on server x which references tables on an
entirely seperate server. Is this possible? Is seems
strange to have to copy the tables over just to create a view. In
the view wizard I can't seem to 'browse' to the tables on the other
server.
The code I'm working with would conceptually be something like this:
select server name.database instance.owner.table.field
from server name.database instance.owner.table
where <field name> like 'xxxx%'
or something along those lines.
Any help would be appreciated!
Thanks.
in sql server I want to update the "UpdatedDate" field automatically when I change ANY other field in that specific record.it has to be set to the current server time.
View 2 Replies View RelatedHi guys,
I need to update one table in database A based on the other database B, which is on a romote server. The requirement is to update it automatically at 12:00 in the mid-night everyday. Database A is MS SQL Server 7, and database B is Oracle database.
What's the best to implement it? I thinking to use trigger, am I right?
Thanks in advance for any advice.
Henry
Hi guys,
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time.
2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I appreciate any help you provide.
Dogli
Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.
I just want to do one thing:
In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.
My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.
How to update the source columns'metadata automatically?
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.
CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO
I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
any ideas?
hi e'body:
I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).
thanks in advance.
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View RelatedI have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:
insert SomeTable(keywords)values('this is a test')
and then query:
SELECT * FROM Message WHERE Contains(keywords, ' "test" ');
I get the expected rows all rows that have "test" in the keyword.
On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.
Is this a known limitation of Express?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE
Any new record inserted or update in source table SSIS package automatically run, how can we achieve without using service broker.
View 7 Replies View RelatedWhen writing an INSERT statement, what code do I pass to the SQL database to automatically truncate a value to fit in to the database if it contains more characters than the field the data is being entered into?
View 7 Replies View Relatedhi,
i want to lost data automatically after 3days.
but i don't know how to use job scheduling in sql2000 and how to query to add date to datatime column automatically when the data is inserted.
Plz help me!
hi,
is it possible to get the back up of a particular database automatically at fixed interval of time i.e for every two days or two hours etc. i am sql server 2005 as back end of my project. please show me a way in sql express edition too along with sql sever edition.
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
I am very well versed on manipulating data using passthrough queries through access or the command object in ADO/VBA but this problem requires a sql server side solution and I have been assigned the task
Scenario
Access via an ODBC link cannot view Sql table data that uses a BigInt as a primary key. Access can update the data, insert data ands delete data to these tables - but viewing (by design) returns the actual count of the rows but each field is populated with #deleted.
I can view the table data if I use a passtyhrough query but performing row level and field level edits from there is impossible. Not all people have Studio manager so that is not an option.
We are moving away from our legacy application and the development company has already developed the schema using the BigInt so changing that to a numeric or integer is out of the question. My boss has determined that using a set of bridge tables with identical tables in a different database where we replace the bigint with int will work - we can view the data in the "bridged" database and even update it from select queries and simply by opening the table in view mode from Access.
Here is the hard part (for me) because I have never done this. I can write SQL and understand the security/roles etc, but I have never administered SQL server regarding server side triggers and stored procedures - I have always used the command object or passthrough queries to deal with sql stored procedures. I need to either
A)Create some trigger behind the tables in my bridge table that will fire a stored procedure to replicate the data to the bigint database. They are both on the same sever.
B)Somehow create a "mirror" that always replicates from my bridge database tables (5 of them) to the new database.
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
View 5 Replies View RelatedScenario:
I have 2 identical Analytics cubes, Cube 1 & 2, my reports point to Cube 1, when Cube 2 is refreshed the reports data source is manually repointed to the updated Cube 2. The following day the Cube 1 is refreshed and again the reports data source is edited and repointed back to Cube 1.
This ensures that if a cube build failes the reports aren't down.
To modify the data source I connect to Reporting Services using SSMS go to the Data source folder and manually edit the Data source.
I have looked through the Reporting database in SQL Server but didn't see anything that resembled the connection string for me to edit.
Are the connection strings for the Data sources stored in the Reporting Database ? Where can I find them?
Here is a Microsoft document that gave me the idea. http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/scoqryas.mspx
It talks about changing the connections strings in the Web UI? But doesn't tell you how or point to any docs that do.
Is there a way to automate this and is anyone else running a similar setup.
I appreciate you help
Thanks
Marcus
I have a windows app which is used in australia and uses web services to communicate with the database located in new zealand. I have a problem where dates that are returned from SQL are automatically modified to suit the timezone and i dont know how to turn it off.
For example, i insert the value '10/Jan/2006 20:00' into the database from a PC located in australia. When that data is selected back out, a 4 hour time offset is automatically applied to it and the time is returned as '10/Jan/2006 16:00'.
My current workaround is to modify my queries to say;
"Select convert(varchar, tb_date) tb_date from tablename"
Which returns the date as a string rather than a datetime which means the time offset is not applied.
This is a really poor solution and i'd be really keen to find out a better way...
Thanks
Good Morning,
I am trying to find a way of creating my Repoting Services reports of automatically refreshing once our ETL load has completd. I have created a process that creates a file is a particular directory once the data has completed loading however I am unsure how to make the report look for this file and then refresh the report.
Is this something that can be created?
Thanks in advance.
Steve (Novice!)
at first let me specify my requirement.
a) i have an excel file with more than one sheets
b) i want to import data from that excel file into sqlserver 2000 using asp.net & c#
NOW
i need a program that automatically realize total sheets of excel file
AND
insert into seperate table
please help me
Hi,
This is my problem :
I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.
I wrote one stored procedure to retrieve data from my sql database like :
select Id, Name,Address,Amount from KalakaDB
Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order
how can i connect my sqldatacontrol to the stored procedure ?
Thanks
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
I have an Integration Services package that loads new data into tables that are dimension tables wi my cube. The same situation exists for my fact table. If I perform an "Analysis Services Processing Task" for the dimensions ,cube and measures, will that move the new data into my cube or do I need to perform the "Dimension Processing Destination" data flow task prior to this? Is the initial processing task good enough?
thx,
-Marilyn
how to load the data from oracle to sql server
oracle source is having 7 tables
sql server target is having 7 tables
i have used VISUAL STUDIO and created the one data for individual but how to run in sql server that ssis package
I pull data from Sql Server through the query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data. The parameter should pick the value from the excel range. And also how to control this through VBA
View 4 Replies View RelatedI have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedI run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies View RelatedI want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View 4 Replies View RelatedHow do u achieve this -- While SSIS Data Load Execution itself?
Update Col B with Col A value if Col B is NULL in the Data File?