need help connecting to sql sever

Hi

I am able to connect to an sql server from my server explorer. Can't seem to connect
to an sql server from my code.

this is what i am doing

String strConnection = "server=FRANK;database=Northwind;integrated security=true;";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open
objConnection.Close();

error Login Failure for user FRANK

I know that my server name is FRANK because thats what it says on my Sql Service manager.

And i use that same server name when i connect from my Server Explorer(using Visual Studio.NET) and then i get a listing of the the Databases that are associated with my SQL Server.

I have no problems coonecting to an Access dataBase using OLedbConnectionI'm not sure if "server" is the same as "Data Source", but try using "Data Source" instead:

String strConnection = "Data Source=FRANK;database=Northwind;integrated security=true";sql

Need function for padding

Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.you need to use convert or cast, not format.|||it sounds like you are doing SQL Passthrough in jet against sql server, and now you need to use SQL Server syntax instead of jet/access sql.'

cheers|||Originally posted by eisoffind
Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.

If i understand your question correctly, you want to be able to pad the left of a number with another value. For example '555' --> '000555'

Attached are two SQL2K UDFs to lpad and rpad.sql

need cursor help

hi
i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra wrote:
> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoon
What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegr oups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon
|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>
|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon
|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi
> Thanks.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>
|||Thanks
JCVoon

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have yo
ur log backups scheduled
if you wish, the log backup job will just sit a wait until the database has
been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon

Need a SP to read Table A and update Table B

Hi

I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.You need to help us a little bit. What, if any relationship exists between Table A and B? Is there a column in each that links one to the other?|||The two tables (A and B) are identical and both have a primary key. What I need to do is to update all rows in Table B with the new one from Table A. This is like a batch update from A to B.

need -> max(sum(salary)) query

hi

i have a table employee:

dept ename salary
-- --- ---
10 A 2500
20 B 3500
30 C 4000
20 D 5500
10 E 4500
30 F 5200

FIRST QUERY:
select dept,sum(salary) from employee group by dept

the above one is working fine..

after working the first query output,
i want to select the dept,max(sum(salary)) from the table...

how?? could any one send me immediately...

thanks in advancethe below one query will give only the max(sum(salay))

-----
select max(sal) from (select sum(salary) as sal from employee
group by dept) as a
-------

but i need - Which DEPT has the max(sum(salary)) ??

need these two values:

dept, max(sum(salary))

thanks|||select top 1
dept
, sum(salary)
from employee
group
by dept
order
by sum(salary) desc|||hi
it works .

thanks rudy....