|
what the meaning of QUOTED_IDENTIFIER ,ANSI_NULLS in this view(sqlserver 2000)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-
-select *.........
--
-
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
vivek
|
|
|
|
|
|
HI all
I have 50 tables in my database
In 50 tables the fields are same(Name,year,date these 3 fields only)
Now i want write query to retrive all 50 tables information in 1 QUERY
Can any one plz help me
THX
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
|
|
|
|
|
|
Hi!
You can generate the query you need using the following SQL-Statement:
select 'select [name], [year], [date] from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLES
Execute this statement with result type = text, copy the result to a new query windoe and delete the last "union all". It will give you a union all of all your tables -> your 50 tables combined into one.
Hope this helps.
Regards,
Rainer
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
HI
Both of u THX for replying
My problum is
i have an query to search and display
so i given query like this
select name from '" & textbox1.text & "' where date = ''....
table name come from textbox which is enter by client.
now i want to do is to i have to remove the client textbox.
And
by date or any thing i have search the names which is in 50 tables
50 table names i have to display
PLZ help me
THX
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
|
|
|
|
|
Hi!
Ok, I think I understand your problem. You have to change my SQL generation query like this:
select 'create view MyHelperView as '
union all
select 'select [name], [year], [date], '
+''''+table_name+''' as tablename from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLES
This query generates a helper view that you can use in your program. It contains a new colum named "tablename" that you can display. Therefore your query could look like this:
select tablename, name from MyHelperView where date = ...
Hope this helps!
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
The solution provided by r.stropek is good.
Basically I surprise, why do you maintain 50 tables of same structure? Can you not combine all the 50 tables into one table (with one additional column as TableName or Type or Category or anything like that)? (r.stropek suggests to do this through a view.)
I guess combining tables will solve maintenance problem, and help normalization too!
|
|
|
|
|
I am getting the following error while building the dynamic sql query
Server: Msg 245, Level 16, State 1, Procedure proc_sch_ReceivedMails_Sort, Line 45
Syntax error converting the varchar value 'INSERT INTO @tblTemp(
and the dynamic query is with one parameter +@pProjectPK
DECLARE @tblTemp TABLE
(
TempPK INT IDENTITY(1,1),
ContactTypeCodeFK tinyINT,
ContactTypeDesc VARCHAR(50),
ProjectFK INT
)
set @queryString = 'INSERT INTO @tblTemp
(
ContactTypeCodeFK,
ContactTypeDesc,
PartFK
)
(
SELECT ContactTypeCodeFK,
ContactTypeDesc,
PartFK,
PartMaster.PartDesc
FROM tbl_Correspondence CORES
INNER JOIN tbl_PartCode
ON CORES.PartCodeFK = tbl_PartCode.PartCodePK
WHERE CORES.ProjectFK = "'+@pProjectPK+'")'
Print @querystring
exec @querystring
Kalyan
|
|
|
|
|
What I don't understand is why you are even using dynamic SQL for this? You use Dynamic SQL when you need to do something that cannot be done otherwise because of the security risks involved.
INSERT INTO @tblTemp
(
ContactTypeCodeFK,
ContactTypeDesc,
PartFK
)
SELECT ContactTypeCodeFK,
ContactTypeDesc,
PartFK,
PartMaster.PartDesc
FROM tbl_Correspondence CORES
INNER JOIN tbl_PartCode
ON CORES.PartCodeFK = tbl_PartCode.PartCodePK
WHERE CORES.ProjectFK = @pProjectPK
This will work fine like this without being "dynamic"
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
|
|
|
|
|
Actaually this is a part of query only and I am using the dynamic query because I aslo have sort column this will be changed based on the column that the user selects from the screen.
Regards,
Kalyan
-- modified at 18:27 Monday 12th June, 2006
|
|
|
|
|
|
Hi!
Sorry, I don't think that your query will work. You cannot insert into a table variable from inside a dynamic query. Try the following:
declare @test table ( MyId int )
insert into @test values (5)
declare @stmt varchar(128)
set @stmt = 'insert into @test values (6)'
exec( @stmt )
The dynamically executed statment will not work because it does not know about the table var @test.
Additionally I saw that you forgot the ( ) in your exec-statement. exec @querystring cannot execute a dynamic query; you have to write exec ( @querystring ) . Otherwise SQL Server will think you want to execute a stored procedure with the name stored in @querystring. See Transact-SQL-Reference for EXECUTE in Books Online for details.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
I have an problem in sequel query. i.e I have 2 table where master child relationship exits
Table1 CorrespondentCodePK, ContactCode,PartNo,
the data in Table1 is 1,1,1
2,1,1
3,1,2
Table2 CorrespondentCodeFK,PartNo,PartName,
the data in Table2 is 1,1,Part1
1,1,Part2
2,1,Part1
and I want the result in the bellow mentioned format i.e
CorrespondentCodePK,ContactCode,PartName (i.e all part names for the CorrespondentCodePK should come in PartName column as a one record)
1,1,Part1Part2
2,1,Part1
|
|
|
|
|
First, make a function:
NOTE: The lengths of the varchar variables will depend upon your data.
CREATE FUNCTION GetPartNames (@nCorrCode int)
RETURNS varchar(1000) AS
BEGIN
DECLARE @cPartNames varchar(1000)
DECLARE @cPartName varchar(50)
SELECT @cPartNames = ''
DECLARE partcursor CURSOR FOR
SELECT DISTINCT PartName
FROM table2
WHERE correspondentcodefk = @nCorrCode
OPEN partcursor
FETCH NEXT FROM partcursor INTO @cPartName
WHILE @@FETCH_STATUS = 0
BEGIN
select @cPartNames = @cPartNames + @cPartName
FETCH NEXT FROM partcursor INTO @cPartName
END
CLOSE partcursor
DEALLOCATE partcursor
RETURN(@cPartNames)
END
Next, use this SQL:
SELECT correspondentcodepk, contactcode, dbo.GetPartNames(correspondentcodepk) AS PartNames FROM table1
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
If you have SQL 2005 you could also write a short user-defined aggregation function in C# that concats strings.
Regard,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
hi i have a question about the possibility of joining 2 database tables that are on different servers in different countries, there are 2 tables that need to be stored in one location as the information changes and is updated everyday this location is seperate from the rest of the database tables that store specific company information and must be on the companies server.so i guess my question is can this be done and how to go about joining the tables
any ideas, advice as im new to development or help would be appreciated thanks in advance
Tim
|
|
|
|
|
|
How can I send data from MSSQL to HTML ? Suggestions Please.
|
|
|
|
|
|
Reply to me if solution is helpful to you.
In Sqlserver 2000
1. Goto the menu click "query" tab.
2. select "Results to file " option.
3. Execute the query F5.
4. It will prompt the window to save the file .Save the file as ".html" extension.
5. Your requirement is fullfilled.(note -->The result is not in order/linear in order).
vivek
|
|
|
|
|
Thank you Vivek for your answer but, I wanted it specifically configured in HTML format with HTML tags and sp_CreateWebTask can very well do that.
|
|
|
|
|
why " setcount on" is used in stored procedures ?
what does it actually means ?
vivek
|
|
|
|
|
used to display the "messages" return by running a sql statement. Experiment with it in Query Analyzer to see what it does to a basic SQL statement.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Hi,
I hope that there is no option such as setcount on in sql, Instead we have a statement set nocount on/Off
The explanation is given below which is available in SQL books online please refer the same for further explanation
<definition from="" books="" online="">
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
Happy Programming
|
|
|
|