how to create report from multiple tables

hi all,
i want to create report from 4 to 5 tables by providing one value
through which i move from one table to other.
how that is possible . can any body help me .
looking for reply
good bye
umarWhen creating the report, add all the required tables and field and link them by common column|||hi
thanks for reply but i want to create report via code by providing
one value at run time and clicking button to generate report.

Thanking u for ur help
good bye
umar|||hi
try like this

Dim cr As New SaleBalanceReport
da = New SqlDataAdapter("select s.*, d.* from saleorder s, dispatchdetails d where d.Sale_note_no=s.saleorderno and s.saleorderno='" & txtsalenote.Text & "'", con)
da.Fill(ds, "salebalance")
cr.SetDataSource(ds.Tables(0))
SBRViewer1.ReportSource = cr
SBRViewer1.Refresh()
ds.Clear()

happy programming
kameswararao

How to create nested sql scripts

Hello,

I want to be able to create the tables in a database from multiple scripts.

I have a separate sql script for each table and I want to make one master sql script that call all separate table scripts.

I searched for hours now in the documentation and the internet and I can't find a simple solution. I hope somebody here can help me. I just want to run a sql script from another sql script...

Bernard Dijkstra

This is a solution:

1. I made in c:\ the file master.sql that contain

EXEC xp_cmdshell 'sqlcmd -i "c:\child1.sql"';

EXEC xp_cmdshell 'sqlcmd -i "c:\child2.sql"';

2. child1.sql contain : select 'This is script 1'

child2.sql contain : select 'This is script 2'

3.if i run in command:c:>sqlcmd -i "c:\master.sql" the output is :

--
--
--

-

This is script 1

NULL

(1 rows affected)

NULL

output

--
--
--

-

This is script 2

NULL

(1 rows affected)

NULL

C:\>

hth

so, the ideea is to use sqlcmd to run scripts

|||

Thanks for your idea, but I think it is no solution for me.

So there is no simple solution that also can be used in the Query Analyzer and other tools. :-(

|||

For SQL 2000 i think you can replace sqlcmd (that it is for 2005) with isql or osql; xp_cmdshell is in 2000 too.

Try it, don't be angry !

hth

|||

This still won't work for me, since I want to be able to run the script in the Query Analyzer since that's the tool our customers use to run SQL scripts.

I want them to select the database in the Query Analyzer, then load a script and run it.

With this solution this won't work (easily). So I have to put everything in one script, what will be a problem with version management on object level...

I'm not angry, but I'm disappointed...

Thanks for your help anyway|||I think you can do :

exec("use YourDB")
go
EXEC xp_cmdshell 'sqlcmd -i "c:\master.sql"';

|||

I tried this and it seems that the context isn't preserved.

I will write a simple program that will generate a master script that can be used to create the database...

How to create named query across 2 different data sources?

I have a scenario where i need to create a named query between 2 tables are in 2 difference data sources. Iam not sure how to create one for this case. The named view UI forces to pick one data source and validates both the table against it. ( in my case the second table belongs to a different data source). I even tried using the data souce name as prefix and forming the query directly and nothing seems to work even though the documentation clearly mentions that we can create named queries across multiple data source. Any ideas on how to do this? Do i have to use a OpenRowset and directly try to use the second table from a different data source?I ran into the same problem, where my lookup tables were in one database (SQLServer 2k5) and my fact tables were in another database (SQLServer 2k). I ended up copying my lookup tables over to the 2k machine, and eliminating one of my datasources.

What i wanted to try (and maybe you could) was create a linked server between the 2 database servers, and just reference the other machine in your named query.

I never got around to it, because my DBA said there was some problem (pre SP2 i think) with creating linked servers between 2k and 2k5.

Anyway, if you can create the linked servers, i'd give it a go.

Csql

How to create multiple tables on the fly so that every user each has his/her own set of tables?

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

To be honest, this is not really a good idea. Why don′t you store the just in one table with an identifier for the actual user which the data belong to. You will additionally have less pain finding the right table for the user and therefore need less coding logic. The naming part as you mentioned can′be done in SQL Server 2005 unless you create a schema for every user who will have the availbility to create a table. (SQL Server changed the objectowner to the Schema identifier) So I would better use the first option mentioned above to do the work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The problem is, I need to create several tables per user this way, including a number of interrelated primary and foreign keys, for my webapplication. As mentioned, I can already do this for the dbo. using a stored procedure, but this would give the wrong behavior, because all users would then use the same set of tables. I want to give each user his/her own set of tables (dynamically) so that one user would not overwrite the stored work of another user...|||

i really dont know what you are up to but this is really a very, very bad idea.

please review database normalization first.

any way if you badly need it. here's a code to start with

use northwind
BEGIN TRANSACTION -- just in case you want to abort

--list the users

select IDENTITY(int, 1,1) AS ID_Num,name into #mytempx
from sysusers

create table modeltable change the table definition to the one you need
(
x varchar(10)
)

declare @.x int,@.MAX_ID INT,@.CMD NVARCHAR(200)
select @.x=0
SELECT @.MAX_ID=max(id_num)from #mytempX
while @.x<>@.MAX_ID loop thru the users and create each db
begin
select @.x=@.x+1
select @.CMD='SELECT * INTO [' + RTRIM(LTRIM( NAME)) + '] FROM MODELTABLE ' FROM #MYTEMPX
WHERE ID_NUM=@.X
SELECT @.CMD this is the create scripts
EXEC sp_executesql @.STATEMENT=@.CMD run the create scripts
end

drop table #mytempx
DROP TABLE MODELTABLE

SELECT * FROM GUEST -- test the create table

ROLLBACK TRANSACTION -- change to commit if you are very sure

regards,

joey

|||

OK, thanx for the info... I'm beginning to see that it would probably be easier to store the data I need as a BLOB (e.g. a serialized bunch of ArrayLists) instead of based on many tables per user. I then take the logic for the program away from the server (like it should be in a three tier solution, I guess), and that is probably your point as well...

Thanx for the suggestions nonetheless, it seems that this is far beyond my skills as a DB programmer...

Greetingz,

DJR

How to create multiple tables on the fly so that every user each has his/her own set of tabl

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

To be honest, this is not really a good idea. Why don′t you store the just in one table with an identifier for the actual user which the data belong to. You will additionally have less pain finding the right table for the user and therefore need less coding logic. The naming part as you mentioned can′be done in SQL Server 2005 unless you create a schema for every user who will have the availbility to create a table. (SQL Server changed the objectowner to the Schema identifier) So I would better use the first option mentioned above to do the work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The problem is, I need to create several tables per user this way, including a number of interrelated primary and foreign keys, for my webapplication. As mentioned, I can already do this for the dbo. using a stored procedure, but this would give the wrong behavior, because all users would then use the same set of tables. I want to give each user his/her own set of tables (dynamically) so that one user would not overwrite the stored work of another user...|||

i really dont know what you are up to but this is really a very, very bad idea.

please review database normalization first.

any way if you badly need it. here's a code to start with

use northwind
BEGIN TRANSACTION -- just in case you want to abort

--list the users

select IDENTITY(int, 1,1) AS ID_Num,name into #mytempx
from sysusers

create table modeltable change the table definition to the one you need
(
x varchar(10)
)

declare @.x int,@.MAX_ID INT,@.CMD NVARCHAR(200)
select @.x=0
SELECT @.MAX_ID=max(id_num)from #mytempX
while @.x<>@.MAX_ID loop thru the users and create each db
begin
select @.x=@.x+1
select @.CMD='SELECT * INTO [' + RTRIM(LTRIM( NAME)) + '] FROM MODELTABLE ' FROM #MYTEMPX
WHERE ID_NUM=@.X
SELECT @.CMD this is the create scripts
EXEC sp_executesql @.STATEMENT=@.CMD run the create scripts
end

drop table #mytempx
DROP TABLE MODELTABLE

SELECT * FROM GUEST -- test the create table

ROLLBACK TRANSACTION -- change to commit if you are very sure

regards,

joey

|||

OK, thanx for the info... I'm beginning to see that it would probably be easier to store the data I need as a BLOB (e.g. a serialized bunch of ArrayLists) instead of based on many tables per user. I then take the logic for the program away from the server (like it should be in a three tier solution, I guess), and that is probably your point as well...

Thanx for the suggestions nonetheless, it seems that this is far beyond my skills as a DB programmer...

Greetingz,

DJR

How to create multiple personal tables on the fly for each registered user of a website using VW

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

In SQL Server you will need to create a schema for each user name to get the effect that you want, for example:

CREATE SCHEMA [user name]
AUTHORIZATION [user name];

CREATE TABLE [user name].Table1
(id int);

Then you can do things like:

SELECT * FROM [user name].Table1;

There are a number of variations of how you might do this, but this is a simple example of the basics.

Dan

|||

OK, doing some extra research in Server Books Online 2005 Express I found the same info. But here's my problem: How to do it dynamically.

Here's the full scenario:

1) I create a user in my web application using the default database ASPNETDB.mdf created by using the login controls in ASP.NET 2.0 (all goes well so far)

2) I then want to create the complete set of tables dynamically for the user that was just created, in a different database (e.g. APPDB.mdf)

So far, I think I have to tackle it this way, but I don't seem to be able to figure out how to actually make it work (or find out if it's really possible at all):

- Use the username for the user just created to create a USER in SQL Server. I would like to do this using a stored procedure, so I can put in the username as a parameter and have the USER created automatically as soon as the user is created in ASP.NET

- Then, I would have to create the SCHEMA in SQL Server, so that the tables I create in this SCHEMA (seems one can create the tables at the same time as you create the SCHEMA, according to Server Books Online) are named [username].Table1 etc.

- After this, there would have to be a way to make sure that when the user logs in each time he gains control over his/her own tables in the database, i.e. the Database Username would have to be linked to the ASPNET Username.

Any Thoughts? All are wellcome...

DJR

sql

How to create multiple personal tables on the fly for each registered user of a website usin

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

In SQL Server you will need to create a schema for each user name to get the effect that you want, for example:

CREATE SCHEMA [user name]
AUTHORIZATION [user name];

CREATE TABLE [user name].Table1
(id int);

Then you can do things like:

SELECT * FROM [user name].Table1;

There are a number of variations of how you might do this, but this is a simple example of the basics.

Dan

|||

OK, doing some extra research in Server Books Online 2005 Express I found the same info. But here's my problem: How to do it dynamically.

Here's the full scenario:

1) I create a user in my web application using the default database ASPNETDB.mdf created by using the login controls in ASP.NET 2.0 (all goes well so far)

2) I then want to create the complete set of tables dynamically for the user that was just created, in a different database (e.g. APPDB.mdf)

So far, I think I have to tackle it this way, but I don't seem to be able to figure out how to actually make it work (or find out if it's really possible at all):

- Use the username for the user just created to create a USER in SQL Server. I would like to do this using a stored procedure, so I can put in the username as a parameter and have the USER created automatically as soon as the user is created in ASP.NET

- Then, I would have to create the SCHEMA in SQL Server, so that the tables I create in this SCHEMA (seems one can create the tables at the same time as you create the SCHEMA, according to Server Books Online) are named [username].Table1 etc.

- After this, there would have to be a way to make sure that when the user logs in each time he gains control over his/her own tables in the database, i.e. the Database Username would have to be linked to the ASPNET Username.

Any Thoughts? All are wellcome...

DJR

How to create dimension with attributes from different tables?

Hi, all here,

I am having a question about how to create dimension with attributes from different tables? For example, I am going to create a dimension called Country-region with attibutes from country table and region table. So how can it be done then?

Thanks a lot in advance for any guidance and help.

Hi,

You will need to create a 'named query' in your DSV and use this named query as a source for your dimension.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. But would you please post some sample code about named query for that then? Thanks a lot.|||

Hi,

I don't know your tables structure but you could create a named query that would look like that:

SELECT DISTINCT a.IdCountry, a.CountryCode, a.CountryName, a.OtherCountryAttributes,b.IdRegion,b.RegionCode, b.RegionName,b.OtherRegionAttributes

FROM Country a

JOIN Region b on b.IdCountry = a.IdCountry

Hopefully, your fact table keeps the region id or region code information. You will then create your Dimension based on the named query and btw, the id of the dimension should be the IdRegion column.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. Got it done.

how to create database inSQl server 2005?

how to start work for creating the database and creating the tables .......which commands we use.?Please don't repost the same question (http://www.dbforums.com/showthread.php?t=1212244) many times in the same day.

-PatP

How to create Database and tables in MSDE

I have install the MSDE from Ofiice 2000 cd and my MSDE sql server is running fine that I can see from my tray bar with green arrow button. Now i want to create the database so can any body tell me how do i create database. Is there ne query analyser or enterprise manager by which I can create database. Also when i try to install starterkit it doen not allow me to install it. It ask me to select database from the dropdown list and when I select Localhost (only option available) and click on test connection it gives me error Unsuccessfull

I am new at MSDN so please help me.

Yoiu have a couple of options.
1) You can rul SQL scripts directly against MSDE using the command line interface that comes with it.
2) If you have Access 2000, XP, or 2003, youcan create a new "Access Project" and connect to your MSDE instancegraphically.
3) For ~$50 U.S, you can purchase a copy ofSQL Server 2000 Developer Edition, and connect to MSDE via EnterpriseManager.
4) You can download and install a copy of SQLServer 2000 Evaluation Edition, and use Enterprise Manager fromthat. Attempt to decipher its licensing limitations at your ownrisk.
|||Could u tell me where to find command line b'se i tried to find it but coudn't. Sorry i m new at MSDE. pls tell me how can I connect through access project. Also I am unable to install Starter kit too.
Pls help me|||The command line tool is called osql.exe. Here's an article that'll help you use it: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003
For connecting though an Access Project, either follow the wizard, or look it up in Access help.

|||Thank you very much for your answer. I was able to find out the osql.exe utility.
I am having one problem when I try to execute the command osql -e it ask me password and when i type 'sa' as password it gives me error that the username is not associated with trusted sql server. I think when I installed the MSDE I did not give it any strong user name and password so I wil try again by giving strong [password t the time of installing it.
Once again thanx very much for your time.sql

How to create CHECK CONSTRAINT with SMO

I am using C# and SMO to create tables. Have figured out how to create the tables and foreign keys. but not how to add a Check Constraint in code as in:

ALTER TABLE [dbo].[BackgroundChecks] CHECK CONSTRAINT [FK_BackgroundChecks_BackgroundCheckTypes1]

Any help is appreciated.

Have you looked at the AddDefaultConstraint method of the Column object?|||

On second thought, you probably want to work with the ForeignKey object. Here's an example from BooksOnline that should help:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Declare a Table object variable and reference the Employee table.
Dim tbe As Table
tbe = db.Tables("Employee", "HumanResources")
'Declare another Table object variable and reference the EmployeeAddress table.
Dim tbea As Table
tbea = db.Tables("EmployeeAddress", "HumanResources")
'Define a Foreign Key object variable by supplying the EmployeeAddress as the parent table and the foreign key name in the constructor.
Dim fk As ForeignKey
fk = New ForeignKey(tbea, "test_foreignkey")
'Add EmployeeID as the foreign key column.
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, "EmployeeID", "EmployeeID")
fk.Columns.Add(fkc)
'Set the referenced table and schema.
fk.ReferencedTable = "Employee"
fk.ReferencedTableSchema = "HumanResources"
'Create the foreign key on the instance of SQL Server.
fk.Create()

|||That creates the foreign key, but not the Check Constraint.|||

OK, got it. Much simpler than it should have been given the amount of time spent.

string sqlCmd = "ALTER TABLE [" + myTable.Name + "] CHECK CONSTRAINT [" + myForeignKey.Name + "]";

myDB.ExecuteNonQuery(sqlCmd);

|||There have been a number of cases where I've used Transact-SQL to solve a problem where I couldn't find the solution within SMO, but in this case it might be worth your while to check out the AddDefaultConstraint method of the Column object, as I mentioned in my first post. The argument you'd use with the method would be the myForeignKey.Name property.

How to create categories tree with subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:

News

--With country

--With world

How i write in Select Command ?

SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

How do you connect categories and subcategories? Does db_subcategories have ID which says to which category that subcategory belongs?

If that is true, than:

SELECT db_categories.ID, [ID_subcategries], [Name_subcategories]FROM [db_subcategories], [db_categories]WHERE db_categories.ID = db_subcategories.IDOrder By 1Asc, 2Asc

|||

Hi,

this is my conncetion in web.config:

<

connectionStrings>

<

addname="ConnectionString"connectionString="Data Source=FS_AMILO_2065;Initial Catalog=sklep.mdf;Integrated Security=True"providerName="System.Data.SqlClient" />

</

connectionStrings>

I have in categories.aspx GridView I link to subcategories.asp

subcategories?id={0}

|||

You can use modification of above query to return all subcategories for given ID:

SELECT [ID_subcategries], [Name_subcategories]FROM [db_subcategories]WHERE db_subcategories.ID = @.catIDOrder By 1Asc, 2Asc

where you use @.catID to store supplied ID

|||

ID is a ID from categories ?

I mustMust declare the scalar variable "@.ID".

So i go to Pramters and ID is a QuertyString and ??

sql

How to create categories tree with subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:

News

--With country

--With world

How i write in Select Command ?

SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

That is not easy but the link below will take you in the right direction. Hope this helps.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm#Figure1

How to create apps that write code to retrieve data with foreign keys?

Hi Guys

Off late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?

Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?

Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?

Thanks

You use the Pk-FK relation to join the two tables and retrieve the columns from either of the two tables.

SELECT t1.col1, t2,col2, t2.col3

FROM Table1 t1

JOIN Table2 t2 ON t1.somecolumn = t2.somecolumn

Assuming the 'SomeColumn' here is the common column between the two tables, the above SELECT statement can be modified to retrieve columns from either of the tables. And I dont think it is cumbersome to retrieve info from another table. Your tables have to be properly normalized. This is the key. Joining too many tables in the query could also be detrimental. It depends on how well your tables are normalized.

>> Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?
It may not be a good tatabase technique to bend normalcy rules but from a practical/real world perpspective, sometimes, people do have redundant data. If you have enough justification (not just laziness or saving time or writing less code) then yes.

>>Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?
Sure, I did that above already.

|||

Thanks ndinakar for the reply. Code that I referred here is not the SQL query, that is not the cause of concern as I too know to retrieve the data from another table which has the Pk-Fk relationship another table with an SQL query. However, it often requires quite a lot of C# or VB code to retrieve the data from the other table when relationships are used.

Can you tell me if there are any in-built code that could be used to retrieve the data from the Pk table given the Fk table? I think I was not clear in my post and this is now clear.

Thanks

sql

How to create and schedule full-text catalogue

Hi,
I have writen a database installation application that simply creates
tables inside a database. I also need to create and schedule
full-text indexing on some of my tables.
I am using SQL server 2000 and and VB.NET 2003.
Thank you,
_dino_You could generate scripts for full-text indexes from Enterprise Manager.
Here's how the scripts look like for example:
http://www.databasejournal.com/features/mssql/article.php/1459591
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:jgale198gjv7suate4st95si2v5it5tbf4@.4ax.com...
> Hi,
> I have writen a database installation application that simply creates
> tables inside a database. I also need to create and schedule
> full-text indexing on some of my tables.
> I am using SQL server 2000 and and VB.NET 2003.
> Thank you,
> _dino_
>|||Thank you
On Sat, 30 Jul 2005 11:30:20 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:
>You could generate scripts for full-text indexes from Enterprise Manager.
>Here's how the scripts look like for example:
>http://www.databasejournal.com/features/mssql/article.php/1459591|||thank you
On Sat, 30 Jul 2005 11:30:20 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:
>You could generate scripts for full-text indexes from Enterprise Manager.
>Here's how the scripts look like for example:
>http://www.databasejournal.com/features/mssql/article.php/1459591

How to create and delete tables in SQL Server.

I have started learning SQL server...please help me to tell me how to create and delete table in a database!!Hi, and welcome to TSDN!

Take a look at this article. Even tho it's written for MySQL the query examples should work on SQL Server as well.

In the future, you might want to consider posting questions about SQL Server in the SQL Server forums, accessible from blue navigation bar at the top of the page.|||I have edited the thread's title to better describe it's content and moved it to the SQL Server forums.
Please read the posting guidelines before posting.

Moderator|||Here is the Microsoft SQL language reference.

Also a very good book I recommend is called SQL Bible by Alex Siegel and Boris Trukhnov. ISBN # 0764525840

You can get it here if your interested.
Barnes & Noble|||Hi darsh999 ,

If you have enterprise manager (sql 2000) or SQL server studio (sql 2005) availabile you can create and delete databases and tables through a gui allowing you to visualise the referential links between the tables.

If not, a good place to start if you are learning is to download SQL 2005 Express and do a full install. This will give you the SQL server studio to practise with.

Regards Purple

How to create an table with composite Key?

Hi all,

well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.

here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)

my question is, how can i create these tables with composite key?

ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.

Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.

here are the tables

1) empidtable

empid,fname,lname

2)empcountrytable

empid,country

3)empvisatable

empid,visa

4)empdoctable

empid,doc

5)empfile

empid,filename,filecontenttype,filesize,filedata

Plz do help me

thanx in advance

If you're using Enterprise Manager, go to Design View of the table anduse control + click to select all the columns you want as part of thecomposite key, then right-click Set Primary Key.
|||The table with the composite key will have all the primary key columns of the five tables because a composite index must be on one table. You can have up to 16 columns and 900bytes so if you are using unicode you can only have NVARCHAR (450). The alternative which is used by Microsoft Consulting is to create a small Clustered index and add the column of the clustered index in all your nonclustered index on the table. You can use the index tuning Wizard which is part of the profiler to tell you the indexes you need on a table. Hope this helps.|||

Hey thanks for that.

will try to implement the same

How to create an Audit Table

Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry
Here is my trigger:
Create TRIGGER tg_audit_task_order_awardees on xxx
for INSERT,UPDATE,DELETE as

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)

Select
'OLD',
del.to_awardee,
del.solicitation_id,
del.contract_id,
del.order_number,
del.amount,
del.show_public,
getdate(),
del.modified_user
FROM deleted del

/* for a new record */

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)
Select
'NEW',
ins.to_awardee,
ins.solicitation_id,
ins.contract_id,
ins.order_number,
ins.amount,
ins.show_public,
getdate(),
ins.modified_user
FROM inserted ins

Take a look at this article... offcourse there are other ways to do it, but this might give you some ideas to do it... I believe the only problem is it can't handle text and ntext fields:http://www.codeproject.com/database/AuditTriggers.asp

How to create a View from temporary table ?

Hi all,
Iam aware that #Temporary tables can not be used in creation of views, but
iam in a situation where in iam needed to do so.
can anybody provide me with a workaround for this, its very urgent....any
help would be highly appreciated.
Thanks in advance,
Kishore> Iam aware that #Temporary tables can not be used in creation of views, but
> iam in a situation where in iam needed to do so.
Can you elaborate? Why do you need a temporary table and a permanent view
of that temporary table? This sounds like buying a house in Florida because
you're going there for spring break.|||Well, you can't do that.
So if that view that you want is something you will be using in all queries,
then select the required rows and insert into another temporary table and
use this assuming you are using a view.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWPH%23AedGHA.3632@.TK2MSFTNGP05.phx.gbl...
> This sounds like buying a house in Florida because
> you're going there for spring break.
Aaron,
One of these days I am going to put together a compilation of these
analogies of yours. You have a particular knack for them.
I'm not being sarcastic at all. You always manage to come up with a
comparison that suits the situation perfectly.|||Hi,
Thanks for your prompt reply.
As asked by you, i will try to elaborate a bit more, please pardon me for
this lengthy explanation as i am in very urgent need of it, any help would b
e
highly appreciated.
My Business need requires that i need to create a table whose columns names
are taken from data contained in different tables, in essence iam creating a
table dynamically, which will have different number of columns in each run.
For efficeincey purposes, my customers earlier implementation uses Temporary
tables.
finally that dynamic #Temptable is filled up with data and a select
statement applied on it returns the needed data in XML format.
Then they came across the SQL Server row size limit of some 8060 bytes,
since each of the column in my dynamic table is of type NVARCHAR(255), at
maximum i can add 15 columns only to it. so instead of having only single
table, i decided to host those columns in multiple tables, i mean 1-15
columns will be added to #temptable-1 then 16-30 columns will be added to
#temptable-2 and so on....
so that problem has been addressed, but the output of the final select
statement that works on the dynamic #Temptable is creating problem...earlie
r
there was only one table, but now there can be 'n' number of tables dependin
g
on the number of columns to be added, so the XML resulting from the select
statement on these 'n' number of dynamic #Temptables is different from the
earlier on when there was only one dynamic #Temptable, but i need to maintai
n
that format due to some other dependencies.
so i thought of creating a single view(since a table can not hold all the
columns resulting from the select statement on those 'n' number of tables)
from all those #Temptables and then generate XML from that single
view...which will be similar in syntax to the earlier case when there was
only a single #Temptable.
Hope this explains...
Thanks & Regards,
Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>|||becuase of the SQL Server 2000 row size limit of 8060 bytes i can put all my
columns taked from other #Temptables into a new #Temptable to be used as a
view as suggested by you, Total size of all my columns exceeds tht limit so
table option is ruled out.
"Omnibuzz" wrote:

> Well, you can't do that.
> So if that view that you want is something you will be using in all querie
s,
> then select the required rows and insert into another temporary table and
> use this assuming you are using a view.|||Mr Aaron,
have you seen my explanation, can you suggest me anything.
-Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>sql

How to create a update button to update two SqlDataSource controls?

I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:

btnUpate_Click(object sender, EventArgs e)
{

sqlDataSource1.Update();

sqlDateSource2.Update();
}

But, the records haven't updated.

In SqlDataSource2_Updating() function, I found all the parameters is null.

So, how to modify my code to do it.

Zhang

You need to actually set the update parameters. Take a look at this link.

http://msdn2.microsoft.com/en-us/library/fkzs2t3h(VS.80).aspx

Sub EmployeeDetailsSqlDataSource_OnInserted(senderAsObject, eAs SqlDataSourceStatusEventArgs)
Dim commandAs System.Data.Common.DbCommand = e.Command

EmployeeDetailsSqlDataSource.SelectParameters("EmpID").DefaultValue = _
command.Parameters("@.EmpID").Value.ToString()

EmployeesGridView.DataBind()
EmployeeFormView.DataBind()
EndSub

|||

I set the parameters' DafaultValue in the Clicked Event of the Update button. And it work fine.

But it is too hard to set so much parameters by codes. Are there any ways to set them automatically as the update link button had done in the FormView?