|
You need PIVOT functionality.
What database and version do you use?
|
|
|
|
|
Ofcourse, but with some restrictions,
such
how many h1 h2 h3 exists bcs they will becom coulmns
Perhaps there will be a temp table #T(name nvarchar(x),[h1] integer,...)
and over some loop or cursor(wich is slow) u will fill the #T
|
|
|
|
|
Hi
Can you send me the table structure and with same data? You need to use CrossTab SP. I will modify it based on your table structure and will mail you.
Harini
|
|
|
|
|
Hi every one,
Well I’m working on this project that requires the database to be replicated on another server as well. I’m working in SQL server 2000 Enterprise Ed. And this is my first project of that sort. It also requires a little horizontal partitioning of rows of some tables. Now I’m trying to get this replication going but every time its giving me errors. I’m trying to establish transactional replication here and the servers are connected over LAN.
Right now I’ve tried doin it by making a new database in the subscriber and selecting the option to ‘make new schema and data’ and already ‘existing schema’ as well. In the first case it gives me error that the
‘ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'FK_Products_Products'. The conflict occurred in database 'Server_ATech', table 'Products', column 'CatID'.’
And while I was not initializing the schema it gave me an error over the absence of a stored procedure and that one looked like something related to replication but anyway…
Now I don’t have any idea what else to do… plz tell if it’s a good idea to make a single publication when ur having abt 40 tables to replicate. What should I do… or more likeably; how should I do it???
Please help me as early as you can coz I really have a very short time to complete this thing.
Thanks in advance,
Rocky
|
|
|
|
|
Rocky71 wrote: And while I was not initializing the schema it gave me an error over the absence of a stored procedure and that one looked like something related to replication but anyway…
There is an option while creating the article that specifies whether to call stored procedures to insert,update,delete data or use queries (third tab I think, I don't have SQL 2000 now) Try to use queries not stored procedures.
Also check:
Setup Transactional replication: step by step guide[^]
|
|
|
|
|
hi
i'm doing somtehing like that right now, using linked servers and remote sp.
|
|
|
|
|
yea well, I've kept on trying n trying again and now the replication is working... but the changes at the subscriber are not getting updated at the publisher although I have the proopagate updates my using MSDTC checked.... ahh I'll just tri it again lets see what happens now.
Rocky
|
|
|
|
|
all the matter that is the first time in network applications all earlier was in stand alone machine , i just want someone to give me guidelines
|
|
|
|
|
Does remote queries supports using of temp tables, and how?
|
|
|
|
|
alexrad wrote: remote queries
What do you mean by a remote query?
As far as I'm aware any query can make use of temp tables.
|
|
|
|
|
Is there a way for me to check if a specific row in a table is odd or even? Thanks.
|
|
|
|
|
Do you want to check for the ID of the PK column or the order of the record?
in the first case:
Select *,case (ID % 2) when 1 then 'Odd' Else 'Even' End from items
in the second case (SQL Server 2005 only):
Select *,case (row_number() over (order by ID) % 2) when 1 then 'Odd' Else 'Even' End<br />
from items
|
|
|
|
|
Hi all,
I'm very new to designing and working with DB based apps so please forgive me if I write down things that can make seasoned developer's cry in aguish.
We are right now developping a Remote application scenario with a client app and a server app. The server app will be connected to a DB, SQL Server for more info, which is not, theoretically, reachable from any other machine in the intranet or internet. Only access is through the server app.
Ok the thing is we want the developers of our client app and the client app itself,which is basically 99% of the project workload, to "know as little as possible" of the DB behind the whole project and make all query and update to the DB as simple as possible (through the server app of course).
The design we (I) came up with is the following: (Shuts his eyes and prays he hasnt blown all major rules of designing applications and if so please forgive me)
The server app will have a main worker class named DbEngine which basically does all the work with a few helper classes. The method used to execute selection queries and bring back info to the client app would be the following:
byte[] ExecuteCommand(string commandName, Dictionary<string,object> parameters).
byte[] is a serialized datatable, compressed and most likely, allthough not yet encrypted.
The only thing the client application needs to know is what commandname to call and what parameterName and value to send if any. Please not that the parameters Dictionary does not store any Parameter class, its simply a string key with the parameter name (@columnname) and its value. Its pretty lightweight and easy to code.
The whole work is done in the server side. The thing is, we are storing a table in the SQL Server Database, which we would call TBL_SYS_COMMANDS with all the SQL/Stored Procedure commands our app is going to use. The info stored in this table would be:
[CommmandName] (Primary Unique Key)
[CommandText] (this would be a stored procedure name or a SQL command)
[CommmandType] (Text, StoredProcedure, etc. Server app would parse the string into the corresponding .NET enum)
[Parameters] (would contain custom serialization of parameter(s) info, except Value of course).
The server app would recieve the ExecuteCommand, look up the commandname in the cached TBL_SYS_COMMANDS datatable and if found, build up command and the parameters through reflection, assign their values, execute the command and send back info compressed in a byte array.
Security measures taken: The DbEngine class in the server side which takes care of executing the ExecuteCommand has a custom attribute with a list of allowed commandNames. If somebody somehow injected a harmfull command in TBL_SYS_COMMANDS it would not be executed because its not included in the custom attribute's allowed command list and the DBEngine class would throw a SecurityException...any new command needs the server code recompiled once the new command has been added to the allowed commands list in the DbEngine's custom attribute.
Thats basically it. Am I forgetting some great security hole somewhere storing my SQL commands in a database table? Is this design way to complicated? Basically we want to minimise coding and specially recoding if we make changes and fine tune the database, queries, stored procedures etc, and this system basically allows you to change all this without having to dig into almost any code at all...only update the allowed commands attribute.
Please recommend other approaches etc.
Thanks in advance, and sorry for the long post.
-- modified at 13:16 Friday 9th March, 2007
|
|
|
|
|
I have been writing SQL for about 10 years now. Never have I ever used the words "inner" or "outer" in my stored procedures. I use "join", "left join" and "right join". My question is what are the "inner" and "outer" key words used for? Isn't a join always an "inner join" and a left or right join always an "outer" join or am I missing somethin?
|
|
|
|
|
ToddHileHoffer wrote: My question is what are the "inner" and "outer" key words used for?
Explicitness.
You can alias a table like this:
SELECT * FROM MyTable t or like this
SELECT * FROM MyTable AS t I always use the latter form as I feel that it makes the intention that bit clearer.
Having said that I don't use OUTER as I feel that the LEFT or RIGHT part add enough explicitness. I do use INNER though.
|
|
|
|
|
i'm a biggener , and asked to design SQL database project run on server and send specific fields to clients on the local network ..
i need some recommenditions and books to help me
send accounting informations to and from accounting application , and another feilds to the administrator , and so on
how to train my ideas to begin ?
|
|
|
|
|
how to send data from server to client on the local network and vice versa , using SQL server 2000 ?
|
|
|
|
|
Mr.Kode wrote: how to send data from server to client on the local network and vice versa , using SQL server 2000 ?
You mean executing queries remotely ?
|
|
|
|
|
Hello
My requirement is to do search facility for my website. So what is your idea for maintaining the index ? I read one article that when google was created they used to crawl 10billion pages and index size was just 120GB. how's it possible ? Will they store all words in the target website ? How can I implement a search for single website ?
|
|
|
|
|
I have my DB in SQL Server 2000 with the applications developed in ASP, VB. Now i am looking for mirroring my DB because i am going to change the db strucute with similar modifications. i m going to recreate older ASP, vb applications into C#.net & ASP.net
if any one have the idea.. kindly help in this issue..
thanks in advance.
Tech_spidy
|
|
|
|
|
|
hi every one
well cananyone plz tell how we can backup a database so tat we can restore it with ONLY its table structure WITHOUT the data u know.
Rocky
|
|
|
|
|
Hi,
I presume u work with an SQL Server database.
Make a schema export using Entreprise manager by right clicking on the database / All tasks / Export schema.
The exported file is an sql script.
HTH
|
|
|
|
|
yes that looks preety help ful thanks alot 
|
|
|
|
|
Iam having a table containing 10 rows.
Generally for retrieving the Data we will use SELECT Query as
Select * from tablename;
But if we want a particular ROw then we use as
Select * from tablename where colname="xyz"
If I want to display first row without using where clause and to get the results from select Query then how is it possible?
Thanx in Advance
Priya
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
