I want to design my little e-commerce project database. I am using ASP.NET
and Role based authorization.
I have 2 types of customers:
1)On-line customers
2)Walk-in customers processed by employee.
I come up with these tables so far:
1)Customers -- Collects cust info
2)Employees -- Collects emp info
3)Roles -- Role Info
4)EmployeeRoles
5)Orders
-- Online customers are processed by a default employee designated for that purpose.
-- Walk-in customers are processed under the servent's role ( the employee who process their orders).
-- tables customers & employees contain the same data can I merge them into on table "Users"?
-- My problem with this is Orders contain OrderID, CustomerID, EmployeeID.
WHERE (Cono = @Company) AND (DATEPART(month, PaymentDate) = @Month) AND (DATEPART(year, PaymentDate) = @Year)
If I run the job in preview mode I enter the parameter data as requested and it runs correctly. When I go back to design mode and run the query using the ! (the parameter box pops up with the data I entered in preview mode) I get an error message - The Parameter is incorrect.
I've tried setting the parameters to every combination of string/interger I can think of.
SSIS, I would like to perform/validate an experssion in design mode. In particular, i would like to see the value of the varibles before running the whole package. I tried several time in Command Windows or Immedite Window unsuccessfully. Do those windows can only be used in running mode? Or how can I do that in other way?
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
Hi everybody.. need help on this situation which i am to.
I have two databases named db1 and db2 both of which has two identical tables named tbl1 and tbl2
I need to compare tbl1 of db1 to the tbl2 of db2 if there is a record that is existing on tbl1 and not on the tbl2 then i need to create a tblnew on db2 from that tblnew then i need to append all the data from tblnew to tbl2 of db2.
I don't know how to start with it because i'm used to appending data on two tables on the same database but not on a different one...
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem? Thanks.
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV) 1X 1Y
Results Table(SQL) CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point 2015-08-04 08:35:29.203 successfully inserted value 1Y 2015-08-04 08:30:29.203 Test Pass 2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output: All values from values table are inserted successfully Total count of values inserted: 2 If only few values are inserted, example only 1X from Values table is inserted in Message
Example: Results Table CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point
Output: All values from values are not inserted successfully in result table. Total count of values inserted: 1 Missing Values not inserted in results table are: 1Y
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew If jdoenew is missing in the 2nd db I will need to write entries like: db1.usr_table.usr_name = jdoenew db1.usr_table.password = tmppassword db1.usr_table.active = 1
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
I am using two database server. Both are sql server.
My task is :I want to insert data from server 1 table to server 2 table and update same when modified the existing data from server 1. is it possible to do the integration in single package.
I know to do insert and update seperately by ssis but need to do same with single task.
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
I need to update the AcquiredFrom table from source_office table. both the tables are from different database and server. we can match them on Code and source code. Initially if we can find out the discrepancies and then how can we fix them. Also there might be some dealerships that would have to be added to acquiredfrom table from the source_office table if they are not there already.
Table 1 (AcquiredFrom) database (Service) Server(FS3) Select Code, Name, ContactName, AddLine1, AddLine2, city, state, zip, PhoneNumber FROM [dbo].[AcquiredFrom] Order by Code Table 2 (source_office) database (DCP_PROD) Server (DPROSQL) Select source_code, name, addr1, addr2, city, state_id, zip, phone FROM [dbo].[source_office] order by source_code
I can't seem to find a good tutorial in transfering data from one table in database a into a different table in database b. The catch is that the fields in the tables don't match exactly. How can I do this? Examples, tutorials, etc are greatly appreciated.Thanks!
Hi, I have a Users table that I use for membership. Now I am using username varchar(30) as the primary key for this table since username will always be unique.I am storing username in UsersInRole table so that I can find what roles each user has.The question I have is regarding how SQL Server actually stores data:I see that when I add users, they are always stored alphabetically sorted on username. I was expecting that all users will appear on the users table in the order they were added.Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob is has become the first row of the table.My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case huge other rows will have to move.Bottom line, insert, delete will be expensive. I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.Thanks
I am new to sql and my boss want me to write the program for database and he wants to pull the info from existing table from sql server which is used by Microsoft GP Dynamics and write me code or some kind of front end so when he wants he can pull same data from GP dynamics table and generate some report with other custom table.
How i can do this task? What I have to do in sql so I can use same table to view info in real time so that means if i enter new data in the table it will show up same time in the front end as well.
I am construvcting a number of databases, some of which containsensitive data and most of which do not. I am attempting to handle thesecurity issues involved in protecting sensitive data in part byputting it in its own database. If the sensitive data is in a databasecalled d_SensitiveData, and in that database there is a table called't_A' (I know, not very informative, but this is only a triviallysimple example :-), and I have a script running in a database'NotSensitiveData' (i.e. there is a statement at the beginning of thescript "USE NotSensitiveData"), how do I referenece the primary key intable 'A'?I tried a variety of things like:ALTER TABLE t_nsXADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)REFERENCES SensitiveData.t_A (p_idPerson);The above is, in fact, my latest attempt. Everything I tried hasfailed precisely at the point where I specify a table in a differentdatabase. So what is the trick to refering to a table in one databasewhen using another database?This will be used in a ASP.NET v3 application where one of the things Iwant to do is have the authentication provider be a different databasefrom the one used for the main application data.Does anyone know of an example I can download from the web that doesthe same sort of thing I want to try, with some discussion of securityissues involved (i.e. what I can do to harden the application and dataserver)?NB: I am an application developer, not a DBA nor a systemadministrator.ThanksTed
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
How do I insert data that I have collected in a local database onto a table on my online ie hosted database which is on a different server?
At the moment I am just uploading all the data to the hosted DB but this is wasting bandwith as only a small percentage of data is actually selected and used.
I thought that if i used a local DB and then update the table on my hosted DB this would be much more efficient, but I am not sure how to write the SQL code to do this!
I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.
I have "inherited" a project working on a SQL 2000 database. The project calculates commissions based on data from an invoice header table and an invoice details table. The goal is to extract data from the primary database tables, perform limited manipulations, and store the resultant data into a table in a different database for reference and reporting. I have the query complete that extracts and manipulates the data, but I am stuck in trying to add this data to the final storage/reporting table. I would also like to do error checking to be certain that a record is not "re-inserted" from the source data to the destination table. Thanks in advance, Barry
I have two databases in SQL Server and they both have almost the same tables. I want to insert the data of a table from the first database into a table with the same name, but from the second database. How can i do this?
We copy a table from a progress database into our sql server database that we use as a read-only table. We perform this action through a job. Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table?
if i have one table from database A, and i want to copy the table to database B, then how to do it in SQL server management studio express? is it i have to make sql query?
I am using Visual Studio 2005 to connect to a database on a remote SQL2000 server. I need to copy a table in a database on my local machine up to the server. How can I do that? Thank you,
