|
Have a look at SUBSTRING.
I believe that you would have gotten far more answers if you wouldn't have requested to reply ASAP. That's considered rude.
The need to optimize rises from a bad design
|
|
|
|
|
Hi
I want to execute below query in ms-sql server 2000 but its giving errors.
Can anybody help please.
select * from ( select vRefTable from mainmenu where iMenuId=58 )
where (select vFieldsName from TableField where iMenuId=58
and bIsPrimary=1 and iTableFieldId=11) = 1
Its showing below errors. your help is greatly appreciated.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'where'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.
Thanks
Ansari
|
|
|
|
|
Well, I am not sure what are you trying to say with that T-SQL code but I think your query looks like this:
<br />
select * ,<br />
( select vRefTable from mainmenu where iMenuId=58 ) <br />
from TableField<br />
where vFieldsName iMenuId=58 and bIsPrimary=1 and iTableFieldId=11
For more help post more descriptive question.
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.
|
|
|
|
|
Hi
I have tried to analyse my requirement below.
mainmenu--TableName
iMenuId vRefTable----ColoumnNames
58 news ---------Records
TableField
iMenuId bIsPrimary iTableFieldId vFieldsName
58 1 11 iNewsId
news
iNewsId vTitle vUR
1 news1 http:
I am storing tablenames in MainMenu in vRefTable coloumn and coloumn names in vFieldsName of TableFields table.
I want to extract tablename from MainMenu whose iMenuId=58 and coloumn name from TableField table whose iMenuId=58 and bIsPrimary=1 and iTableFieldId=11 i.e iNewsId. After this I will be having both table name and coloumn name, using this I want to extract data based on the table name & coloumn name which I am having.
Thanks again
Ansari
|
|
|
|
|
Well, I'm not sure what you are trying to acheive, and in my opinion its a rubbish way of doing it, but your problem is (I think) that you need to alias you sub-selects.
select *
from ( select vRefTable from mainmenu where iMenuId=58 ) as a
where (select vFieldsName from TableField where iMenuId=58and bIsPrimary=1 and iTableFieldId=11) as b = 1
But I still think you should re-write it
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi,
i display edit procedures (add/delete/edit) in datagridview.
Is it ok (do people often) display report procedures the same way? not in crystal reports?
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
A customer gave me his database which is in Paradox and secured by a password.The problem is the Arabic data appears unreadable.When I open it in Office Access it appears unreadable.If I copy the arabic data to outlook message and send it to my self then open it in outlook and change the encoding to Arabic(Windows) it appears correctly.I changed the aspx page encoding to Arabic(Windows) encoding but the data stiil unreadable.
Then I used a paradox viewer which displayed the arabic data correctly but only for 100 records(It show 100 records only may be because it is a Demo).
The customer will come tommorow to see his database with relations between them and I still don't know how to solve this problem.Do any one know how could I solve this problem? 
I am too late but i will never give up
|
|
|
|
|
I'm working on a app that will act as a front end to a SQL Server Db. Originally the db was in Access and I used the migration wizard to move it to SQL Server. Subsequently I made changes to the schema on the SQL Server. Nothing radical, I added a few extra look up tables, extra foreign keys things like that.
Now, I have to come up with a way to transfer data from the original schema into the new schema. I was hoping I could have a menu option "Import data" where the user selects an Access file and all the data from that file is loaded into the (presumably) empty SQL Server Db.
However, I'm not sure how to do that. My project is in C#, with which I'm very familiar, but I do not have experience with the different SQL Server packages. I'm currently looking into SSIS, but I'm not sure if that's the way I should go. Should I use DTS instead ?? I also found the Microsoft.SqlServer.Management.Smo.Tranfer class that seems to copy and modify schemas programatically.
Any ideas ??
|
|
|
|
|
Could you clarify a little bit (schema in SQL Server is not the same as schema for example in Dataset).
Is the situation that you want to transfer data from Access file (pointed by end user) to a similar (but not equal) structure in SQL Server and perhaps create the structure on the fly under predefined schema (owner)
or
do you want to move your existing tables in SQL Server under a different schema (owner) in the same database?
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Mika Wendelius wrote: Is the situation that you want to transfer data from Access file (pointed by end user) to a similar (but not equal) structure in SQL Server and perhaps create the structure on the fly under predefined schema (owner)
Exactly. The user installs the app and configures the settings to point to a running SQL server. That SQL server might not have configured database, so the user will then have the option to select an Access file that will be transformed into similar but not exactly the same schema. The database will be created and the data from the Access tables will populate the tables.
So in essence I have predefined schema that will be created on the Server side that will then have to be populated by the similar not not equal Access database.
Hope this explains it better. By what I've been reading it seems SSIS is the way to go ??
|
|
|
|
|
It's really hard to give you an exact answer what's the right way to go, but maybe I can point out some info that could be useful.
SSIS
- the package must reside in SQL Server so simply setting up instance isn't enough. You'll have to create the target DB and deliver the package (or vice versa if the package creates the database)
- SQL Server must have access to the mdb-file that user selects (could mean transferring the file to the db server etc)
- SSIS can be a little bit tricky if transformations for the data are not simple
bcp utility (Bulk Copy Program)
- reads a file (delimited or fixed) and inserts the data to a table based on mappings provided in configuration file
- bcp can be executed on client side since it has ability to connect to server or the
- data in Access database must transformed to conform the structure of the table before creating the input file for bcp (bcp cannot do anything fancy)
SQLBulkCopy class in .Net Framework
- same idea as in bcp utility but inside C#
- can use for example DataTable as a source so transformations could be done using C#
- could be potential especially as you are familiar with C#
DTS
- legacy product, don't recommend to use
In all cases you must figure out when tables are actually created and by what program. You can use scripts, attach an empty database which has the structure you want, but tables are empty etc. Also when you have foreign keys, remember to enable them after loading or arrange loading so that constraint violations do not happen.
Hope this helps you forward,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Yeah, I guess DTS is out of the picture, since it has been superseded by SSIS.
I don't like bcp either (I did look into it). I generally do not like running outside apps/batches.
I think I'll export the current working db and use the generated SQL to create the database on the client side.
The problem is transforming the Access data to match the SQL schema. I think I might end up doing it manually in code.
I'm going back to researching the topic a bit more. I'll keep you informed.
|
|
|
|
|
recordset.open
if (lack a record)
add one use connection.execute("INSERT INTO ...")
draw lines using data in recordset
recordset.close
The problem is that when I try to add a record, it throws a _com_error,
which says #IDispatch error 3127. And I tried adOpenDynamc/Static, adLockReadOnly/Optimistic.
professional
|
|
|
|
|
If I understand correctly you are reading a recordset and trying to add missingn records using the same connection. You can't do this, you need to insert your record on another connection as the current one is still busy reading data.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have SQL SERVER 2000 English version. But My program trait date with french version.
What to do during my installation so that my default language be french instead of English.
Because when I already install default English date format, when I try to change the date format, it can not change.
I need help.
|
|
|
|
|
|
Hi,
I need an approach to perform an advance search in one table with given choice of multiple fields in SQL SERVER EXPRESS 2008. For example: If I wan't to edit a customer record with given mutiple paramaters such as (Customer Number, Email, First Name, Last Name, Phone Number), the user can submit more than one field value for the search.
It is easy to do that in ADO.NET but how it would be possible to perfom this with a SELECT statement inside a stored procedure to retrive the record according to given some of the paramaters. If you give me an example, it would make my day 
Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
This is the sort of thing you need
select {column list}
from {table}
where (name = @name or postcode = @postcode)
Of course its up to you to decide whether to use OR or AND, and you also need to deal with nulls.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
that a look in this i made for CD albums search:
(table album has = id_album,...,album_name,id_singer,id_record_company,year,id_song1,id_song2,...id_song20 other tables have primarykeys)
create procedure album_search
@autor as int = null, -- singer
@editora as int = null, -- record company
@genero as int = null, -- pop,jazz,country,rock ect
@ano as int = null, -- year
@faixa varchar(50)= null -- single song
as
begin
select al.id_album as 'ID',al.data_registo as 'Data Registo',al.nome as 'Nome',au.nome as 'Autor',ed.nome as 'Editora',ge.nome as 'Genero',al.ano as 'Ano',fa1.nome as 'Faixa 1',fa2.nome as 'Faixa 2',fa3.nome as 'Faixa 3',fa4.nome as 'Faixa 4',fa5.nome as 'Faixa 5',fa6.nome as 'Faixa 6',fa7.nome as 'Faixa 7',fa8.nome as 'Faixa 8',fa9.nome as 'Faixa 9',fa10.nome as 'Faixa 10',fa11.nome as 'Faixa 11',fa12.nome as 'Faixa 12',fa13.nome as 'Faixa 13',fa14.nome as 'Faixa 14',fa15.nome as 'Faixa 15',fa16.nome as 'Faixa 16',fa17.nome as 'Faixa 17',fa18.nome as 'Faixa 18',fa19.nome as 'Faixa 19',fa20.nome as 'Faixa 20'
from dbo.albuns as al
inner join dbo.AUX_autores as au on al.id_autor = au.id_autor
inner join dbo.AUX_editoras as ed on al.id_editora = ed.id_editora
inner join dbo.AUX_generos as ge on al.id_genero = ge.id_genero
inner join dbo.faixas as fa1 on al.id_faixa1 = fa1.id_faixa
inner join dbo.faixas as fa2 on al.id_faixa2 = fa2.id_faixa
inner join dbo.faixas as fa3 on al.id_faixa3 = fa3.id_faixa
inner join dbo.faixas as fa4 on al.id_faixa4 = fa4.id_faixa
inner join dbo.faixas as fa5 on al.id_faixa5 = fa5.id_faixa
inner join dbo.faixas as fa6 on al.id_faixa6 = fa6.id_faixa
inner join dbo.faixas as fa7 on al.id_faixa7 = fa7.id_faixa
inner join dbo.faixas as fa8 on al.id_faixa8 = fa8.id_faixa
inner join dbo.faixas as fa9 on al.id_faixa9 = fa9.id_faixa
inner join dbo.faixas as fa10 on al.id_faixa10 = fa10.id_faixa
inner join dbo.faixas as fa11 on al.id_faixa11 = fa11.id_faixa
inner join dbo.faixas as fa12 on al.id_faixa12 = fa12.id_faixa
inner join dbo.faixas as fa13 on al.id_faixa13 = fa13.id_faixa
inner join dbo.faixas as fa14 on al.id_faixa14 = fa14.id_faixa
inner join dbo.faixas as fa15 on al.id_faixa15 = fa15.id_faixa
inner join dbo.faixas as fa16 on al.id_faixa16 = fa16.id_faixa
inner join dbo.faixas as fa17 on al.id_faixa17 = fa17.id_faixa
inner join dbo.faixas as fa18 on al.id_faixa18 = fa18.id_faixa
inner join dbo.faixas as fa19 on al.id_faixa19 = fa19.id_faixa
inner join dbo.faixas as fa20 on al.id_faixa20 = fa20.id_faixa
where au.id_autor = isnull(@autor,au.id_autor)
and ed.id_editora = isnull(@editora,ed.id_editora)
and ge.id_genero = isnull(@genero,ge.id_genero)
and al.ano = isnull(@ano,al.ano)
and (fa1.nome like '%' + isnull(@faixa,fa1.nome) + '%'
or fa2.nome like '%' + isnull(@faixa,fa2.nome) + '%'
or fa3.nome like '%' + isnull(@faixa,fa3.nome) + '%'
or fa4.nome like '%' + isnull(@faixa,fa4.nome) + '%'
or fa5.nome like '%' + isnull(@faixa,fa5.nome) + '%'
or fa6.nome like '%' + isnull(@faixa,fa6.nome) + '%'
or fa7.nome like '%' + isnull(@faixa,fa7.nome) + '%'
or fa8.nome like '%' + isnull(@faixa,fa8.nome) + '%'
or fa9.nome like '%' + isnull(@faixa,fa9.nome) + '%'
or fa10.nome like '%' + isnull(@faixa,fa10.nome) + '%'
or fa11.nome like '%' + isnull(@faixa,fa11.nome) + '%'
or fa12.nome like '%' + isnull(@faixa,fa12.nome) + '%'
or fa13.nome like '%' + isnull(@faixa,fa13.nome) + '%'
or fa14.nome like '%' + isnull(@faixa,fa14.nome) + '%'
or fa15.nome like '%' + isnull(@faixa,fa15.nome) + '%'
or fa16.nome like '%' + isnull(@faixa,fa16.nome) + '%'
or fa17.nome like '%' + isnull(@faixa,fa17.nome) + '%'
or fa18.nome like '%' + isnull(@faixa,fa18.nome) + '%'
or fa19.nome like '%' + isnull(@faixa,fa19.nome) + '%'
or fa20.nome like '%' + isnull(@faixa,fa20.nome) + '%')
end
go
as you see in code above a CD album has 20 slots for songs,
you can search for a album that has a particular song
in slot 12 or in slot 6 or whatever slot!!!
you can type exact song name or just a substring(or...like...'%')
you can search for singer, year, kind of music etc.
you can search it all together and see results
it work sweet
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi
I guess you could try something like this.
CREATE PROCEDURE client_stuff
@variable1 int = NULL,
@variable2 varchar(10) = NULL,
@variable3 double = NULL
AS
SELECT * FROM mytable t1
WHERE client_id = ISNULL(@variable1,t1.client_id)
AND client_name = ISNULL(@variable2, t1.client_name)
AND client_balance = ISNULL(@variable3, t1.client_balance)
... etc
I hope that is the right syntax for your DB. Basically you pass in NULLABLE parameters which may or may not be specified, then in the select use ISNULL to replace any NULL values with the value in the column, so if NULL then give me everything in this column, otherwise give me only records whose value in the column match the parameter.
I hope that helps,
Cheers,
Kevin
|
|
|
|
|
hi all
i want to manipulate the receive the table as a parameter in SQL procedure and then want to iterate it to get values and use them in select caluse
i havent done it before if any one can help me out
.............
|
|
|
|
|
You are looking at using dynamic sql, but without further information I cannot point you to any further help.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have hug number of records and the query take many time to execute how can i solve this problem
thank you
|
|
|
|
|
mahmoudinirat wrote: i have hug number of records and the query take many time to execute how can i solve this problem
Look up how to create and use indexes.
Without looking at the structure of your tables and your query that seems to run slow, I don't think we can help a lot.
|
|
|
|
|
Well, you need to look at optimising your queries and indices. With no other information nobody can help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
