|
If you have different databases for different web sites, and your users would need separate logins to each web site, then keeping the login data with the rest of the site data sounds like a better choice from the design perspective.
|
|
|
|
|
Go with option 2, it sound horrible duplicating user profiles in each db. You could setup tables in your memberDB to control which app each user can login to.
|
|
|
|
|
I am trying to read table data from all my tables in a database to a summary table which contains the table anme in a column, but I am not sure what is the best way to go about doing this. Should I use temp tables or sp_executesql or some other method. I want to use a variable for the table name but I don't think that will work unless I split the string below in 2 and insert a table name. Or should I use temp tables which I am not familiar with or other method, if so how would I do that.
I appreciate any help I can get.
Thanks in advance,
Michael
SET @SQLString1 = N'
DECLARE
@dtLatestDate DATETIME
SELECT @dtLatestDate = B.dtDateTime
FROM
(
SELECT TOP (1) dtDateTime
FROM '
(split here the add tablename, see below)
@TableName
SET @SQLString2 = N'
ORDER BY dtDateTime DESC
) AS B'
OPEN strTableName_cursor;
FETCH NEXT FROM strTableName_cursor INTO @strTableName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @SQLString1 + @TableName + @SQLString2
UPDATE [dbo].[Summary] SET dtLatestDate = @dtLatestDate WHERE strTableName = @strTableName
FETCH NEXT FROM strTableName_cursor INTO @strTableName
END
CLOSE strSymbol_cursor
modified on Saturday, May 14, 2011 3:50 PM
|
|
|
|
|
If you want to vary the table name dynamically then you must use dynamic SQL. There is no alternative.
Either sp_executesql or 'execute' can be used.
Are you sure it needs to be dynamic though?
|
|
|
|
|
Hi All,
I have two tables (sql server)
MainTable
---------
Emp_code BasicPay TotalAddition
------- -------- --------------
160 3000 200
161 4000 100
Additions Table
--------
trx_amount desc emp_code
---------- ---- --------
100 Bonus 160
100 Transport 160
20 Bonus 161
30 Transport 161
50 Food 161
My output grid should be like this
Emp_code Basicpay Bonus transport Food TotalAddition(total of additions)
-------- --------- ------ --------- ------ -------------
160 3000 100 100 0 200
161 4000 20 30 50 100
Please suggest with this
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
You need to pivot[^] the "Additions" table, before joining it to the "MainTable" on emp_code.
|
|
|
|
|
I m a new bee i have used sql server 2000 before my question is when creating two tables in sql server 2000 say
location and projects table projects having a foreign key referencing the location table when inserting values in
location the projects is also updated thats is no need to insert the similar value in the foreign key in projects table
why is it not possible in mysql when i insert values in location using insert command and when using select command on
projects it does not shows the value in foreign key please check the below code
mysql> create table location(
-> id int not null,
-> primary key(id))
-> engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> create table projects(
-> id int,
-> location_id int,
-> foreign key(location_id) references location(id) on update cascade on del
ete cascade)
-> engine=innodb;
Query OK, 0 rows affected (0.31 sec)
mysql> insert into location values('1')
Query OK, 1 row affected (0.34 sec)
mysql> select * from location;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> select * from projects;
Empty set (0.00 sec)
as u see in the above code it was possible in sql server 2000 that the value was reflected in the child table why is
it not possible in MySql Why should the value be insert in both the tables isn't it possible in MySql that when i insert
in the location table the projects table pick the value automatically and show it in select Query when i query the projects table
|
|
|
|
|
Do not create duplicate posts, you can edit your previous post and make your additions.
|
|
|
|
|
I m a new bee in mysql i have used sql server 2000 before my question is when creating two tables in sql server 2000 say location and projects table projects having a foreign key referencing the location table when inserting values in location the projects is also updated thats is no need to insert the similar value in the foreign key in projects table why is it not possible in mysql when i insert values in location using insert command and when using select command on projects it does not shows the value in foreign key why should the value be insert in both the tables isn't it possible that when i insert in the location table the projects table pick the value automatically and show it in select result when i query the projects table
|
|
|
|
|
There is nothing it TSQL (Microsoft SQL Server "Transactional SQL") that is going to automatically insert foreign key values.
You might have used some tool that created TSQL for you, but then it generated TSQL which explicitly inserted the key value.
|
|
|
|
|
Hi there,
i have a sql server table there i have thousands of record and when i call it from Stored procedure then it will take very huge time to display records how can i make Fast.
Thanks in Advance
|
|
|
|
|
Returning thousands of rows is usually not the problem.
The problem is usually in the query (your stored procedure).
It might help if you were to post the Stored Procedure?
|
|
|
|
|
Without your query I can only suggest things to try as I had to do something similar just recently
1. Break your query down into sections and find what part is slow
2. Indexing your data.
or like the other poster suggested, post your query
Nagy Vilmos wrote: And eat bacon. Bacon's real important for 'puters.
|
|
|
|
|
The general answer is write more efficient code. Since you haven't posted your code, we can't tell you where to improve it.
|
|
|
|
|
In general you shouldn't be attempting to display "thousands" of records in the first place.
This typically occurs in list box type displays for users who are interested in looking at the records. But those users don't want 'thousands" what they want is a very small subset very likely one record. And they know what they are looking for. So only display the single record they are looking for.
|
|
|
|
|
Posting your query will help us determine your problem, but here are some general tips:
1. Index your table(s) properly.
2. Select only the required rows using a properly constructed WHERE clause.
3. Select only the required columns.
|
|
|
|
|
I managed to duplicate a single record. However I intended to display the duplicate record with different values. For example:
A ABC 065 001 A10111 Sell 54.847500 0 0
T ABC 065 001 A10111 Sell 50.847500 0 1
SELECT
case i.ordStatus
when 'Queued' then 'N'
when 'Filled' then 'A'
when 'Partial Filled' then 'A'
when 'Cancel' then 'C'
end AS 'ordStatus',
f.code AS 'exchCode',
g.code AS 'companyCode',
h.code AS 'branchCode',
c.code AS 'clientCode',
i.transType AS 'transType',
case i.ordStatus
when 'Queued' then ((i.quantity * i.price * i.exchRate) / j.denomination)
when 'Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Partial Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Cancel' then ((i.adjust * i.price * i.exchRate) / j.denomination)
end AS 'ordAmount',
case i.ordStatus
when 'Queued' then 0
when 'Filled' then 0
when 'Partial Filled' then 0
when 'Cancel' then 0
end AS 'matchAmount'
FROM TM_TradingProfile a
LEFT JOIN TM_clientTradingProfile b
ON a.id = b.id
LEFT JOIN TM_Client c
ON c.id = b.TM_client_fk
LEFT JOIN BKL_User d
ON d.id = c.id
JOIN BKL_Authentication e
ON c.id = e.BKL_user_fk
LEFT JOIN RM_Exchange f
ON a.RM_exchange_fk = f.id
LEFT JOIN BKL_Company g
ON a.BKL_company_fk = g.id
LEFT JOIN BKL_Branch h
ON a.BKL_branch_fk=h.id
LEFT JOIN RM_MarketTransaction i
ON a.tradingAccountNumber = i.tradingAccNo
LEFT JOIN bkl_forexexch j
ON i.tradCurr = j.currencyCodeFrom
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
WHERE a.tradingAccountNumber='ST3273'
Besides, I should be able to display a duplicate record when the ordStatus is filled or partial filled.
Your help is kindly appreciated.
Thank You.
|
|
|
|
|
Can't tell exactly what's wrong but you seem to have too many joins. That cross join... hmmm... I am nos sure what you want to do. Personally, I would use a union operator to duplicate records.
SELECT a.col1, a.col2,... 0 As Flag
FROM myTable a
WHERE ...
UNION
SELECT b.col1,b.col2,... 1 as Flag
FROM myTable b
WHERE ....
|
|
|
|
|
Hi,
I am using dot net and sql server. I am sending my data from the database table to the pdf file. It looks using lots of tables to get data slowing down the program and so I tend to use the view instead of tables. Will it be the good idea to use view and if yes, i have few more questions.
1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
2. If I insert data in the view, will the data be there once i close my program?( I am assuming view as the cache table)
3. what is the difference between using view and actual table ?
4. How the use of view and table different?
Thanks in advance
suchita
|
|
|
|
|
SayamiSuchi wrote: 1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
You can't insert data into View.
SayamiSuchi wrote: 3. what is the difference between using view and actual table ?
Difference between View and table[^]
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Blue_Boy wrote: You can't insert data into View.
Actually, most modern databases support what is called "updatable views". If your view does not contain calculated columns and the base tables have a default defined on the columns not selected in the view, then the view must be updatable.
|
|
|
|
|
|
A "view" is a way of looking at data stored in table(s), therefore using a view instead of the underlying base table will not solve your performance issue. If your query is slow, consider these tips:
1. Index your base table(s).
2. Use a WHERE clause to filter only those rows that is needed.
3. Select only the required columns (don't use a * in the SELECT statement).
|
|
|
|
|
i have create a function in pubs data base
create function show(@temp varchar(11))
returns table
return(select *from author where au_id=@temp)
now i wants to access this function from aspx page using c#
please give me an applicable answer so that i will satisfied
thanks
regards
(manoj bhatt)
|
|
|
|