clinno's blog

1. SQL Server 2000 Restore pre-requirement:

(1)You need to backup the following database : master , msdb and your own databases.
(2)sa's default database must be in database master.
(3)The new server's installed directory must be as the same as original one.
(4)New Server's version and server pack must be the same.

2.Open Enterprise Manager.

3.Restore database , msdb.

4.Now ready to restore database , master. Please stop SQL server and open a DOS prompt window. Change to the installed directory of SQL ,usually it should be in C:\Program Files\Microsoft SQL Server\MSSQL\Binn. Enter the following command to let SQL Server running in Single-User Mode, sqlservr.exe -c -m.

5.Re-open Enterprise Manager.

6.Restore database, master.

7.When SQL Server prompt the 'reconnect' message, please choose NO. At this moment, SQL server is shutdown when you successfully restore database , master.

8.Restart SQL Server, and Open Enterprise Manager to restore all your own database.

*This method could restore all your jobs and packages in the new server.

透過SQL EIS，您可以完成簡易好用BI。

SQL建立程序如下：
1.在SQL連結伺服器，透過ODBC或是原生資料庫的驅動程式與來源資料建立連結。並設定每日將資料轉換進SQL Server，以加速後面資料計算與整合之效率。若有須要，可對這些轉進來的原始資料作索引。

2.透過SQL case when的語法進行ETL(Extract, Transformation, and Loading)。

3.依據使用者需求，透過T-SQL寫出使用者需要的欄位，並將結果透過select ....into table 或是create view的方式產生一個Table 或是 View。

4.請使用者透過Excel的樞紐分析表連結到SQL Server中剛剛建立的Table或是View(此部份可以做權限的控管)抓取資料，當資料抓取完成後，使用者就可以在自己的電腦進行樞紐分析。

5.如果是用T-SQL寫成一個Table，那就把該程式放到DTS中，並安排排程，這樣SQL Server就可以自動轉換資料，不需要在人為介入。

/* fpont 12/99 */
/* pont.net */
/* tcpClient.c */

#include
#include
#include
#include
#include
#include
#include /* close */

int main()
{
int sockfd;
struct sockaddr_in dest;
char buffer[128];

/* create socket */
sockfd = socket(PF_INET, SOCK_STREAM, 0);

/* initialize value in dest */
bzero(&dest, sizeof(dest));
dest.sin_family = PF_INET;
dest.sin_port = htons(8889);
inet_aton("192.168.10.33", &dest.sin_addr);

/* Connecting to server */
connect(sockfd, (struct sockaddr*)&dest, sizeof(dest));

/* Receive message from the server and print to screen */
bzero(buffer, 128);
recv(sockfd, buffer, sizeof(buffer), 0);
printf("%s", buffer);

/* Close connection */
close(sockfd);

return 0;
}

/* fpont 1/00 */
/* pont.net */
/* tcpServer.c */

#include
#include
#include
#include
#include
#include
#include /* close */


#define SUCCESS 0
#define ERROR 1

#define END_LINE 0x0
#define SERVER_PORT 1500
#define MAX_MSG 100

/* function readline */
int read_line();

int string_length(char string[]) {
int i;

i = 0;
while (string[i] != '\0') i++;

return i;
}

void string_concat(char st1[], char st2[], char result[]) {
int i, j;

i = 0;
while ( (result[i] = st1[i]) != '\0' ) i++;
j = i;
while ( (result[i] = st2[i - j]) != '\0' ) i++;
}

void substring(char string[], int start, int length, char result[]) {
int i;

if ( string_length(string) <= start) {
result[0] = '\0';
return;
}

i = 0;
while ( i < length && (result[i] = string[start + i]) != '\0' ) i++;

result[i] = '\0';
}




main( int argc ,char *argv[])
{
int sockfd;
int size;
struct sockaddr_in dest;
char buffer[] = "post ok!";
char buf[255];
char cmd[255];
char st1[1];
char st2[20];
int st1_i;
/* create socket , same as client */
sockfd = socket(PF_INET, SOCK_STREAM, 0);

/* initialize structure dest */
bzero(&dest, sizeof(dest));
dest.sin_family = PF_INET;
dest.sin_port = htons(8890);
/* this line is different from client */
dest.sin_addr.s_addr = INADDR_ANY;

/* Assign a port number to socket */
bind(sockfd, (struct sockaddr*)&dest, sizeof(dest));

/* make it listen to socket with max 20 connections */
listen(sockfd, 20);
/* infinity loop -- accepting connection from client forever */
while(1)
{
int clientfd;
struct sockaddr_in client_addr;
int addrlen = sizeof(client_addr);
printf("waiting connecting ...");
/* Wait and Accept connection */
clientfd = accept(sockfd, (struct sockaddr*)&client_addr, &addrlen);

size= recv(clientfd, buf , 30,0);

/* Send message */
substring(buf , 0 , 1 , st1);
printf(">%s\n",st1);
substring(buf , 1 , 20, st2);
printf(">%s\n",st2);
st1_i = atoi(st1);
if ( st1_i == 1 ) {
string_concat("fglrun /u/siltron/aim/4gi41/pda_aimt370S ", st2, cmd);
printf("1>>%s\n",cmd);
}
if ( st1_i == 2 ) {
string_concat("fglrun /u/siltron/aim/4gi41/pda_aimt306Y ", st2, cmd);
printf("2>>%s\n",cmd);
}
if ( st1_i == 3 ) {
string_concat("fglrun /u/siltron/aim/4gi41/pda_aimt306S ", st2, cmd);
printf("3>>%s\n",cmd);
}
if ( st1_i == 4 ) {
string_concat("fglrun /u/siltron/aim/4gi41/pda_aimt309S ", st2, cmd);
printf("4>>%s\n",cmd);
}
if ( st1_i == 5 ) {
string_concat("fglrun /u/siltron/aim/4gi41/pda_aimt324S ", st2, cmd);
printf("5>>%s\n",cmd);
}
if ( st1_i == 6 ) {
string_concat("fglrun /u/siltron/apm/4gi41/pda_apmt722Y ", st2, cmd);
printf("6>>%s\n",cmd);
}
system(cmd);

send(clientfd, buffer, sizeof(buffer), 0);

/* close(client) */
close(clientfd);

}

/* close(server) , but never get here because of the loop */
close(sockfd);
return 0;
}

select
[前三次Remark]=
(
select '('+dbo.fyymmdd(fse_comment_date)+')'+CHAR(10)+'●'+fse_comment from
(select p1.*,
(select count(*) from DesignT_DesignT_DODIDW_Comment as p2 where p2.unique_id> p1.unique_id and p2.faedesign00=p1.faedesign00) as c
from DesignT_DesignT_DODIDW_Comment as p1 where p1.faedesign00=DesignT_DODIDW.unique_id
) as A where c=3
)
from DesignT_DODIDW