I have monthly tables named as 'Tablename_yyyy_mm' etc. I want to make a view that will capture the current months table and the last 3 months data. for eg: if today is november 19th, 2003. The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables if today is jan 01,2003 The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables
I was searching some books about SQL but all that i found was "Big books", i mean, i found books about all SQL Server and i just need to know a few things.
I'll need to make queries to a DB and make excel or acces reports based on the query result.
Hi, I am new to SSIS. 1) I want to create a SSIS package to execute 5 stored procedures one by one. Stored procedures has parameters. 2) I want to create a SSIS package for ETL. To extract data from different tables from a database and insert into a new table in the same database on a monthly basis. How do i do these two? Is there a good material i can go through to achieve these tasks?
Hello,I'm new to SQL Server, have alot of experience with DB2 and Oracle,and have beentasked with supporting SQL Server. Could anyone suggest a goodadministrator book?Thanks!!Dave Schaeffer
I am in the process of creating a user interface that will give the users a list of reports to choose from. I wanted to write an interface in ASP .NET with C# to pull information for different reports from the reporting services web service. I have successfully done this with C# Windows Form. The project requires it to be web-based.
Problem is, I am new to web forms and would like a recommendation for reference materials to help me program ASP .NET with C# and using the web service. I am still learning ASP .NET and C#, so I would like something fairly basic.
Has anyone used any CBT's for SQL7 or could recommend a good company for study materials. I have seen the product from CBT systems and Keystone and was not impressed.
I am certified Oracle DBA/Developer/Internet Developer. Now, I am interested in Microsoft Certificated DBA (MCDBA/MCSD).
By the way, I don't want to waste money to purchase the Microsoft exam software (Self Test Software) and training material. So, I want to exchange the MCDBA exam software and training material with you by using Oracle OCP 8i, Developer 2000 Rel 2.0 and MCSE 2000 Self Test Software.
I can use Oracle 8iDBA, Oracle Developer and MCSE 2000 self test software listed below to exchange 70-228,70-229,70-215,70-216,70-175 or other MCDBA 2000 and MCSE 2000 self test software and training materials.
<Oracle 8i DBA> 1Z0-001 Introduction to Oracle: SQL and PL/SQL 1Z0-023 Oracle8i: Architecture and Administration 1Z0-024 Oracle8i: Performance Tuning 1Z0-025 Oracle8i: Backup and Recovery 1Z0-026 Oracle8i: Network Administration
<Oracle Developer> 1Z0-101 Develop PL/SQL Program Units 1Z0-121 Developer/2000: Build Forms I 1Z0-122 Developer/2000: Build Forms II 1Z0-123 Developer/2000: Build Reports
<MCSE 2000> 70-210 Installing, Configuring and Administering Microsoft Windows 2000 Professional 70-215 Installing, Configuring, and Administering Microsoft Windows 2000 Server 70-216 Implementing and Administering a Microsoft Windows 2000 Network Infrastructure 70-217 Implementing and Administering a Microsoft Windows 2000 Directory Services Infrastructure 70-219 Designing a Windows 2000 Directory Services Infrastructure - (Includes 4 Case Studies)
By the way, if you have any one of 70-228,70-229,70-175,70-176,70-100, I can use whole set of Self test software to exchange yours. Please provide email accounts with at least 5MB space for exchanging Self Test Software.
Thanks and Regards,
Jack OCP DBA/Developer/Internet Developer jacklam@tom.com
I'd like to display the our range of coverage of material stocks via SSRS. The underlying database provides all the actual stocks of our materials as well as the current and planned consumption. Now, my problems are the design, as I am not sure If I can realise it with SSRS, and the way of displaying the actual range of coverage as I have no future time informations.
Has anybody made experiences with reports like that?
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission from items as I RIGHT JOIN Categories AS C on I.categoryID = C.categoryID INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID INNER JOIN Missions as M on LM.missionID = M.MissionID where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0 GROUP BY C.categoryName, C.categoryID ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
I know this is not SQL forums but still if some body has solutions..then plz help My backend is SQL. My Ms Access database is always open and getting updated continously.
I need to create Job for SQL. This job will run every 10-15 min may be. This job should pick latest data from MS Access and put in my SQL server database Is it possible to create such kind of Job>
OR
Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!
I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..
Hello to all! I have a procedure "rebuild_index" and I would like to create a job running that procedure. May someone send me a script (template) how to create this job, the most important: When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script) Thank you very much for your help!
create proc p_rebuild_index as declare @name varchar(100), @string varchar(200) declare c1 cursor for select name from sysobjects where type = 'U' open c1 fetch c1 into @name while @@fetch_status = 0 begin set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)' execute (@string) --print @string fetch c1 into @name end close c1 deallocate c1
How can i create sdf files? Is there any way to convert a mdb or a xml file to sdf? The only way i was able to do this was with data port wizard from primeworks, but its not freeware... Can i populate my sdf database from xml files by writing code...?? Does someone knows?? I have searched everywhere and didnt find anything... I'm working in VS2005 vb.net and i have installed compact sql... Thank you!
Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?
Hi, I am not sure is this a proper place for my question. All we know that creating a database can be done as the following. 1) Create a project named DBExample 2) Click Add->New Item. 3) Select SQL Databse and type a name.(Ex: Database1.mdf) A database is added to the project 4) Then add table and input values. 5) Set the primary key.... My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
Hello... I want to develop a web site having two features 1. Online Shopping2. Forums Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once. ____________Thanks in advNauman Ahmed
I'm sorry to beat this to death but something odd is happening that I'm not quite sure I understand.
I have a number of DTS packages that when originally created with the wizard were scheduled and the jobs have run fine. Today, I created an import job that runs great but then crashes when I try to schedule it from the wizard. When I manually go in and try to schedule the DTS job, it accepts the input but does not create the job.
Since I've done this before, I'm confused about why I was able to do this previously, but not now. Permissions, etc. have not changed at all and I am the dbo for the database. The DTS package and attempted job scheduling are being done on Windows 2000 Server. SQL-Server version is 7.0.
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
procedure creation I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
any clues? i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.
create table CUSTOMER ( CUSTOMER_ID int identity(1000,1), NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256) )
But while writing insert statement didnt specify this column.