hide system tables: include

Showing posts with label include. Show all posts

Friday, March 23, 2012

How to include variable in CURSOR SQL Filter clause?

After trying every way I could come up with I can't get a filter clause
to work with a passed variable ...
I have a cursor that pulls a filter string from a table (works OK),
then I want to use that filter in a second cursor, but can't get the
syntax ...

@.bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetch
from the second cursor. Here is the cursor declaration:

DECLARE curFiles CURSOR FOR
SELECT FileName, FileDate
FROM DataFile
WHERE (((Active)=1) AND ((FileName) LIKE '@.bak_filter'))
ORDER BY FileDate DESC

What do I need to do to get it to use the string contained in
@.bak_filter?

Thanks in advance, JimOn 10 Jan 2005 14:53:35 -0800, jfro wrote:

>After trying every way I could come up with I can't get a filter clause
>to work with a passed variable ...
>I have a cursor that pulls a filter string from a table (works OK),
>then I want to use that filter in a second cursor, but can't get the
>syntax ...
>@.bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetch
>from the second cursor. Here is the cursor declaration:
>DECLARE curFiles CURSOR FOR
>SELECT FileName, FileDate
>FROM DataFile
>WHERE (((Active)=1) AND ((FileName) LIKE '@.bak_filter'))
>ORDER BY FileDate DESC
>What do I need to do to get it to use the string contained in
>@.bak_filter?

Hi Jim,

Remove the quotes: LIKE @.bak_filter

However, I hope that you're aware that cursor-based processing is usually
not needed? Set-based solutions are almost always better. Of course, if
you need to perform an intrinsically row-based operation (like sending
mail or performing some OS command with xp_cmdshell), this doesn't apply.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

How to include total in query?

I'll admit that I'm a new MSSQL query writer; however, I'm learning.
I'm curious how I could get a GRAND TOTAL to be included directly under
the [Total] column. Any information would be great!
Thank you!
SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
FROM Subscribers
JOIN Sites on Sites.Id = Subscribers.SiteId
WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
DateOptedOut IS NULL
GROUP BY Sites.Name
ORDER BY Sites.NameTry something like this:
SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
FROM Subscribers
JOIN Sites on Sites.Id = Subscribers.SiteId
WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
DateOptedOut IS NULL
GROUP BY Sites.Name
ORDER BY Sites.Name
COMPUTE SUM(COUNT(Subscribers.Id))|||I really appreciate your help with this. However, I get the following
error:
TDS Protocol error: Unsupported TDS token: 0x88
Thanks again!|||You might also check out the WITH ROLLUP option on GROUP BY
"JeffB" wrote:

> Try something like this:
> SELECT Sites.Name [Signups Before 4pm], COUNT(Subscribers.Id) [Total]
> FROM Subscribers
> JOIN Sites on Sites.Id = Subscribers.SiteId
> WHERE Subscribers.DateEntered BETWEEN @.beforestart AND @.beforeend AND
> DateOptedOut IS NULL
> GROUP BY Sites.Name
> ORDER BY Sites.Name
> COMPUTE SUM(COUNT(Subscribers.Id))
>

how to include the nulls?

Hi, I have the following query stored:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)

Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...

As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.

The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.

Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.

I'd get

Prod | Qty | Week

1 23 1

1 12 2

2 10 3

3 22 1

3 15 2

3 12 3

3 4 4

Although this looks fine - what I actually need is:

1 23 1

1 12 2

1 0 3

2 0 1

2 0 2

2 10 3

2 0 4

3 22 1

3 15 2

3 12 3

3 4 4

Does that make sense?

Any ideas on how to do this?

Yes, sure that makes sense, but therefore yopu will have to join your "data" table either with a calendar table, or with a subquery returning the weeks that are present for all products to appear in the resultset (cross joining)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok the calendar tbl sounds the correct option - is something already in my db, or do i have to create it?

if so, any examples of how to do this?

|||Hi,

look here: http://www.aspfaq.com/2519

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok I'm really a novice with all that..

I created it based on just pasting the example they had.. but that's not what I wanted..

I guess what I want is a calendar with the following:

Year, Week and that's it...

that one in that example had a load of extra data which I couldn't make head nor tail of....

|||

ok nearly there.. got my calendar as I want it, but now I've hit a bit of a problem with my join.

If I join my orders tbl by orderdate with the Dt column in my calendar.. then it doesn't return any retults.. here we are:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek,
dbo.Calendar.W AS SalesWeek, dbo.Calendar.Y AS SalesYear
FROM dbo.Calendar LEFT OUTER JOIN
dbo.Orders_Retail ON dbo.Calendar.dt = dbo.Orders_Retail.OrderDate INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(dbo.Calendar.D, dbo.Calendar.dt, @.StartDate) <= 0) AND (DATEDIFF(dbo.Calendar.D,
dbo.Calendar.dt, @.EndDate) >= 0)
GROUP BY dbo.Calendar.Y, dbo.Calendar.W, dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, dbo.Calendar.Y, dbo.Calendar.W

Any ideas?

|||

ok I've narrowed it down to the fact that it doesn't like the join.

The one that joins the orderdate in my orders tbl with the dt field in my calendar tbl..

Even though both are datetime fields, both the same.. the only difference I can see between the two in terms of the values they have is that the order date usually has the date and then time, whereas the dt field just has the date...

therefore I was thinking the:

dbo.Calendar ON dbo.Orders_Retail.OrderDate = dbo.Calendar.dt

needs to be changed so that the orderdate vlaue removes it's time from it perhaps?

Any ideas?

James

||||||

Hi!,

I don't think I mad myself clear - I'm not getting an errors -I'm just not getting any results.

|||

anyone got any ideas?

I know exactly what the issue is....

in the calendar the date is held as 1/1/2003.. and up to a point the dates where held in the db like this also, however after a certain date, the time was also held so the date looks like 1/1/2003 00:00:00

This the results that aren't returned.

As I'm doing my JOIN on the dt held in the calendar tbl and then dt in the orders tbl.. as the times are held in the orders tbl also, this means that the join won't work as the calendar tbl will not hold the EXACT date and time held in the orders tbl..

does that make sense?

How do I fix that?

|||

Could you please post you table definitions to get a clear idea on how your tables look like. Seems like the datetime formats in the two columns are different. Schema definition would help in this case.

HTH,

~riyaz~

|||

ok I'm getting somewhere with this now.. however I have another problem.. here's the current query

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, SUM(dbo.OrderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.Orders_Retail INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID INNER JOIN
dbo.Calendar ON CONVERT(datetime, CONVERT(Nvarchar, dbo.Orders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(Nvarchar,
dbo.Calendar.dt, 102), 102) RIGHT OUTER JOIN
dbo.Products ON dbo.OrderDetails_Retail.ProductID = dbo.Products.ProductID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> '
Deleted ') AND
(dbo.Orders_Retail.PayStatus <> ' Pending ') AND (dbo.Orders_Retail.OrderStatus <> ' Refunded ') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate,
@.StartDate) <= 0) AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.ProductBrand
ORDER BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductBrand, dbo.Products.ProductName

What this does is first get the products tbl, join that the orders_detail tbl, joins that the orders tbl which is finally joined to the calendar tbl.

What I'm finding is although it's working as intended, it's still not returning the product that haven't been bought..

I've narrowed this down to the following:

It I just to a Outer join on the products tbl to the order details tbl with no WHERE queries.. this returned what I want,, however as soon as I add the WHERE clause, it loses all the products and only shows the ones that have been purchased.

I would have thoughy my Outer join on the products tbl would get round this problem?

How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

How to include SSIS into install file?

I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer

Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.

What will I need to do now to use old DTS and new SSIS packages on these clients?

Can someone to help me?|||

See my reply to your post on the SSC forum

-Jamie

|||Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).|||

Michael Entin SSIS wrote:
Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).

So, Do I have to install SSIS runtime on each 500-1000 single computers of our company's clients?

And what type of license do we need for this?

Or maybe I don't understand something how it works. What we need that every user of our clients will be able to run SSIS packages to copy/backup data from Server on his own computer or remote(Server) computer itself. It depends on where this user want to see this copied data. Maybe for this puporses there is some feather of SSIS that I still don't know. Another thing - type of DBes may be diffrent on Server and on client computers.

|||

In an ideal world you would have a single package. You would loop 500 times (or however many you need) changing the connection string each time appropriately.

If you have a different RDBMS on each source system though this will probably not be possible because the metadata will most likely change. If every one of the 500 clients has the same schema on the same RDBMS then it is theoretically possible. And certainly alot easier than installing SSIS on 500 machines (and having to do it all over again when machines are upgraded).

Might I suggest you change your architecture to a proper client/server one? Have a centralised server that contains all the data and the clients connect to get their own data.

-Jamie

|||Company clients are real estate agencies. Each agency has many agents in the field who need copied DB on their computers which most of the time disconnected from the Server. So, they need the smart client on their computers. After they update data about properties they will copy updated data from their client to the Server using DTS. Usually local computer has Access DB, and MySql or MSDE (now we want instead it SQL Express) on Server. So, how exactly do we have to change architecture? More easier I think still to work with DTS and wait for distributable SSIS runtime or something like this.|||

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

-Jamie

|||

Jamie Thomson wrote:

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

Do you meen that each client will send request to Server to start pulling or pushing data on this client computer DB using SSIS or DTS packages which are located on Server itself?

May be it will be effective solution. If we don't find easier way without changing architecture to use SSIS we'll use this solution.

Thank you.

|||

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

-Jamie

|||

Jamie Thomson wrote:

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

Yes, I have already read about it. I hope we'll find something else, not so complicated as changing architecture.

But anyway thank you for your answers.

sql

Wednesday, March 21, 2012

How to include SSIS into install file?

I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer

Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.

What will I need to do now to use old DTS and new SSIS packages on these clients?

Can someone to help me?|||

See my reply to your post on the SSC forum

-Jamie

|||Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).|||

Michael Entin SSIS wrote:
Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).

So, Do I have to install SSIS runtime on each 500-1000 single computers of our company's clients?

And what type of license do we need for this?

|||

In an ideal world you would have a single package. You would loop 500 times (or however many you need) changing the connection string each time appropriately.

Might I suggest you change your architecture to a proper client/server one? Have a centralised server that contains all the data and the clients connect to get their own data.

-Jamie

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

-Jamie

|||

Jamie Thomson wrote:

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

Do you meen that each client will send request to Server to start pulling or pushing data on this client computer DB using SSIS or DTS packages which are located on Server itself?

May be it will be effective solution. If we don't find easier way without changing architecture to use SSIS we'll use this solution.

Thank you.

|||

Yep. That's exactly what I mean.

-Jamie

|||

Jamie Thomson wrote:

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

Yes, I have already read about it. I hope we'll find something else, not so complicated as changing architecture.

But anyway thank you for your answers.

How to include SQL Express in Installation Package and change some option?

Hi, there,
I'm working on a windows application project which uses SQL Express as
database.
I found I can include SQL Server 2005 Express Edition in the installation
project as Prerequests,
that's cool.
But I need do more things, I want to change the "Authentication Mode" from
"Windows Authentication mode"
(which is default) to "SQL Server Authentication mode" and assign a "default
password" to user "sa", and
I want to attach a database file which will be include on the installation
disc.
So what shall I do? Write some script in the installation project?
Does SQL Express provide any program interface to do that?
It seems there is no help content for this in the MSDN and I did some search
job and got nothing.
Help needed. Thank you.
Best wishes!
I could write a chapter on this but here it is in a nutshell.
You need to use the Unattended Install tenplate.ini file along with the
LOGName and Datasource.xml.
Here is a fantastic article.
http://www.devx.com/dbzone/Article/31648
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi, there,
> I'm working on a windows application project which uses SQL Express as
> database.
> I found I can include SQL Server 2005 Express Edition in the installation
> project as Prerequests,
> that's cool.
> But I need do more things, I want to change the "Authentication Mode" from
> "Windows Authentication mode"
> (which is default) to "SQL Server Authentication mode" and assign a
> "default password" to user "sa", and
> I want to attach a database file which will be include on the installation
> disc.
> So what shall I do? Write some script in the installation project?
> Does SQL Express provide any program interface to do that?
> It seems there is no help content for this in the MSDN and I did some
> search job and got nothing.
> Help needed. Thank you.
> Best wishes!
>
|||Hmm.....
Thank you.
I have read it, and there is really something interesting.
But there is another thing I can not do with this article.
I want to open Name Pipe and TCP connection of the SQL EXPRESS during the
setup,
but there is no such setting in the template.ini file.
Any other help?
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uFytSE%235GHA.3592@.TK2MSFTNGP05.phx.gbl...
>I could write a chapter on this but here it is in a nutshell.
> You need to use the Unattended Install tenplate.ini file along with the
> LOGName and Datasource.xml.
> Here is a fantastic article.
> http://www.devx.com/dbzone/Article/31648
>
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
> news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||Philip,
All you need to do is edit the package.xml file - you can specify the
command line parameters you want to use for installing SQLExpress. The file
is located in
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
The default setup is
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1'
You might want to change it to something like
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
DISABLENETWORKPROTOCOLS=0'
See the readme file for SQLExpress for the command line options. That will
get SQLExpress installed the way you want it. Use a custom action dll to
install your db, attach it, set up the user accounts you want, etc.
Russ Stevens
|||Ah haaaaaa!
This is right the solution I am looking for!
Powerful!
Thank you very much!
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:OkMqQxy8GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Philip,
> All you need to do is edit the package.xml file - you can specify the
> command line parameters you want to use for installing SQLExpress. The
> file is located in
> C:\Program Files\Microsoft Visual Studio
> 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
> The default setup is
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1'
> You might want to change it to something like
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
> DISABLENETWORKPROTOCOLS=0'
> See the readme file for SQLExpress for the command line options. That will
> get SQLExpress installed the way you want it. Use a custom action dll to
> install your db, attach it, set up the user accounts you want, etc.
> Russ Stevens
>

How to include parameter in WHERE statment of a stored procedure?

I have a stored procedure like:

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000)
AS
BEGIN
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable)

END

My problem is, the locationId will be null or length equals 0 some time, how can I make the statement "table.LocationId in (SELECT id FROM tempTable)" included into the WHERE condition dynamically depends on locationId is null or length equals 0?

Is that possible to do it at Database side? Or I have to do it at code side?

Thank you.

Perhaps something like this:

SELECT t.id

FROM Table t

WHERE ( t.id = @.Id
AND ( t.LocationID in (SELECT ID FROM TempTable)

OR t.LocationID IS NULL

)

)
END

|||

Looks like you want dynamic search capabilities based on the parameters passed. You can take a look at the link below for various options:

http://www.sommarskog.se/dyn-search.html

You can do below in your case:

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and @.LocationId is not null and len(@.LocationId) > 0

UNION ALL

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

Query optimizer will use startup expression filter to evaluate the expression containing @.LocationId at run-time. This will result in only one of the UNION ALL branches being executed. You could use this approach. You can also put this query in a inline TVF and use it.

|||

Thank you for your advise.

For your code, my understanding is you list both situations, either parameter has value or not has value, run both and combine the results. Is that correct?

My question is, if @.LocationId is not null, the condition "(@.LocationId is null or len(@.LocationId) = 0)" will be false, and it will AND with condition "table.LocationId in (SELECT id FROM tempTable)", the result will also be false right? Finally, it will AND with "table.id = @.Id", which will be false again. That will make the result set of

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

be null?

Cause I have about 8-9 parameters need to be passed in, follow your code, I guess the stored procedure will be very long, right?

The link you gave provides a coding way to do the if statement to build the query at database.

Thank you.

|||Try the following:

Code Snippet

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000),
@.Parameter2 INT,
@.Parameter3 NUMERIC(18, 2)
AS
SELECT
table.id
FROM
table
WHERE
table.id = @.Id AND
(table.LocationId in (SELECT id FROM tempTable) OR LEN(ISNULL(@.LocationId, '')) = 0) AND
(table.Value2 = @.Parameter2 OR @.Parameter2 IS NULL) AND
(table.Value3 = @.Parameter3 OR @.Parameter3 IS NULL) etc...

If you're checking VARCHARs, NVARCHARs, CHARs, to see if they're either NULL or have length = 0, put an ISNULL around the parameter and compare the length to 0.

For every other parameter you want to check, compare it to the table value and OR it with a check to see if it's null.

Each of those OR'd NULL checks needs to be in brackets with AND clauses otherwise you'll get strange results.

How to include multiple membes in an expression?

Hello, I'm wondering how can I include multiple members from a dimension in a Calculated Measure Expression?

Good (single member) ([ActionGroup].[ActionTag].&[147300], [Measures].[Unique Visitors])

#Value Error (multiple members)

([ActionGroup].[ActionTag].&[147300].&[107139], [Measures].[Unique Visitors])

({[ActionGroup].[ActionTag].&[147300], [ActionGroup].[ActionTag].&[107139]}, [Measures].[Unique Visitors])

Any pointer is appreciated

-Lawrence

Is this what you are looking for ?

Aggregate({[ActionGroup].[ActionTag].&[147300], [ActionGroup].[ActionTag].&[107139]} , [Measures].[Unique Visitors])

How to include MSDE with Setup of custom applications

I am using VS .NET 2003 Bootstrapper Plug-In to create an
installer that redistribute the .NET Framework 1.1 and
MDAC 2.8 with my application.
How can I include MSDE and provide the switches in order
to install MSDE on clients machine as well?
Thanks
Hi,
Unfortunately, all the auto-install options currently have "issues". I'd
strongly suggest you:
1. Configure the setup.ini file to have your default settings.
2. Get the client to run the setup.exe program to install the MSDE.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"pankaj banga" <pankaj@.discussions.microsoft.com> wrote in message
news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
>I am using VS .NET 2003 Bootstrapper Plug-In to create an
> installer that redistribute the .NET Framework 1.1 and
> MDAC 2.8 with my application.
> How can I include MSDE and provide the switches in order
> to install MSDE on clients machine as well?
> Thanks
|||Thanks HTH,
Then how would I create my tables, stored procs and load
client specific data into tables. Actually I am new to
all this. I read about msde bootstrap installer, are
there issues with it as well?

>--Original Message--
>Hi,
>Unfortunately, all the auto-install options currently
have "issues". I'd
>strongly suggest you:
>1. Configure the setup.ini file to have your default
settings.
>2. Get the client to run the setup.exe program to
install the MSDE.
>HTH,
>--
>Greg Low [MVP]
>MSDE Manager SQL Tools
>www.whitebearconsulting.com
>"pankaj banga" <pankaj@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
an[vbcol=seagreen]
order
>
>.
>
|||hi,
"pankaj banga" <pankaj@.discussions.microsoft.com> ha scritto nel
messaggio news:230901c4a950$1bb0dcb0$a301280a@.phx.gbl
> Thanks HTH,
> Then how would I create my tables, stored procs and load
> client specific data into tables. Actually I am new to
> all this. I read about msde bootstrap installer, are
> there issues with it as well?
>
Microsoft provides a deployment toolkit, in release candidate at the current
time, you can dowload from
http://www.microsoft.com/downloads/d...displaylang=en
I did not installed it, but I think some database deployment feature are
present... and some drawbacks of this toolkit have been posted here... don't
know the current state and/or the final release...
personally I do deploy apps with a companion tool which will read and
execute DDL scripts as long as INSERT INTO scipts, BCP and so on.. other
ways are backup/restore and sp_attach_db
I already discused these 3 methods of mine in http://tinyurl.com/6ux7p and
http://tinyurl.com/4x8pv ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How to include MSDE installation in my apps installation package

Hi all,
Anybody knows how to include MSDE in my apps installation package? I've
downloaded MSDE Deployment Package from
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
d98-bb0b-2c9d6414071f&DisplayLang=en.
but unfortunatelly:
1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
Edition, and;
2) it's a beta product, and Microsoft doesn't approve its use in the
production environment nor use it to deploy any applications.
I'm also considering alternatives to use InnoSetup. Will the solution be
easier if I use Inno?
Looking forward to hearing from all of you!
TIA,
Willianto
Try going to the tools menu in Installshield and go to "Check for updates."
I don't have limited edition of Installshield but this should be there. In
the updates you will find MSDE in there. I believe this will then show up
in your redistributables area and can then add it to your install.
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Anybody knows how to include MSDE in my apps installation package? I've
> downloaded MSDE Deployment Package from
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> d98-bb0b-2c9d6414071f&DisplayLang=en.
> but unfortunatelly:
> 1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
> Edition, and;
> 2) it's a beta product, and Microsoft doesn't approve its use in the
> production environment nor use it to deploy any applications.
> I'm also considering alternatives to use InnoSetup. Will the solution be
> easier if I use Inno?
> Looking forward to hearing from all of you!
> TIA,
> Willianto
>
|||Altman:

> Try going to the tools menu in Installshield and go to "Check for
updates."
Hmmm... is there anyway to still have the MSDE in the checklist without
having to update the ISE?
What about InnoSetup?
Regards,
Willianto
"Altman" <NotGiven@.SickOfSpam.com> wrote in message
news:ut8I9WDNEHA.3016@.tk2msftngp13.phx.gbl...
> I don't have limited edition of Installshield but this should be
there. In
> the updates you will find MSDE in there. I believe this will then
show up[vbcol=seagreen]
> in your redistributables area and can then add it to your install.
> "Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
> news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
I've[vbcol=seagreen]
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4[vbcol=seagreen]
solution be
>
|||I don't think it comes with it, it is a free update to installshield, I
believe it is just a merge module. Otherwise check out this site
http://msdn.microsoft.com/library/de...eddingmsde.asp
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:uwlMliENEHA.3476@.TK2MSFTNGP09.phx.gbl...
> Altman:
> updates."
> Hmmm... is there anyway to still have the MSDE in the checklist without
> having to update the ISE?
> What about InnoSetup?
> Regards,
> Willianto
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ut8I9WDNEHA.3016@.tk2msftngp13.phx.gbl...
> there. In
> show up
> I've
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> solution be
>
|||Hi Altman:
Thanks for still being with me

> I don't think it comes with it, it is a free update to installshield,
I
> believe it is just a merge module.
I've found the page where InstallShield put the msm:
http://support.installshield.com/kb/...icleid=Q106295
But it looks like, the msms are for InstallShield Developer 7.0SP3,
8.xx, and DevStudio 9.0. Since I connect to the internet with DUN, I
cannot afford to 'just try todownload and see whether it works with ISE
LT or not'. Can anybody give me a clue whether it works or not?

> Otherwise check out this site
>
http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/sql_embeddingmsde.asp
Ok. I've read the article. It is said that I can create my own MSI
package. Okay. That's acceptable. And for that I'm going to need
Microsoft Platform SDK. Okay, I'd check it... and... YIKES!!!!
342,000,000 bytes of download! More than 300 megs! And I connect thru
DUN! Do you have any alternatives?

Willianto
[vbcol=seagreen]
> "Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
> news:uwlMliENEHA.3476@.TK2MSFTNGP09.phx.gbl...
without[vbcol=seagreen]
message[vbcol=seagreen]
package?[vbcol=seagreen]
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4[vbcol=seagreen]
FoxPro[vbcol=seagreen]
the
>
|||You can save yourself a lot of wasted time and research by running the
setup.exe that comes with MSDE. You can pass command line arguments to
configure it. The merge modules are buggy. This what MS recommends.
HTH Bill
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Anybody knows how to include MSDE in my apps installation package? I've
> downloaded MSDE Deployment Package from
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> d98-bb0b-2c9d6414071f&DisplayLang=en.
> but unfortunatelly:
> 1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
> Edition, and;
> 2) it's a beta product, and Microsoft doesn't approve its use in the
> production environment nor use it to deploy any applications.
> I'm also considering alternatives to use InnoSetup. Will the solution be
> easier if I use Inno?
> Looking forward to hearing from all of you!
> TIA,
> Willianto
>
|||Bill:
> You can save yourself a lot of wasted time and research by running the
> setup.exe that comes with MSDE. You can pass command line arguments to
> configure it. The merge modules are buggy. This what MS recommends.

Looks like that's the only way to go...
Thank you. And thanks to you too, Altman.
I've been expecting too much from M$ freebies
Regards,
Willianto

How to include more databases in DB Maintenance Plan ?

Hi,
I have created a DB maintenance plan to backup a production database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I have checked the maintenance plan but I am not able to
work out how to include 2 more databases.
Is it possible to give me some advice ?
ThanksPeter
Right Click on MP and then Properties . Add more user databases. Is that
what you mean?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have created a DB maintenance plan to backup a production database
> daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I have checked the maintenance plan but I am not able
> to work out how to include 2 more databases.
> Is it possible to give me some advice ?
> Thanks
>|||Yes.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8m1RToIHHA.780@.TK2MSFTNGP03.phx.gbl...
> Peter
> Right Click on MP and then Properties . Add more user databases. Is that
> what you mean?
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan to backup a production database
>> daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I have checked the maintenance plan but I am not able
>> to work out how to include 2 more databases.
>> Is it possible to give me some advice ?
>> Thanks
>

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Mainteance Plan for SQL Server 2005

Hi,
I have created a DB maintenance plan by using wizard to backup a production
database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I select the maintenance plan and press "Modify" but I am
not able to work out how to include 2 more databases. Besides, it appears
that both transaction log & database backup are not deleted since the plan
is executed, is there something wrong ?
Is it possible to give me some advice ?
ThanksPeter
You asked the question yesterday and I gave you the answer. Don't you
rememner that?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a
> production database daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I select the maintenance plan and press "Modify" but I
> am not able to work out how to include 2 more databases. Besides, it
> appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases.
You need to add the databases to the backup task inside the plan. Right-lock the backup task, select
"Edit..." and in the "Databases:" drop.down you select the databases you want to include.
> Besides, it appears that both transaction log & database backup are not deleted since the plan is
> executed, is there something wrong ?
Add a "Maintenance Cleanup Task" to the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a production database daily.
> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases. Besides, it appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||Dear Uri,
I forget to mention that it is SQL Server 2005 yesterday. In this way, your
advice is applicable for SQL Server 2000.
Thanks
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEfBDB1IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Peter
> You asked the question yesterday and I gave you the answer. Don't you
> rememner that?
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor and Uri,
Thank you for your advice.
I find that the reason why I am not able to add more databases in the task
is because I use SA in my workstation while Windows Authentication is used
at the server side. In this way, I change the connection from Windows
Authentication to SQL Authentication for both local and target servers and I
am able to do it on my workstation. I have changed the ownership of the
jobs to SA (Instead of Administrator).
I would like to seek your advice
1) Is it possible to change the ownership of the "Database Maintenance Plan"
from Administrator to SA ?
2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so that old transaction log backup will be deleted ?
3) Which step should be performed first - Cleanup Task or Backup Task ?
Should the constraint be success or finish ?
4) Is it necessary to add another task to delete old reports in the "Weekly
Maintenance Plan" so that the logging will be deleted ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases.
> You need to add the databases to the backup task inside the plan.
> Right-lock the backup task, select "Edit..." and in the "Databases:"
> drop.down you select the databases you want to include.
>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
> Add a "Maintenance Cleanup Task" to the plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
I would guess that you would change the owner of the job. To the best of my knowledge, a maint plan
doesn't have an owner, the job does. Not sure, though.
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
Yes, if you want the old backup files to be removed and if you don't remove them some other way.
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
This is really your decision. I prefer to do the backup first, and if it fails I don't remove old
backups.
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
Yes, if you want the old report files to be removed and if you don't remove them some other way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor and Uri,
> Thank you for your advice.
> I find that the reason why I am not able to add more databases in the task is because I use SA in
> my workstation while Windows Authentication is used at the server side. In this way, I change the
> connection from Windows Authentication to SQL Authentication for both local and target servers and
> I am able to do it on my workstation. I have changed the ownership of the jobs to SA (Instead of
> Administrator).
> I would like to seek your advice
> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases.
>> You need to add the databases to the backup task inside the plan. Right-lock the backup task,
>> select "Edit..." and in the "Databases:" drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not deleted since the plan
>> is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a production database daily.
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases. Besides, it appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor,
Thank you for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejNfWICJHHA.3424@.TK2MSFTNGP02.phx.gbl...
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
> I would guess that you would change the owner of the job. To the best of
> my knowledge, a maint plan doesn't have an owner, the job does. Not sure,
> though.
>
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
> Yes, if you want the old backup files to be removed and if you don't
> remove them some other way.
>
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
> This is really your decision. I prefer to do the backup first, and if it
> fails I don't remove old backups.
>
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
> Yes, if you want the old report files to be removed and if you don't
> remove them some other way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
>> Dear Tibor and Uri,
>> Thank you for your advice.
>> I find that the reason why I am not able to add more databases in the
>> task is because I use SA in my workstation while Windows Authentication
>> is used at the server side. In this way, I change the connection from
>> Windows Authentication to SQL Authentication for both local and target
>> servers and I am able to do it on my workstation. I have changed the
>> ownership of the jobs to SA (Instead of Administrator).
>> I would like to seek your advice
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
>> Thanks
>> Peter
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases.
>> You need to add the databases to the backup task inside the plan.
>> Right-lock the backup task, select "Edit..." and in the "Databases:"
>> drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>>
>sql

How to Include months not in the records?

Hi,
I have 2 tables created.
One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)
PersonID | PersonName PersonID | Month | Year | Total Sales
================== ==================================
ID1 | Sally ID1 | 1 | 2005 | 1000
ID2 | David ID2 | 2 | 2005 | 1500
Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)
I would like the result to be like the following:
PersonID | Month | Year | Total Sales
===================================
ID1 | 1 | 2005 | 1000
ID1 | 2 | 2005 | 0
ID1 | 3 | 2005 | 0
(For month 4 - 12) Total Sales will be 0 too as no records exist in the first place
ID2 | 1 | 2005 | 0
ID2 | 2 | 2005 | 1500
........

Do you have to do it in SQL or can you do it in code?|||

Create a temporary table using a sql proc or in code that contains the year(s) and months that you do want to include. Then you just have to OUTER join it to your existing tables to fill in the missing gaps.
HTH.

how to include database with installation files of msde

I have created an application using VB6. The application uses MSDE to
access data. How do I include the required database with the installation
package.
Daniel
In the package & deployment wizard, include it in the "other files" listing
you get to complete as part of creating the setup package.
Randy Birch
MVP Visual Basic
http://vbnet.mvps.org/
Please respond only to the newsgroups so all can benefit.
"Daniel" <dwbnews@.hotmail.com> wrote in message
news:#WXyl04REHA.3124@.TK2MSFTNGP12.phx.gbl...
: I have created an application using VB6. The application uses MSDE to
: access data. How do I include the required database with the installation
: package.
:
: Daniel
:
:
|||Are you saying that a database in MSDE is saved on the drive as an actual
file?
Daniel
"Randy Birch" <rgb_removethis@.mvps.org> wrote in message
news:uxkYY84REHA.3016@.tk2msftngp13.phx.gbl...
> In the package & deployment wizard, include it in the "other files"
listing
> you get to complete as part of creating the setup package.
> --
> Randy Birch
> MVP Visual Basic
> http://vbnet.mvps.org/
> Please respond only to the newsgroups so all can benefit.
>
> "Daniel" <dwbnews@.hotmail.com> wrote in message
> news:#WXyl04REHA.3124@.TK2MSFTNGP12.phx.gbl...
> : I have created an application using VB6. The application uses MSDE to
> : access data. How do I include the required database with the
installation
> : package.
> :
> : Daniel
> :
> :
>
|||MSDE is SQL Server, and for each database in SQL Server, you have at least two files, a primary database file
(typically .mdf) and at least one transaction log file (typically .ldf). You can detach the files, ship them
with your app and after msde is installed attach them again. Read about sp_detach_db and sp_attach_db i Books
Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <dwbnews@.hotmail.com> wrote in message news:%23aKOEJ5REHA.3628@.TK2MSFTNGP12.phx.gbl...
> Are you saying that a database in MSDE is saved on the drive as an actual
> file?
> Daniel
> "Randy Birch" <rgb_removethis@.mvps.org> wrote in message
> news:uxkYY84REHA.3016@.tk2msftngp13.phx.gbl...
> listing
> installation
>
|||Hi Daniel
Also check out:
http://support.microsoft.com/default...-us;Q314546#10
John
"Daniel" <dwbnews@.hotmail.com> wrote in message
news:%23aKOEJ5REHA.3628@.TK2MSFTNGP12.phx.gbl...
> Are you saying that a database in MSDE is saved on the drive as an actual
> file?
> Daniel
> "Randy Birch" <rgb_removethis@.mvps.org> wrote in message
> news:uxkYY84REHA.3016@.tk2msftngp13.phx.gbl...
> listing
> installation
>
|||Sorry ... my error ... I read MSDE and thought Access MDB. I didn't see the
cross-post to the sqlserver groups.
Randy Birch
MVP Visual Basic
http://vbnet.mvps.org/
Please respond only to the newsgroups so all can benefit.
"Daniel" <dwbnews@.hotmail.com> wrote in message
news:#aKOEJ5REHA.3628@.TK2MSFTNGP12.phx.gbl...
: Are you saying that a database in MSDE is saved on the drive as an actual
: file?
:
: Daniel
:
: "Randy Birch" <rgb_removethis@.mvps.org> wrote in message
: news:uxkYY84REHA.3016@.tk2msftngp13.phx.gbl...
: > In the package & deployment wizard, include it in the "other files"
: listing
: > you get to complete as part of creating the setup package.
: >
: > --
: >
: > Randy Birch
: > MVP Visual Basic
: > http://vbnet.mvps.org/
: > Please respond only to the newsgroups so all can benefit.
: >
: >
: > "Daniel" <dwbnews@.hotmail.com> wrote in message
: > news:#WXyl04REHA.3124@.TK2MSFTNGP12.phx.gbl...
: > : I have created an application using VB6. The application uses MSDE to
: > : access data. How do I include the required database with the
: installation
: > : package.
: > :
: > : Daniel
: > :
: > :
: >
:
:

How to include data from 2 unlinked tables

I am using VB6 and CR10 & SQL Server 7
I am working on an inventory project nw.. I'm new to crystal reports..

For the report i need to pick data from 2 tables(Sales_Details, Purchase_Details)
There is no link between these 2 tables (except a similarity that both have got a date field)

TABLE 1
Sale_Date... SalesQty
01/01/07...... 50
01/03/07...... 24
01/04/07...... 10

TABLE 2
Purch_Date... Purchase Qty
01/01/07 ....... 100
01/06/07 ....... 100

I need to take a report which shows the movement of a particular Item based on date range
It should look like this

------x----x----x
Date ......... Sales.......Purchase
------x----x----x

01/01/07 ........ 50 ........ 100
01/03/07 ........ 24 ........ 0
01/04/07 ........ 10 ........ 0
01/06/07 ........ 0 ........ 100
--------------
I tried inner and outer joins to links these tables, but all in vein( data is getting repeated then), tried with groups also..no use

Pleeeaaase... help me to do this...
This may be a simple issue for most of you...
But i am a beginner.. i need your valuable help..
Plzzzzz...Just to make sure, but there are no PKeys in either one of your tables?|||You say you need to report by item, so surely there must be some sort of item code in both tables otherwise how do you know what item you're buying / selling?|||Ofcourse item code is there... For sake of simplicity i dint mention that.

Actually both the purchase and sales table has the fileds- Date, Itemcode, Quantity

Sales Table

Date...... ItemCode........Qnty
------------
01/01/07 ..A001............50
01/03/07...A001............24
01/04/07...A001............10

Purchase Table
Date...... ItemCode........Qnty
------------
01/01/07...A001............100
01/06/07...A001............100

and i need a report (like the one mentioned above)based on the movement of Item -A001 ..

Please...........|||First:
You wrote:

There is no link between these 2 tables (except a similarity that both have got a date field)
This is the reason for the questions now I assume itemcode is your link.
Next you will group by date and itemcode if you want, now for grouping by date go to options and choose section to be printed by day.
Then you will need to create formulas that will check to see the quantity of sales and purchases.

Something like:

If {Sales.quantity} > 0
Then {Sales.quantity}
Else 0

Hope that helps,
GJ

how to include an apostrophy inside a string

I am doing a row update in a table and the text contains a ' character, just
wondering how to do this?
for example
UPDATE table
SET field2 = 'text to enter to table's but does not work'
WHERE field1 = 133
the second apostrophy I want to not end the text section with.
Thanks.
--
Paul G
Software engineer.YOu have to double quote that:
UPDATE table
SET field2 = 'text to enter to table''s but does not work'
WHERE field1 = 133
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> I am doing a row update in a table and the text contains a ' character, just
> wondering how to do this?
> for example
> UPDATE table
> SET field2 = 'text to enter to table's but does not work'
> WHERE field1 = 133
> the second apostrophy I want to not end the text section with.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks. One other question, when I use select from query analizer to
retreive a long text field and then copy and paste it to a word doc it looks
like it only returns the first portion of the large text string. Also is
there anyway to view a large text field from query analyzer when you open the
table? thanks again.
--
Paul G
Software engineer.
"Jens SÃ¼Ã?meyer" wrote:
> YOu have to double quote that:
> UPDATE table
> SET field2 = 'text to enter to table''s but does not work'
> WHERE field1 = 133
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > I am doing a row update in a table and the text contains a ' character, just
> > wondering how to do this?
> >
> > for example
> > UPDATE table
> > SET field2 = 'text to enter to table's but does not work'
> > WHERE field1 = 133
> > the second apostrophy I want to not end the text section with.
> > Thanks.
> >
> > --
> > Paul G
> > Software engineer.|||QA is limited to a maximum outpur of 8000 characters, if you donÂ´t have even
that you should look in Tools--> Options --> Results --> Maximum charcters
per column
to increase it to 8000
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> ok thanks. One other question, when I use select from query analizer to
> retreive a long text field and then copy and paste it to a word doc it looks
> like it only returns the first portion of the large text string. Also is
> there anyway to view a large text field from query analyzer when you open the
> table? thanks again.
> --
> Paul G
> Software engineer.
>
> "Jens SÃ¼Ã?meyer" wrote:
> > YOu have to double quote that:
> >
> > UPDATE table
> > SET field2 = 'text to enter to table''s but does not work'
> > WHERE field1 = 133
> >
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > I am doing a row update in a table and the text contains a ' character, just
> > > wondering how to do this?
> > >
> > > for example
> > > UPDATE table
> > > SET field2 = 'text to enter to table's but does not work'
> > > WHERE field1 = 133
> > > the second apostrophy I want to not end the text section with.
> > > Thanks.
> > >
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks that should do it as it is way less than 8000 characters.
--
Paul G
Software engineer.
"Jens SÃ¼Ã?meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you donÂ´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens SÃ¼Ã?meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Just wondering if you know how to direct the results to a file?
--
Paul G
Software engineer.
"Jens SÃ¼Ã?meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you donÂ´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens SÃ¼Ã?meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
Results in File
or you use OSQL on the commandline with the -o <outputfile >switch
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> Just wondering if you know how to direct the results to a file?
> --
> Paul G
> Software engineer.
>
> "Jens SÃ¼Ã?meyer" wrote:
> > QA is limited to a maximum outpur of 8000 characters, if you donÂ´t have even
> > that you should look in Tools--> Options --> Results --> Maximum charcters
> > per column
> >
> > to increase it to 8000
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > ok thanks. One other question, when I use select from query analizer to
> > > retreive a long text field and then copy and paste it to a word doc it looks
> > > like it only returns the first portion of the large text string. Also is
> > > there anyway to view a large text field from query analyzer when you open the
> > > table? thanks again.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Jens SÃ¼Ã?meyer" wrote:
> > >
> > > > YOu have to double quote that:
> > > >
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table''s but does not work'
> > > > WHERE field1 = 133
> > > >
> > > >
> > > > --
> > > > HTH, Jens Suessmeyer.
> > > >
> > > > --
> > > > http://www.sqlserver2005.de
> > > > --
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > wondering how to do this?
> > > > >
> > > > > for example
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > WHERE field1 = 133
> > > > > the second apostrophy I want to not end the text section with.
> > > > > Thanks.
> > > > >
> > > > > --
> > > > > Paul G
> > > > > Software engineer.|||ok thanks this seems very useful.
--
Paul G
Software engineer.
"Jens SÃ¼Ã?meyer" wrote:
> Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
> Results in File
> or you use OSQL on the commandline with the -o <outputfile >switch
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > Just wondering if you know how to direct the results to a file?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens SÃ¼Ã?meyer" wrote:
> >
> > > QA is limited to a maximum outpur of 8000 characters, if you donÂ´t have even
> > > that you should look in Tools--> Options --> Results --> Maximum charcters
> > > per column
> > >
> > > to increase it to 8000
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > ok thanks. One other question, when I use select from query analizer to
> > > > retreive a long text field and then copy and paste it to a word doc it looks
> > > > like it only returns the first portion of the large text string. Also is
> > > > there anyway to view a large text field from query analyzer when you open the
> > > > table? thanks again.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Jens SÃ¼Ã?meyer" wrote:
> > > >
> > > > > YOu have to double quote that:
> > > > >
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table''s but does not work'
> > > > > WHERE field1 = 133
> > > > >
> > > > >
> > > > > --
> > > > > HTH, Jens Suessmeyer.
> > > > >
> > > > > --
> > > > > http://www.sqlserver2005.de
> > > > > --
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > > wondering how to do this?
> > > > > >
> > > > > > for example
> > > > > > UPDATE table
> > > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > > WHERE field1 = 133
> > > > > > the second apostrophy I want to not end the text section with.
> > > > > > Thanks.
> > > > > >
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.sql

how to include a sql database in a web service

i am using vs2005 and sql 2005 and i want to include a database in a webservice, how to do that? and where are the items in the toolbox?

Hi,

If you are looking to add a database to SqlExpress in a webservice application please have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=276342&SiteID=1 posting which mentions that you can add a database by using "Add New Item...". Another way would be to go to the "Tools" menu and click on "Connect to database..." and follow the instructions to add a new database.

Please reply if this is not the scenario you are trying to achieve.

Jimmy

Friday, March 23, 2012

How to include variable in CURSOR SQL Filter clause?

How to include total in query?

how to include the nulls?

How to include the date as part of the log file name...

How to include SSIS into install file?

Wednesday, March 21, 2012

How to include SSIS into install file?

How to include SQL Express in Installation Package and change some option?

How to include parameter in WHERE statment of a stored procedure?

How to include multiple membes in an expression?

How to include MSDE with Setup of custom applications

How to include MSDE installation in my apps installation package

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Mainteance Plan for SQL Server 2005

How to include more databases in DB Mainteance Plan for SQL Server 2005

How to Include months not in the records?

how to include database with installation files of msde

How to include data from 2 unlinked tables

how to include an apostrophy inside a string

how to include a sql database in a web service

hide system tables

Blog Archive

About Me