Best Query/Search Method

Hi,

I'm wondering about the following:

I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.
An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I have
reviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're found
inside some DLL.

I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.

So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?

Thanks in advance for your advise.

Stewart

Go into sql server management studio and open up a new query window, then set the query window to the the database in question.

Execute this query:

select 'union select ''[' + colu.table_schema + '].[' + colu.table_name + '].[' + colu.column_name + ']'' as "Schema.Table.Column" '
+ ',[' + colu.column_name + '] as "Value" '
+ ' from [' + colu.table_schema + '].[' + colu.table_name + '] '
+ ' where [' + colu.column_name + '] like ''ZZZ%'' '
from INFORMATION_SCHEMA.columns as colu
where colu.data_type in ('varchar','nvarchar','char','nchar','text','ntext')

Below the query will be the results, one sql statement per text-based column.

You can click, then right-click on the top-left button-looking box in the grid header and copy the text into the buffer.

Paste it into a new query window.

Delete the first "union" on the first line and execute the query.

It will return one row per column value per table that matches ZZZ%.

Depending upon the number of tables/columns in the database, and the number of rows in the table, you might need to split the results into multiple, smaller queries.

Enjoy!

|||

Hi David,

Thanks very much for the assistance. It worked perfectly!

Regards,
Stewart

|||

The views in the master database are very powerful. Try the information_schema views first, and switch to the sys... views if the information_schema views don't have what you need.

Glad it helped!

sql

Best Practices of cube release process

Have you guys come across best way to move/release cube changes from test to production server. Any way to automate this process.

- Ashok

You're probably going to want to give the Deployment Wizard a try:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1485584&SiteID=1

|||The Deployment Wizard is probably a good option. It will handle things like not overwriting datasources, roles and partitions which can be handy if these vary between your environments (which they often do). This tool can also be run from the command line which allows for a degree of automation.

Best place for company info?

Say I have 4 applications which each use their own database. There are two
SQL Servers involved and the databases are split across servers like this:
SQLServer1
- BigApp1
- SmallApp1
SQLServer2
- BigApp2
- SmallApp2
The above helps balance load. If I want to store information about the
company developing these applications (such as company name, address, phone
number, etc), where should I put it? In a seperate database or in one of
the application databases?
This information will be idenpendent of any application but all applications
will reference it.
Thanks,
BrettHi
It is not clear if this is one company per application or one company for
all applications. I would add this information to each database if the
information is application specific, you could create a partitioned view if
you wish to view all values. If this is a common information, then I would
consider adding it to it's own database along with any other common/shared
information.
John
"Brett" <no@.spam.net> wrote in message
news:%23BLwNcXLFHA.724@.TK2MSFTNGP14.phx.gbl...
> Say I have 4 applications which each use their own database. There are
> two SQL Servers involved and the databases are split across servers like
> this:
> SQLServer1
> - BigApp1
> - SmallApp1
> SQLServer2
> - BigApp2
> - SmallApp2
> The above helps balance load. If I want to store information about the
> company developing these applications (such as company name, address,
> phone number, etc), where should I put it? In a seperate database or in
> one of the application databases?
> This information will be idenpendent of any application but all
> applications will reference it.
> Thanks,
> Brett
>|||One company owns all four applications. It is the parent of these four
apps. I thought about putting the company info in its own database but that
creates additional connections and use of resources right?
Each of the four databases has its own website. The websites run CFMX,
which creates a connection to its respective database. If the company info
is in its on database in either SQLServer1 or SQLServer2, would that create
more connections?
If not, which server should that database go on - SQLServer1 or SQLServer2,
with respect to load balancing since all four websites will be accessing the
company database (displaying company info on certain pages of the website
for example). It can eleviate some of the load problem by storing the
company info in an application variable.
Thanks,
Brett
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi
> It is not clear if this is one company per application or one company for
> all applications. I would add this information to each database if the
> information is application specific, you could create a partitioned view
> if you wish to view all values. If this is a common information, then I
> would consider adding it to it's own database along with any other
> common/shared information.
> John
>
> "Brett" <no@.spam.net> wrote in message
> news:%23BLwNcXLFHA.724@.TK2MSFTNGP14.phx.gbl...
>|||How much info are you talking about? And how often is it updated? By
putting the same duplicate info in separate databases, you make updates to
that info a little trickier. Are you using Replication to keep the
databases in sync? If so, then it would seem you could put it on both and
that would handle most of the update issues. Of course, as you point out,
if there's only a couple of lines of information, it might make more sense
to put it in application variables and skip the DB overhead altogether...
"Brett" <no@.spam.net> wrote in message
news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> One company owns all four applications. It is the parent of these four
> apps. I thought about putting the company info in its own database but
> that creates additional connections and use of resources right?
> Each of the four databases has its own website. The websites run CFMX,
> which creates a connection to its respective database. If the company
> info is in its on database in either SQLServer1 or SQLServer2, would that
> create more connections?
> If not, which server should that database go on - SQLServer1 or
> SQLServer2, with respect to load balancing since all four websites will be
> accessing the company database (displaying company info on certain pages
> of the website for example). It can eleviate some of the load problem by
> storing the company info in an application variable.
> Thanks,
> Brett
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The info won't be much but may grow. It will be items such as:
- Company name
- address
- tel/fax numbers
- maybe company employees and their details
- may a map URL
and what ever else.
The info won't be updated often. Maybe 5 times/mo or so. Logically it is
completely independent of any application or database. It will need to be
in a database. Just, do I want to put it inside of an existing application
database or its own?
The application variables for each app will still need to get the info from
somewhere. I don't want them hard coded. That's why I'd like the database
setup. One change, one place.
Any application will hit the company info DB maybe once/w to get updates.
Little overhead actually.
Suggestions?
Thanks,
Brett
"Michael C#" <xyz@.abcdef.com> wrote in message
news:zEk%d.1527$BX1.327@.fe08.lga...
> How much info are you talking about? And how often is it updated? By
> putting the same duplicate info in separate databases, you make updates to
> that info a little trickier. Are you using Replication to keep the
> databases in sync? If so, then it would seem you could put it on both and
> that would handle most of the update issues. Of course, as you point out,
> if there's only a couple of lines of information, it might make more sense
> to put it in application variables and skip the DB overhead altogether...
> "Brett" <no@.spam.net> wrote in message
> news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
>|||Sounds like you're leaning toward putting it in its own database. That
being the case, unless it's specifically tied to the data stored in one of
your current databases, it makes sense to create a separate database for it.
And if you decide you want to access the data from within your other
database, just fully qualify the table names in your queries. It also
sounds like the information (company employees/details, etc.) could be
subject to a some growth/changes.
"Brett" <no@.spam.net> wrote in message
news:OcgM$zYLFHA.244@.TK2MSFTNGP12.phx.gbl...
> The info won't be much but may grow. It will be items such as:
> - Company name
> - address
> - tel/fax numbers
> - maybe company employees and their details
> - may a map URL
> and what ever else.
> The info won't be updated often. Maybe 5 times/mo or so. Logically it is
> completely independent of any application or database. It will need to be
> in a database. Just, do I want to put it inside of an existing
> application database or its own?
> The application variables for each app will still need to get the info
> from somewhere. I don't want them hard coded. That's why I'd like the
> database setup. One change, one place.
> Any application will hit the company info DB maybe once/w to get
> updates. Little overhead actually.
> Suggestions?
> Thanks,
> Brett
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:zEk%d.1527$BX1.327@.fe08.lga...
>|||Hi
"Brett" <no@.spam.net> wrote in message
news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> One company owns all four applications. It is the parent of these four
> apps. I thought about putting the company info in its own database but
> that creates additional connections and use of resources right?
You can always create a view.
> Each of the four databases has its own website. The websites run CFMX,
> which creates a connection to its respective database. If the company
> info is in its on database in either SQLServer1 or SQLServer2, would that
> create more connections?
> If not, which server should that database go on - SQLServer1 or
> SQLServer2, with respect to load balancing since all four websites will be
> accessing the company database (displaying company info on certain pages
> of the website for example). It can eleviate some of the load problem by
> storing the company info in an application variable.
This would depend on how/when it is updated and how/how often it is used and
if you always have to display the current information that is in the table.
You may want to consider using replication as suggested by Michael.
John
> Thanks,
> Brett
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||Yes - this all true.
If the company DB is on a server seperate from a DB that wish to call data
from the company DB, how do I fully qualify the company DB table path in say
a SELECT?
Thanks,
Brett
"Michael C#" <xyz@.abcdef.com> wrote in message
news:0ml%d.11715$xQ5.2876@.fe10.lga...
> Sounds like you're leaning toward putting it in its own database. That
> being the case, unless it's specifically tied to the data stored in one of
> your current databases, it makes sense to create a separate database for
> it. And if you decide you want to access the data from within your other
> database, just fully qualify the table names in your queries. It also
> sounds like the information (company employees/details, etc.) could be
> subject to a some growth/changes.
> "Brett" <no@.spam.net> wrote in message
> news:OcgM$zYLFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:Ol5aFBZLFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> "Brett" <no@.spam.net> wrote in message
> news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> You can always create a view.
> This would depend on how/when it is updated and how/how often it is used
> and if you always have to display the current information that is in the
> table. You may want to consider using replication as suggested by Michael.
Wouldn't replication cause more overhead than the once/w query from each
application?
Thanks,
Brett|||On a separate server? You're jumping around a little bit here. To answer
your question, look up sp_addlinkedserver in BOL.
"Brett" <no@.spam.net> wrote in message
news:O7sbMRZLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Yes - this all true.
> If the company DB is on a server seperate from a DB that wish to call data
> from the company DB, how do I fully qualify the company DB table path in
> say a SELECT?
> Thanks,
> Brett
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:0ml%d.11715$xQ5.2876@.fe10.lga...
>

Best MSSQL management tools

Hi everyone.

I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.

Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)

Cheers~

gmefmax:angel:You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.

Example:
osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'|||You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).

It's not impossible, but I admit that I'm not up to the challenge right now.

Regards,

hmscott|||I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?|||I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.

basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.|||Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?|||I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?

Then use osql.exe instead.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.

I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?

I keep meaning to get into powershell, but haven't yet.

ps combined with SMO would be a nice combo:

http://www.google.com/search?q=smo+powershell|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.

I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.

Before Insert

Hi,

Im migrating an interbase database across to sql server 2005 the problem I have is that it uses before triggers which allow data to be modified before the table is populated. So if a large value was inserted into a smaller data type it will scale the value down.

The idea is to not change the front end if at all possible is there a way that I can mimick this behaviour.

I've tried triggers but the insert fails before it gets to the trigger the same with using instead of triggers as I believe the insert table is identical to the base table and so this fails.

I've looked at rules but this just allows me to restrict the vales going in?.

Any ideas would be appreciated.

Many thanks.

Code Snippet

-- Rename your base table
Create table RenamedTable (
Srno int,
descr varchar(30)
)

-- Create a view with same name as your base table
alter view BaseTable
as
select
Srno, Descr = convert(varchar(50), Descr )
from
RenamedTable -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
ALTER TRIGGER trI_BaseTable on BaseTable
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO RenamedTable
SELECT Srno, left(Descr,30) FROM inserted
END
GO

insert into BaseTable select * from BaseTable
insert into BaseTable values (1, 'One')
insert into BaseTable values (2, 'Two123123123123123123123123123123123123123123123')
select * from RenamedTable.

|||Refer to Books Online, Topic: 'INSTEAD OF TRIGGERS'|||

SunnyD,

I'm sure there's more to the picture than meets the eye, but I have a couple of questions.

1. If a value is larger than the datatype of the field, how are you scaling it down?

2. When you scale it down, are you losing value or simply trimming the fat?

The reason I ask is that the frequency of scaling down may justify the means to utlimately increase the datatype size for the field.

Triggers are often a reasonable solution but we have to be careful that we don't abuse the intent.

Also, the filtering can be accomplished at the source instead of waiting to trim at the database level. I realize you don't want to change the front end, but this is where validation should occur. This helps eliminate the GIGO (garbage in, garbage out) potential.

In closing, although databases do have the functionality to clean house, it doesn't mean that we should neglect the programming practice of using validation on the front end. Remember, databases were designed as a storage facility, not a programming environment.

Just my twist on it,

Adamus

|||

Totally agree with everything said and given the choice I would rather change the front-end however that's not an option at the moment and my scope is to get the back-end working in an identical fashion to the way it works now. I've tried instead of triggers but again this does not appear to work see example below:

CREATETABLE BaseTable

(OrderKey intPRIMARYKEYIDENTITY(1,1),

Quantity smallintNOTNULL)

GO

--Create a view that contains all columns from the base table.

CREATEVIEW InsteadView

AS

SELECT OrderKey, Quantity

FROM BaseTable

GO

--Create an INSTEAD OF INSERT trigger on the view.

CREATETRIGGER InsteadTrigger on InsteadView

INSTEADOFINSERT

AS

BEGIN

--Build an INSERT statement

INSERTINTO BaseTable(Quantity)

SELECTCASEWHEN Quantity > 99999 THEN Quantity/10.0 ELSE Quantity END

FROM inserted

END

GO

INSERTINTO InsteadView (Quantity)SELECT 9999 --Works ok

INSERTINTO InsteadView (Quantity)SELECT 99999

Msg 220,Level 16,State 1, Line 1

Arithmetic overflow error for data typesmallint,value= 99999.

The statement has been terminated.

Any other thought guys?

|||

smallint is defined as a value between -32768 and 32767. So, 99999 definitely causes overflow.

Either change your datatype or change your case/when to trap the correct range.

e.g.

SELECT CASE WHEN Quantity > 32767 THEN Quantity/10.0 ELSE Quantity END

|||

If data type not changed changing case will not work! Im still getting overflow as inserted will be based on the base table and therefore will not hold a value greater than 32,767. i.e. Quantity column in inserted will not store a value greater than 32767.

|||

The VIEW has the SAME datatypes as the underlaying table.

You are attempting to INSERT a value larger than 32767 into the VIEW and it will FAIL since the datatype is smallint.

The only way that you will be able to accomplish this task is to create another table with a larger datatype, and use a trigger on that table to move the data to your original table.

In my opinion, a very bad kludge... (Change the original table's datatype and stop perverting the data.)

|||I believe you can create a view with different data type (length)

Create table BaseTable (Srno int, varchar(30))
go

Create view my View as
select
Srno, Descr = convert(varchar(50), Descr )
from
BaseTable
go

Now you can create a instead of trigger on this view...

|||

Bushan,

I'm not too sure that will work for an INSERT. Have you tried it and been successful?

|||

Code Snippet

-- Rename your base table
Create table RenamedTable (
Srno int,
descr varchar(30)
)

-- Create a view with same name as your base table
alter view BaseTable
as
select
Srno, Descr = convert(varchar(50), Descr )
from
RenamedTable -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER trI_BaseTable on BaseTable
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO RenamedTable
SELECT Srno, left(Descr,30) FROM inserted
END
GO

insert into BaseTable select * from BaseTable
insert into BaseTable values (1, 'One')
insert into BaseTable values (2, 'Two123123123123123123123123123123123123123123123')
select * from RenamedTable.

|||

I should have been a bit more explicit. The OP's table DDL indicates the presence of an IDENTITY field.

Because of the IDENTITY field, the code suggestion you provided doesn't seem to work as presented to solve the OP's issue.

I'm trying to understand if you have created a 'work-around' for handling the absence of the IDENTITY value in inserted. Even setting IDENTITY_INSERT ON in the Trigger doesn't seem to allow a way to get around the absence of the IDENTITY value in inserted. But I'm hoping you have found a way...

...Inquiring minds want to know...

|||

Code Snippet

Here you go...

-- Rename your base table
Create table NewDepartment (
DeptID int identity(1,1),
Dname varchar(6),
Location varchar(20)
)

-- Create a view with same name as your base table
create view Department
as
select
DeptID,
Dname = convert(varchar(50), Dname ),
Location
from
NewDepartment -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER trI_Department on Department
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO NewDepartment (Dname, Location)
SELECT
left(Dname,6), Location
FROM
inserted
END
GO

insert into Department values (1,'Sales', 'California')
insert into Department values (2,'Marketing', 'NewYork') -- 'ing' will be truncated from Marketing

|||

Here is a kludge that changes the underline datatype of the view to allow large value.

--Create a view that contains all columns from the base table.

CREATEVIEW InsteadView

AS

SELECT OrderKey, cast(Quantity as bigint) [Quantity]

FROM BaseTable

GO

--Create an INSTEAD OF INSERT trigger on the view.

CREATETRIGGER InsteadTrigger on InsteadView

INSTEADOFINSERT

AS

BEGIN

--Build an INSERT statement

INSERTINTO BaseTable(Quantity)

SELECTCASEWHEN Quantity > 32676 THEN Quantity/10.0 ELSE Quantity END

FROM inserted

END

GO

|||

Thanks for taking the time and effort.

It is so much more helpful when we provide the OP a response with a suggested solution that actually solves his/her problem.