Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts

Wednesday, March 28, 2012

Need help / suggestion

Hey guys

I have to implement a dynamic Parent - > Child Scenario ... but the catch is
as Follows :

I need to create this Table Design so that I can have multiple Parent ->
child - > parent Relationships

(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folder
that holds another Folder etc to Infinite )

So if the Above makes any sense ... Suggestions would be Welcome

Thanx1) Get a copy of TREES & HIERARCHIES IN SQL for several different
methods

2) Google for "nested sets", "path enumeration" and "adjacency list"
models in SQL|||Thanx !!!

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL|||Nice 1 celko !!! :P
http://www.intelligententerprise.co...equestid=315563

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL

Friday, March 23, 2012

need example of sproc

looking for an example of how to correctly build up a dynamic where clause
based upon input parametersHi,
See the link for the detailed article on dynamic sql.
http://www.microsoft.com/technet/pr...n/spltmirr.mspx
Thanks
Hari
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:e2Ixgr6aFHA.1660@.tk2msftngp13.phx.gbl...
> looking for an example of how to correctly build up a dynamic where clause
> based upon input parameters
>|||WHERE name = ' + @.name +N'
"TJS" wrote:

> looking for an example of how to correctly build up a dynamic where clause
> based upon input parameters
>
>

Need Dynamic SQL Select turotrial

Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.

Thank you

Do you mean full text searching? (Searching for a particular word within a lot of text)

|||

I have a database column 'Description' varchar(600) that contains product descriptions. I need to search it looking for any of the keywords contained in a Keyword string.

Example Keyword string - sail boat hardware

I need it to find all rows WHERE 'sail' or 'boat' or 'hardware' is found.

Thank you

|||

That form of search is more a full text search rather than a dynamic search. The latter is searching on one to many columns as selected at runtime.

|||

I am using a shared SQL2000 server. What I want to do is find a way in ASP.Net to take the keyword string entered and convert it into a Sql2000 select statement like below.

Keywords entered: sail boat hardware

SELECT Company, Phone, Description
FROM tblCompanyInfo
WHERE (tbl.CompanyInfo.Description LIKE '%' + "sail" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "boat" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "hardware" + '%')

|||

Look athttp://msdn2.microsoft.com/en-gb/sql/aa336289.aspx

|||

I am actualy looking for an ASP routine that splits the Keyword string and creates the OR elements for a Sql2000 compatible Sql SELECT command. I have looked at the many CONTAINS options discussed in the article but did not find any that offer a split like solution. As for a full text search, that option is not available at least not in the shared SQL environment that I am using.

Thank you

|||

In which case you need to roll your own text search solutions.

Create a Noise words table to contain words that you do not want indexed.

|||

Looks like someone should create a new ASP.Net SearchControl. A bunch of us out here could sure use it.

Thank you for the follow up.

sql

Need dynamic SQL

Hi all,

I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to
implement a logic to receive an adress build out of various user definable
fields from various user defined tables. The function is already implemented
in the Client software and as UDF-compliant in MySQL and Oracle. Now there's
just MS-SQL left...

The problem now is for sure, I'm in need of a scalar return value (a
varchar) composed out of a dynamic sql-context. The wohle informations are
placed in a definition table and would result in a sql-string like "select
@.cFieldlist from @.cTable where @.cWhere", e.g. "Select
title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+char
(13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10) from
customer where customer.tooffice=1"...

The problem is of course the calling of the dynamic sql statement within my
function. As read in different articles, this is impossible in MS-SQL. Is
there any possibility to workaround like creating a stored procedure with
output variables and call this within my function ?

Any hint is welcome
Ralphstored procedure with
> output variables and call this within my function ?

You can use OPENQUERY .|||[posted and mailed, please reply in news]

Ralph (Ralph.Backes@.web.de) writes:
> I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need
> to implement a logic to receive an adress build out of various user
> definable fields from various user defined tables. The function is
> already implemented in the Client software and as UDF-compliant in MySQL
> and Oracle. Now there's just MS-SQL left...
> The problem now is for sure, I'm in need of a scalar return value (a
> varchar) composed out of a dynamic sql-context. The wohle informations are
> placed in a definition table and would result in a sql-string like "select
> @.cFieldlist from @.cTable where @.cWhere", e.g. "Select
> title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+
> char (13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10)
> from customer where customer.tooffice=1"...
> The problem is of course the calling of the dynamic sql statement within
> my function. As read in different articles, this is impossible in
> MS-SQL. Is there any possibility to workaround like creating a stored
> procedure with output variables and call this within my function ?

You cannot call stored procedures from UDF:s. The idea is that a UDF
should not change the state of the database. Therefore you are severely
constraint in what you can do.

You can call extended stored procedures, though, so you could call
xp_cmdshell for do what you need from a second connection or a custom-
written XP for the task. Yura suggested OPENQUERY, and OPENQUERY is indeed
another loophole. But since OPENQUERY requires a constant SQL string,
you are still in a dead end.

And I would not really encourage the XP solution either. It may be
logicially correct, but performance will be poor. And if you use
xp_cmdshell there are security considerations. (And you have to be
really desperate to write your own XP.)

As I didn't really understand the problem, it's difficult to come
with suggestions, but you should probably use a stored procedure instead.
I have an article on my web site, which discusses the techniques
around this: http://www.sommarskog.se/share_data.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

Ralph (Ralph.Backes@.web.de) writes:
> So this piece of code should be called in the fieldlist, somehow like
> 'select customer.cust_no,dbo.adress('Bill',customer.cust_n o) from customer
> where deleted=0' and give in return a char (varchar(4000)?)like
> 'Mr.<cr>Hans Mustermann<cr>somewhere street 10 <cr><cr>55555 ZipLand'. To
> find the fields and the correct records, this code has to scan the
> definition table, read the fields to use and under which conditions to use
> and perhaps the tables which should be joined to the query, all written
> there.
> Is this really impossible ?

Generally, if you want to do something in an RDBMS, and also insist on a
certain syntax, it is not possible, unless you happen to pick the syntax
of the RBDMS in question.

For SQL Server, write a stored procedure that returns the address in
output parameters. If you need to handle many at a time, you can use a
temp table. See further here: http://www.sommarskog.se/share_data.html.

Sharing code between RBDMSs is very difficult, because there is a great
variety in what they support. It may simplify programming and maintenance,
to get the data to the client, using basic SQL which works in any RDBMS.
This may be not be good for performance, as more data will be shoveled
across the network.

Then again, your complex and dynamic data model is apparently not designed
for performance anyway. RBDMSs work from the idea that tables and columns
are stable, and that your schema does not change dynamically. So one idea
could be to have custom fields as rows rather than columns. This can lean
to unwieldy self-join, but at least you will not need dynamic SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952F32E37589Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Generally, if you want to do something in an RDBMS, and also insist on a
> certain syntax, it is not possible, unless you happen to pick the syntax
> of the RBDMS in question.
Misunderstanding, we don't insist on syntax, we insist on
functionality. The implementation is of course different on every
RDBMS. But til now every RDBMS gives us a possible way to implement
this stuff with more or less knowledge and doing. Hard to believe
MS-SQL can't...

> For SQL Server, write a stored procedure that returns the address in
> output parameters. If you need to handle many at a time, you can use a
> temp table. See further here: http://www.sommarskog.se/share_data.html.
A stored procedure isn't useful, I need a scalar return value... This
function is called within a reportengine and there is no other
processhandling than straight forward doing a select.

> Sharing code between RBDMSs is very difficult, because there is a great
> variety in what they support. It may simplify programming and maintenance,
> to get the data to the client, using basic SQL which works in any RDBMS.
> This may be not be good for performance, as more data will be shoveled
> across the network.
There is a lot of functionality on the client side, even the discussed
functionality is implemented at the client. But this really is a
dead-end for performance tuning, so a server-based solution is
necessary.

> Then again, your complex and dynamic data model is apparently not designed
> for performance anyway. RBDMSs work from the idea that tables and columns
> are stable, and that your schema does not change dynamically. So one idea
> could be to have custom fields as rows rather than columns. This can lean
> to unwieldy self-join, but at least you will not need dynamic SQL.
It's simply not true, that a flexible and dynamic system can't have a
good performance. It's just more and harder work to implement. But the
maintainance and most of all the customers (and this software is very
very succesfull) are the best reason to do this work.

This just so far, back to the technical problem. Just one question
remaining, are these so called extended procedure only procedure or
are within the c-dll also functions like we need possible ? To be more
precise, is it possible to call a XP-Funtion like 'select
MyXpFunc('Test') from customer' ?|||Ralph (Ralph.Backes@.web.de) writes:
> Misunderstanding, we don't insist on syntax, we insist on
> functionality. The implementation is of course different on every
> RDBMS. But til now every RDBMS gives us a possible way to implement
> this stuff with more or less knowledge and doing. Hard to believe
> MS-SQL can't...

Well, MS-SQL can, but...

> A stored procedure isn't useful, I need a scalar return value... This
> function is called within a reportengine and there is no other
> processhandling than straight forward doing a select.

...you are insisting on a specific syntax.

You would have to invoke a batch first calls the stored procedures,
which puts the data in a variable or a temp table (have you looked at
http://www.sommarskog.se/share_data.html yet?), and then SELECTs the
data to the client. Presuming that you are looking for many rows
at a time, this is the only way to a scalable solution.

So that batch will look different than from what it does for MySQL,
but the syntax you wished to use included dbo.yourfunc(), and I don't
think you have the dbo bit on MySQL, so you are already prepared to
handle different syntax.

> This just so far, back to the technical problem. Just one question
> remaining, are these so called extended procedure only procedure or
> are within the c-dll also functions like we need possible ? To be more
> precise, is it possible to call a XP-Funtion like 'select
> MyXpFunc('Test') from customer' ?

No, there are no extended functions, but you can call extended stored
procedures from a user-defined function. So you could write a extended
stored procedure, which returns the value in an OUTPUT parameter, and
then the scalar UDF could return that function.

But: 1) An error like an access violation in an extended stored procedure
is no small accident - the entire SQL Server perishes.
2) You will get one more - and odd - component do deploy.
3) This is definitely not going to scale.

In short: don't go there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 21, 2012

Need Assistance with Dynamic Properties and FTP Task

Hello,

I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:

CDNSC.CDNSC.SC00015.04012007

The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.

My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.

What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?

Thank you for your help!

cdun2On Apr 4, 5:35 am, "cdun2" <ChrisDunnM...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:
>
CDNSC.CDNSC.SC00015.04012007
>
The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.
>
My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.
>
What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?
>
Thank you for your help!
>
cdun2


I wound up creating a script solution that was pretty straight
forward. I basically set up string variables using DATE to get the
data portion of the file name, concatenated that to the first part of
the filename, and set the whole string equal to the SourceFileName of
the FTP Task. It seems to work fine.

cdun2sql

Friday, March 9, 2012

Need advice on buiding a dynamic TSQL command to run against all DB's

Hi All,

I am putting together a series of TSQL statements to be used as part of a DB maintenance plan on SQL server 2005 on Windows Server 2003. I know that the statement itself works against a single database. The intention is to write one routine that will execute on all non-system DB's. Here are the system specs:

__

Microsoft SQL Server Management Studio 9.00.2047.00

Microsoft Analysis Services Client Tools 2005.090.2047.00

Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324

1447)

Microsoft MSXML 2.6 3.0 6.0

Microsoft Internet Explorer 7.0.5730.11

Microsoft .NET Framework 2.0.50727.42

Operating System 5.2.3790

__

So I've actually tried two methods. The first is without using sp_MSforeachdb

__


-- Declare variables

Declare @.dbname sysname,

@.cmd varchar(4096)

-- Select DB Names

Select @.dbname = min(name) from master.dbo.sysdatabases

-- Loop through each database.

WHILE @.dbname is not null -- <-- (NEED TO NOT INCLUDE SYSTEM DB'S HERE)

Begin

Select @.cmd = 'use ' + @.dbname +

'
-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1)


-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in

('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

'

End -- (NOT SURE IF THIS IS IN THE RIGHT PLACE)

-- Execute the command

Exec (@.cmd)

__

The second attempted method was trying to use sp_MSforeachdb. I modified the following to successfully loop a simple print command to ensure that the looping occurs:

__

declare @.cmd1 varchar(4096)

set @.cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

exec sp_MSforeachdb @.command1=@.cmd1

-- THIS WORKS

__

However after many attempts, I haven't been able to get the syntax right to run my

statements abonve using this method. Seems very unforgiving with spaces and apostrophes.

Any advice is much appreciated. Thank you in advance.

Rick B

Hi Rick,

Try something like this:

--THIS CREATES ONE TEMP TABLE WITH ALL NON SYSTEM DATABASES

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM from master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);


-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in ('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

I thinks this can do your trick Smile

Regards,

|||

One of the ways I use to test the syntax of dynamic sql is to print the SQL rather than run it. Then you can see the outputted SQL and see if its correct and if not, where you have gone wrong

hope this helps

|||

Hey Luis

Upon execution against Master, I'm receiving the following error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'DBNAME'.

After a few tweaks, the following is the current syntax that I am using based off of Luis's feedback. Can anyone ID additional syntax errors? Thanks in advance. Rick B.

__

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in (TableX,TableY,TableZ)
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DBs

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

__

|||

Replace this statement:

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

with this one

SELECT IDENTITY(INT, 1,1) AS ID, NAME AS DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

Hope it works Smile

Saturday, February 25, 2012

Need - help to create DLOOKUP with SSIS

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.
I find the Lookup component works best for this. Jamie wrote an article that compares lookup as well as other methods.
http://www.sqlis.com/default.aspx?311
Adrian