Need SQL server advice

Hi, I need some advice concerning a vb.net windows application I'm building using SQL server as it's database.

The idea is to have a main enterprise SQL server database on the company network. The application will be installed on stand alone computers with it's own Standard SQL server database which will mirror the network database. This is so users can work with the application when not connected to the network.

The Network database will often be updated with new data and I will program the application so that everytime it is opened it will check the network database for new data and then update the local database. Of course this can only happen if the computer is connected to the network.

My questions are:

1. When installing the application on a users computer is it possible to initate the setup for the local SQL server in the application setup?

2.Once the setup is complete can there be a transfer of all the data tables and stored procedures on the network database to the local database automatically or would I have to create all that through code?

3.When there is an update of data from the network database to the local database can this be done transparently to the user or would this have to happen through DTS.

thanks for any advice on these questions

1. When installing the application on a users computer is it possible to initate the setup for the local SQL server in the application setup?

You must have a "class module" in the application that stores the connection string. when the application run the very first time you may read the connection string from a file then store it to the CM.connstring

At the form load event of every form you can assign the connection string to your connection object.

sqlconn.connectionstring=cm.connstring.

2.Once the setup is complete can there be a transfer of all the data tables and stored procedures on the network database to the local database automatically or would I have to create all that through code?

you must design a transactional replication or merge replication with the server

3.When there is an update of data from the network database to the local database can this be done transparently to the user or would this have to happen through DTS.

answer on #2 resolves this also

sql

Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
undocumented stored procedure:
sp_MSforeachdb
ex.:
EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
dbo.Table'
Tom
http://sqlserverbuilds.blogspot.com/|||thanks pal,
u solved my big problem. as i'm a software developer, i dont have much
knowledge of DB. i can only write some queries but this is something i
dont do it regularly.
Lucky
<@.> wrote:
> > all i need is:
> > 1. need to find out whether the table exists,
> > 2. if table exists, clear all rows into the table.
> undocumented stored procedure:
> sp_MSforeachdb
> ex.:
> EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
> dbo.Table'
>
> Tom
> http://sqlserverbuilds.blogspot.com/|||Use master
GO
Declare @.SQL nvarchar(1000)
Declare @.DBID int, @.DBName varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT dbid, [Name]
FROM sysdatabases
-- WHERE dbid > 4
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @.DBID, @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Start of Cursor
Set @.SQL = '
USE ' + @.DBName + '
GO
EXEC sp_spaceused @.updateusage = N''TRUE'';
GO'
Print @.SQL
exec sp_executeSQL @.SQL
-- Movenext of cursor
FETCH NEXT FROM db_cursor
INTO @.dbid, @.dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.sql

Need some advice. Thank You.

Hello,
Today I am creating my first one-to-many relationship database.
My main table is:
USERS
Then I have 4 tables related with this one:
PAYMENTS, ORDERS, BOOKS, ARTICLES
For each user I need to create a field named VALUE.
VALUE = N(PAYMENTS)*4 + N(ORDERS)*2 + N(BOOKS)*10 + N(ARTICLES)*5
N = Number of... / Example: N(PAYMENTS) means "Number of Payments".
My questions are:
1. Should I place the value field in USERS table or create a table named
VALUES and have it connected to USERS table?
2. How can I keep my VALUE field updated for each USER?
Thank You Very Much,
Miguel> 1. Should I place the value field in USERS table or create a table
named
> VALUES and have it connected to USERS table?
None of the above. Don't store calculated values in the database. Put
the calcs in your views, queries and procs.

> 2. How can I keep my VALUE field updated for each USER?
Not a problem if you don't store the value.
If you need more help then the following article explains the best way
to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> 1. Should I place the value field in USERS table or create a table named
Consider using a view instead. Deriving calculated values are generally
better than having them as persisted data in any base table.
This is not an issue, if a view is used. For details, see the topic on
views, creating a view and if required for any performance reasons, indexed
views, in SQL Server Books Online.
Anith

Need some advice for a query

HI all,

I use this query for some stats but the problem with it is, i can't sort on the day. Can someone help me with this query so that i can see the unique hits a day sorted by day?

SELECT CONVERT (varchar, SessionStart, 106) AS dag,
COUNT(DISTINCT IPAdress) AS visitors
FROM tblStats
WHERE Crawler = 'False'
GROUP BY CONVERT (varchar, SessionStart, 106)

Cheers WimHI all,

I use this query for some stats but the problem with it is, i can't sort on the day. Can someone help me with this query so that i can see the unique hits a day sorted by day?

SELECT CONVERT (varchar, SessionStart, 106) AS dag,
COUNT(DISTINCT IPAdress) AS visitors
FROM tblStats
WHERE Crawler = 'False'
GROUP BY CONVERT (varchar, SessionStart, 106)

Cheers Wim

Try using one of the other formats? Maybe 111 or 120?

Regards,

hmscott|||Try using one of the other formats? Maybe 111 or 120?

Regards,

hmscott

Thanx 111 certainly solves my problem!

Regards Wim

Need security advice on xp_cmdshell, bcp, xml procedure

I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.

ThanksTerri (terri@.cybernets.com) writes:
> I have a stored procedure that creates an xml file. It executes a SELECT
> statement with the FOR XML clause and then writes the xml file using bcp
> and xp_cmdshell. I am calling this procedure by passing it a parameter
> via ADO. I have configured the SQL Server Agent with a proxy account so
> non-SysAdmin can execute xp_cmdshell.
> I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
> concerned about having to maintain the password on my proxy account when
> that users' password changes.
> Is there a better, more secure way to generate this xml file.

I'm not really sure what you but it sounds like you do something like:

bcp "SELECT ... FOR XML" queryout outfile.bcp

This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> This is not likely to work very well. ODBC will chop the XML document
> after each 2033 character. See KB 275583.
> So you would need to get the XML document to the client, and have the
> client to create the file and put it where it belongs. Which probably
> is better from a security perspective as well.

Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.

I'm calling the following procedure via ADO

CREATE PROCEDURE procGenerateXML
@.CheckRequestID int
AS
declare @.sql nvarchar(4000)
set @.sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@.ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @.sql
GO

The procedure proctest looks like:

CREATE PROCEDURE proctest
@.ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO

I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID

cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection= Yes"
cn.Open provstr

Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute

I'm looking for guidance on the following:

-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell

Thanks|||Terri (terri@.cybernets.com) writes:
>> This is not likely to work very well. ODBC will chop the XML document
>> after each 2033 character. See KB 275583.
>>
>> So you would need to get the XML document to the client, and have the
>> client to create the file and put it where it belongs. Which probably
>> is better from a security perspective as well.
> Thanks Erland,
> I'm not using Query Analyzer so I don't think KB 275583 applies to me.

But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.

Erland Sommarskog wrote:
> Terri (terri@.cybernets.com) writes:
> >> This is not likely to work very well. ODBC will chop the XML document
> >> after each 2033 character. See KB 275583.
> >>
> >> So you would need to get the XML document to the client, and have the
> >> client to create the file and put it where it belongs. Which probably
> >> is better from a security perspective as well.
> > Thanks Erland,
> > I'm not using Query Analyzer so I don't think KB 275583 applies to me.
> But you are using BCP which is implemented in ODBC. So I would definitely
> encourage you to test to generate a large XML document, before you
> ponder the issues with access to xp_cmdshell.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||pb648174 (google@.webpaul.net) writes:
> If you are already calling this code from ADO, then instead of BCP and
> all that you can just directly execute the FOR XML statement, loop
> through each 2033 char return results and create the XML file from the
> web server.

Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to those who replied, I'm going to investigate the ADO stream object.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970CB19EE5537Yazorman@.127.0.0.1...
> pb648174 (google@.webpaul.net) writes:
> > If you are already calling this code from ADO, then instead of BCP and
> > all that you can just directly execute the FOR XML statement, loop
> > through each 2033 char return results and create the XML file from the
> > web server.
> Actually, if he would do it the simple-minded way, he would not get
> 2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
> thingie instead.
> I have not investigated it, but I believe the proper way to receive FOR
> XML in ADO with SQLOLEDB is to use the Stream object.
> But apart from that fine detail, I agree with you. Doing this from SQL
> Server will be diffictul.
> (*) If you use the MSDASQL provider, that is ODBC, then you would have
> to as you say. But I would not recommend that.|||I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.

need matrix report advice

I have to create a report that displays monthly actual and forecasted values by project with a variance column for the current month. Has anyone ever attempted something like this? I am having a difficult time determining how the data should return from the procedure so that it can be display in the following format:

JUL

AUG

SEP

OCT

NOV

DEC

JAN

FEB

Actuals

Var

Forecast

PROJECT A

10

20

15

-15

30

15

41

26

47

64

PROJECT B

15

10

25

5

20

20

10

5

10

10

I need to use a matrix since the number of columns can vary. I have thought about returning the following fields: project, date, label, hours.

I am hoping someone has attempted this before and can offer advice on whether I am approaching the problem correctly.

Thanks for any help.

hey there

not sure if this is what you want but this is how I approached a similar report using a Matrix - with varying columns

Report was for a Weekly user worktime

put this in your layout view

(1)=(Parameters!PersonNameValue)

(2-across)=Fields!WorkDate.Value)

(3)=Fields!CallSubject3.Value

(4-across)=Format(Sum(Fields!WorkTime.Value)

(5)=Format(Sum(Field...hrs total)

(6) blank

this shows like below

person date1 date 2 etc

subject time time etc

total time totaltime totaltime etc

sorry I wasn't sure how to post a graphical view on here so I hope this helps you

just add extra fields as required

any questions please ask

Jewel