Data transformation services: production

Showing posts with label production. Show all posts

Thursday, March 22, 2012

Data Retrieval

Our production environment has multiple SQL Servers installed and running and
i need to retrieve data from multiple different servers and databases. We
have established multiple Web Service DAL's to retrieve data for out
applications but i cannot see how to leverage that into Reporting Services.
Any ideas?
Thanks in advance
Goaler...Read up on data processing extentions. That should fullfill your needs.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> Our production environment has multiple SQL Servers installed and running
and
> i need to retrieve data from multiple different servers and databases. We
> have established multiple Web Service DAL's to retrieve data for out
> applications but i cannot see how to leverage that into Reporting
Services.
> Any ideas?
> Thanks in advance
> Goaler...|||Thaks Bruce
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||As a follow-up, I think i need a bit more clarification and understanding.
Here is the issue
I need to be able to read multiple predefined shared data sources from
within MS-Reporting Services, to avoid the configuration of connection
information out side of the product. Then to combine the result set and
return it into a new report object.
There must be a way to do this, the designer allows you to create multiple
data sources but you cannot join the data together. Additionally, linked
servers are not an option in my environment.
Thanks again,
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||You can not join datasets together.
Your clarification confused me, I thought you had web services that you
wanted to use to return the data? If so then data processing extensions are
the way to go.
Bruce Loehle-Conger
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:6C959BD5-C681-4E15-96EE-78EA69C282BA@.microsoft.com...
> As a follow-up, I think i need a bit more clarification and understanding.
> Here is the issue
> I need to be able to read multiple predefined shared data sources from
> within MS-Reporting Services, to avoid the configuration of connection
> information out side of the product. Then to combine the result set and
> return it into a new report object.
> There must be a way to do this, the designer allows you to create multiple
> data sources but you cannot join the data together. Additionally, linked
> servers are not an option in my environment.
> Thanks again,
> "Bruce L-C [MVP]" wrote:
>> Read up on data processing extentions. That should fullfill your needs.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "goaler" <goaler@.discussions.microsoft.com> wrote in message
>> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
>> > Our production environment has multiple SQL Servers installed and
>> > running
>> and
>> > i need to retrieve data from multiple different servers and databases.
>> > We
>> > have established multiple Web Service DAL's to retrieve data for out
>> > applications but i cannot see how to leverage that into Reporting
>> Services.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance
>> > Goaler...
>>

Monday, March 19, 2012

Data problem when publishing website

I published my website directly to its production folder then opened Sql Express Management Studio and attached the ASPNETDB file to it in that folder. However, the location information displayed in the Databases window shows the file is actually mapped back to my development folder. ?

Has anyone else encountered this problem?

Yes, I did verify that I selected the correct location.

Okay, my mistake, it isn't actually mapped back to that file, it just names the database with the original path. Gotta wonder what genius thought that one up.

Data or index corruption?

Since this morning we have strange behaviour on our production SQL Server
With a simple select I have those results :
select serialno from table1 where serialno=205749
'Syntax error converting the varchar value '15LA02269' to a column of data
type int.'
But this one work :
select serialno from table1 where serialno='205749'
Of course serialno is a auto increment of type 'INT', but some other columns
are varchar
Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
error in SQL error Log.
I don't know if one or several tables are corrupted because I ve got those
type of message on other simple query with other tables.
I don't know what to do now... I hope someone have an idea.
Thanks in advanceOuch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> Since this morning we have strange behaviour on our production SQL Server
> With a simple select I have those results :
> select serialno from table1 where serialno=205749
> 'Syntax error converting the varchar value '15LA02269' to a column of data
> type int.'
> But this one work :
> select serialno from table1 where serialno='205749'
> Of course serialno is a auto increment of type 'INT', but some other columns
> are varchar
> Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> error in SQL error Log.
> I don't know if one or several tables are corrupted because I ve got those
> type of message on other simple query with other tables.
> I don't know what to do now... I hope someone have an idea.
> Thanks in advance|||Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
No error or warning.
Arrghhh : I've just realized something : serialno is not 'int' it's a
VARCHAR !!! And it appears in lot of tables, that why I have this message in
several cases.
I've never had this sort of message before. But few days ago we have changed
SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
my problems.
I think I have now to change all query for that columns (change int
parameters to string in ADO) ... If SantaClauss have nothing to do now
perhaps could he help me?
Many thanks for your help
"Tibor Karaszi" wrote:
> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "smf" <smf@.discussions.microsoft.com> wrote in message
> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> > Since this morning we have strange behaviour on our production SQL Server
> > With a simple select I have those results :
> >
> > select serialno from table1 where serialno=205749
> > 'Syntax error converting the varchar value '15LA02269' to a column of data
> > type int.'
> >
> > But this one work :
> > select serialno from table1 where serialno='205749'
> >
> > Of course serialno is a auto increment of type 'INT', but some other columns
> > are varchar
> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> > error in SQL error Log.
> >
> > I don't know if one or several tables are corrupted because I ve got those
> > type of message on other simple query with other tables.
> >
> > I don't know what to do now... I hope someone have an idea.
> > Thanks in advance
>
>|||That explains it.
In SQL Server 2000, a WHERE clause when you don't have the same datatype is evaluated according to
the section in Books Online called "datatype precedence". According to this, a varchar is converted
to an int. So your where clause would be the same as
select serialno from table1 where CAST(serialno AS int) = 205749
And not only can't above use an index on the serialno column, if you have something which isn't
convertible to an int, you get that error message.
In earlier releases the constant was converted to the columns datatype. I.e.:
select serialno from table1 where serialno= CAST(205749 AS varchar(nn))
Above can use an index on the serialno column and will also work for not numeric values in the
serial no column.
So, yes. changing compatibility level is most probably what made you see these run-time errors.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:D9CCEA0A-8754-421C-A57E-FB286A5E32E5@.microsoft.com...
> Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
> No error or warning.
>
> Arrghhh : I've just realized something : serialno is not 'int' it's a
> VARCHAR !!! And it appears in lot of tables, that why I have this message in
> several cases.
> I've never had this sort of message before. But few days ago we have changed
> SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
> my problems.
> I think I have now to change all query for that columns (change int
> parameters to string in ADO) ... If SantaClauss have nothing to do now
> perhaps could he help me?
> Many thanks for your help
>
> "Tibor Karaszi" wrote:
>> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "smf" <smf@.discussions.microsoft.com> wrote in message
>> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
>> > Since this morning we have strange behaviour on our production SQL Server
>> > With a simple select I have those results :
>> >
>> > select serialno from table1 where serialno=205749
>> > 'Syntax error converting the varchar value '15LA02269' to a column of data
>> > type int.'
>> >
>> > But this one work :
>> > select serialno from table1 where serialno='205749'
>> >
>> > Of course serialno is a auto increment of type 'INT', but some other columns
>> > are varchar
>> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
>> > error in SQL error Log.
>> >
>> > I don't know if one or several tables are corrupted because I ve got those
>> > type of message on other simple query with other tables.
>> >
>> > I don't know what to do now... I hope someone have an idea.
>> > Thanks in advance
>>

Data or index corruption?

Sunday, March 11, 2012

Data not displaying (in DataList) from SQL on GoDaddy

Everything works great on my development box. I am using GoDaddy for production (ASP.Net v2, SQL 2000).

I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages.

I updated the connection string in web.config to this:

<addname="snsb"connectionString="
Server=whsql-vXX.prod.mesaX.secureserver.net;
Database=DB_42706;
User ID=username;
Password=pw;
Trusted_Connection=False
"providerName="System.Data.SqlClient" / >

But I am unsure if this is the issue?? Any insights? This is the page I am working on:www.sugarandspicebakery.com/demo/bakery/default.aspx. So, the page displays fine, but it should be showing data from the database. This particular page uses a DataList with ItemTemplate. There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results

I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now!

Thank you

JenniferPost the code that you bind the datalist with the data source.
|||

Here is the code:

<asp:SqlDataSourceID="snsb"runat="server"ConnectionString="<%$ ConnectionStrings:snsb %>"SelectCommand="SELECT [itemNumber], [name], [shortDescription], [category], [imageFileName] FROM [snsb] WHERE ([category] = @.category)"EnableCaching="true"><SelectParameters><asp:QueryStringParameterDefaultValue="Cakes"Name="category"QueryStringField="cat"Type="String"/>
</SelectParameters></asp:SqlDataSource><asp:DataListID="DataList1"runat="server"RepeatColumns="3"RepeatDirection="Horizontal"CellPadding="25"AlternatingItemStyle-BorderWidth="1"DataSourceID="snsb"><ItemTemplate>

<asp:HyperLinkID="HyperLink1"runat="server"NavigateUrl='<%# "~/bakery/item.aspx?item=" + Eval("itemNumber") %>'>
<asp:Imagerunat="server"BorderColor="#000000"BorderStyle="solid"BorderWidth="1"ImageUrl='<%# "../images/page/" + Eval("itemNumber") + "_a.jpg" %>'Width="123"Height="82"/></asp:HyperLink><br/>
<%# Eval("name") %><br/>
<%# Eval("shortDescription") %>
</ItemTemplate></asp:DataList>

Sorry it looks all over the place here on the forum, the insert code option doesn't appear to be working correctly.

Thanks,

Jennifer

Wednesday, March 7, 2012

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a staging
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alot
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:

> I often have to copy an entire database from production server onto a staging
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will run
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is my
> biggest problem. Pleassssssse Help. Thanks alot

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a stagin
g
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alotHOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:

> I often have to copy an entire database from production server onto a stag
ing
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + '
)'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd +
')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will r
un
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is m
y
> biggest problem. Pleassssssse Help. Thanks alot

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a staging
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alotHOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:
> I often have to copy an entire database from production server onto a staging
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will run
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is my
> biggest problem. Pleassssssse Help. Thanks alot

Friday, February 17, 2012

data import

I have two databases identical in structure but with different data (staging database and a production database). There is a bunch of data in the staging database that needs to be moved to the production database. The data is in two tables and a foreign key link exists between them. The ID's in the tables of both databases started with '1' and started incrementing, so if I import all the data into production, there will be duplicate ID's.

Is there an easy way to combine these tables? I'm not really a dba, but familiar enough with sql from an admin standpoint.

Thanks for any adviceThis is why you should use GUIDs instead of Identity values.

One way around this problem is to add a column to your production tables to store the original ID. Then you can recreate the record relationships after new Identity values are assigned.

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
Phil
Inline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixtur
e
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocat
e
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.
> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.
> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.
> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.
> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA
>

Data From SQL 2000 to SQL 6.5

We're running production on an SQL 2000 server but still use and old
6.5 server for test purposes.
Is there a way to copy data from a table on the 200 server to the same
table on a 6.5 server?
Thanks!!!Some options are: DTS, BCP, BULK INSERT, INSERT... SELECT and SELECT INTO (last two ones over a
linked server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <jhenk17@.yahoo.com> wrote in message
news:1142610016.021081.56310@.j52g2000cwj.googlegroups.com...
> We're running production on an SQL 2000 server but still use and old
> 6.5 server for test purposes.
> Is there a way to copy data from a table on the 200 server to the same
> table on a 6.5 server?
> Thanks!!!
>

Data From SQL 2000 to SQL 6.5

We're running production on an SQL 2000 server but still use and old
6.5 server for test purposes.
Is there a way to copy data from a table on the 200 server to the same
table on a 6.5 server?
Thanks!!!
Some options are: DTS, BCP, BULK INSERT, INSERT... SELECT and SELECT INTO (last two ones over a
linked server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <jhenk17@.yahoo.com> wrote in message
news:1142610016.021081.56310@.j52g2000cwj.googlegro ups.com...
> We're running production on an SQL 2000 server but still use and old
> 6.5 server for test purposes.
> Is there a way to copy data from a table on the 200 server to the same
> table on a 6.5 server?
> Thanks!!!
>

Data From SQL 2000 to SQL 6.5

We're running production on an SQL 2000 server but still use and old
6.5 server for test purposes.
Is there a way to copy data from a table on the 200 server to the same
table on a 6.5 server?
Thanks!!!Some options are: DTS, BCP, BULK INSERT, INSERT... SELECT and SELECT INTO (
last two ones over a
linked server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <jhenk17@.yahoo.com> wrote in message
news:1142610016.021081.56310@.j52g2000cwj.googlegroups.com...
> We're running production on an SQL 2000 server but still use and old
> 6.5 server for test purposes.
> Is there a way to copy data from a table on the 200 server to the same
> table on a 6.5 server?
> Thanks!!!
>

Tuesday, February 14, 2012

Data flow task reports different row count than actual rowcount

I have a data flow task that moves all the rows from 18 tables on a production server to a reporting services server. One table, which does not contain the most rows (about 650K rows) reports all the rows have been transferred. However, if I go in to the SQL Mgmt Studio and do a Select count(*) on the table, there are only 110k rows.

Has anyone else experienced this problem?

Thanks,

Nick Anzano

How does the data flow report that 650k rows have been transferred? Are you looking in the logs?

SSIS could certainly report that it has sent 650k to the database - what happens then is up to the database! Is the target database SQL Server? If so, use SQL Profiler to see what is happening when the rows are being sent to the server. Perhaps they are not being committed for some reason.

Donald Farmer

Thursday, March 22, 2012

Data Retrieval

Monday, March 19, 2012

Data problem when publishing website

Data or index corruption?

Data or index corruption?

Data or index corruption?

Sunday, March 11, 2012

Data not displaying (in DataList) from SQL on GoDaddy

Wednesday, March 7, 2012

Data Migration Recommendations Needed

Data Migration Recommendations Needed

Data Migration Recommendations Needed

Friday, February 17, 2012

data import

Data handling in a production environment

Data handling in a production environment

Data handling in a production environment

Data From SQL 2000 to SQL 6.5

Data From SQL 2000 to SQL 6.5

Data From SQL 2000 to SQL 6.5

Tuesday, February 14, 2012

Data flow task reports different row count than actual rowcount

Data transformation services

Blog Archive

About Me