Clarification On Variables!
Apr 1, 2008
I have a question , because I€™m not very experienced with VB.
The function declares
Public myString
Public myKey
When something is declared this way, what happens if two or more users happen to execute the report at the same time? Is myString and myKey public to all of those reports at the same time, or does the Report Server manage those variables separately for each of the reports running concurrently?
Any clarification on this......................?
thanks
View 7 Replies
ADVERTISEMENT
Jan 20, 2000
Hi friends,
How r u?..i have one clarification..Curently, i am doing one search engine stuff...for that, i have to populate data from sql server database to flat file..i got java script search engine code from one web-site,in that code,data's are fetching from flat-file only..so i have populate the data from sql server to flat-file, and then get the key-word value from that flat-file according to the user input..Is it possible?..send mail..
Bye from
Jan Reddy
View 1 Replies
View Related
Jan 22, 2008
I just finished getting a local database mirrored to an offsite "DR" server. Interesting experience getting that working, but that's another story! It's set up as "High Safety without automatic failover (synchronous) (without a witness). I was hoping I could get a sanity check in this flow....
Local database gets updated each morning with a few meg of data, so it's pretty low use. It's mirrored through a T1 (soon to be dual). My remove copy was restored from yesterday€™s backup, and the mirror was sync'd in less than 10 minutes, so I don't expect bandwidth to be an issue...
In the event that my local server or network is down, I'd be able to log into my remote server, and run my application remotely. From what I've seen, the "Failover" option is only available on the "Primary" server? Will this become an option if the remote no longer sees the primary, or do I need to do this through the command: ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS?
Since the updates are so limited, I'm not too concerned with data loss, but will need to make sure I can get this running quickly if needed...
As I understand it, this will put the mirrored database as the primary, and when the old primary comes back only, it'll become the new mirror? I can then let them sync, and force a failover from the remote system to put the primary back to the local network.
I just want to make sure I understand this correctly before I start testing. Thanks for any assistance!
Steve
View 10 Replies
View Related
Jan 19, 2006
I want to create a DataList that shows products, which will be on
multiple pages. I have my stored proc to show paged results, which
contains a return value for more records.
I have found examples of coding the DataReader, defining all the
parameters etc, but what about the drag and drop SqlDataSource?? You
can select the DataSource Mode to be "DataReader". I can put select
parameters in, with input and my return value. I don't know how to then
access the return value, or output value if needed, from this? My
DataList references the SqlDataSource, but I don't know how to get the
return/output value out??? This is very frustrating, cause I can't find
any info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
DataSourceMode="DataReader" ConnectionString="<%$
ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Page" QueryStringField="page" />
<asp:Parameter Name="RecsPerPage" DefaultValue="10" />
<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"
/>
<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"
/>
</SelectParameters>
</asp:SqlDataSource>
If I take out the RETURN_VALUE Parameter, my results display in my data
list, but that's useless if I can't access the return value to
determine the remaining number of pages etc. Is my RETURN_VALUE
parameter wrong? How do I access that? My stored proc is shown below...
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int,
@CategoryID int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
No varchar(100),
Name varchar(100),
SDescription varchar(500),
Size varchar(10),
ImageURL varchar(100)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@CategoryID
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
View 3 Replies
View Related
Jan 15, 2008
mine is a small organisation and we have deployed an ERP on windows 2003 server. their are total 60 ERP users and we have 25 concurrent licences for ERP.
at the backend we are running MS SQL 2000 standard edition. we have created one user in Database and ERP accesses data through this user only.
now i need to understand how many CAL for database i need to purchase. either 60 or 25 or 1.
thanks
View 1 Replies
View Related
Jul 23, 2007
I hope someone can help here. I found that I was having replication failing due to the fact that there is a foreign key being referrenced, here is the error:
Could not drop object 'dbo.tblName' because it is referenced by a FOREIGN KEY constraint.
(Source: Database Name (Data source); Error number: 3726)
In searching for some sort of answer - I came across this:
Join Filters
Foreign key relationships are central to most database designs. For example, in a database that tracks product orders, you often see a CUSTOMER table with a CustID primary key and an ORDER table with a CustID foreign key that references the CUSTOMER table. In merge replication, if you filter the CUSTOMER table with a subset filter clause, you must also filter the ORDER table, so that only the rows that reference the filtered subset of rows from the CUSTOMER table are replicated. Filtering requirements that are based on a foreign key relationship must be explicitly represented in the merge replication configuration through a join filter (which lists the two related table articles and a logical expression that identifies the relationship), as shown in the following example:CUSTOMER.CustID = ORDER.CustIDThe effect of this join filter between the CUSTOMER table and the ORDER table, together with the subset filter
clause on the CUSTOMER table, is that Subscribers to the publication receive only those rows from the CUSTOMER
table where State = 'WA' and only those rows from the ORDER table that correspond to the CustID
values in the filtered CUSTOMER table.
________________________________________________________________________
Am I reading this right? If I have only one table that I need to replicate, I would need to included the other tables connected by
the foreign key for the replication to work, and in doing so - the table I designate for updating in this replication will be the only
one affected and the others that have go with this - won't update their corresponding dupe tables? Sorry - I hope this makes sense.
View 3 Replies
View Related
Feb 12, 2008
My goal is to populate a dropdownlist with only with users that are "Techs". I am using the membership database that you ccan set up through VWD. I added this column to the aspnet_Users table: IsTech as bit datatype. I thought I had the right SQL statement but apparently not, because I get an Invalid column name 'True'.Here is my statement: <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:HRIServiceConnectionString1 %>" SelectCommand="SELECT [UserID], [FirstName]+ ' ' + [LastName] AS techid FROM [aspnet_Users] WHERE [isTech] = True ORDER BY [LastName], [FirstName]"> </asp:SqlDataSource>
View 9 Replies
View Related
Oct 2, 2007
The distinction between Entity and Relationship is obvious to me. But I am confused by Table. Table describes Entity, Relationship, or both?
Thanks for any input.
View 9 Replies
View Related
Sep 15, 2007
http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx
The above link says the pros and cons of two SQL Server 2005 editions.
Document says Compact Edition is not good for €˜When you need a multi-user database server
€™.
What is the meaning of multi-user database?
Does this means the database strictly will not support two database connections at a time?
-Thank you,
Gish
View 1 Replies
View Related
Jun 2, 2006
Can come one help me how to disable an index using select query.
View 1 Replies
View Related
Nov 21, 2007
Hi,
I am working on a SQL Server database that will be accessed (mainly) via a web interface that will have users from various countries. As I understand it, the best way to store data is to use 'n' (nvarchar etc) datatypes for columns, which allow users to enter data and it will be stored in the raw Unicode format. For other reasons the collation of the db has been changed (to Slovenian) but I understand that this will only affect how data is sorted. However, whenever I try to put certain data into the nvarchar field (Such as the £ symbol) the data get transferred to a letter L. Is that how it would be expected to work and if so am I missing something here - note I Know ent manager sometimes shows data incorrectly, but this happens when I add/view data from anywhere (i.e. end manager/ the website/ query analyser).
Any clarification would be much appreciated.
Kenny
View 1 Replies
View Related
Apr 10, 2008
Hi,
I'm seeing confusing results coming back from a query and I want to make sure my joins are working as expected.
I have 3 tables, tbl_family, tbl_familyPhone, and tbl_phone. tbl_FamilyPhone is a linking table between families and phones that specifies if it's the primary number.
tbl_Family tbl_FamilyPhone tbl_Phone
--------------- ------------------------- ----------------
pk_FamilyID pk_FamilyPhoneID pk_phoneID
fName fk_FamilyID fk_TypeID
lName fk_PhoneID ...
... ...
So a family has many familyPhones and a phone has many family phones. I'm trying to get all the families and their home phone only, if they have one. I don't want families to duplicate and I don't want any left out. Here's what I've got
Code Snippet
select [whatever]
from tbl_family
LEFT OUTER JOIN tbl_familyphone on tbl_family.pk_familyid = tbl_Familyphone.fk_familyid
inner JOIN tbl_phone on tbl_familyphone.fk_phoneID = tbl_phone.pk_phoneid and tbl_phone.fk_phonetypeid = 'E6F1688E-015B-481D-8C41-DCC1FEA5D5AB'
My thinking is the inner join between tbl_Phone and tbl_FamilyPhone will cause any FamilyPhone record without a phone record to be left out (fk_phonetypeid is the id of a home phone). Though some FamilyPhone records may be left out, I will not lose any families because it is left outer joined to familyphone. Is this right? Because if I just do select count(*) from families I get 4517 records, but when running the query above with the joins, I get 4383 records.
View 13 Replies
View Related
May 5, 2008
Does the Table Lock option in the OLE DB Destination task in a data flow refer to a lock on the destination table or on the source table from which the table is loading from?
The reason I ask is because I have a package run twice simultaneously pulling from the same server and table onto two different tables on two different servers. When I kick off my job to run the two data pulls, one of the jobs terminates with a
"TCP Provider: An existing connection was forcibly closed by the remote host." message. I believe this is due to a table lock on the server, but can't figure this out.
Thanks in advance for your help.
View 1 Replies
View Related
Jun 7, 2015
My environment looks like:
- Windows 2008R2 SP2 VM (VMware 6)
- SQL 2012 SP2 Std.
- NetApp iSCSI LUNs, Snapmanager for SQL
So I have created a test database and configured the logs to grow to max. 120MB in size.
To check the allocated and free space I right-clicked the database -> tasks -> shrink file
I see currently allocated: 5,00MB and 2,50MB free space for the MDF Database
I see currently allocated: 1,00MB and 0,63MB free space for the LDF Logfile
Next I use a data generator to fill the database with random data.
After adding around 500k rows I check the size again:
I see currently allocated: 17,00MB and 0,44MB free space for the MDF Database
I see currently allocated: 61,94MB and 0,27MB free space for the LDF Logfile
Next I take a full backup incl. truncating log files. After that I check the size again:
I see currently allocated:Â 17,00MB and 0,44MB free space for the MDF Database
I see currently allocated: 61,94MB and 57,24MB free space for the LDF Logfile
So now my question is where are those 56,97MB? I imagined they should now be added to the MDF file but they seem to be just gone. I did this procedure 2x more time and the MDF stays the same size while the LDF is almost empty after backups. Then I thought maybe its in the memory of the server so I rebooted it. But still the MDF has the same size... Is this normal? How it should work?
View 2 Replies
View Related
Mar 25, 2014
From BOL, I see these remarks with respect to the MODIFY FILE subcommand (my underline added):
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
Create a database
Add files to an existing database
Increase the size of an existing file
Restore a database or filegroup
Which leads me to believe that expanding the size of a datafile will also wipe out (my definition of 'initialize') any existing data within that file.
I may be misunderstanding 'initialize', because when I tested it out, I found this wasn't the case - my table data written to the file was still there after a resize.
Need to clarify to what degree I'd be taking a risk by increasing the file size on a datafile which already has data in it.
View 3 Replies
View Related
May 15, 2015
We're upgrading our SQL Server database from 2005 to 2012.I ran the Upgrade Advisory report and got this issue "Non-integer constants are not allowed in the ORDER BY clause in 90" because of the script below
SELECT
gp.BRAND+' <> '+gp.CATEGORY AS 'full name',
gp.PRODCODE,
gp.CATEGORY
FROM dbo.GFK_PRODUCT gp
ORDER BY
'full name'
I tried running the same query in our test SQL Server 2012 and it ran successfully. Now I'm confuse if i still need to change it.I google the issue a bit and came across this link and mentioned this.
1.) Non-integer constants are ... constants that are not integer number.
Example:Â 'string1'Â represents
a string constant
0x01Â represents
a varbinary constant
{ts
'2015-02-26 06:00:00'}Â represents a datetime constant
1.23Â represents
a numeric constants
2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier
delimiter:
SELECT ... expression AS 'Column1'
FROM ...
In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name."Do I still need to change the existing code even though it's working fine in 2012? If yes, it is because of best practice reason or it will total get deprecated/not working in the future version?
View 4 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Jul 13, 2007
What is @@variables means in sql server?
View 3 Replies
View Related
Jan 21, 2004
Is there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
View 1 Replies
View Related
Jun 4, 2004
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
View 7 Replies
View Related
Jun 8, 2005
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies
View Related
Feb 16, 2000
Hi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
View 1 Replies
View Related
Nov 2, 2000
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
View 2 Replies
View Related
Mar 28, 2003
Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim
View 2 Replies
View Related
Jul 26, 2000
I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.
View 2 Replies
View Related
Oct 31, 2005
Hey folks,
i'm trying to make a variable inside of a loop do this.
@a = 1
@b = Variable + @a
and then inside of the loop @a incriments by 1, so i'd get
variable1
variable2
...etc
but I can't get the variable to work right...
Any help would be great!
Caden
View 2 Replies
View Related
Apr 14, 2006
I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms.
I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?
In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL.
Would you please tell me how can i use a variable in MS SQL query?
Thank
Ebi
View 8 Replies
View Related
Jan 29, 2007
I hope this is the place for questions about DTS.
I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.
To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?
Is there some sort of tutorial out there that explains use of global variables?
Thanks in advance for any and all leads
View 9 Replies
View Related
Jun 5, 2007
Basically I am creating a wizard(using forms) within a database to import client files and reformat them into the format that we need.
I have a form that list all 26 column headings that I need and tied to each column headings is a combo box that that lists the column headings from the imported client file.
I need to create a query that either appends it to an already formatted file or creates a new table with the column headings.
The problem is the field headings from the imported file change.
This is the sql that I used that doesn't work:
INSERT INTO Formatted_Data ( Field X )
SELECT Imported_Data.Forms![Import Data-3]![Combo33]
FROM Imported_Data;
Because Imported_Data.Forms![Import Data-3]![Combo33] is not recognized. I think if I could create a variable to replace the Forms! part then it would work but I do not know how to do this,
Thanks in advance to anyon that can help
View 3 Replies
View Related
Nov 14, 2007
Hello,
I have a recordset that only has 1 record. I want to use this record to initialize the package variables. How do I do this? Or where do I do this?
View 2 Replies
View Related
Mar 14, 2007
Hi
I have a simple task flow which takes data from an external table and puts it into a SQL server table. In the SQL server table I have a column which flags the data source. I have set up a variable in the variable window to hold a constant which was the appropriate value for the column. I cannot see how to map the variable I have created to the column in my output table.
Not sure if variables do what I think they do, can anyone give me a simple overview, and point me in the right direction to add my constant into the output table.
Thanks in advance
ADG
View 1 Replies
View Related
Apr 25, 2007
Hi everyone,
Is the only way to get a SSIS variable to change values via a configuration file or table? I thought that at one point in developing some SSIS packages that if I changed the value of a variable in a script that value was then retained.
Thanks
View 7 Replies
View Related
