Hi
I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.
Thanks
I'm looking to get counts on historical data where the number of records exists on or after May 1 in any given year. I've got the total number of records for each year worked out, but now looking for the number of records exist after a specific date. Here's what I have so far.
SELECT p.FY10,p.FY11,p.FY12,p.FY13,p.FY14,p.FY15
FROM
(
SELECT COUNT(recordID) AS S,
CASE DateFY
[code]...
Got a table like:
Reporting_DateCustomerSales...
In such a case need a view with custom headers with a prefix of Latest date found in the first field of Reporting_Date.
In other words if Reporting_Date consists data like following six entries:
1-JAN-201031-DEC-2013NULL14-AUG-2014NULL31-MAR-2015
Then, could there be anyway to achieve a View with the headers (i.e. complete data without any filtration) in the following form:
Mar312015_Reporting_DateMar312015_CustomerMar312015_Sales...
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.
The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data.
I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.
What SQL code or script can i use to populate the 5 tables in ServerB
Below are the names of the 5 tables:
TABLE1: [ServerB].[ProdDB].dbo.[Orders]
TABLE2: [ServerB].[ProdDB].dbo.[Sales]
TABLE3: [ServerB].[ProdDB].dbo.[Employee]
TABLE4: [ServerB].[ProdDB].dbo.[Customer]
TABLE5: [ServerB].[ProdDB].dbo.[Region]
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
Hi,I want to clear all data from 200 tables in a database from a sql query. Some of the tables have got relationship as well. I want to clear data from there as well.What could be the best approach? Does anyone have an idea or script from where I could do the needful?Do let me know.Thanks
View 2 Replies View RelatedHello all,
I'v got a database full with an administration. This database needs to be protected and can not be accessed from the internet. Now we are making an internetpage for the employees and i want to read from the database who's working here.
For the security i want to copy some database data from one database to another... Is that possible? With a script ore something?
Thanks i advance!
Willio
How do i make a new database file and then copy all the tables and data out of an old SQL file into it? I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file.
brandon jelinek
www.ccgspokane.com
Hi. I need to move data from one database table to
another across database instances. A simple example of the typical
move would be:
[CODE]
INSERT into destination_db.dbo.table1
SELECT column1, column2, column3, column4 from source_db.dbo.table2
[/CODE]
My options are:
1. Create an SSIS package to perform the move.
2. Create sprocs and schedule the data move as jobs.
3. Write .NET code using sprocs to perform the move.
I'll have to move hundreds of thousands of records, so I want the
option that provides the best performance. I'm guessing that option 3
will be the slowest.
Thanks for the help!
Hi all
I have blank tables and i want to fill tables with data but i have many tables and i need script or whatever automated this operation like loop
thanks in advance
Hi, is it possible to print out the data tables? If, yes how to go about doing it? how to print 1 table per page?
View 9 Replies View RelatedTransfer tables with Data from one database to another one on a same server?
View 10 Replies View RelatedI have a database available and it is in live production.
I want to show only three tables in the front end. asp.net application in dropdown menu. I checked the internet and found only query for seeing all the tables from the DB.
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies View Relatedi want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005.
can any one help
plz reply
tnx alot
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
View 2 Replies View RelatedI'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?
I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
View 5 Replies View RelatedI need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedI found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
c) thinking about sql replication.
I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only). So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.
Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.
We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.
Accounting2014, Accounting2015 in AccountingDB;
Sales2014, Sales2015 in SalesDB;
Products2014, Products2015 in ProductsDB;
Inventory2014, Inventory2015 in InventoryDB etc....
These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....
Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamen
Hi,
I'm new to these forums (and to SQL Server), so please be gentle with me.
I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.
I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.
I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)
I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.
Many thanks
I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLMSoftwareMicrosoftMSSQLServerSetup
It seems that a SQL2005 installation does not have the same default
directory.
Is there a way to query this from the server itself so I don't have to read the registry?
What is the query to get 2nd last record from a table on a specific note_id key?
This is the data I have:
Note_id ======From Date===== =======To Date======
892481====2010-07-27 00:00:00===2010-07-27 00:00:00.000
892481====2010-07-27 00:00:00===2010-07-27 23:36:30.063
892481====2010-11-01 00:00:00===2010-11-02 00:15:23.027
892481====2011-03-08 00:00:00===2011-03-08 13:18:37.873 <====== This is the result i want
892481====2011-04-21 00:00:00===2011-04-21 07:57:20.567
I'll be working on a web project that utilizes sql server 2005. Our web host will be providing the server however I'll need a version of Sql Server 2005 management studio to work with it.
I believe the studio software costs around $50 however the express edition software is free.
So my question is, is the express edition software compatible with the full version of sql server 2005 or only sql server 2005 express edition?
Thanks.
-Goalie35
How is it possible to tell which version of ADO is installed on an XP workstation?Thanks
View 1 Replies View RelatedIn MS SQL 2000, I would like to obtain the timezone offset from UTC fora given date. For today's date, I can doDATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a futuredate may not be the same as today because some countries go in DaylightSaving mode. Can you suggest a way to obtain the timezone offset forany given date?ThanksYash
View 3 Replies View RelatedIs there a way to query a SQL server such that it will return thecurrent counter value of a field that increments automatically (anidentity field)? Using a MAX query might not necessarily give you thecorrect information because it is possible that the high value recordswere deleted. I checked the system tables and none of them appear tohave what I need. I suppose it would be possible to maintain a tablethat lists all the incrementing columns, but ideally there would be away already built into SQL Server.Thanks.
View 3 Replies View RelatedHi People
We want one trigger which captures the data as mentioned below.
Table Name
Date and Time
User
type
Mode of modification
TABLE_ABC
03/07/2007 12:00:04
XCSRaoa
Update
Procedure
TABLE_DEF
03/07/2007 12:00:34
XCSRaoa
Insert
Class Integration SSIS Package
TABLE_GHI
03/07/2007 12:01:04
XCSRaoa
Insert
Procedure
TABLE_GHI
03/07/2007 12:01:34
XCSRaoa
Update
XCSRaoa (Manual)
I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.
Could someone help me achieving this?
Regards
Abhi
Hello,
By using DBCC Page I am able to exemine data entered to row and other changes related to the rows. What I want to know is; is it possible to find out (and how) which Login ID commited these change directly from pages or where do I need to look to find it out?
Any guidance is appreciated....