MS SQL Server :: Required Suggestion About Table

Required Suggestion

Aug 18, 2007

Hi Friends

I required a suggestion from your side
Actually I am working in MS Access from last 6 months. Now I want to convert my database into SQL Server
I have a knowledge of SQL Server and I don't have any problem with this.

But what I want to know how to maintain a report in SQL Server I don't have any knowledge for creating a report. My freinds suggest me a Visual Studio 2005 for creating a Crystal Report.

So Please suggest me it is helpful or not and onething more is that I have to work on SQL Server 2000 or SQL Server 2005 which is more beneficial.

Please reply me for this
Waiting for your any kind of reply
Please
Thanks
Ashish

View 2 Replies View Related

Need Suggestion On Loading A 50 Million Records Table From Oracle

Feb 16, 2006

All,

I need to load a 50 million records table monthly. Any suggestion about the best/fast way to do it?

Thanks a lot

View 2 Replies View Related

Table Join, Advice Required

Jan 27, 2007

Hi,
I have the following two tables in MS SQL 2000
1.Products:
ProdID intProName char(10)
2.Orders:
OrderID intProdID intOrderDate DateTimeQuantity int
I want to join these two tables to form the following result format:
_________Prod1__Prod2__Prod3__Prod421/01/07__1______0_______1_____0___22/01/07__5______1_______2_____3___23/01/07__8______0_______1_____2___24/01/07__3______3_______4_____3___25/01/07__2______0_______1_____4___26/01/07__1______2_______6_____2___
So the first row would have the product name, Left hand column has the order date and then the sum of all the orders within
Any pointers on how to achive this would be great.

View 2 Replies View Related

Help Required W.r.t Alter Table Statements

Aug 7, 2001

Hello ,
I am having a problem related to Alter table syntax in MS SQL Server .
What I want to do is in a single alter table statement I want to add as well as modify columns in a table.

For E.g.
alter table testtable add fld1 numeric(10),
alter column fld2 numeric(15)
But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also.
Fast Help would be extremely appreciated.
Thanks,
Anand

View 2 Replies View Related

What Permission Required To DROP TABLE ?

May 2, 2007

Hi,
I would execute the following query to allow a user to create table

USE dbname
GRANT CREATE TABLE TO username

but it doesnt let me execute the following query
GRANT DROP TABLE TO username

I am wondering if there is a way to do this.

I know db_ddladmin does that. But I dont want to assign the whole role or schema to do that. I want specific privilege

According to SQL Server documentation, we need CONTROL permission to do this so I tried following query

GRANT CONTROL TO username

and

GRANT CONTROL ON tablename TO username

neither of them helped..

Please tell me what permission do I require to DROP a table.

Thanks,

View 5 Replies View Related

Trigger In Child Table That Required Parent Info

Aug 5, 2007

Hi,
I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.

I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.

How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.

Please advice.

View 7 Replies View Related

Help Required :- Table Space Management In Sql Server 2000

Mar 27, 2007

Hi ,

if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.

Thanks

Shiva

View 1 Replies View Related

Row-level Security: Permissions Required On Base Table?

Jun 2, 2007

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

View 10 Replies View Related

SQL Server 2012 :: Output Of Table Required In Specific Format

Apr 17, 2014

I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.

I tried with pivot but does not seems to be working.

View 2 Replies View Related

SQL Server 2012 :: A Valid Table Name Is Required For In / Out Or Format Options

Apr 24, 2015

Why am I getting message "A valid table name is required for in, out, or format options."

I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.

My server instance is "stat-hpsqlexpress"

The database name is "STATRLO"

Owner is "dbo"

Table name is "PM-allactivity-emaillog_042315"

The bcp comand I am trying to run is:

bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:databaseActivity_c.fmt -S stat-hpsqlexpress - T

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Yes I know the instance says sqlexpress...it was upgraded.

View 3 Replies View Related

SQL Server 2008 :: Write A Cursor To Fetch Required Data From Table?

Oct 21, 2015

I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.

I have a temp table named getInvoice where I have five important columns

1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time

and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.

An example is below :

InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime

579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00

My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.

The query I wrote is below

declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0

[Code] .....

This query runs forever and doesn't stop.

View 6 Replies View Related

Reducing Storage Space By Moving Rarely Required Columns To Separate Table While Sharing Primary Key Constraint

Aug 17, 2006

Dear all,

In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.

During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.

However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.

Hence, I had to create a quite awkward design to enforce the constraints:

1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.

Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.

Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.

Thanks in advance for any advice.

View 6 Replies View Related

Need Suggestion

Apr 8, 2008

Hello,

I will have to create a table that consists of only of two main fields. one: them employeeID and two: the SupervisorID,
my question is what should I define as my primary key. Should it be an aditional field, or could it be the EmployeeID field.

The employeeID is an unique filed. The end user for this application will be updating rearly some of this records, and may be adding or deleting some new records exporadically.

Thanks for suggestions.

View 3 Replies View Related

Suggestion Please

Nov 4, 1999

I have a table it contians 3735300 rows. and 5 col
Everyday more than 200000 rows will be inserted

When I run a query to extract a week data from the above table It is taking more than 2hrs.

The table has clustered index on all the 4 col.

Can anyone suggest me how to improve the performance.

Thanks in advence.

sqlranga@yahoo.com

View 1 Replies View Related

Need Suggestion

Aug 27, 2006

hi.. guys

one of my table has more than 300million rows. that i need to remove the data b4 may 2006 which is over 250 million rows. since the insert rate so high(none stop) and i cant truncate the whole table and dump the data after may 2006 back in (they wont let me). and if i use delete statment to delete only 200k rows data it will freeze up the whole database for long time(around 45 mins) and it will take me few months i believe. so that's not the option either.
is anyone out there got better idea?

also i got another table has more than 150 millions rows that index need to rebuild .... anyone got any idea how long it gonna take? just roughly guess. thanks

View 2 Replies View Related

Need Some Suggestion

May 20, 2008

Dear all
I am using sql server 2005 , asp.net 2.0 ,C#.
I need some suggesstion on how to pass request info to new level . that is if a employee request for something mail should go its next level ie. immidiate boss(next level can be null) and at any time requested or chain of employee( immidiate boss and succesor ).
Please suggest something

View 1 Replies View Related

Need Some Suggestion?

Jun 24, 2006

Hi,

We are plan to develop a new VB6 / MS Access application that will produce a whole bundle of reports for the outside salesman team (all of them have a laptop). The data is come from midrange AS400 tables.

Now we have decided should we using SQL Express with SQL tables or just MS Access MDB tables. Some of the laptop the salesman is only P3-800 and I'm worry about the speed for SQL express on those laptop.

The other hand, I like to use SQL databases, since we have the SQL server 2005 running for other application already.

Any one tested the performance between the SQL Express via MS MDB, it seems the SQL 2005 express is use more overhead than MSDE.

We tried the MSDE and the performance is not that great after the database grow beyond 500MB .

Any suggestion are welcome.

Thnaks!

View 4 Replies View Related

Need Help / Suggestion

Jul 23, 2005

Hey guysI have to implement a dynamic Parent - > Child Scenario ... but the catch isas Follows :I need to create this Table Design so that I can have multiple Parent ->child - > parent Relationships(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folderthat holds another Folder etc to Infinite )So if the Above makes any sense ... Suggestions would be WelcomeThanx

View 3 Replies View Related

Need A DBA Suggestion.

Nov 23, 2005

Hi,I've 2 Databases one is online and one is offline (connected toInternet).. using SQL SERVER for both servers, i need to update theOFFLINE Database one on a time schedule for every 15 mins from theOnline DATABASE.. can anybody suggest a better way other than web services.. coz ourserver is quite old and doesn't support webservices..any ideas.--chavasreedharMessage posted via http://www.exforsys.com for all your training needs.

View 1 Replies View Related

Need Suggestion

Mar 14, 2008

All,
I need to keep checking (every 30 minutes) for a record in a table to appear,
Any idea on how to do it? Should it be done in a SSIS package or in sql server job schedule?
Please give more detail on to accomplish.

Thanks in advance

View 6 Replies View Related

Suggestion Please

Aug 27, 2007

We have SQL server 2005 as backend in our portal project.We have job search interface with below searchable fields
1.) City
2.)State --length 2
3.)Zip-- 5
4.)Keywords ( space or comma delimeted words)
5.)Category (Drop down List)
6.)Subcategory (Drop down List)
Based on the above fields we have 2 options to search and avoid performance related issues.
1.) concatenate all the above 6 columns into 1 column and apply a full text search. Fields like state & Zip will be appended with some delimeter to do a search on those particular values .
Ex : store the state "CA" as '#stCA#'. so that it will not search the rest of the data that has keyword CA.
2.) create a table with the above 6 columns and create a Full-Text search on all the columns.
Can you suggest us which one will best and why? If not any alternate better solution

View 1 Replies View Related

SQL Insert Suggestion

May 27, 2008

I have a table that allows the scheduling of devices.

I can create an Insert Statement that allows a user to schedule a device as follows:
Device #1 From 5/1/08 To 5/30/08
Is there away to create a check that prevents the user from scheduling that SAME device for any days within that month?
Device #1 is NOT available to be scheduled anytime during the month of May because it is already scheduled.
Thanks for any suggestion.
Wayne

View 10 Replies View Related

Suggestion Needed.

Jun 24, 2004

Hi

I have a few tables which store classinfo, testsinfo, scoresinfo respectively.
For a given classID/TestID/CourseID i need to be able get the top 5/10/15 etc, avg, max, min of scores for reporting purposes.
I am wondering if i should maintain a seperate table which would be populated by a job daily and which would maintain the topper IDs rather than having a Stored proc return them dynamically when needed. I am looking for some suggestions. Please let me know.

Thanks.

View 1 Replies View Related

Really Need Some Suggestion On SQLCacheDependency.

May 3, 2006

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.
The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.
I am using 2.0 + SQL 2005.
Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.
Any suggestion?

View 12 Replies View Related

Suggestion Requested

Oct 18, 2006

My primary data files are ponting to F drive which is 200G and one db has occupied 90% of the space and the applications are not running. What is the best solution

View 1 Replies View Related

Suggestion To Use Text

Oct 21, 2007

Hai, This posting related to my previous posting. Updating Bulk record. Actully im writing SP for windows Services, which does bulk updating of records to Sql 2000.
In one point of time, i may recieve 2000 records for single update..So what i have done, im concatinating the values to be updated in string and passing the string..
But when i did the calcuation for 2000 records, the length of the character comes around 10000. So i cannot user Varchar to hold me concatinated value in SP. There is another datatype "Text" but im not sure abt that. For my requirment what is the data type can i go for.. if i can use "Text" how to declare that...Ordinary declaration gives me error..
Help pls

View 3 Replies View Related

Suggestion Abt Using Ntext

Oct 24, 2007

Hai, i need ur suggestion to use ntext in my table. basically i want to store the xml data(will be in same xml format only) in sql2000 database. the max size of the data will be 5mb.
so can i use ntext to store this data.basically, only once i insert the data to the database. no update after that. only i throw the query to retrieve that xml data from the database. is it advisable to use,ntext for this requirment. is there any other suggestion ??
help pls

View 2 Replies View Related

Book Suggestion?

Apr 16, 2007

Can anyone recommend good books for learning Reporting Services? Assume the reader is familiar with Crystal Reports and other reporting environments, but has no experience with SQL Server 2005 and very little with Visual Studio 2005.

View 1 Replies View Related

Need Suggestion On Using SSIS

Mar 25, 2008

Hi,

I have push scripts using transcation replication . can i use some task of SSIS to automate the process .

Thanks,
Nav

View 5 Replies View Related

Re:Please Give Me Ur Suggestion

Nov 13, 2007

Hi
I am new to Sql Server

I have to select all fields from a table based on min(date) . i have duplicate accountnumbers in the query i need to eliminate that based on min(dateTime).
when i am writing a query

Select a,b,c,min(d) from table , it is not working?
Below is the example

A B C D
222 1 11 2007-02-12 14:09:16.000
222 1 8 2007-11-06 13:57:18.480

Any suggestions would be very helpful

View 3 Replies View Related

DataFlow Suggestion

Dec 12, 2006

I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.

Here is what i do.

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved???

2.add one more column to my text file which should contain the status(insert or update).

how can this be done??

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

please help...

View 5 Replies View Related

Need Suggestion/advice

Aug 28, 2006

Hi guys,

I would like to be advised from experts on one career related question.

I have 5 + years of Experience as a DEV and Test.

I had mostly worked on VB6, .NET 1.1[ asp.net ,C#] for 3 years as DEV and since 2 years i am into Software System testing [ its more related to Functionality Testing of applications built using .NET [ asp.net/c#, Sql server 2005 ].

I would like to switch the career to Core Development again and would like to seek experts's adivice..

Iam interested in learning SQL Server 2005 advanced features[ BI]:

Could you pls suggest me one area in SQL server 2005 from below[ parameters:I should be able to learn it quickly on my own,great demand in the market , great scope for development in near fure etc.,] :

1) SSIS

2) Analysis Services

3) Reporting Services etc.,

or any other suggestions are welcome..I appreciate your time responding to this thread.

thanks!

View 5 Replies View Related